INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Syntax Question Related to If Statement

SQL Syntax Question Related to If Statement

(OP)
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:

CODE

  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?

CODE

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!

RE: SQL Syntax Question Related to If Statement

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
 
 

RE: SQL Syntax Question Related to If Statement

(OP)
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!

RE: SQL Syntax Question Related to If Statement

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 effortwink

RE: SQL Syntax Question Related to If Statement

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
 
 

RE: SQL Syntax Question Related to If Statement

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

RE: SQL Syntax Question Related to If Statement

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close