Convert ANSI-89 to Ansi-92 outer join

Last Post 02 Oct 2007 12:09 AM by SwePeso. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jcasement
New Member
New Member

--
01 Oct 2007 11:27 AM
I've been using this syntax for years on SQL Server and now comes the time to convert to SQL 2005 (90 compatibility). Does anyone know how to convert this to ANSI-92 or a better way to get row values into column values on SQL 2005?:

SET NOCOUNT ON

DECLARE @tblConfiguration table
( ServerName VARCHAR(255) NOT NULL,
Component VARCHAR(255) NOT NULL,
Context VARCHAR(255) NOT NULL,
Property VARCHAR(255) NOT NULL,
Value VARCHAR(255) NOT NULL
)


INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'Proc', 'Adjustment.@prcAdjExtractMFFiles' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'RunTime', '13:25' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'Schedule', '2,3,4,5,6' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'Ids', 'Extract' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'ReportClass', 'ReportFixed' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'FixedRecLength', '71' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'WriteFileHeader', 'Y' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'WriteTempTable', 'Y' )

INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'Proc', 'prcAdjValidations' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'ReportClass', 'ReportCSV' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'Ids', 'Validation' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'RunTime', '15:06' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'Schedule', '2,3,4,5,6' )
INSERT INTO @tblConfiguration VALUES ( 'SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'DefaultFileName', 'Adj_ValidationReport_MMDDYYHHMM.csv' )



select distinct
substring(t1.context,1,25) 'TaskName'
,substring(t4.value,1,6) 'StartTime'
--,(case when t20.insertDateTime is not null then convert(char(8),t20.insertDateTime,114) else '' end) 'EndTime'

, case when t12.value is not null and t14.value is not null then substring(t12.value + '/' + t14.value,1,15)
when t12.value is null and t14.value is not null then substring('00:00' + '/' + t14.value,1,15)
when t12.value is not null and t14.value is null then substring(t12.value + '/' + '23:59',1,15)
else '00:00/23:59'
end 'FromToRange'
, t3.value 'Class'
,case when t1.component like '%Outgoing%' then 'OutBound'
else 'Incoming'
end 'Direction'
,t5.Value 'Schedule'
, (case when t17.Value is not null then 'FTP=' + t17.Value + ' ' else '' end ) +
+ (case when t19.Value is not null then 'GPG=' + 'Y ' else '' end ) +
+ (case when t1.Value in ('prcDirectDeposit_PIN','prcDirectDeposit_MailConfirm') then t2.value + ' ' else '' end ) +
+ (case when t3.Value = 'RunProcedure' then t1.value + ' ' else '' end ) +
+ (case when t15.value is not null then substring(t15.value,1,8) + ' ' else '' end) +
+ (case when t16.value is not null then substring(t16.value,1,8) + ' ' else '' end)
-- + (case when t10.value is not null then substring(t10.value,1,10) else '' end)
'RptParameters'

, (case when t6.Value is not null then 'Upd=' + t6.Value + ' ' else '' end ) +
+ (case when t7.Value is not null then 'Cde=' + replace(t7.Value,'0','NA') + ' ' else '' end )
'StatusUsage'
, case when t17.Value = 'Y' then isnull(t21.value,' ')
else isnull(
SwePeso
New Member
New Member

--
02 Oct 2007 12:09 AM
DECLARE @tblConfiguration TABLE (ServerName VARCHAR(255) NOT NULL, Component VARCHAR(255) NOT NULL, Context VARCHAR(255) NOT NULL, Property VARCHAR(255) NOT NULL, Value VARCHAR(255) NOT NULL)

INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'Proc', 'Adjustment.@prcAdjExtractMFFiles')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'RunTime', '13:25')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'Schedule', '2,3,4,5,6')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'Ids', 'Extract' )
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'ReportClass', 'ReportFixed')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'FixedRecLength', '71')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'WriteFileHeader', 'Y')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ExtractAdjFile', 'WriteTempTable', 'Y')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'Proc', 'prcAdjValidations')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'ReportClass', 'ReportCSV')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'Ids', 'Validation')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'RunTime', '15:06')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'Schedule', '2,3,4,5,6')
INSERT @tblConfiguration VALUES ('SQLEDEV1', 'AdjProcessUtility', 'ValidationReport', 'DefaultFileName', 'Adj_ValidationReport_MMDDYYHHMM.csv')

SELECT DISTINCT SUBSTRING(t1.Context, 1, 25) AS TaskName,
SUBSTRING(t4.Value, 1, 6) AS StartTime,
CASE
WHEN t12.Value IS NOT NULL AND t14.Value IS NOT NULL THEN SUBSTRING(t12.Value + '/' + t14.Value, 1, 15)
WHEN t12.Value IS NULL AND t14.Value IS NOT NULL THEN SUBSTRING('00:00/' + t14.Value, 1, 15)
WHEN t12.Value IS NOT NULL AND t14.Value IS NULL THEN SUBSTRING(t12.Value + '/23:59', 1, 15)
ELSE '00:00/23:59'
END AS FromToRange,
t3.Value AS Class,
CASE
WHEN t1.Component LIKE '%Outgoing%' THEN 'Outbound'
ELSE 'Incoming'
END AS Direction,
t5.Value AS Schedule,
CASE
WHEN t17.Value IS NOT NULL THEN 'FTP=' + t17.Value + ' '
ELSE ''
END
+ CASE
WHEN t19.Value IS NOT NULL THEN 'GPG=Y '
ELSE ''
END
+ CASE
WHEN t1.Value IN ('prcDirectDeposit_PIN', 'prcDirectDeposit_MailConfirm') THEN t2.Value + ' '
ELSE ''
END
+ CASE
WHEN t3.Value = 'RunProcedure' THEN t1.Value + ' '
ELSE ''
END
+ CASE
WHEN t15.Value IS NOT NULL THEN SUBSTRING(t15.Value, 1, 8) + ' '
ELSE ''
END
+ CASE
WHEN t16.Value IS NOT NULL THEN SUBSTRING(t16.Value, 1, 8) + ' '
ELSE ''
END AS RptParameters,
CASE
WHEN t6.Value IS NOT NULL THEN 'Upd=' + t6.Value + ' '
ELSE ''
END
+ CASE
WHEN t7.Value IS NOT NULL THEN 'Cde=' + REPLACE(t7.Value, '0', 'NA') + ' '
ELSE ''
END AS StatusUsage,
CASE
WHEN t17.Value = 'Y' THEN ISNULL(t21.Value, ' ')
ELSE ISNULL(t11.Value, ' ')
END AS MailTo,
CASE
WHEN t17.Value = 'Y' THEN ISNULL(t22.Value, ' ')
ELSE ISNULL(t23.Value,' ')
end AS MailCc,
CASE
WHEN t9.Value IS NOT NULL AND t1.Component LIKE '%Outgoing%' THEN t9.Value
ELSE ISNULL(t8.Value, ' ')
END
+ CASE
WHEN t9.Value IS NOT NULL AND t1.Component LIKE '%Incoming%' THEN t9.Value
ELSE ''
END AS FileName
FROM @tblConfiguration AS t1
INNER JOIN @tblConfiguration AS t2 ON t2.Servername = t1.Servern
SwePeso
New Member
New Member

--
02 Oct 2007 12:13 AM
It seems to me you are doing some obscure CROSSTAB or PIVOT report.

Try to use this syntax for more effective query!


SELECT MAX(CASE WHEN ... ELSE ... END) AS Col1,
MAX(CASE WHEN ... ELSE ... END) AS Col2,
MAX(CASE WHEN ... ELSE ... END) AS Col3,
MAX(CASE WHEN ... ELSE ... END) AS Col4,
MAX(CASE WHEN ... ELSE ... END) AS Col5
FROM @tblConfiguration
GROUP BY ServerName,
Component,
Context
ORDER BY ServerName,
Component,
Context
You are not authorized to post a reply.

Acceptable Use Policy