export to a formatted file? 1

[leploep]

In SQL we have an order-header table and an order-lines table.
For our financial software we need to create a formatted csv file wich will have to look like this:

"header","ordernbr","company","adress",etc.
"line","productnbr","Title","quantity","price",etc.
"line","productnbr2","Title2","quantity2","price2",etc.
"header","ordernbr2","company2","adress2",etc.
"line","productnbr","Title","quantity","price",etc.
"line","productnbr2","Title2","quantity2","price2",etc.

So we need to create some sort of a loop that exports the orderheader first and then puts the orderlines from that same order in the next line(s).

In SQL both tables contain the ordernbr, but in our financial software only the orderheader contains this number. Thats why we need the csv file in this format.

I hope someone could give us a hint or at least a little nudge in the right direction.

Thanks in advance for your time and wisdom...

Regards

Rene

 

[mrdenny]

I'm thinking something with a union.

select Something, something_else
from (select ordernbr, 'a' hidden1,  something, something_else
from header_table
union all
select ordernbr, 'b' hidden1, other_stuff, other_stuff2
from lines_table
order by ordernbr, 2) a

This will return everything via the union, ordered by the ordernbr (this will get the line items and the header info together with the line item info and in the correct order via the ORDER BY). Then it will strip the un-needed columns and return what's left for your file.

Because of the union all be sure that the number of columns being returned from each half of the union are the same. You may need to pad one with blank columns to make it work.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[leploep]

Thanks so much!

this was absolutely very helpfull!

 

[mrdenny]

no problem.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)