| View previous topic :: View next topic |
| Author |
Message |
timfoster
Beginner
Joined: 08 Jul 2004
Posts: 12
Topics: 5
|
 Posted: Thu Oct 19, 2006 12:47 pm Post subject: Is (select col from a) allowed in select of query |
 |
|
Folks,
can anyone tell me if the following works in db2. I am trying to use the
second select to get a column from a table that I don't want to add to my
main query although I suppose I can use a left outer join.
| Code: |
SELECT
PER.SOCSECNO,
(SELECT GTNAMT FROM F8IO.ISI_PYH_GTN_T //wondering
WHERE EMPLID = BAS.EMPLID //about this select
AND ORGID = BAS.ORGID
AND GTNNO = 162) TIMS
FROM
F8IO.ISI_EDB_PER_T PER
INNER JOIN
F8IO.ISI_EDB_BAS_T BAS
ON PER.QTRNO = BAS.QTRNO
AND PER.EMPLID = BAS.EMPLID
AND PER.ORGID = BAS.ORGID
WHERE PER.QTRNO = 0
AND BAS.EMPLSTAT IN ('A','L','P');
|
thanks muchly |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Thu Oct 19, 2006 12:57 pm Post subject: |
|
|
timfoster,
The SELECT statement is used to create a Temp table named TIMS from which are selecting certain values.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
timfoster
Beginner
Joined: 08 Jul 2004
Posts: 12
Topics: 5
|
| Posted: Thu Oct 19, 2006 1:55 pm Post subject: |
|
|
So with DB2 then you are not allowed to use the second select the way I am trying? As you mention, it needs to be added to the from clause to create a temporary table which I can join to. Something more like
| Code: |
SELECT
PER.SOCSECNO,
CHAR(GTN.GTNAMT)
FROM
F8IO.ISI_EDB_PER_T PER
INNER JOIN
F8IO.ISI_EDB_BAS_T BAS
ON PER.QTRNO = BAS.QTRNO
AND PER.EMPLID = BAS.EMPLID
AND PER.ORGID = BAS.ORGID
LEFT OUTER JOIN
(SELECT ORGID, EMPLID, GTNAMT FROM F8IO.ISI_PYH_GTN_T
WHERE GTNNO = 162 ) GTN
ON BAS.EMPLID = GTN.EMPLID
AND BAS.ORGID = GTN.ORGID
WHERE PER.QTRNO = 0
AND BAS.EMPLSTAT IN ('A','L','P');
|
thanks
Tim |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
timfoster
Beginner
Joined: 08 Jul 2004
Posts: 12
Topics: 5
|
| Posted: Thu Oct 19, 2006 3:17 pm Post subject: |
|
|
| thanks for the help |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
