custom SQL in ODBC
Moderators: chulett, rschirm, roy
custom SQL in ODBC
i have two tables cbfact and attr.there are 3 columns in cb fact: id,monthcode,pqr and 4 columns in attr:id,weekcode,abc,xyz.i want to have a left outer join between the two tables on the basis of latest partition.latest partition means from mothcode current monthcode data will be compared with current weekcode data of attr.monthcode comes in this fashion 200701,200702...200712 nad weekcode comes in the fashion 200701,200702,...200750,200751,200752 like this.
if in the odbc stage i am writing the below query it is showing error message..
can u suggest me some sql.i want to do it through database level not in job level
select a.ID,a.mthcode,b.abc,b.xyz
from
(
select * from cbfact where mthcode in (max(mthcode) from cbfact
) a
and
(
select * from cbfact where weekcode in (max(weekcode) from attr
) b
where
a.ID=b.ID(+)
if in the odbc stage i am writing the below query it is showing error message..
can u suggest me some sql.i want to do it through database level not in job level
select a.ID,a.mthcode,b.abc,b.xyz
from
(
select * from cbfact where mthcode in (max(mthcode) from cbfact
) a
and
(
select * from cbfact where weekcode in (max(weekcode) from attr
) b
where
a.ID=b.ID(+)
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
Re: custom SQL in ODBC
You need to do something like this:dr46014 wrote: select a.ID,a.mthcode,b.abc,b.xyz
from
(
select * from cbfact where mthcode in (max(mthcode) from cbfact
) a
and
(
select * from cbfact where weekcode in (max(weekcode) from attr
) b
where
a.ID=b.ID(+)
Code: Select all
select a.ID,a.mthcode,b.abc,b.xyz
from
cbfact a, attr b
where
a.ID=b.ID(+)
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
this is the sample data what i want
cbfact attr
id monthcode pqr id weekcode abc xyz
aa 200606 1234 aa 200625 1256 2563
bb 200606 4563 bb 200625 1563 1563
cc 200606 4589 cc 200625 1256 2563
dd 200606 4568 dd 200624 7865 4569
ee 200605 4189
output
id montcode weekcode abc xyz
aa 200606 200625 1256 2563
bb 200606 200625 1563 1563
cc 200606 200625 1256 2563
dd 200606 null null null
cbfact attr
id monthcode pqr id weekcode abc xyz
aa 200606 1234 aa 200625 1256 2563
bb 200606 4563 bb 200625 1563 1563
cc 200606 4589 cc 200625 1256 2563
dd 200606 4568 dd 200624 7865 4569
ee 200605 4189
output
id montcode weekcode abc xyz
aa 200606 200625 1256 2563
bb 200606 200625 1563 1563
cc 200606 200625 1256 2563
dd 200606 null null null
cbfact | attr
*********** *********
id monthcode pqr id weekcode abc xyz
aa 200606 1234 | aa 200625 1256 2563
bb 200606 4563 | bb 200625 1563 1563
cc 200606 4589 | cc 200625 1256 2563
dd 200606 4568 dd 200624 7865 4569
ee 200605 4189 |
output
id montcode weekcode abc xyz
aa 200606 200625 1256 2563
bb 200606 200625 1563 1563
cc 200606 200625 1256 2563
dd 200606 null null null
*********** *********
id monthcode pqr id weekcode abc xyz
aa 200606 1234 | aa 200625 1256 2563
bb 200606 4563 | bb 200625 1563 1563
cc 200606 4589 | cc 200625 1256 2563
dd 200606 4568 dd 200624 7865 4569
ee 200605 4189 |
output
id montcode weekcode abc xyz
aa 200606 200625 1256 2563
bb 200606 200625 1563 1563
cc 200606 200625 1256 2563
dd 200606 null null null
CREATE TABLE TAB1 AS select a.ID,a.MTHCODE
FROM CBFACT a
where
a.mthcode in(select max(mthcode) FROM CBFACT)
CREATE TABLE TAB3 AS
SELECT b.ID,b.ABC,b.XYZ,b.weekcode
FROM ATTR b
WHERE b.weekcode in (select max(weekcode) from attr)
SELECT TAB1.ID,TAB1.MTHCODE,TAB3.WEEKCODE,TAB3.XYZ,TAB3.ABC
FROM TAB1,TAB3
WHERE TAB1.ID=TAB3.ID(+)
if i am using these three queries its giving corretc out put.but i dont want two more tables in my database.So please help me to combine these three queries into a single query
FROM CBFACT a
where
a.mthcode in(select max(mthcode) FROM CBFACT)
CREATE TABLE TAB3 AS
SELECT b.ID,b.ABC,b.XYZ,b.weekcode
FROM ATTR b
WHERE b.weekcode in (select max(weekcode) from attr)
SELECT TAB1.ID,TAB1.MTHCODE,TAB3.WEEKCODE,TAB3.XYZ,TAB3.ABC
FROM TAB1,TAB3
WHERE TAB1.ID=TAB3.ID(+)
if i am using these three queries its giving corretc out put.but i dont want two more tables in my database.So please help me to combine these three queries into a single query
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The SQL may not work because the OP is not using Oracle. When posting SQL please indicate whether it is database-specific. Only Oracle prescribes outer joins in the fashion indicated.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Do something like this - (I have not tested it, as I dont have valid data)
Code: Select all
SELECT aa.ID, aa.mthcode, bb.weekcode, bb.xyz, bb.abc
FROM (SELECT a.ID, a.mthcode
FROM cbfact a
WHERE a.mthcode IN (SELECT MAX (mthcode)
FROM cbfact)) aa,
(SELECT b.ID, b.abc, b.xyz, b.weekcode
FROM attr b
WHERE b.weekcode IN (SELECT MAX (weekcode)
FROM attr)) bb
WHERE aa.ID = bb.ID(+)
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.