Question OR/AND in SQL CASE Statement

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
So I have been struggling getting this sql statement to work. It works just fine in access. Yes, I converted it over to actual sql for the most part. I am having issues getting it working and have narrowed it down to my case statements. Here is one of the statements giving me the problem and I do not know how to make it work:

VB.NET:
CASE 
WHEN (c.CATS_SPENDER='PA' Or c.CATS_SPENDER='OA') And (c.CATS_PRIM='0' Or c.CATS_PRIM='1') THEN 1 
ELSE 0 
END

How do I use logical operators AND/OR in a case statement. This is the simplest one. I have one with a lot more or's. Please help
 
I don't really see a problem with those, but I guess that largely depends what result you are expecting. This should return 1 if c.CATS_SPENDER is 'PA' or 'OA' AND c.CATS_PRIM is '0' or '1'. In all other cases it will return 0.

What result are you expecting?
 
Actually, i thought it was that, but it isnt. Here is the full sql statement.

VB.NET:
                "SELECT e.EMPL_ID AS EMPL_ID,e.EMPL_SAP_ID AS SAP_ID,e.EMPL_LAST_NAME || ', ' || e.EMPL_FIRST_NAME || ' ' || e.EMPL_MIDDLE_INIT AS EMPLOYEE,c.CATS_PAYWK AS PAY_PERIOD,c.CATS_SPENDER AS COST_CENTER,c.CATS_PRIM AS LOCATION_CODE, CASE WHEN (c.CATS_BUDCTR='') THEN 'Contract' ELSE 'Overhead' END AS TYPE,CASE WHEN substr(c.CATS_GENORDER,1,3)='000' THEN '' ELSE c.CATS_GENORDER || c.CATS_TASK_ END AS TASK,Sum(c.CATS_RHRS+c.CATS_OHRS) AS HOURS,m.EMPL_LAST_NAME AS SUPERVISOR,s.SECT_DESC AS SECTION  " & _
                "FROM FSET.TFSETCATS c LEFT JOIN ((FSET.TFSETEMPL e LEFT JOIN FSET.TFSETEMPL m ON e.EMPL_EMPL_ID = m.EMPL_ID) LEFT JOIN FSET.TFSETSECT s ON m.EMPL_SECT = s.SECT_ID) ON c.CATS_EMPL_ID = e.EMPL_ID " & _
                "GROUP BY e.EMPL_ID,e.EMPL_SAP_ID,e.EMPL_LAST_NAME || ', ' || e.EMPL_FIRST_NAME || ' ' || e.EMPL_MIDDLE_INIT,c.CATS_PAYWK,c.CATS_SPENDER,c.CATS_PRIM,CASE WHEN c.CATS_BUDCTR='' THEN 'Contract' ELSE 'Overhead' END,CASE WHEN substr(c.CATS_GENORDER,1,3)='000' Then '' ELSE c.CATS_GENORDER || c.CATS_TASK_ END,m.EMPL_LAST_NAME,s.SECT_DESC,CASE WHEN (c.CATS_SPENDER='PA' Or c.CATS_SPENDER='OA') And (c.CATS_PRIM='0' Or c.CATS_PRIM='1') THEN 1 ELSE CASE WHEN (c.CATS_SPENDER='R5205' Or c.CATS_SPENDER='R5206' Or c.CATS_SPENDER='A3570' Or c.CATS_SPENDER='EK2' Or c.CATS_SPENDER='2C' Or c.CATS_SPENDER='H31' Or c.CATS_SPENDER='JZ' Or c.CATS_SPENDER='A37') And (c.CATS_PRIM='2' Or c.CATS_PRIM='3' Or c.CATS_PRIM='4' Or c.CATS_PRIM='5' Or c.CATS_PRIM='6' Or c.CATS_PRIM='7' c.CATS_PRIM='8' Or c.CATS_PRIM='9' Or c.CATS_PRIM='X' Or c.CATS_PRIM='Y' Or c.CATS_PRIM='Z') THEN 1 ELSE 0 END END " & _
                "WHERE (c.CATS_PAYWK= 25) AND (c.CATS_SPENDER='A3570' Or c.CATS_SPENDER='R5205' Or c.CATS_SPENDER='R5205' Or c.CATS_SPENDER='2C' Or c.CATS_SPENDER='A37' Or c.CATS_SPENDER='PA' Or c.CATS_SPENDER='OA' Or c.CATS_SPENDER='H31' Or c.CATS_SPENDER='BG' Or c.CATS_SPENDER='JZ' Or c.CATS_SPENDER='EK2'  Or c.CATS_SPENDER='SA') AND CASE WHEN (c.CATS_SPENDER='PA' Or c.CATS_SPENDER='OA') And (c.CATS_PRIM='0' Or c.CATS_PRIM='1') THEN 1 ELSE CASE WHEN (c.CATS_SPENDER='R5205' Or c.CATS_SPENDER='R5206' Or c.CATS_SPENDER='A3570' Or c.CATS_SPENDER='EK2' Or c.CATS_SPENDER='2C' Or c.CATS_SPENDER='H31' Or c.CATS_SPENDER='JZ' Or c.CATS_SPENDER='A37') And (c.CATS_PRIM='2' Or c.CATS_PRIM='3' Or c.CATS_PRIM='4' Or c.CATS_PRIM='5' Or c.CATS_PRIM='6' Or c.CATS_PRIM='7' Or c.CATS_PRIM='8' Or c.CATS_PRIM='9' Or c.CATS_PRIM='X' Or c.CATS_PRIM='Y' Or c.CATS_PRIM='Z') THEN 1 ELSE 0 END END=1 " & _
                "ORDER BY m.EMPL_LAST_NAME,e.EMPL_LAST_NAME || ', ' || e.EMPL_FIRST_NAME || ' ' || e.EMPL_MIDDLE_INIT,c.CATS_PAYWK,CASE WHEN substr(c.CATS_GENORDER,1,3)='000' THEN '' ELSE c.CATS_GENORDER || c.CATS_TASK_ END"

I think the problem is somewhere in the group by clause. If I take that out and the sum in the select clause, it will work. If I run with the statement above, I get ORA-00907 - missing right parenthesis.

Any ideas
 
Wow. First off, break that down a bit. Forget string concats for queries this big. Use xml literals:

        Dim Query As String = <string>DECLARE @MachineId int;
                                      SET @MachineId = ( SELECT TOP 1 m.ID
                                                         FROM TestMachines m
                                                         WHERE m.MachineName = '<%= MachineName %>' );
                                      DELETE FROM ProductLicenses WHERE ExMachineID = @MachineId;
                                      DELETE FROM TestMachines WHERE ID = @MachineId;
                              </string>


Pretty ain't it? Now second, double pipes for concats is fugly. Good old-fashionned '+' works just fine. Third, INDENT YOUR CODE. Fourth, nested left joins makes the baby jesus cry.

Finally, why do you have so many filters in your where clause? A normal query should have 1 maybe two external variables involved. Find a better way to filter your data.

Oh and your problem comes from a missing OR. If you follow what I posted you should not have a hard time to find it.
 
Normally I would do it better. This is a program that i converted from vb6. Didnt make it .net worthy. I am basically using the sql statement to fill a recordset. Entire thing is dumb, i know, but it is what I got to do. I just need to do it this way and I can be done with it. I don't want and don't have the time to go back and make it better. I wish I could, but I cant. I have two other apps I am working on (which I am trying to do the right way, still learning). I know vba and vb6, still trying to figure out the .net stuff. I am use to doing everything with sql statements. Anyway, I will look for that or that I am missing. Thanks for the help
 
Ok, found it. Cleaned it up a lil, cant test it till tomorrow though.
 

Attachments

  • new 2.txt
    3 KB · Views: 52
For the record, I still mostly use T-SQL statements, like you, because most of the time they are easier to debug, unless I go Linq + EF. XML literals for sql queries is the best thing since sliced bread.
 
Didn't realize this, nevertheless all I see above is SQL (or PL/SQL...) code that seems completely compatible. Are there any particulars of Oracle I missed that makes the solution invalid?
 
Didn't realize this, nevertheless all I see above is SQL (or PL/SQL...) code that seems completely compatible. Are there any particulars of Oracle I missed that makes the solution invalid?

Last I was aware, Oracle doesnt..

..declare variables inline
..have an INT datatype
..use @ in variable names
..use SET (variablename) = SCALARQUERY to popualte a variable with the results of a query
..support TOP N as a way of limiting rows

In oracle, your code would look more like:

VB.NET:
DECLARE
  MachineId PLS_INTEGER;
BEGIN
  SELECT MAX(m.ID) INTO MachineID
  FROM TestMachines m
  WHERE m.MachineName = '<%= MachineName %>';
  
  DELETE FROM ProductLicenses WHERE ExMachineID = MachineId;
  DELETE FROM TestMachines WHERE ID = MachineId;
END;

But I don't even guarantee this would work.. Suffice to say that the only lines in the T-SQL that transported to PL/SQL without any modification were the FROM/WHERE in the select.. Maybe Oracle has provided T-SQL support in more recent versions, I stopped at 11g and I'm not sure what has been done since ;)
 
ps; XML literals for SQL queries aren't the best thing since sliced bread, because they still encourage developers to ram SQL into vb code or aspx files.. SQLs should go in their own resources file at least. I'll admit to them being better than hundreds of lines of "SELECT *" & _ " FROM blah " & _ "WHERE.." though :)
 
I stand corrected... My experience with Oracle is virtually nil, I just didn't realize we were in the Oracle forum (big fan of the "New Posts" feature... :))
 
Back
Top