I think this is perhaps closer to what you want, but I can't run it
because it references fields that are not there. A lot of what you did
may be valid (such as using double quotes) but I've not seen it used
that way so I just converted it to look in a way that I am more
comfortable with. Try it, and if you get errors like I am, it's because
the fields are not valid.
Also, if you have to do SQL much, you may want to investigate a tool
called SQLAssistant. I use it to help me mostly with automatically
typing in names of fields on SQL statements, but it also can be used to
help with the syntax.
SELECT
c.Company AS Agency,
CASE c.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,
c.[Name] AS InstanceName,
s.Site,
s.Street,
s.City,
s.State_Province AS State,
s.Zip_Postal_Code AS Zip,
s.Additional_Site_Details
FROM SIT_Site s
INNER JOIN AST_ComputerSystem c
ON s.Site = c.Building
WHERE c.assetlifecyclestatus <> 8
and c.datasetid = 'BMC.ASSET'
and c.classid IN ('BMC_COMPUTERSYSTEM', 'BMC_VIRTUALSYSTEM', 'BMC_LPAR')
AND c.item in ('Intel','UNIX')
order by c.company, c.AssetLifecycleStatus, c.[name];
Thanks,
Shawn Pierson
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Moore, Christopher Allen
Sent: Monday, June 02, 2008 8:02 PM
To: [email protected]
Subject: SQL help
**
I'm trying to do a query on 2 tables- SIT:Site and AST:ComputerSystem.
I'm a bit rusty on my SQL.
The error this gives is:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Can anyone help?
Thanks!
Chris
SELECT
"AST_ComputerSystem"."Company" AS 'Agency',
"AST_ComputerSystem"."assetlifecyclestatus" AS 'Status'=
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,
"AST_ComputerSystem"."Name" AS 'Instance Name',
"SIT_Site"."Site",
"SIT_Site"."Street" AS 'Street',
"SIT_Site"."City" AS 'City',
"SIT_Site"."State Province" AS 'State',
"SIT_Site"."Zip/Postal Code" AS 'Zip',
"SIT_Site"."Additional_Site_Details",
<snip: too long for the list>
FROM "SIT_Site" "SIT_Site" INNER JOIN "AST_ComputerSystem"
"AST_ComputerSystem" ON
"SIT_Site"."Site"="AST_ComputerSystem"."Building"
WHERE "AST_ComputerSystem"."assetlifecyclestatus" <> 8
and "AST_ComputerSystem"."datasetid" = 'BMC.ASSET'
and "AST_ComputerSystem"."classid" IN ('BMC_COMPUTERSYSTEM',
'BMC_VIRTUALSYSTEM', 'BMC_LPAR') AND "AST_ComputerSystem"."item" in
('Intel','UNIX') order by 1, 2, 3 ;
__Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
html___
Private and confidential as detailed here:
http://www.sug.com/disclaimers/default.htm#Mail . If you cannot access the
link, please e-mail sender.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
