When I run a case logic statement through the MS SQL Query everything checks out. However, when I use the logic in SQL Passthrough I get the NASTY MESSAGE noted below:
SET SQLENGINE=SQLMSS
SQL SQLMSS
Select
[Sales Rep Rep Code] AS REPCODE
, [Sales Rep Last Name] AS LAST_NAME
, [Sales Rep First Name] AS FIRST_NAME
, DBA
, ReferralDate AS REFERRALDATE
, StatusDate AS STATUSDATE
, [Current Status] AS CURRENT_STATUS
, ReferralID AS REFERRALID
, PROBABILITY = Case when [Current Status] in ('Approved','Credit Review')
then 'HIGH'
when [Current Status] in
('Dead Lead','Declined','Do Not Call- DNS List Verified', 'Duplicate Lead')
then 'LOW'
else 'MEDIUM' end
, Sum(GrossSales) AS GROSS_SALES
, Sum(AnnualCardVolume) AS ANNUAL_VOLUME
from dbo.tblReferralData
Where ReferralDate between '2006/04/01' and '2006/04/30'
and PROBABILITY = 'HIGH'
Group by
[Sales Rep Rep Code] AS REPCODE
, [Sales Rep Last Name] AS LAST_NAME
, [Sales Rep First Name] AS FIRST_NAME
, DBA
, ReferralDate AS REFERRALDATE
, StatusDate AS STATUSDATE
, [Current Status] AS CURRENT_STATUS
, ReferralID AS REFERRALID
Order by StatusDate DESC
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOLDA FORMAT FOCUS INDEX REPCODE
END
-RUN
-* NOTE THE NASTY MESSAGE
(FOC1400) SQLCODE IS 207 (HEX: 000000CF) XOPEN: 42S22
: Microsoft OLE DB Provider for SQL Server: [42S22] Invalid column name 'P
: ROBABILITY'. [42S22] Invalid column name 'PROBABILITY'. [42000] Statemen
: t(s) could not be prepared. [] Deferred prepare could not be completed.
L (FOC1405) SQL PREPARE ERROR.

Can you reference PROBABILITY in the where statement? In Oracle SQL it always gives me problems when I do that.

Shouldn't your where look like this:

Where ReferralDate between '2006/04/01' and '2006/04/30'
and [Current Status] in ('Approved','Credit Review')

Thanks - should have thought of that but I still wish I could get an explanation on the SQL error.

I normally don't create/test my SQL in dev studio. I use a tool like SQL Plus or TOAD.

Hi Sandy,

Try this syntax for case logic in MSSSQL via WF -

Case when [Current Status] in ('Approved','Credit Review')
then 'HIGH'
when [Current Status] in
('Dead Lead','Declined','Do Not Call- DNS List Verified', 'Duplicate Lead')
then 'LOW'
else 'MEDIUM' end as PROBABILITY

T