trying to loop through records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

trying to loop through records

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2011-05-05 : 08:55:42

Good morning,

I'm not sure where exactly to start here so I thought I would ask.

I'm trying to get a flat file in a specific format so I thought I would try it with a simpler example. What I need it for the SQL statement to present data like so:


VCHR_HDR_STG   CH512123  
VCHR_LINE_STG  CH512123
VCHR_DIST_STG  CH512123
 
--if there is more than one record in the line and dist show them too:
VCHR_LINE_STG  CH512123
VCHR_DIST_STG  CH512123

--then start again with the next record:
VCHR_HDR_STG   CH512124
VCHR_LINE_STG  CH512124
VCHR_DIST_STG  CH512124

I just created three tables so I could get the idea with a simple idea I was thinking maybe CTE would work but I'm not really sure how to go about it.

The three tables are like so:


SELECT * 
FROM dbo.VCHR_HDR_STG 

SELECT *
FROM dbo.VCHR_LINE_STG

SELECT *
FROM dbo.VCHR_DIST_STG

This gives me :


FILE_LABEL                                         VOUCHER_NUMBER
VCHR_HDR_STG                                       CH512123  
VCHR_HDR_STG                                       CH512124  
VCHR_HDR_STG                                       CH512125  

(3 row(s) affected)

FILE_LABEL                                         VOUCHER_NUMBER
VCHR_LINE_STG                                      CH512123
VCHR_LINE_STG                                      CH512124
VCHR_LINE_STG                                      CH512125
VCHR_LINE_STG                                      CH512123
VCHR_LINE_STG                                      CH512124

(5 row(s) affected)

FILE_LABEL                                         VOUCHER_NUMBER
VCHR_DIST_STG                                      CH512123
VCHR_DIST_STG                                      CH512124
VCHR_DIST_STG                                      CH512125
VCHR_DIST_STG                                      CH512123
VCHR_DIST_STG                                      CH512124

(5 row(s) affected)

Can anyone point me in the right direction for doing this?

Thanks so much.

Laura

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-05 : 13:01:37

Not sure this will completely meet your requirement. I was not clear on what if anything associates a given row in VCHR_LINE_STG to a row in VCHR_DIST_STG in addition to the VOUCHER_NUMBER. In any case, this should give you some data, even if it is not ordered exactly as you would like it. The ordering logic can be tweaked.

with cte as
(select
   'VCHR_HDR_STG' as SourceTable,
   0 as RN,
   *
from
   dbo.VCHR_HDR_STG 
union all
select
   'VCHR_LINE_STG' as SourceTable,
   ROW_NUMBER() over (partition by VOUCHER_NUMBER order by (select null)),
   *
from
   dbo.VCHR_LINE_STG
union all
select
   'VCHR_DIST_STG' as SourceTable,
   ROW_NUMBER() over (partition by VOUCHER_NUMBER order by (select null)),
   *
from
   dbo.VCHR_DIST_STG
)   
select
   *
from
   cte
order by
   VOUCHER_NUMBER,
   case when SourceTable = 'VCHR_HDR_STG' then 0 else 1 end,
   RN

mayerl
Yak Posting Veteran

95 Posts

Posted - 2011-05-05 : 15:41:04

Thanks for that. I'll give it a shot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources