Ok . In Crystal when you want to give something a new name you use the
NewName = Something syntax. In SQL you do Something AS NewName (and the
AS is optional).
So for the Crystal
'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,
it would be
CASE "AST_ComputerSystem"."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 AS 'Status',
Fred
________________________________
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Moore, Christopher Allen
Sent: Tuesday, June 03, 2008 9:28 AM
To: [email protected]
Subject: Re: SQL help
**
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: [email protected]
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_____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"
