| View previous topic :: View next topic |
| Author |
Message |
bstillwa
Beginner
Joined: 01 Nov 2004
Posts: 23
Topics: 6
Location: NJ
|
 Posted: Thu Jan 18, 2007 12:52 pm Post subject: SPUFI display col from 'and exists' result from same table |
 |
|
I have the following SQL I am running in SPUFI. It works fine, but I would also like to display B.REL_VER_NUM. Can someone please show me how to change this statement so I can display the extra column?
| Code: |
SELECT
ERR_NUM
,ERR_SUB_NUM
,REL_VER_NUM
FROM DV01.EET A
WHERE A.REL_VER_NUM < '170'
AND EXISTS (SELECT ERR_NUM, ERR_SUB_NUM, REL_VER_NUM
FROM DV01.EET B
WHERE (B.ERR_NUM = A.ERR_NUM
AND B.ERR_SUB_NUM = A.ERR_SUB_NUM
AND B.REL_VER_NUM > A.REL_VER_NUM
AND B.REL_VER_NUM <= '170')
)
|
_________________
Bev. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 18, 2007 1:32 pm Post subject: |
|
|
bstillwa,
Try this
| Code: |
SELECT A.ERR_NUM
,A.ERR_SUB_NUM
,A.REL_VER_NUM
,B.REL_VER_NUM
FROM DV01.EET A
,DV01.EET B
WHERE A.REL_VER_NUM < '170'
AND B.REL_VER_NUM <= '170'
AND B.ERR_NUM = A.ERR_NUM
AND B.ERR_SUB_NUM = A.ERR_SUB_NUM
AND B.REL_VER_NUM > A.REL_VER_NUM
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
bstillwa
Beginner
Joined: 01 Nov 2004
Posts: 23
Topics: 6
Location: NJ
|
| Posted: Thu Jan 18, 2007 1:45 pm Post subject: |
|
|
Thanks, and I will try that, but in the meantime I got what I wanted by coding this (and can u plz tell me how to get this to look like code in this forum, I tried to use the Code button but does not work for me):
| Code: | SELECT
A.ERR_NUM
,A.ERR_SUB_NUM
,A.REL_VER_NUM AS OBS_REL_VER_NUM
,C.REL_VER_NUM AS PROD_REL_VER_NUM
FROM DV01.EET A
,DV01.EET C
WHERE A.REL_VER_NUM < '170'
AND EXISTS (SELECT B.ERR_NUM, B.ERR_SUB_NUM, B.REL_VER_NUM
FROM DV01.EET B
WHERE (B.ERR_NUM = A.ERR_NUM
AND B.ERR_SUB_NUM = A.ERR_SUB_NUM
AND B.REL_VER_NUM > A.REL_VER_NUM
AND B.REL_VER_NUM <= '170')
)
AND A.ERR_NUM = C.ERR_NUM
AND A.ERR_SUB_NUM = C.ERR_SUB_NUM
AND A.REL_VER_NUM <> C.REL_VER_NUM
AND C.REL_VER_NUM <= '170' |
_________________
Bev. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 18, 2007 1:51 pm Post subject: |
|
|
bstillwa,
You have opted to Disable BBCode in this post when you are posting. It is below the post reply box. I removed that
Aren't you using the Quick reply box which is below ?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
bstillwa
Beginner
Joined: 01 Nov 2004
Posts: 23
Topics: 6
Location: NJ
|
| Posted: Thu Jan 18, 2007 2:29 pm Post subject: |
|
|
Thanks kolusu, your answer is simpler than mine and works just as well!
I was not using the Quick Reply box. If we should use that then why are two "postreply" buttons supplied both top and bottom of message? I guess I just don't post often enough to know all these tricks. Suggestion would be to add a button here on How to Post and Reply (for newbies and infrequent users). There is skimpy info in FAQ.
Thanks again.
_________________
Bev. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 18, 2007 2:43 pm Post subject: |
|
|
| Quote: |
If we should use that then why are two "postreply" buttons supplied both top and bottom of message?
|
Well Quick reply is saves 1 additional click and you have the option of reading all the posts above. Also it has some other cool features which are explained here
http://www.mvsforums.com/helpboards/viewtopic.php?t=363
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
