|
View previous topic :: View next topic
|
| Author |
Message |
Amit_Singh47
New User
Joined: 15 May 2013
Posts: 22
Location: India
|
|
|
|
| Apologies for not specifying that I needed this in the same SELECT and not 3 diff SELECTS in SAME QUERY. |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Amit,
If you are not sure about performance you need to execute EXPLAIN against the query
Also please read manuals on how to decrypt the message from EXPLAIN |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Amit,
| Amit_Singh47 wrote: |
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 |
Can you please explain what kind of output you are expecting for above set of data and what will be your input host variables?
Regards,
Chandan |
|
| Back to top |
|
|
Amit_Singh47
New User
Joined: 15 May 2013
Posts: 22
Location: India
|
|
|
|
okay...
if user enters only SSN say SSN1 then output SSN1 sid1
SSN1 sid4
SSN1 sid5
if user enters student ID say sid2SSN2 sid2
SSN5 sid2
if user enters both student ID (sid1) and SSN(SSN1)
SSN1 sid1
INPUT HOST VARIABLES:
1) WS-SSN; if in the CICS map SSN field is not inputed this is defaulted to spaces
2) WS-SID;if in the CICS map SID field is not inputed this is defaulted to spaces |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Try this it's untested
| Code: |
WHERE( STUDENT_ID = :WS-STUDENT-ID
AND STUDENT_SSN = CASE WHEN :WS-SSN >SPACES
THEN :WS-SSN ELSE STUDENT_SSN END)
OR (STUDENT_SSN = :WS-SSN
AND STUDENT_ID = CASE WHEN :WS-STUDENT-ID > SPACES
THEN :WS-STUDENT-ID ELSE STUDENT_ID END ) |
Here before execution of query if WS-SSN or WS-STUDENT-ID then populate them with low values so that they will not be true for gretaer than spaces
See if it helps
Regards,
Chandan |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I would have ~ billion records in database. Would using a UNION/UNION All be a good option? |
Processing a billion records will take much more time than you desire if the querie(s) cannot be handled by key reads and even these may bog down (depending on how many "duplicate entries" there might be).
How are there a billion student records?
If we knew more about the process, we might be able to offer better suggestions. |
|
| Back to top |
|
|
seagull
New User
Joined: 28 May 2007
Posts: 24
Location: Dublin
|
|
|
|
| In an ideal world, the database should be set up so that those fields are either required or hold nulls, rather than having spaces. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| chandan.inst wrote: |
| Code: |
WHERE( STUDENT_ID = :WS-STUDENT-ID
AND STUDENT_SSN = CASE WHEN :WS-SSN >SPACES
THEN :WS-SSN ELSE STUDENT_SSN END)
OR (STUDENT_SSN = :WS-SSN
AND STUDENT_ID = CASE WHEN :WS-STUDENT-ID > SPACES
THEN :WS-STUDENT-ID ELSE STUDENT_ID END ) |
Here before execution of query if WS-SSN or WS-STUDENT-ID then populate them with low values so that they will not be true for gretaer than spaces
|
I have tested above query in IBM data studio and its working as expected.
The only thing is somehow > SAPCES check not working in Data studio. You may check that with cobol code so I modified the query as below
| Code: |
WHERE( STUDENT_ID = :WS-STUDENT-ID
AND STUDENT_SSN = CASE WHEN :WS-SSN-PRESENT = 'Y'
THEN :WS-SSN ELSE STUDENT_SSN END)
OR (STUDENT_SSN = :WS-SSN
AND STUDENT_ID = CASE WHEN :WS-STUDENT-ID-PRESENT = 'Y'
THEN :WS-STUDENT-ID ELSE STUDENT_ID END ) |
Here WS-SSN-PRESENT and WS-STUDENT-ID-PRESENT needs to be populated with 'Y' before query execution as per the input data.
Agree with Dick if you need to suggestion with performance we will need more inputs or you can also get in touch with DBA's at your shop if required
Let us know if it works for you
Regards,
Chandan |
|
| Back to top |
|
|
|
|
