May 01, 2006, 10:45 AM
S.J. KadishCase Logic in SQL Passthrough Problem
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. May 01, 2006, 11:01 AM
reFOCUSingCan 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')
May 01, 2006, 11:24 AM
S.J. KadishThanks - should have thought of that but I still wish I could get an explanation on the SQL error.
May 01, 2006, 11:48 AM
reFOCUSingI normally don't create/test my SQL in dev studio. I use a tool like SQL Plus or TOAD.
May 02, 2006, 03:49 AM
Tony AHi 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