Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Complex WHERE clause, if ... then ...

Hi,

I'm not really sure exactly how to describe what I'm asking, but this Pseudocode should make sense:

@TYPE varchar

SELECT *
FROM mytable
WHERE.....

If @TYPE = 'Red' then only return records where IS_RED = True
If @TYPE = 'Green' then only return records where IS_GREEN = True
If @TYPE = 'Blue' then only return records where IS_BLUE = True
If @TYPE = 'All' then return all records

Thanks!
0
tjrobinson
Asked:
tjrobinson
  • 4
  • 3
  • 2
2 Solutions
 
adatheladCommented:
SELECT * FROM MyTable
WHERE CASE @Type
    WHEN 'Red' THEN is_red
    WHEN 'Blue' THEN is_blue
    ELSE 1
END = 1
0
 
adatheladCommented:
I've assumed the Is_red, is_Green fields etc. are BIT fields, hence the " = 1"
0
 
Renante EnteraCommented:
Hi tjrobinson!

I think this is what you are looking for :

SELECT *
FROM mytable
WHERE
  CASE @TYPE
    when 'Red' then IS_RED = True
    when 'Green' then IS_GREEN = True
    when 'Blue' then IS_BLUE = True
    else 1=1
  END

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tjrobinsonAuthor Commented:
Hmm, they look right but I'm getting an error. Here's the full WHERE clause for the sproc.

WHERE
dbo.PS_PERSON.PS_A_APP_STATUS_CODE IS NOT NULL
AND
PS_AP_START_DATE_REQUIRED >= @PS_START_DATE_1
AND
PS_AP_START_DATE_REQUIRED < @PS_START_DATE_2
AND
PS_AP_GAP_YEAR_ID = @PS_AP_GAP_YEAR_ID
AND
 CASE @PS_TYPE      
    WHEN 'UK' THEN PS_IS_UK_APPLICANT = True
    WHEN 'OEV' THEN PS_IS_OEV_APPLICANT = True
    WHEN 'IV' THEN PS_IS_IV_APPLICANT = True
    ELSE 1 = 1
END

Incorrect syntax near '='
0
 
Renante EnteraCommented:
Hi tjrobinson!

If you run this where clause :

WHERE dbo.PS_PERSON.PS_A_APP_STATUS_CODE IS NOT NULL
AND PS_AP_START_DATE_REQUIRED >= @PS_START_DATE_1
AND PS_AP_START_DATE_REQUIRED < @PS_START_DATE_2
AND PS_AP_GAP_YEAR_ID = @PS_AP_GAP_YEAR_ID
AND PS_IS_UK_APPLICANT = True

Does it encounter any error ???  Let me know.


Regards!
eNTRANCE2002 :-)
0
 
adatheladCommented:
The solution entrance2002 posted is incorrect - if you see my post, I have slightly different syntax

Try:

WHERE dbo.PS_PERSON.PS_A_APP_STATUS_CODE IS NOT NULL
AND PS_AP_START_DATE_REQUIRED >= @PS_START_DATE_1
AND PS_AP_START_DATE_REQUIRED < @PS_START_DATE_2
AND PS_AP_GAP_YEAR_ID = @PS_AP_GAP_YEAR_ID
AND
 CASE @PS_TYPE    
    WHEN 'UK' THEN PS_IS_UK_APPLICANT
    WHEN 'OEV' THEN PS_IS_OEV_APPLICANT
    WHEN 'IV' THEN PS_IS_IV_APPLICANT
    ELSE 1
END = 1
0
 
adatheladCommented:
i.e. in each WHEN block, you should just supply the relevant field name and NOT include the " = True". This should go at the end of the CASE statement after the END keyword as above
0
 
Renante EnteraCommented:
Yah!  I agree with adathelad.  

In your case, I think the datatype of PS_IS_xxx_APPLICANT is a bit which has a value of 1, 0 or null.  Then the query provided by adathelad should work.

Actually, the one that I have provided shows the logic on how to accomplish what you want to achieve but the only problem is that we should confirm it from you about the datatype of the involved column.

If in case that it's not a bit but a string, so you can have your partial condition like this :

AND
 CASE @PS_TYPE    
    WHEN 'UK' THEN PS_IS_UK_APPLICANT = 'True'
    WHEN 'OEV' THEN PS_IS_OEV_APPLICANT = 'True'
    WHEN 'IV' THEN PS_IS_IV_APPLICANT = 'True'
    ELSE 1 = 1
END
0
 
tjrobinsonAuthor Commented:
Sorry, forgot to award the points!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now