DB2 - HOW to USE CASE in WHERE clause

Amit_Singh47 · New User Joined: 15 May 2013 Posts: 22 Location: India

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.

dick scherrer · Posted: Fri May 17, 2013 1:15 am

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.

dick scherrer · Posted: Fri May 17, 2013 1:19 am

Add on:

Why not try

Craq Giegerich · Posted: Fri May 17, 2013 2:05 am

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).

Pandora-Box · Posted: Fri May 17, 2013 10:11 am

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

Amit_Singh47 · New User Joined: 15 May 2013 Posts: 22 Location: India

Thanks Dick and Pandora-Box.

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?

Pandora-Box · Posted: Fri May 17, 2013 2:31 pm

Amit,

You dont need CASE for this requirement.

chandan.inst · Posted: Fri May 17, 2013 2:43 pm

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

Amit_Singh47 · New User Joined: 15 May 2013 Posts: 22 Location: India

Okay, then can you please provide me a viable solution. I have been thinking but it seems difficult.

Amit_Singh47 · New User Joined: 15 May 2013 Posts: 22 Location: India

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

Amit_Singh47 · New User Joined: 15 May 2013 Posts: 22 Location: India

However i need his functionality in only one query.
is their any way...if not using case

Pandora-Box · Posted: Fri May 17, 2013 2:51 pm

Apologies

I oversaw your post

Why not handle it application program?

So that you will execute only one query a given time

chandan.inst · Posted: Fri May 17, 2013 2:51 pm

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

Pandora-Box · Posted: Fri May 17, 2013 3:38 pm

I am seriously confused now

chandan.inst · Posted: Fri May 17, 2013 3:49 pm

Ok I will try my best to explain what I meant to say

Amit_Singh47 · New User Joined: 15 May 2013 Posts: 22 Location: India

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

chandan.inst · Posted: Fri May 17, 2013 4:23 pm

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

dick scherrer · Posted: Fri May 17, 2013 7:21 pm

Hello,

dick scherrer · Posted: Fri May 17, 2013 7:54 pm

Oops

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

d

Amit_Singh47 · New User Joined: 15 May 2013 Posts: 22 Location: India

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

Pandora-Box · Posted: Sat May 18, 2013 3:10 pm

You can split the query which Dick had provided and use Union all

Amit_Singh47 · New User Joined: 15 May 2013 Posts: 22 Location: India

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.

Wii kip posting..

Pandora-Box · Posted: Sun May 19, 2013 11:55 pm

Single or multiple the result set is one

dick scherrer · Posted: Mon May 20, 2013 7:14 am

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.

Amit_Singh47 · New User Joined: 15 May 2013 Posts: 22 Location: India

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?