IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 - HOW to USE CASE in WHERE clause


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Thu May 16, 2013 9:27 pm
Reply with quote

Hi All,

Problem Statement:

A student has a student_id and a ssn number. In my program I get both of them from user and store in host variables :ws-student-id and :ws-ssn. However, user can input either student_id and/or ssn in which case the value field not inputted by user will be defaulted to spaces.
I want to write "ONE" query which will handle all the 3 cases (only ws-student-id is entered, only ws-ssn is entered and both ws-ssn and ws-student-id are entered). It should look something like this.

Code:
SELECT
STUDENT_NAME,
     STUDENT_CLASS,
   STUDENT_AGE
FROM
       STUDENT
WHERE
CASE 1
       (IF WS-STUDENT-ID IS NOT SPACES)
       STUDENT_ID = : WS-STUDENT-ID
CASE 2
       (IF WS-SSN IS NOT SPACES)
       STUDENT_SSN = : WS-SSN
CASE 3
(IF WS-STUDENT-ID IS NOT SPACES AND
IF WS-SSN IS NOT SPACES )
STUDENT_ID = : WS-STUDENT-ID
AND    STUDENT_SSN = : WS-SSN
WITH UR

Can this be done in one query or I will have to write 3 separate queries? icon_sad.gif

Code'd
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri May 17, 2013 1:15 am
Reply with quote

Hello,

You'll get more and better replies if you post in the proper part of the forum . . .

Your question concerns DB2, Not CICS.

I'll move it.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri May 17, 2013 1:19 am
Reply with quote

Add on:

Why not try
Code:
WHERE STUDENT_ID  = :WS-STUDENT-ID
   OR STUDENT_SSN = :WS-SSN
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri May 17, 2013 2:05 am
Reply with quote

What are you going to do when the SSN and Student ID are both entered but they aren't related? I know that will never happen (at least not in testing).
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri May 17, 2013 10:11 am
Reply with quote

What you could also do is handle this at cobol instead of DB2

or You can go for UNION/UNION ALL if both returns different rows
Back to top
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Fri May 17, 2013 2:21 pm
Reply with quote

Thanks Dick and Pandora-Box. icon_smile.gif

The problem with using
WHERE STUDENT_ID = :WS-STUDENT-ID
OR STUDENT_SSN = :WS-SSN


is that it will pick up records where student_id/ssn is blank (this is valid scenario in my table).
Whereas I want to run the query only on that key ( student_id/ssn) which is NOT blank.

UNION ALL would be a good option but for that also i would need to write all the 3 queries . but I was just wondering if this could be done in one query only.

I hope you get my problem here. Can this be done using CASE clause in one query only?
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri May 17, 2013 2:31 pm
Reply with quote

Amit,

You dont need CASE for this requirement.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri May 17, 2013 2:43 pm
Reply with quote

Hi Amit,

You can check WS-STUDENT-ID and WS-SSN before executing the query uggested by Dick.

If either one of them is non-blank then only execute the query else skip the execution of query

Hope this helps

Regards,
Chandan
Back to top
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Fri May 17, 2013 2:43 pm
Reply with quote

Okay, then can you please provide me a viable solution. I have been thinking but it seems difficult.
Back to top
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Fri May 17, 2013 2:47 pm
Reply with quote

wouldnt i need three queries in that case

1st query to check on student id only
2nd query to check on ssn
3rd qury to check on both the key fields?

if ssn is not spaces and studentid is spaces
run 2nd query
else
if ssn is spaces and student id is not spaces
run 1st query
else
if ssn is not spaces and student id is not spaces
run 3rd query
Back to top
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Fri May 17, 2013 2:49 pm
Reply with quote

However i need his functionality in only one query.
is their any way...if not using case
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri May 17, 2013 2:51 pm
Reply with quote

Apologies

I oversaw your post

Why not handle it application program?

So that you will execute only one query a given time
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri May 17, 2013 2:51 pm
Reply with quote

Sorry my bad in earlier solution

Can you populate Host variable with High values which is having Spaces in Host variable and then execute Dick's query


I hope there are no records with High Values for Studen and SSn in your database

Regards,
Chandan
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri May 17, 2013 3:38 pm
Reply with quote

icon_biggrin.gif I am seriously confused now icon_biggrin.gif
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri May 17, 2013 3:49 pm
Reply with quote

icon_biggrin.gif Ok I will try my best to explain what I meant to say

dick scherrer wrote:

Code:
WHERE STUDENT_ID  = :WS-STUDENT-ID
   OR STUDENT_SSN = :WS-SSN


With this query if any one of the host variable is Spaces those records also will show up in results.

So the solution to achieve this with same query is

Code:
IF WS-STUDENT-ID > SPACES OR WS-SSN > SPACES
    IF WS-STUDENT-ID = SPACES
         MOVE HIGH VALUES TO WS-STUDENT-ID
    END-IF
    IF WS-SSN =  SPACES
        MOVE HIGH VALUES TO WS-SSN
   END-IF
   EXECUTE THE QUERY
ELSE
  SKIP THE QUERY EXECUTION
END-IF
This will work if there are no records in database for SSN or STUDENT ID with high values

I hope I am clear now icon_biggrin.gif

Regards,
Chandan
Back to top
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Fri May 17, 2013 4:08 pm
Reply with quote

Thanks a lot Chandan. I hope this will work.
However, need to see if low/high values will be allowed in host variables without ending up with bad sql code
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri May 17, 2013 4:23 pm
Reply with quote

I don't think it will be a issue as far as your Column is defined as CHAR
Let us know if it works for you

Regards,
Chandan
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri May 17, 2013 7:21 pm
Reply with quote

Hello,


Quote:
The problem with using
WHERE STUDENT_ID = :WS-STUDENT-ID
OR STUDENT_SSN = :WS-SSN

is that it will pick up records where student_id/ssn is blank (this is valid scenario in my table).


Instead of
Code:
WHERE STUDENT_ID  = :WS-STUDENT-ID
   OR STUDENT_SSN = :WS-SSN
you might try
Code:
WHERE (STUDENT_ID  = :WS-STUDENT-ID AND STUDENT_ID NOT = 'however many spaces")
   OR (STUDENT_SSN = :WS-SSN AND STUDENT_SSN NOT = "however many spaces")
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri May 17, 2013 7:54 pm
Reply with quote

Oops icon_redface.gif

The predicates in the WHERE should have parentheses - previous reply edited.

d
Back to top
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Sat May 18, 2013 11:34 am
Reply with quote

these approaches suggested may work for unique combination of two fields, like the one i said

Student_id
SSN

what if the realationship between student_id and ssn is m:n.

i.e. more than one student can have same SSN and Student_id.
just in case)it will never happen)I just want to know if this scenario also can be handled using ONLY one query.


SSN1 sid1
SSN2 sid2
SSN3 sid3
SSN1 sid4
SSN1 sid5
SSN4 sid1
SSN5 sid2
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Sat May 18, 2013 3:10 pm
Reply with quote

You can split the query which Dick had provided and use Union all
Back to top
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Sun May 19, 2013 12:37 pm
Reply with quote

Okay..so the bottomline is that this cant be done in one query.
And my requirement does not falicitate use of case.
Thanks a lot guys for your help. icon_biggrin.gif
Wii kip posting..
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Sun May 19, 2013 11:55 pm
Reply with quote

Single or multiple the result set is one
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Mon May 20, 2013 7:14 am
Reply with quote

Hello,

A single query may contain more than one SELECT . . . As in using UNION.

If you (or your organization) has determined that using UNION is more than one query, other organizations would disagree.

If you wanted only a single SELECT it would be good to specify this.
Back to top
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Mon May 20, 2013 9:26 am
Reply with quote

Hi Dick,

Thanks. I am not sure about the performance of the query, if we use UNION/UNION all. I would have ~ billion records in database. Would using a UNION/UNION All be a good option?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts COBOL -Linkage Section-Case Sensitive COBOL Programming 1
No new posts To search DB2 table based on Conditio... DB2 1
No new posts Zunit Test case editor error Testing & Performance 4
No new posts NOT IN clause in COBOL pgm COBOL Programming 8
No new posts SUSBSCRIPT WITH SIGN IN PIC CLAUSE COBOL Programming 3
Search our Forums:

Back to Top