sidlet


I've been using this syntax for years on SQL Server and now comes the time to convert to SQL 2005 (90 compatibility). This syntax returns four rows. Basically it returns one row for each servername/component/context/property/value even when there does not exist a property of 'fff' since it's a left join:

Code Block

select t1.* from tblconfiguration t1
,tblconfiguration t2
where t1.component = 'AdjProcessUtility'
and t1.servername *= t2.servername
and t1.component *= t2.component
and t1.context *= t2.context
and t1.property = 'proc'
and t2.property = 'fff'

Result:

SQLEDEV1 AdjProcessUtility DuplicatesReport Proc Adjustment.dbo.prcDuplicatesReport
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport Proc Adjustment.dbo.prcValidationReport

When the converted (using SQL enterprise Mgr) runs it returns no rows:

Code Block

SELECT t1.*
FROM dbo.tblConfiguration t1 LEFT OUTER JOIN
dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context
WHERE (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff')

I don't really see how to change this query to make it work. I've searched the web and I really don't see any examples of left joins which use more than one column.

Here's the table definition:

Code Block

CREATE TABLE dbo.tblConfiguration
(
ServerName VARCHAR(30) NOT NULL,
Component VARCHAR(255) NOT NULL,
Context VARCHAR(255) NOT NULL,
Property VARCHAR(255) NOT NULL,
CONSTRAINT PK_tblConfiguration PRIMARY KEY NONCLUSTERED( ServerName, Component, Context, Property ),
Value VARCHAR(255) NOT NULL
)

I use this table to define reports and there attribues. The rows repeat themselves except for the Property and Value columns

Here is some of the data:

SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles

SQLEDEV1 AdjProcessUtility ExtractAdjFile RunTime 13:25
SQLEDEV1 AdjProcessUtility ExtractAdjFile Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ExtractAdjFile FixedRecLength 71
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteFileHeader Y
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteTempTable Y

SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations

SQLEDEV1 AdjProcessUtility ValidationReport ReportClass ReportCSV
SQLEDEV1 AdjProcessUtility ValidationReport Ids Validation
SQLEDEV1 AdjProcessUtility ValidationReport RunTime 15:06
SQLEDEV1 AdjProcessUtility ValidationReport Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ValidationReport DefaultFileName Adj_ValidationReport_MMDDYYHHMM.csv

etc.

Any help is greatly appreciated,

Sid




Re: Convert ANsi-89 to Ansi-92 outer join

Madhu K Nair


SELECT t1.*
FROM dbo.tblConfiguration t1 LEFT OUTER JOIN
dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context
WHERE (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff')

Replace WHERE with AND

Try this

SELECT t1.*
FROM dbo.tblConfiguration t1 LEFT OUTER JOIN
dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context
AND (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff')

Madhu







Re: Convert ANsi-89 to Ansi-92 outer join

Arnie Rowland

Often it is necessary to filter on the table level before the JOIN.

So in the situation above, try moving the WHERE clause filtering into the JOIN...ON statement.

Code Block

SELECT

t1.{ColumnList}
FROM dbo.tblConfiguration t1

LEFT OUTER JOIN dbo.tblConfiguration t2

ON ( t1.ServerName = t2.ServerName

AND t1.Component = t2.Component

AND t1.Context = t2.Context

AND t1.Component = 'AdjProcessUtility'

AND t1.Property = 'proc'

AND t2.Property = 'fff'

)

It is a good practice to specify the columns desired and NOT rely upon the 'SELECT *' kludge.





Re: Convert ANsi-89 to Ansi-92 outer join

sidlet

Arnie,

Doesn't work as expected. The fixed code as shown gives me every row in the table. Not what's wanted. Let me try to explain what is in this table and the results you get when the ANSI-89 version runs:

  • The table has repeating values in the first two columns: Servername and Component. In my case the ANSI-89 results looks like this:

SQLEDEV1 AdjProcessUtility DuplicatesReport Proc Adjustment.dbo.prcDuplicatesReport
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport Proc Adjustment.dbo.prcValidationReport

with servername "SQLEDEV1" repeated and "AdjProcessUtility" over and over again.

The third thru six columns have the "context" (aka report name I'm configuring), the "property" (Proc aka stored procedure) and finally the ''value' column which indicates the actual stored procedure name.

If you can imagine that this table has many rows for each "context" so that I can drive a report dynamically. I specify properties like "Runtime", "WriteHeader" etc. for each context. But in some cases I don't have all the same properties across each context and that's why I use the left join.

What I expect and get from the ANSI-89 query is one row per context.

Any ideas

Sid





Re: Convert ANsi-89 to Ansi-92 outer join

Arnie Rowland

OK, now I better understand what you are trying to accomplish.

(In the future, if you prepare your sample data similar to the example below, you 'may' get more folks involved with helping you solve your problem. When it takes time to 'set up' the problem, few folks will help out.)

Perhaps the following will move you into a direction you need. (Using your DDL and sample data ...)

Code Block

SET NOCOUNT ON

DECLARE @tblConfiguration table
( ServerName VARCHAR(15) NOT NULL,
Component VARCHAR(20) NOT NULL,
Context VARCHAR(20) NOT NULL,
Property VARCHAR(20) NOT NULL,
Value VARCHAR(50) 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', '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', 'ValidationProcess', '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
t1.ServerName,
t1.Component,
t1.Context,
t1.Property,
t1.Value
FROM @tblConfiguration t1
JOIN (SELECT
ServerName,
Component,
Context
FROM @tblConfiguration
WHERE Property = 'Proc'
) t2
ON ( t1.ServerName = t2.ServerName
AND t1.Component = t2.Component
AND t1.Context = t2.Context
)
ORDER BY
t1.ServerName,
t1.Component,
t1.Context

ServerName Component Context Property Value
---------- ----------------- ----------------- --------------- --------------------------
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.@prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ExtractAdjFile RunTime 13:25
SQLEDEV1 AdjProcessUtility ExtractAdjFile Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ExtractAdjFile FixedRecLength 71
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteFileHeader Y
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteTempTable Y
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations







Re: Convert ANsi-89 to Ansi-92 outer join

sidlet

Code Block

Arnie,

Thanks for taking the time to set up the sample (I'll do this in the future) but I still see a problem. If you run the code below which is my ANSI-89 code for the sample data you inserted you'll see the difference:


Code Block
select t1.* from @tblConfiguration t1
,@tblConfiguration t2
where t1.servername *= t2.servername
and t1.component *= t2.component
and t1.context *= t2.context
and t1.property = 'proc'
and t2.property = 'this property does not exist'

Results:

SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.@prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations

What my ANSI-89 code returns is two rows. One for each of the of the context even though the t2.property does not exist. Maybe I've looked at this too long but I know that this must be something that others have hit when they have moved to SQL 2005 and have more than one column in the left join criteria.

Do you see the difference in the result Maybe I'm looking at this completely the wrong way. What I'm trying to do is take the many rows which of each context (think report attributes) and get them onto a single line. I've looked at Pivot but that seems to only be for aggregating fields. I've looks at CTE but that doesn't fit this scenario either so my aim was to just to get this working without re-writing the whole thing.

Any further thoughts

Thanks again,

Sid





Re: Convert ANsi-89 to Ansi-92 outer join

Arnie Rowland

Maybe like you, I can't see straight today -it happens.

This returns the same as your query above, albeit a bit simpler...

Code Block
SELECT
ServerName,
Component,
Context,
Property,
Value
FROM @tblConfiguration
WHERE Property = 'Proc'






Re: Convert ANsi-89 to Ansi-92 outer join

sidlet

Arnie,

Sorry but you've missed the whole point. I'll try somewhere else.

Sid





Re: Convert ANsi-89 to Ansi-92 outer join

Arnie Rowland

I'm still not sure what you are really attempting to accomplish.

The last code example I provided returns the exact same resultset as your ANSI-89 JOIN. Perhaps the resultset you provided wasn't really what you desired.

If you are attempting to return the additional columns from the Properties list, using PIVOT is the way to go.

BUT your example DOES NOT demonstrate that is your intention.

So perhaps this is a good moment to 'start over', carefully laying out a clear explanition of your desired results -including a complete display of the desired resultset, and also supply the table DDL and appropriate sample data.






Re: Convert ANsi-89 to Ansi-92 outer join

sidlet

Arnie,

Here's the whole SQL details. I tried to keep it simple but here goes:

Code Block

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(t11.value,' ')
end 'MailTo'
, case when t17.Value = 'Y' then isnull(t22.value,' ')
else isnull(t23.value,' ')
end '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 )
'FileName'
--, case when charindex(',',t7.value) > 0 then substring(t7.value,1,charindex(',',t7.value) -1)
-- else substring(t7.value,1,50)
-- end 'MailCc'
--,t7.value 'extended MailCc'
from @tblConfiguration t1
, @tblConfiguration t2
, @tblConfiguration t3
, @tblConfiguration t4
, @tblConfiguration t5
, @tblConfiguration t6
, @tblConfiguration t7
, @tblConfiguration t8
, @tblConfiguration t9
, @tblConfiguration t11
, @tblConfiguration t12
, @tblConfiguration t14
, @tblConfiguration t15
, @tblConfiguration t16
, @tblConfiguration t17
, @tblConfiguration t19
, @tblConfiguration t21
, @tblConfiguration t22
, @tblConfiguration t23
--, dbo.tblJobAuditTrail t20
where t1.component in ('AdjProcessUtility')
and t1.servername = 'SQLEDEV1'
and t1.servername = t2.servername
and t1.component = t2.component
and t1.context = t2.context

and t1.property = 'Proc'
and t2.property = 'Ids'
and t3.property in ('ReportClass')
and t4.property like 'runtime%'
and t5.property = 'Schedule'
and t6.property = 'UpdateStatus'
and t7.property = 'UpdateStatusCode'
and t8.property = 'DefaultFileName'
and t9.property = 'EncryptedFileName'
and t11.property like 'MailTo%'
and t12.property = 'StartTime'
and t14.property = 'EndTime'
and t15.property = 'StartDate'
and t16.property = 'EndDate'
and t17.property = 'FTP'
and t19.property = 'Encryption'
and t21.property = 'FTPNotify'
and t22.property = 'FTPCCNotify'
and t23.property = 'MailCc'

and t1.servername = t3.servername
and t1.component = t3.component
and t1.context = t3.context

and t1.servername *= t7.servername
and t1.component *= t7.component
and t1.context *= t7.context

and t1.servername = t4.servername
and t1.component = t4.component
and t1.context = t4.context

and t1.servername *= t5.servername
and t1.component *= t5.component
and t1.context *= t5.context
and t1.servername *= t6.servername
and t1.component *= t6.component
and t1.context *= t6.context
and t1.servername *= t8.servername
and t1.component *= t8.component
and t1.context *= t8.context
and t1.servername *= t9.servername
and t1.component *= t9.component
and t1.context *= t9.context

and t1.servername *= t11.servername
and t1.component *= t11.component
and t1.context *= t11.context
and t1.servername *= t12.servername
and t1.component *= t12.component
and t1.context *= t12.context
and t1.servername *= t14.servername
and t1.component *= t14.component
and t1.context *= t14.context
and t1.servername *= t15.servername
and t1.component *= t15.component
and t1.context *= t15.context
and t1.servername *= t16.servername
and t1.component *= t16.component
and t1.context *= t16.context
--and t1.servername *= t20.servername
--and t1.component *= t20.component
--and t1.context *= t20.context
--and t4.property *= t20.property
--and t4.value *= t20.value
and t1.servername *= t17.servername
and t1.component *= t17.component
and t1.context *= t17.context
and t1.servername *= t19.servername
and t1.component *= t19.component
and t1.context *= t19.context
and t1.servername *= t21.servername
and t1.component *= t21.component
and t1.context *= t21.context
and t1.servername *= t22.servername
and t1.component *= t22.component
and t1.context *= t22.context
and t1.servername *= t23.servername
and t1.component *= t23.component
and t1.context *= t23.context

--and convert(smalldatetime,convert(char(10),t20.insertDateTime,101)) = convert(smalldatetime,convert(char(10),getdate(),101))
order by --server,
starttime ,TaskName





Re: Convert ANsi-89 to Ansi-92 outer join

Arnie Rowland

OUCH!!!

Using the entire bit of code above, the resultset is empty.

It definitely looks like using PIVOT and a bit of client side code would be much easier.

(SQL Server is such a dog with string manipulation...)

I'll look it over in a bit and see if I can offer a more pointed suggestion.






Re: Convert ANsi-89 to Ansi-92 outer join

sidlet

Arnie,

I ran this on SQL 2000 and it works as desired with two rows in the result set.

Sid





Re: Convert ANsi-89 to Ansi-92 outer join

Arnie Rowland

Not for me.

Straight 'copy and paste' -yields an empty resultset.






Re: Convert ANsi-89 to Ansi-92 outer join

sidlet

Arnie,

Yes -- I have edited the SQL and fixed the @@servername. It must be hardcoded to 'SQLEDEV1' since that's how the inserts were coded. It worked on mine since that's my dev server.

Sorry,

Sid





Re: Convert ANsi-89 to Ansi-92 outer join

Tom Phillips

If you look closely at your code, you have an INNER join on T2, T3 and T4.

This is the proper translation of your joins:

.......

FROM @tblConfiguration t1
INNER JOIN @tblConfiguration t2 ON t2.property = 'Ids' and t1.servername = t2.servername and t1.component = t2.component and t1.context = t2.context
INNER JOIN @tblConfiguration t3 ON t3.property in ('ReportClass') and t1.servername = t3.servername and t1.component = t3.component and t1.context = t3.context
INNER JOIN @tblConfiguration t4 ON t4.property like 'runtime%' and t1.servername = t4.servername and t1.component = t4.component and t1.context = t4.context
LEFT OUTER JOIN @tblConfiguration t5 ON t5.property = 'Schedule' and t1.servername = t5.servername and t1.component = t5.component and t1.context = t5.context
LEFT OUTER JOIN @tblConfiguration t6 ON t6.property = 'UpdateStatus' and t1.servername = t6.servername and t1.component = t6.component and t1.context = t6.context
LEFT OUTER JOIN @tblConfiguration t7 ON t7.property = 'UpdateStatusCode' and t1.servername = t7.servername and t1.component = t7.component and t1.context = t7.context
LEFT OUTER JOIN @tblConfiguration t8 ON t8.property = 'DefaultFileName' and t1.servername = t8.servername and t1.component = t8.component and t1.context = t8.context
LEFT OUTER JOIN @tblConfiguration t9 ON t9.property = 'EncryptedFileName' and t1.servername = t9.servername and t1.component = t9.component and t1.context = t9.context
LEFT OUTER JOIN @tblConfiguration t11 ON t11.property like 'MailTo%' and t1.servername = t11.servername and t1.component = t11.component and t1.context = t11.context
LEFT OUTER JOIN @tblConfiguration t12 ON t12.property = 'StartTime' and t1.servername = t12.servername and t1.component = t12.component and t1.context = t12.context
LEFT OUTER JOIN @tblConfiguration t14 ON t14.property = 'EndTime' and t1.servername = t14.servername and t1.component = t14.component and t1.context = t14.context
LEFT OUTER JOIN @tblConfiguration t15 ON t15.property = 'StartDate' and t1.servername = t15.servername and t1.component = t15.component and t1.context = t15.context
LEFT OUTER JOIN @tblConfiguration t16 ON t16.property = 'EndDate' and t1.servername = t16.servername and t1.component = t16.component and t1.context = t16.context
LEFT OUTER JOIN @tblConfiguration t17 ON t17.property = 'FTP' and t1.servername = t17.servername and t1.component = t17.component and t1.context = t17.context
LEFT OUTER JOIN @tblConfiguration t19 ON t19.property = 'Encryption' and t1.servername = t19.servername and t1.component = t19.component and t1.context = t19.context
LEFT OUTER JOIN @tblConfiguration t21 ON t21.property = 'FTPNotify' and t1.servername = t21.servername and t1.component = t21.component and t1.context = t21.context
LEFT OUTER JOIN @tblConfiguration t22 ON t22.property = 'FTPCCNotify' and t1.servername = t22.servername and t1.component = t22.component and t1.context = t22.context
LEFT OUTER JOIN @tblConfiguration t23 ON t23.property = 'MailCc' and t1.servername = t23.servername and t1.component = t23.component and t1.context = t23.context

where t1.component in ('AdjProcessUtility')
and t1.servername = 'SQLEDEV1'
and t1.property = 'Proc'
order by --server,
starttime ,TaskName