I am trying to run a variable in an expression builder SQL command for OBDC.
The expression looks like this below
"SELECT Date(Curdate()) AS 'Load Date',
Sum(CASE
WHEN pomstatus = 'TBR'
AND Date(portdate) < Curdate() THEN 1
ELSE 0
END) AS 'Total Removes',
Min(CASE
WHEN pomstatus = 'TBR' THEN portdate
ELSE 'N/A'
END) AS 'Oldest Removes',
Sum(CASE
WHEN pomstatus = 'TBR'
AND Date(portdate) < Curdate()
AND Date(portdate) > Subdate(Curdate(), @[User::Days] )THEN 1
ELSE 0
END) AS 'Total New Removes',
Sum(CASE
WHEN pomstatus = 'TBR'
AND acctype = 'b'
AND Date(portdate) < Curdate() THEN 1
ELSE 0
END) AS 'SOHO Removes',
Sum(CASE
WHEN pomstatus = 'C-U'
AND Date(dd) < Curdate() THEN 1
ELSE 0
END) AS 'Total Confirmed U',
Min(CASE
WHEN pomstatus = 'C-U' THEN dd
ELSE 'N/A'
END) AS 'Oldest Confirmed U',
Sum(CASE
WHEN pomstatus = 'C-U'
AND discoservice LIKE '%s%'
AND Date(dd) < Curdate()THEN 1
ELSE 0
END) AS 'Total Confirmed Satellite',
Min(CASE
WHEN pomstatus = 'C-U'
AND discoservice LIKE '%s%' THEN dd
ELSE 'N/A'
END) AS 'Oldest Confirmed Satellite',
Sum(CASE
WHEN pomstatus = 'C-U'
AND Date(dd) < Curdate()
AND Date(dd) > Subdate(Curdate(), @[User::Days] ) THEN 1
ELSE 0
END) AS 'Total New Confirmed U',
Sum(CASE
WHEN pomstatus = 'pwo' THEN 1
ELSE 0
END) AS 'Total Pending WO',
Sum(CASE
WHEN pomstatus = 'etbr' THEN 1
ELSE 0
END) AS 'Total Escalated TBR',
Sum(CASE
WHEN pomstatus = 'wtnr' THEN 1
ELSE 0
END) AS 'Total Waiting TNR'
FROM POM.mainorders
WHERE acctype <> 'c';"
I get the below error:
Error popup
I tested the same script but replaced the 2 variables with the number 2 and it was successful, so I know the script itself is working. It looks to me like the variable isn't passing properly.
As for how I am populating the variable, the variable gets populated form an execute SQL Task imaged below:
Execute SQL task
Execute SQL Task
Query Builder Result
