custom SQL in ODBC

[dr46014]

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(+)

[us1aslam1us]

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(+)

You need to do something like this:

Code: Select all

select a.ID,a.mthcode,b.abc,b.xyz 
from 
cbfact a, attr b
where 
a.ID=b.ID(+)

Then specify your mthcode and weekcode logic in the "where" clause.

[dr46014]

i have tried this sql but its giving incorrect records..
the problem here is that from cb fact we need to find the latest month code data and from attr we need to find the latest week data.and perform left outer join.

[us1aslam1us]

what exactly you mean by latest data for month and code? Send some sample data.

[dr46014]

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

[dr46014]

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

[dr46014]

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

[ray.wurlod]

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.

[dr46014]

can you please suggest me how to make this kind of join if my data base is oracle

[narasimha]

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(+)

[dr46014]

yesi got the result.i was also trying the same thing but i missed the subquery.
thanks a lot for your help

[narasimha]

Glad that helped :D
Time to mark your post as "Resolved"