|
View previous topic :: View next topic
|
| Author |
Message |
plvien
New User
Joined: 15 Apr 2007
Posts: 3
Location: Vietnam
|
|
|
|
Hello,
Thank you for your comment on these two SQL statements, that the purpose is "get information of the last created appendix of type 'A' (from table APPENDIX) that have CLIENT, CONT and NUM_OP predefined in host variable H-CLIENT, H-CONT, H-NUM-OP"
1st SQL is
| Code: |
SELECT T1.* INTO (:APPENDIX)
FROM APPENDIX AS T1
WHERE T1.CLIENT = :H-CLIENT
AND T1.CONT = :H-CONT
AND T1.NUM_OP = :H-NUM-OP
AND T1.TYPE_APPENDIX = 'A'
AND EXISTS
( SELECT 1
FROM APPENDIX AS T2
WHERE T2.CLI = T1.CLI
AND T2.CONT = T1.CONT
AND T2.NUM_OP = T1.NUM_OP
AND T2.TYPE_APPENDIX = 'A'
HAVING T1.DATE_APPENDIX = MAX(T2.DATE_APPENDIX)
) |
2nd SQL is:
| Code: |
SELECT * INTO (:APPENDIX)
FROM APPENDIX
WHERE CLIENT = :H-CLIENT
AND CONT = :H-CONT
AND NUM_OP = :H-NUM-OP
AND TYPE_APPENDIX = 'A'
AND DATE_APPENDIX = (SELECT MAX(DATE_APPENDIX)
FROM APPENDIX
WHERE CLIENT = :H-CLIENT
AND CONT = :H-CONT
AND NUM_OP = :H-NUM-OP
AND TYPE_APPENDIX = 'A') |
For me, the 2nd statement is better, but I would like to hear your ideas and explication.
Thank you and nice night (it's 22:10 now here). |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
sql 2 is definitely better.
I would have coded it correlated though (for maintainability), and not with "select *" but specifying all columns. (but that's another discussion)
| Code: |
SELECT * INTO (:APPENDIX)
FROM APPENDIX T1
WHERE CLIENT = :H-CLIENT
AND CONT = :H-CONT
AND NUM_OP = :H-NUM-OP
AND TYPE_APPENDIX = 'A'
AND DATE_APPENDIX = (SELECT MAX(DATE_APPENDIX)
FROM APPENDIX T2
WHERE T2.CLIENT = T1.CLIENT
AND T2.CONT = T1.CONT
AND T2.NUM_OP = T1.NUM_OP
AND T2.TYPE_APPENDIX = T1.TYPE_APPENDIX
) |
|
|
| Back to top |
|
|
plvien
New User
Joined: 15 Apr 2007
Posts: 3
Location: Vietnam
|
|
|
|
Thank you for your response, GuyC.
Yes, "select *" is not good, I used it just for simplify the statement.
I don't know why my question has too few answer. If I made a mistake, please anyone tell me, thank you. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| plvien wrote: |
Thank you for your response, GuyC.
Yes, "select *" is not good, I used it just for simplify the statement.
I don't know why my question has too few answer. If I made a mistake, please anyone tell me, thank you. |
Well,
1) you posted in cobol section and not in DB2.
2) which alternative of two working SQLs is "the best" is a matter of taste
3) explaining the difference in accesspath and performance in a few words is expecting more knowledge from the reader. If he would understand the explanation, he wouldn't have asked the question to begin with. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I don't know why my question has too few answer. |
Because there was no need for lots of people to "chime in". . .
Usually, when one of the more knowledgable people post a direct answer to some question, there is not so much need for "thoughts from all over".
I'll move this to the DB2 part of the forum. . .  |
|
| Back to top |
|
|
plvien
New User
Joined: 15 Apr 2007
Posts: 3
Location: Vietnam
|
|
|
|
Thanks GuyC and Dick
I'll be more careful the next time. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome
| Quote: |
| I'll be more careful the next time. |
Your post was fine - no problem.
As you spend more time here, the forum will become more familiar/comfortable.
d |
|
| Back to top |
|
|
|
|
