Is it possible to fire 1st inner query and then outer query in Resident/QVD File

[#1]

Hello Forums,

I have the data in Oracle table(sal_tab) in following fashion.

A.

doc_no  doc_dt        doc_sal
1           20/8/2014      22
1           25/8/2014      11
2           10/8/2014      25
2           20/8/2014      19

I want max doc_sal for each doc_no.
So, for get the result I can put the following query.

Select s.* from sal_tab s where s.doc_dt in(select max(a.doc_dt) from sal_tab a where a.doc_no = s.doc_no)

The Output will be as follows:-

B.
doc_no  doc_dt       doc_sal
1           25-08-14      11
2           20-08-14      19

Which is fine

If I store the data just like A in Residental Table / QVD. Can we have put same type of Qury or workaround)

in Residental / QVD file so that I can get Result B ?.

Advance Thanks

SD

[maxgro]

yes you have to do a resident load of a ordered by doc_no  doc_dt  desc and only filter (keep) the first record

[maxgro]

A:

load * inline [

doc_no , doc_dt      ,  doc_sal

1      ,     20/8/2014 ,     22

1      ,     25/8/2014 ,     11

2      ,     10/8/2014 ,     25

2      ,     20/8/2014 ,     19

];

B:

NoConcatenate

load

doc_no, doc_dt, doc_sal

Resident A

where peek(doc_no) <> doc_no

order by doc_no, doc_dt desc;

DROP Table A;

[PrashantSangle]

Hi,

You can try this also

Load max(doc_sal) as doc_sal

doc_no,

onlydoc_dt) as doc_dt

from tableName

GroupBy doc_no;

Regards

[anbu1984]

Table:

Load doc_no,Date#(doc_dt,'D/M/YYYY')as doc_dt,doc_sal Inline [

doc_no,doc_dt,doc_sal

1, 20/8/2014,22

1, 25/8/2014,11

2, 10/8/2014,25

2, 20/8/2014,19 ];

NoConcatenate

Load doc_no,FirstSortedValue(doc_dt,-doc_dt ) as doc_dt,FirstSortedValue(doc_sal,-doc_dt ) as doc_sal  Resident Table GROUP BY doc_no;

Drop table Table;

[#6]

Hi Anbu/Max/Massimo/Forums

Thank for Quick Respons.

Suppose, I have 5 Millions of record in QVD. as per conversation first I had to Load the data from QVD into Memory(Resident) and then we can apply above suggestion suggested by all for u.

Is it possible to apply the above suggesion directly in QVD file?

Advance Thanks

SD

[anbu1984]

Yes you can load directly from QVD, provided if you don't need other columns apart from group by columns and calculated columns from QVD

[#8]

Hi Anbu,

Thanks for response, It will be greate if u can u explain me with figures

Advance Thanks

SD

[anbu1984]

Using FirstSortedValue, yes you can load directly from QVD , provided if you don't need other columns apart from group by columns and calculated columns from QVD

You have to use FirstSortedValue as below, if you want to select any other columns from QVD.

For ex, QVD has below columns

doc_no,doc_dt,doc_sal,doc_nm

Load doc_no, FirstSortedValue(doc_dt,-doc_dt ) as doc_dt,FirstSortedValue(doc_sal,-doc_dt ) as doc_sal,

FirstSortedValue(doc_nm,-doc_dt )

From QVD GROUP BY doc_no;

[#10]

Hi Anbu,

Thanks for response. Let me check I will back

SD