View previous topic :: View next topic
|
Author |
Message |
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.
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?
Code'd |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Add on:
Why not try
Code: |
WHERE STUDENT_ID = :WS-STUDENT-ID
OR STUDENT_SSN = :WS-SSN |
|
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
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? |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Amit,
You dont need CASE for this requirement. |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
I am seriously confused now |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
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
Regards,
Chandan |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Oops
The predicates in the WHERE should have parentheses - previous reply edited.
d |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
You can split the query which Dick had provided and use Union all |
|
Back to top |
|
|
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.. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Single or multiple the result set is one |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
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? |
|
Back to top |
|
|
|