function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Karthik RajashekaranKarthik Rajashekaran 

SOQL Query CASE CONDITION in WHERE CLAUSE

How can I have case condition in where clause 

I was trying to run below it says malformed 
  
Select columns 
 FROM table
  WHERE
  1 = CASE
        WHEN 'STD' = 'FULL' THEN 1
        WHEN 'STD' = 'STD'
              AND ((lastmodifieddate >= 2020-05-13T00:00:00.000Z AND lastmodifieddate <= 2020-05-13T23:59:59.000Z) 
                    OR (CreatedDate >= 2020-05-13T00:00:00.000Z AND CreatedDate <= 2020-05-13T23:59:59.000Z) 
                 ) THEN 1
        WHEN 'STD' = 'CSTM'
                AND ((lastmodifieddate >= 1970-01-01 00:00:00T00:00:00.000Z AND lastmodifieddate <= 1970-01-01 23:59:59T23:59:59.000Z)
                      OR (CreatedDate >= 1970-01-01T00:00:00.000Z AND CreatedDate <= 1970-01-01T23:59:59.000Z)
                    ) THEN 1
        ELSE 0
  END

 
Agustin BAgustin B
Hi Karthik there is no CASE function in SOQL like in SQL.
you could do something like 
Select columns 
 FROM table
  WHERE field = 'FULL' OR (field= 'STD' AND ((lastmodifieddate >= 2020-05-13T00:00:00.000Z AND lastmodifieddate <= 2020-05-13T23:59:59.000Z) 
                    OR (CreatedDate >= 2020-05-13T00:00:00.000Z AND CreatedDate <= 2020-05-13T23:59:59.000Z) 
                 ) ) OR (field = 'CSTM' AND ((lastmodifieddate >= 1970-01-01 00:00:00T00:00:00.000Z AND lastmodifieddate <= 1970-01-01 23:59:59T23:59:59.000Z)
                      OR (CreatedDate >= 1970-01-01T00:00:00.000Z AND CreatedDate <= 1970-01-01T23:59:59.000Z)
                    ) )
If you get something then is 1, and if not is 0.
This code may have mistakes like in the braces or the fields so verify and change to meet your requirements.

If it helps please mark as correct, it may help others.
Karthik RajashekaranKarthik Rajashekaran
You mentioned field = 'FULL' Thing is there is no column here for the "field" it is actually load_type which is a parameter passed, to determine if it is full or incremental the where clause is based on load_type if it is full load then fetch all rows, if it is STD then use that where clause condition, etc Regards, Karthik Rajashekaran
Agustin BAgustin B
Hi Karthik sorry but you cant do this with soql.
If you are able to check for a certain field of the object then you could use the example i wrote.
Maybe you could try to use subqueries to try to replicate your requirement.

Please mark as correct as the answer is that case is not a functionality available for soql.
Good luck.
Nalini ch 6Nalini ch 6
Hello 

It looks like our team of experts can help you resolve this ticket. We have Salesforce global help-desk support and you can log a case and our Customer Success Agents will help you solve this issue. You can also speak to them on live chat. Click on the below link to contact our help-desk. Trust me it is a support service that we are offering for free!

https://jbshelpdesk.secure.force.com

Thanks,
Jarvis SFDC team