Hey guys, thanks for the help! I'm really not a SQL pro by any stretch, so I'm working with 2 queries that I know work and trying to combine them. The first is really short:
SELECT "SIT_Site"."Site", "AST_ComputerSystem"."Name", "SIT_Site"."Additional_Site_Details" FROM "SIT_Site" "SIT_Site" INNER JOIN "AST_ComputerSystem" "AST_ComputerSystem" ON "SIT_Site"."Site"="AST_ComputerSystem"."Building" WHERE "SIT_Site"."Additional_Site_Details" LIKE '%Contact%' ORDER BY "SIT_Site"."Site" This query came from Crystal (though I added the WHERE clause). I have never done a join query, so we tried to do it in Crystal, but the report wouldn't run. The SQL did though, so we used that. We were now trying to basically combine that with another query that's too long to post, but here's the first bit. It runs on AST:ComputerSystem. SELECT Company 'Agency', 'Status' = CASE assetlifecyclestatus WHEN 0 THEN 'Ordered' WHEN 1 THEN 'Received' WHEN 2 THEN 'Being Assembled' WHEN 3 THEN 'Deployed' WHEN 4 THEN 'In Repair' WHEN 5 THEN 'Down' WHEN 6 THEN 'End of Life' WHEN 7 THEN 'Transferred' WHEN 8 THEN 'Delete' WHEN 9 THEN 'In Inventory' WHEN 10 THEN 'On Loan' WHEN 11 THEN 'Disposed' WHEN 12 THEN 'Reserved' WHEN 13 THEN 'Return to Vendor' END, Name 'Instance Name', 'Ownership Type' = CASE acquiredmethod WHEN 0 THEN 'Purchase' WHEN 1 THEN 'Lease' WHEN 2 THEN 'Off-Lease' WHEN 3 THEN 'Rented' WHEN 4 THEN 'Transferred' WHEN 5 THEN 'N/A' END, ClassId 'Asset Type', Region 'Location Type (Region)', Site 'Site Name', Floor 'Floor', Room 'Room / Grid', So Frederick that's where I got the "AST_ComputerSystem"."assetlifecyclestatus" AS 'Status'=. I was trying to emulate the format used in the Crystal join query in the longer query. SQL gave me an error on the '='. This was for a report which was needed this morning (kinda sprung on me at the last minute) so I stayed up late and manually combined the results from the two queries, but I would like to know what I did wrong for future reference! Thanks, Chris From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Tuesday, June 03, 2008 8:54 AM To: arslist@ARSLIST.ORG Subject: Re: SQL help Is it a typo in the email or do you have an = after the Status in the 3rd line? I believe it should be a comma there "AST_ComputerSystem"."assetlifecyclestatus" AS 'Status'= should be "AST_ComputerSystem"."assetlifecyclestatus" AS 'Status', Fred __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
