Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 - HOW to USE CASE in WHERE clause
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 - HOW to USE CASE in WHERE clause
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

Site Director


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

PostPosted: Fri May 17, 2013 1:15 am    Post subject:
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

Site Director


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

PostPosted: Fri May 17, 2013 1:19 am    Post subject:
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    Post subject:
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

Moderator


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

PostPosted: Fri May 17, 2013 10:11 am    Post subject:
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    Post subject: Reply to: DB2 - HOW to USE CASE in WHERE clause
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

Moderator


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

PostPosted: Fri May 17, 2013 2:31 pm    Post subject:
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: 269
Location: Mumbai

PostPosted: Fri May 17, 2013 2:43 pm    Post subject:
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    Post subject: Reply to: DB2 - HOW to USE CASE in WHERE clause
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    Post subject: Reply to: DB2 - HOW to USE CASE in WHERE clause
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    Post subject: Reply to: DB2 - HOW to USE CASE in WHERE clause
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

Moderator


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

PostPosted: Fri May 17, 2013 2:51 pm    Post subject:
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: 269
Location: Mumbai

PostPosted: Fri May 17, 2013 2:51 pm    Post subject:
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

Moderator


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

PostPosted: Fri May 17, 2013 3:38 pm    Post subject:
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: 269
Location: Mumbai

PostPosted: Fri May 17, 2013 3:49 pm    Post subject:
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    Post subject: Reply to: DB2 - HOW to USE CASE in WHERE clause
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: 269
Location: Mumbai

PostPosted: Fri May 17, 2013 4:23 pm    Post subject:
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

Site Director


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

PostPosted: Fri May 17, 2013 7:21 pm    Post subject:
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

Site Director


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

PostPosted: Fri May 17, 2013 7:54 pm    Post subject:
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    Post subject: Reply to: DB2 - HOW to USE CASE in WHERE clause
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts Case insenstive in INCLUDE Condtion smileseenu DFSORT/ICETOOL 2 Mon Feb 15, 2016 8:42 pm
No new posts How steps execute in Job in case of a... richiewalia JCL & VSAM 4 Tue Jan 12, 2016 12:34 pm
No new posts Translate UPPER CASE to lower case steve-myers PL/I & Assembler 0 Mon Oct 19, 2015 9:33 am
No new posts [SQL0029] INTO clause missing from em... HABBIE DB2 2 Fri Sep 04, 2015 3:54 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us