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

[#2]

Hi Anbu,

As per your suggession I had tried with the QVD but the result is not as I aspected(Internal Table View).

FYI, It has given only one record that also not proper. Result is some what as follows:-

doc_no,doc_dt,doc_sal

2, 201008, 25

However It is not working. Is there any other way out.

Advance Thanks

SD

[#3]

Hi Massimo,

Thanks for showing the way which work in Resident Load.

FYI, I had also tried in QVD files where script is failing

Is there in way to implement the same or some other way on QVD file directly?. It will be very helpful for me.

Advance Thanks

SD

[anbu1984]

Can you attach sample qvw

[#5]

Hi Anbu,

Thanks for your response. But I am very sorry because I am not able to the sample QVW file. FYI I am 1st trying at home(No Internet Option avaiable) and after coming in the office I communicate with u.

If you don't mind can u please copy past the following scripts and create the require QVW and QVD files.

1. For creation of QVD file(QVW1.QVW)

resitab:

Load * inload [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

2, 26/8/2014, 9];

Store resitab into D:\QVD\dataqvd.qvd(qvd);

2. Data fetching from QVD(QVW2.QVW)

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 D:\QVD\dataqvd.qvd(QVD) GROUP BY doc_no;

Note:

When I view the Internal Table View. The result is coming some what in following fashion:-

doc_no  doc_dt      doc_sal

2           201008     25

My Expecation/Require Result should be as follows:-

doc_no  doc_dt       doc_sal

1           25-08-14      11

2           26-08-14       9

I Hope, I had given the require script. Can u pls take care rest all

Adv Thanks

SD

[anbu1984 · Accepted solution]

Try this

resitab:

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

2, 26/8/2014,9];

Store resitab into dataqvd.qvd(qvd);

Drop table resitab;

Final:

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 dataqvd.qvd(QVD) GROUP BY doc_no;

[#7]

Hi Anbu,

Thanks for reponse. FYI, it is working fine. Thanks once again.

Regards

SD