• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • Last Modified:

Join Header Record, Detail Record and create Trailer Record

Hi Experts,
I have following Question.

I have a TABLE with HEADER RECORDS
FIELDS
______
RISNR
DTYPE
CLIENT
POLNUM
CSTREF
TITLE
INITS
SURNAM
ADD1
ADD2
ADD3
ADD4
...

AND A TABLE with DETAIL RECORDS
FIELDS
_______
RISNR
PARTYP
PARTNO
PDSC1
QTYUSD

And I need a result like that (RISNR is the Key):

DTYPE;CLIENT;POLNUM;CSTREF;TITLE;INITS;SURNAM;ADD1;ADD2;ADD3;ADD4;...; TOTCST <--- HEADER RECORD
PARTYP;PARTNO;PDSC1;QTYUSD <----- DETAIL RECORDS
PARTYP;PARTNO;PDSC1;QTYUSD
T;[Number of Header Records];SUM[TOTCST] <----- TRAILER RECORD

Could you please give a hand. Thanks a lot in advance.

Klaus


0
schulzek
Asked:
schulzek
  • 3
  • 2
1 Solution
 
adatheladCommented:
IMHO, you need to return 3 recordsets. Because you have 3 completely different sets of fields you want to return, you realistically shguldn't try to return all 3 as one recordset as it's more complicated (you'd have to generically name each field in the recordset i.e. Field 1, Field 2, Field 3 etc and each record would need an identifier field to describe what data is in that record (e.g. HEADER, DETAIL, TRAILER). This in turn would mean you would have to have special handling of the recordset along the lines of:

If identifier field = HEADER then FIeld1 = DTYPE, Field2 = POLNUM etc
else if identifier field = DETAIL then Field1 = PARTYP, PARTNO etc
etc

So, I'd recommend 3 different stored procs, each taking a RISR value as a parameter.
CREATE PROCEDURE prGetHeader
    @RISNR INTEGER
AS
SET NOCOUNT ON

SELECT RISNR,DTYPE,CLIENT,POLNUM,CSTREF,TITLE,INITS,SURNAM,ADD1,ADD2,ADD3,ADD4,TOTCST
FROM HeaderTable
WHERE RISNR = @RISNR
GO


CREATE PROCEDURE prGetDetail
    @RISNR INTEGER
AS
SET NOCOUNT ON

SELECT PARTYP,PARTNO,PDSC1,QTYUSD
FROM DetailTable
WHERE RISNR = @RISNR
GO


CREATE PROCEDURE prGetDetail
    @RISNR INTEGER
AS
SET NOCOUNT ON

SELECT T, COUNT(1) AS NoOfHeaderRecords, SUM(TOTCST) AS TOTCSTSum
FROM HeaderTable
WHERE RISNR = @RISNR
GO

You prob may not even need the third procedure as the record count/SUM could be worked out in the front end.

Hope this helps

0
 
schulzekAuthor Commented:
Thanks - So I made the 3 Proc, it makes sense

Field1 = H(Header), P(Detail), T(Trailer)
Fied2 = RISNR

I did the
-Header Table like
Field1, Field2,Field3,...Field20
H, BA1234, 123,...., 60.00
H, BY1234, 123,...., 66.00

-Detail - Table
Field1, Field2,Field3
P, BA1234, part1, 10.00
P, BA1234, part2, 20.00
P, BA1234, part3, 30.00
P, BY1234, part1, 11.00
P, BY1234, part2, 22.00
P, BY1234, part3, 33.00

Trailer

T, Count(NOofHEADERrec), Sum(Field20)
T,2,126.00

How get i the result like
H, BA1234, 123,...., 60.00
P, BA1234, part1, 10.00
P, BA1234, part2, 20.00
P, BA1234, part3, 30.00
H, BY1234, 123,...., 66.00
P, BY1234, part1, 11.00
P, BY1234, part2, 22.00
P, BY1234, part3, 33.00
T,2,126.00

Klaus
0
 
adatheladCommented:
You can't return a recordset exactly like that because header/details/trailer recordsets all have different numbers of fields.

What I was actually saying was call these 3 procedures separately from your front end and handle the display there.

Perhaps you can explain what you want to do with the data?
0
 
schulzekAuthor Commented:
Thank for your quick answer:
yes, i know!!
Is it Possible to make a Header1, Detail1, Trailer1 Table with 1 Field like:
Header1.Field1 = header.field1 & ";" & header.field2 & ";" ....
Detail1.field1 = detail.field1 & ";" & detail.field2 & ";" ....
Trailer1.field1 = trailer.field1 & ";" & Trailer.field2 & ";" ....

Select Field1 from Header1
Union all
Select Field1 from Detail1
Union all
Select Field1 From Trailer1

But i need for an export - file (smicolom as separator) with following result to send it to a Company

H; BA1234; 123;....; 60.00
P; BA1234; part1; 10.00
P; BA1234; part2; 20.00
P; BA1234; part3; 30.00
H; BY1234; 123;....; 66.00
P; BY1234; part1; 11.00
P; BY1234; part2; 22.00
P; BY1234; part3; 33.00
T;2;126.00

Thanks for your efforts :-))


0
 
adatheladCommented:
Aah, I'm with you now!

Ok, to do this you don't need the 3 procedures. Give this a try:

CREATE PROCEDURE prNameThisProcedure
    @RISNR INTEGER
AS
SET NOCOUNT ON

SELECT 'H;' + DTYPE + ';' + CLIENT + ';' + ....
FROM HeaderTable WHERE RISNR = @RISNR
UNION ALL
SELECT 'P;' + PARTYP + ';' + PARTP + ';' + PDSC1 + ';' + CAST(QTYUSD AS VARCHAR)
FROM DetailTable WHERE RISNR = @RISNR
UNION ALL
SELECT 'T;' + CAST(COUNT(1) AS VARCHAR) + ';' + CAST(SUM(TOTCST))
FROM HeaderTable WHERE RISNR = @RISNR
GO

You need to make sure that you use CAST(<fieldname> AS VARCHAR) for any fields that are numeric otherwise you will get an error when it tries to add a string to a number.
Does this make sense?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now