SQL Query Error with CASE Statement?

Question

I'm attempting to run this query using Simba's ODBC SFDC driver but the log shows me an error near the case statement. I'm not totally convinced its an error with the CASE statement but I don't see where my error is. Someone please help!!!!

 SELECT
   Account_Group__c,
   Hospital_Sales_Teammate__c,
   Name,
   StageName,
   CloseDate,
   Yr_Credited__c,
   Probability,
   Census__c,
   Credit__c,
   Related_VSA__c,
   AB_Hospital_Relationship_Type__c,

CASE
   WHEN Age_In_Stage__c >0 and Age_In_Stage__c <= 30 THEN '<30'
   WHEN Age_In_Stage__c >30 and Age_In_Stage__c <= 60 THEN '31-60'
   WHEN Age_In_Stage__c >60 and Age_In_Stage__c <= 90 THEN '61-90'
      ELSE '>90' END AS Age_Bucket,

CASE
   WHEN (Type = "Existing Business - Renewal" OR Type = 'Existing Business - Amendment')
      AND (Account_HHV_Segment__c='A' OR Account_HHV_Segment__c='B') 
      AND AB_Hospital_Relationship_Type__c<>'N/A' 
      AND (RecordType='012300000000PWuAAM'
         OR RecordType='01250000000DcJkAAK'
         OR RecordType='01250000000DpV4AAK'
         OR RecordType='01250000000Dxd7AAC'
         OR RecordType='01250000000DoFPAA0'
         OR RecordType='01250000000DuuEAAS') THEN 'Hosp'
   WHEN Name LIKE '%AB Hospital Loss%' THEN 'Hosp'
         ELSE '' END AS Hospital_Eligible,

CASE
   WHEN RecordType='01250000000DpV4AAK'
      AND Type LIKE '%Acquisition%'
   THEN 'Acq'
         ELSE '' END AS Acquisition_Eligible,

CASE
   WHEN RecordType='01250000000Dxd7AAC'
      AND   (Business_Unit__c="Full Conversion" OR Business_Unit__c="Partial Conversion")
   THEN 'BGC'
         ELSE '' END AS Conversion_Eligible,

CASE
   WHEN RecordType='01250000000DuuEAAS'
      AND Type_of_Agreement__c  ="MDA" OR Type_of_Agreement__c  ="Joinder" OR Type_of_Agreement__c  ="JV"
   THEN 'Incr Doc'
         ELSE '' END AS Incr_Doc_Eligible

FROM
Opportunity

WHERE
    Eligible__c<>'No' 
AND NOT Name LIKE '%test%' 
AND NOT Name LIKE '%Test%' 
AND NOT Name LIKE '%TEST%'

ORDER BY 
Account_Group__c ASC

score 1 · Answer 1 · answered Mar 10 '17 at 14:42

1

Business_Unit__c="Full Conversion" (and other places as well): You are using double quotes instead of single quotes (as you do in the rest of the query). I bet that's the problem...

Also, this is a case expression, not a statement.

answered Mar 10 '17 at 14:42

HoneyBadger

14,750
3
34
48

Good point. I've removed the double quotes and its still placing the error at the first case expression. The issue with Simba is that it doesn't tell you EXACTLY where or what the error is--just that there's an error in the general vicinity. Thoughts? – sqlbg Mar 10 '17 at 14:59
Unless `Age_In_Stage__c ` is not an `integer`, I don't see any other error, just now. I would suggest removing case expressions, until you don't have the error anymore. Than you'll know where the error is, maybe than you can find _what_ the error is – HoneyBadger Mar 10 '17 at 15:06

score 0 · Answer 2 · answered Mar 10 '17 at 14:43

0

Why are you using double quotes?

(Type = "Existing Business - Renewal" OR Type = 'Existing Business - Amendment')

You should change it to

 (Type = 'Existing Business - Renewal' OR Type = 'Existing Business - Amendment')

answered Mar 10 '17 at 14:43

Cookie Monster

475
6
12

I've removed the double quotes and its still placing the error at the first case expression. The issue with Simba is that it doesn't tell you EXACTLY where or what the error is--just that there's an error in the general vicinity. – sqlbg Mar 10 '17 at 15:04

SQL Query Error with CASE Statement?

2 Answers2