Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajadadi
Starting Member
30 Posts |
 Posted - 2010-04-26 : 06:06:14
|
| change this query using 'DECODE'SELECT Distinct AST.Configuration_Item_ID AS [Configuration item ID] , AST.External_System_Number AS [External system number] , STA.Value AS [CI Status] , AST.Status_Reason AS [Status Reason] , AST.Impact AS [Impact] , AST.Urgency AS [Urgency] , AST.On_Loan AS [On Loan] , AST.Quantity AS [Quantity] , AST.Quantity_Used AS [Quantity used] , AST.Quantity_Remaining AS [Quantity remaining] , AST.Tag_Number AS [Tag Number] , AST.Serial_Number AS [Serial Number] , AST.Name AS [CI Name] , AST.System_Roles AS [CI Class] , AST.System_Type AS [CI Type] , AST.Configuration_Item_Role AS [CI Role] , AST.Description AS [Description] , AST.Environment AS [Environment] , BCI.Value AS [Baseline CI] , BUN.Value AS [Bundle] , AST.Company AS [Company] , AST.Product_Categorization_Tier_1 AS [Product Categorization Tier 1] , AST.Product_Categorization_Tier_2 AS [Product Categorization Tier 2] , AST.Product_Categorization_Tier_3 AS [Product Categorization Tier 3] , AST.Product_Name AS [Product Name] , AST.Product_Model_Version AS [Product Model] , AST.Manufacturer AS [Manufacturer] , AST.Specific_SLA_Criteria AS [Specific SLA criteria] , AST.Company AS [Location Company] , AST.Region AS [Region] , AST.Site_Group AS [Site group] , AST.Site AS [Site] , AST.Site_Zone AS [Site Zone] , AST.Additional_Location_Details AS [Additional Location Details] , D.Association_type01 AS [Contact association type] , D.Request_description01 AS [Contact name] , D.Request_ID01 AS [Contact ID] , D.Request_type01 AS [Contact type] , dbo.cgi_fn_ConvertDateTime(AST.Request_Date, 120) AS [Requisition date] , dbo.cgi_fn_ConvertDateTime(AST.PO_Date, 120) AS [Purchase date] , dbo.cgi_fn_ConvertDateTime(AST.Acquisition_Date, 120) AS [Acquisition date] , dbo.cgi_fn_ConvertDateTime(AST.Submit_Date, 120) AS [CMDB Submit date] , dbo.cgi_fn_ConvertDateTime(AST.Configuration_Date, 120) AS [Configuration date] , dbo.cgi_fn_ConvertDateTime(AST.Inventory_Date, 120) AS [Inventory date] , dbo.cgi_fn_ConvertDateTime(AST.In_Service_Date, 120) AS [In Service date] , dbo.cgi_fn_ConvertDateTime(AST.Out_of_Service_Date, 120) AS [Out of Service date] , dbo.cgi_fn_ConvertDateTime(AST.Retired_Date, 120) AS [Retired date] , dbo.cgi_fn_ConvertDateTime(AST.Disposal_Date, 120) AS [Disposed date] , dbo.cgi_fn_ConvertDateTime(AST.Last_Audit_Date, 120) AS [Last Audit Date] , dbo.cgi_fn_ConvertDateTime(AST.Next_Audit_Date, 120) AS [Next Audit Date] , AST.Submitter AS [Submitter] , AST.Last_Modified_By AS [Last Modified By] , dbo.cgi_fn_ConvertDateTime(AST.Last_Modified_Date, 120) AS [Last modified date] FROM dbo.AST_Configuration_Item__CI_ AST (nolock) LEFT OUTER JOIN dbo.AST_CTM_Associations D (nolock) ON D.Request_ID02 = AST.Configuration_Item_ID LEFT OUTER JOIN dbo.ARSchema SST (NOLOCK) ON SST.Name = 'AST:Configuration Item (CI)' LEFT OUTER JOIN dbo.field_enum_values STA (NOLOCK) ON STA.schemaid = sst.schemaid AND STA.fieldid = 7 AND AST.CI_Status = STA.enumID LEFT OUTER JOIN dbo.field_enum_values BCI (NOLOCK) ON BCI.schemaid = sst.schemaid AND BCI.fieldid = 1000001592 AND AST.Baseline_CI = BCI.enumID LEFT OUTER JOIN dbo.field_enum_values BUN (NOLOCK) ON BUN.schemaid = sst.schemaid AND BUN.fieldid = 1000004090 AND AST.Bundle = BUN.enumID WHERE 1=1 AND AST.Company = 'CGI.CANRGM' AND AST.Product_Categorization_Tier_1 = 'Hardware' rajesh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-26 : 06:08:34
|
quote:
Originally posted by rajadadi
change this query using 'DECODE'
Can you be more specific on your question ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-26 : 06:20:00
|
| There is no DECODE in SQL Server. Use CASE instead. And when you ask questions, try and be a little more informative about your requirements.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-04-26 : 06:30:26
|
Or, do you want the query converted into Oracle SQL?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
|
|
change this query using 'DECODE'