| how to select Sub_Query fields [message #690118] |
Sun, 13 October 2024 11:00  |
 |
suji6281
Messages: 147
Registered: September 2014
|
Senior Member |
|
|
Hi All,
Can you please help me with the sql with select fields from sub-query along with the other select fields from main query.
The below sql query is working fine. But when I try to add two more fields from sub-query, i.e PO_TBL its throwing an error.
Ex: SELECT A.ACCOUNT, A.DEPTID FROM GL_TBL A WHERE A.ACCOUNT IN (SELECT B.ACCOUNT FROM PO_TBL B
WHERE B.PO_ID = 'SHARE' AND B.PO_TYPE <> 'CLOSED')
AND A.DEPTID = 'DEP16719'
AND A.STATUS = 'A'
AND A.GL_DATE BETWEEN '01-APR-204' AND '30-APR-2024'
Ex: SELECT A.ACCOUNT, A.DEPTID, B.PO_NBR, B.PO_DT FROM GL_TBL A WHERE A.ACCOUNT IN (SELECT B.ACCOUNT FROM PO_TBL B
WHERE B.PO_ID = 'SHARE' AND B.PO_TYPE <> 'CLOSED')
AND A.DEPTID = 'DEP16719'
AND A.STATUS = 'A'
AND A.GL_DATE BETWEEN '01-APR-204' AND '30-APR-2024'
Regards
Suji
|
|
|
|
| Re: how to select Sub_Query fields [message #690119 is a reply to message #690118] |
Sun, 13 October 2024 11:39  |
John Watson
Messages: 8955
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You need to join the tables. Build on this:
select ... from gl_tbl join po_tbl on (gl_tbl.account = po_tbl.account) where .... ; |
|
|
|
| Re: how to select Sub_Query fields [message #690120 is a reply to message #690119] |
Sun, 13 October 2024 13:17 |
Solomon Yakobson
Messages: 3299
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
|
Jonh, in general, your solution might be not what OP expects since IN (subquery) returns main table row once regarless of how many subquery rows match while join will return main table row as many times as there are matches. OP meeds to clarify if all matches are expected or only one match (then op needs to clarify which one - mx/min, etc.).
|
|
|
|
|
|
| Re: how to select Sub_Query fields [message #690122 is a reply to message #690121] |
Mon, 14 October 2024 03:20 |
cookiemonster
Messages: 13947
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That doesn't answer Solomon's question.
What he's asking is what's the relationship between gl_tbl and po_pbl? One to one or One to Many? And if one to many, which is the one and which is the many?
You need to do a join instead of the sub-query as John already suggested.
Depending on the nature of the relationship between the 2 tables you may also need to use aggregate functions to get the same number of rows as before (as Solomon pointed out).
We can't tell you exactly what the SQL should be without knowing what the relationship between the tables is.
|
|
|
|
| Re: how to select Sub_Query fields [message #690123 is a reply to message #690121] |
Mon, 14 October 2024 08:07 |
Solomon Yakobson
Messages: 3299
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Just to give you an example:
select d.dname
from dept d
where deptno in (
select e.deptno
from emp e
where e.sal > 2000
)
/
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
SQL>
But if we join instead:
select d.dname,
e.sal
from dept d,
emp e
where d.deptno = e.deptno
and e.sal > 2000
/
DNAME SAL
-------------- ----------
ACCOUNTING 2450
ACCOUNTING 5000
RESEARCH 3000
RESEARCH 3000
RESEARCH 2975
SALES 2850
6 rows selected.
SQL>
select d.dname,
max(e.sal) max_sal
from dept d,
emp e
where d.deptno = e.deptno
and e.sal > 2000
group by d.dname
/
DNAME MAX_SAL
-------------- ----------
RESEARCH 3000
SALES 2850
ACCOUNTING 5000
SQL>
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
