SQL Syntax Question Related to If Statement 1

[ks01]

Hello …

I am trying to understand how to use the IF statement in my SELECT statement.

I will be unioning three queries together, each are pretty much the same but the WHERE clauses have a few differences. The type of data I am querying may identify both “error” and “warning” message types. The business rule is if the message type is equal to ERROR, then do not display the warning messages.

Here is the first query from my union:

  SELECT P.USER_CD,                       
         'ERRORS' AS TYPE,                                                     
         COUNT(*) AS TOTAL
    FROM SYS4.VLR_PACK P,                                                    
         SYS4.VLR_EDIT_ERR E,                                                  
         SYS4.VLR_EDIT_MSG M1,                                                 
         SYS4.VLR_EDIT_MSG M2,                                                 
         SYS4.VLR_USER R                                                     
   WHERE E.RECORD_NUM_A = P.RECORD_NUM                                         
     AND E.MSG_ID_1 = M1.MSG_ID                                                
     AND E.MSG_ID_2 IS NOT NULL                                                
     AND E.MSG_ID_2 = M2.MSG_ID                                                
     AND M1.MSG_TP <> 'E'                                                      
     AND M2.MSG_TP = 'E'                                                       
     AND P.USER_CD = R.USER_NUM

The IBM SQL Reference guide provides an example of the IF statement, which I think is what I want, but I’m not sure how to implement this in my syntax and am I only able to update values or can I delete entire records?

 IF rating = 1
 THEN SET new_salary =
  new_salary + (new_salary * .10);
 ELSEIF rating = 2
  THEN SET new_salary =
   new_salary + (new_salary * .05);
 ELSE SET new_salary =
  new_salary + (new_salary * .02);
END IF

Thanks in advance!

 

[blom0344]

Aren't you confusing performing a query with a procedural definition? The IF THEN ELSEIF .... can be used in a stored procedure, which is all about manipulating data. A select query does never perform either updates or deletes.

In a select query you could define logic through CASE WHEN ELSE END, but only in data retrieval..

Ties Blom

 

[ks01]

Hi Ties ... thanks for the response.

I'm sorry I wasn't more clear. The business rule for this work effort state that in my result set, if there are ERRORS and WARNINGS, do not include the WARNINGS in the output. I was hoping the IF statement would somehow allow me to filter out the WARNINGS in this case.

If the result set had WARNINGS only, then I would only be interested in seeing the WARNINGS.

Hope that helps clarify my intention.

Thank you again!

 

[papadba]

If you post some sample input rows and the result you want from your query it will help. If there are multiple situations, show each and the desired result.

Only small samples should be needed to show the "keepers" from the "whole" sample.

It may take a few minutes to create the initial sample, but it should be easily cloned and not require much effort

 

[blom0344]

No, I do not think you can use the IF ....
Unless you built a stored procedure out of it. Post your entire code and we will try to make suggestions..

Ties Blom

 

[MarcLodge]

ks01,
What I think you are after can be obtained without any IFs or CASE statements but just by use of the UNION

1st Union
Gets all Errors and Warnings
2nd Union
Gets all Warnings where no Errors exist (will need to do a not exists sub select)

You will need to use UNION rather than UNION ALL which eliminates duplicates.

If Errors without Warnings, they will get picked up by first Union.
If Warnings exist without Errors they will get picked up by both Unions but only reported once.
If Errors and Warnings exist, they will get picked up by the first Union and ignored by the second.

Does that make sense? Have I understood your problem correctly?

Marc

 

[ks01]

Hi Marc, yes, you have understood perfectly.

I believe this will work, thank you so much. I've been racking my itty-bitty brain trying to figure this out and I was overthinking it.

Thanks to Ties and Papadba for their feedback too! You all give such great feedback, this forum truly is a great resource!