I tried the solution you provided and this is the string it created.

SELECT * FROM #DLFiltered WHERE Active = 1 AND CurrentLevelXID = 10 AND 1=1 AND DownlineLevel...

I have tried both version and they work kinda of.

Update b

SET b.SponsorXID = a.XID

FROM [consultant].[Consultant] AS a

...

Not sure I follow?

I added it as a lookup and it works fine. But I would still like to know how I can right it as a nested expression if you could help.

I did that and it worked. Now I make that query the source for my SSIS package and I recieve the following error:

"ResponseData" cannot convert between unicode and non-unicode string...

I found that if I CAST it as a varchar then it returns data but then the next question how will that effect the data. This query is the basis...

That worked. Thanks a lot.

OK, I tried that example and recieved the following error:

sqlcmd -S hqtst102D\DBA -i ListTables.SQL database="Adventureworks"

Sqlcmd: 'database=': Invalid filename.

Then I tried it like this:

sqlcmd -S hqtst102D\DBA -i ListTables.SQL -v database="Adventureworks"

and recieved...

The only way I found that it might work is like this:

DECLARE @rc INT

SELECT @rc = Count(*) from Temp1

IF @rc =1 AND(Select PaymentTypeXID from...

Great info. The article really help.

I think I am working myself in to this:

Took the previous #Temp1 and filtered outthe top level kit:

Select * From #Temp1 t1

WHERE ParentLineItemID = 0

OrderGroupNumber OrderNumber LineItemId ...

I figured it out. It was

,'PaymentType' = Case

WHEN a.PaymentTypeXID IN (0,7,9,16) THEN A.[CCName] COLLATE SQL_Latin1_General_CP1_CI_AS

WHEN a.PaymentTypeXID IN (2) THEN 'Certficate'

END

Thanks

I found the differences;

FROMdbo.uvw_OrderGroupPayment AS A With (NoLock) -- this is a view

INNER JOIN dbo.OrderForm AS B With (NoLock) ON

A.ORderGroupNumber = B.OrderGroupNumber

LEFT OUTER JOIN SharedDimension.dbo.DimPaymentType AS C With (NoLock)...

I ended up doing a order by and reversing the direction.

I found the issue. It was the connection string was not set correctly in the pass thru query. It was set to different ODBC name on the other workstations.