how to fetch records from two different datasets?

Question

how to fetch records from two different datasets?

abhas

SSCarpal Tunnel

Points: 4681
More actions
July 31, 2008 at 6:10 am

#196378

hi,
I want to add records in my detail part from two different datasets.How to do yhat?
thanks
Abaso

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

skoch SSC Veteran Points: 215 More actions · Answer 1

Abaso,

Assuming there is no way to combine the data into one query the only way I am aware of doing this is by creating a sub-report. What exactly is it that you are trying to do?

Steve

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 2

Steve,

Actually i want to display prv months and current months records depend upon selection of date.So that i am using two different queries but it shoes records only from dataset which has assigned to table.

skoch SSC Veteran Points: 215 More actions · Answer 3

Abaso,

You should be able to do this within one dataset. You do this within the query by creating two sub tables, either within one query or you can just create the two tables as temp tables, and the final selection has both previous and current month data. Then it should not be too difficult to display the data on the report.

Let me know if you need more explanation on how to do this or if I am not getting what you are trying to do.

Steve

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 4

Thank u Steve,

Exact u got my problem.But will u tell me how to use temp tables?

skoch SSC Veteran Points: 215 More actions · Answer 5

Here are two basic ideas, keep in mind that I don't know the data and the tables, so things may need to be slightly different for you but the basics should be the same.

Inner tables:

SELECT prev_Month, curr_Month, prev_Data, curr_Data

FROM (SELECT prev_id, prev_Month, prev_Data

FROM tbl

WHERE Month = previous_month) prev

(SELECT curr_id, curr_Month, curr_Data

FROM tbl

WHERE Month = current_month) curr

WHERE prev_id = curr_id

Or you can also do a JOIN on the two tables.

Temp tables:

SELECT prev_id, prev_Month, prev_Data

INTO #prev_Month

FROM tbl

WHERE Month = previous_month

SELECT curr_id, curr_Month, curr_Data

INTO #curr_Month

FROM tbl

WHERE Month = curr_month

SELECT prev_Month, curr_Month, prev_Data, curr_Data

FROM #prev_Month prev

INNER JOIN curr_month curr ON prev_id = curr_id

The one consideration you need to keep in mind here is dropping the temp tables. Technically they should be dropped when the connection goes away but I am not totally convinced that happens. I usually drop the tables at the end of the script to be sure.

Let me know if you need clarifications on what I am saying here.

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 6

abhas

SSCarpal Tunnel

Points: 4681

August 1, 2008 at 7:50 am

#851881

thank u very much Steve,It's Working.

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 7

Steve I m giving 2 tables in brief

tbl1 contains date,Monthkey,prvmonthkey where monthky becomes prvmonthkey in next month.

tbl2 contains acs,amt.

so i have write nested query n its not working properly.

skoch SSC Veteran Points: 215 More actions · Answer 8

Hi Abaso,

Sorry for the delay I had a long weekend off. Can you post your query so that I can see what you are trying to do?

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 9

Hi Steve,

I solved my problem,i found very useful sql which u send

thank u