March 16, 2006 at 11:50 am
Row from mnt_Header
Related rows from mnt_MaintenanceRows and mnt_PaymentRows
....repeate to next header
I want it to be just like this (in this example, there is no PK/FK relationship but luckily in mine there is):
right now, it's doing this:
h.BatchDate +
h.NotUsed +
h.TransactionCode +
h.GrossBatchTotal +
h.NetBatchTotal +
h.BatchTransactionCount +
h.PNet_ID +
h.PartnerCode +
h.Filler as HeaderRow,
h.PNet_ID as FilePath
FROM mnt_Header h
UNION ALL
(SELECT m.mnt_HeaderID as mnt_HeaderID,
m.TransactionDate +
m.TransactionTime +
m.AccountNumber +
m.TransactionCode +
m.FieldCode +
m.NewValue +
m.InternalExternalFlag +
m.PNetID +
m.RecovererID +
m.LoanCode +
m.NotUsed as mnt_Row,
' '
FROM mnt_MaintenanceRows m)
UNION ALL
(SELECT p.mnt_HeaderID as pmt_HeaderID,
p.TransactionDate +
p.TransactionTime +
p.AccountNumber +
p.TransactionCode +
p.TransactionAmount +
p.InterestFlag +
p.SelfDirectedFlag +
p.TransactionDesc +
p.NetPaymentAmount +
p.CommissionPercent +
p.InternalExternalFlag +
p.PNetID +
p.RecovererID +
p.RMSLoanCode +
p.Filler as pmt_Row,
' '
FROM mnt_PaymentRows p)
How can I do an ORDER BY..how to form that within the UNION or grouping, whatever it takes to get the files in the order like in the txt file agani!!!!!!!!!
(
SELECT h.mnt_HeaderID as hdr_HeaderID,
h.BatchDate +
h.NotUsed +
h.TransactionCode +
h.GrossBatchTotal +
h.NetBatchTotal +
h.BatchTransactionCount +
h.PNet_ID +
h.PartnerCode +
h.Filler as HeaderRow,
h.PNet_ID as FilePath
FROM mnt_Header h
UNION ALL
(SELECT m.mnt_HeaderID as mnt_HeaderID,
m.TransactionDate +
m.TransactionTime +
m.AccountNumber +
m.TransactionCode +
m.FieldCode +
m.NewValue +
m.InternalExternalFlag +
m.PNetID +
m.RecovererID +
m.LoanCode +
m.NotUsed as mnt_Row,
' '
FROM mnt_MaintenanceRows m)
UNION ALL
(SELECT p.mnt_HeaderID as pmt_HeaderID,
p.TransactionDate +
p.TransactionTime +
p.AccountNumber +
p.TransactionCode +
p.TransactionAmount +
p.InterestFlag +
p.SelfDirectedFlag +
p.TransactionDesc +
p.NetPaymentAmount +
p.CommissionPercent +
p.InternalExternalFlag +
p.PNetID +
p.RecovererID +
p.RMSLoanCode +
p.Filler as pmt_Row,
' '
FROM mnt_PaymentRows p)
)
)
ORDER BY hdr_HeaderID, mnt_HeaderID, mnt_HeaderID
March 16, 2006 at 12:01 pm
When using a UNION or a UNION ALL, the order by clause applies to the entire set..since you want to order per set and then in them on the ID columns, you can do this:
SELECT 1 AS ID_VAL,
h.mnt_HeaderID as HeaderID,
h.BatchDate +
....other stuff here
FROM mnt_Header h
UNION ALL
SELECT 2 AS ID_VAL,
m.mnt_HeaderID as HeaderID,
m.TransactionDate +
....other stuff here
FROM mnt_MaintenanceRows m
UNION ALL
SELECT 3 AS ID_VAL,
p.mnt_HeaderID as HeaderID,
p.TransactionDate +
....other stuff here
FROM mnt_PaymentRows p
ORDER BY ID_VAL, HEADERID
March 16, 2006 at 12:11 pm
Well, I guess ORDER BY isn't what I want then. I'm still not gettting Header / rows, Header / Rows combinations. Also, I need to be able to name each HeaderID differently since this View will be used in SSIS 2005 and I need to be able to distinguish the PK / FK field Names when I reference fields from components in SSIS.
So what will give me the grouping like I want between Header Row and corresponding Detail rows? This doesn't give me what I want...it is still ordering all header rows, followed by all maintenance rows, followed by all payment rows....not what I want:
SELECT 1 as ID_VAL,
h.mnt_HeaderID as HeaderID,
h.BatchDate +
h.NotUsed +
h.TransactionCode +
h.GrossBatchTotal +
h.NetBatchTotal +
h.BatchTransactionCount +
h.PNet_ID +
h.PartnerCode +
h.Filler as HeaderRow,
h.PNet_ID as FilePath
FROM mnt_Header h
UNION ALL
SELECT 2 as ID_VAL,
m.mnt_HeaderID as HeaderID,
m.TransactionDate +
m.TransactionTime +
m.AccountNumber +
m.TransactionCode +
m.FieldCode +
m.NewValue +
m.InternalExternalFlag +
m.PNetID +
m.RecovererID +
m.LoanCode +
m.NotUsed as mnt_Row,
' '
FROM mnt_MaintenanceRows m
UNION ALL
SELECT 3 as ID_VAL,
p.mnt_HeaderID as HeaderID,
p.TransactionDate +
p.TransactionTime +
p.AccountNumber +
p.TransactionCode +
p.TransactionAmount +
p.InterestFlag +
p.SelfDirectedFlag +
p.TransactionDesc +
p.NetPaymentAmount +
p.CommissionPercent +
p.InternalExternalFlag +
p.PNetID +
p.RecovererID +
p.RMSLoanCode +
p.Filler as pmt_Row,
' '
FROM mnt_PaymentRows p
ORDER BY ID_VAL, HEADERID
March 16, 2006 at 12:29 pm
Then you need to do a join between the header table and the two child tables (do an outer join if the record existence is not guaranteed in the two child tables) and then do an ordering on the header table's PKand the dummy column. For the join conditions, use the PK/FK columns that establish the relationship, example:
declare @header table (id_col int, col1 varchar(100))
declare @Detail_1 table (id_col int, seq_nbr int, col2 varchar(100))
declare @Detail_2 table (id_col int, seq_nbr int, col3 varchar(100))
insert into @header values (1, 'header 1')
insert into @header values (2, 'header 2')
insert into @header values (3, 'header 3')
insert into @Detail_1 values (1, 1, 'header 1 Detail 1')
insert into @Detail_1 values (1, 2, 'header 1 Detail 2')
insert into @Detail_1 values (2, 1, 'header 2 Detail 1')
insert into @Detail_2 values (2, 1, 'header 2 Detail 1')
insert into @Detail_2 values (2, 2, 'header 2 Detail 2')
insert into @Detail_2 values (2, 3, 'header 2 Detail 3')
insert into @Detail_2 values (3, 1, 'header 3 Detail 1')
select '*THIS IS THE HEADER*' as HDR_DTL_DESC, H.id_col, H.col1, null as Detail_1_Desc, null as Detail_2_Desc, 1 as col from @header H
union all
select ' THESE ARE MY DETAILS ',
H.ID_COL,
null as HDR_DESC,
D1.COL2 as Detail_1_Desc,
D2.COL3 as Detail_2_Desc,
2 as col
from @header H
left outer join @Detail_1 D1
on H.id_col = D1.id_col
left outer join @Detail_2 D2
on H.id_col = D2.id_col
order by H.ID_COL, col
--Output
Hdr_Dtl_Desc id_col col1 Detail_1_Desc Detail_2_Desc col
*THIS IS THE HEADER* 1 header 1 NULL NULL 1
THESE ARE MY DETAILS 1 NULL header 1 Detail 1 NULL 2
THESE ARE MY DETAILS 1 NULL header 1 Detail 2 NULL 2
*THIS IS THE HEADER* 2 header 2 NULL NULL 1
THESE ARE MY DETAILS 2 NULL header 2 Detail 1 header 2 Detail 1 2
THESE ARE MY DETAILS 2 NULL header 2 Detail 1 header 2 Detail 2 2
THESE ARE MY DETAILS 2 NULL header 2 Detail 1 header 2 Detail 3 2
*THIS IS THE HEADER* 3 header 3 NULL NULL 1
THESE ARE MY DETAILS 3 NULL NULL header 3 Detail 1 2
March 16, 2006 at 12:37 pm
Also, by design, the UNION isn't giving me all columns back, just
hdr_HeaderID, HeaderRow, and FilePath
I need the rows from the maintenance and Paymetn selects also!
March 16, 2006 at 12:57 pm
I'm trying to take what you did and try it on mine....
March 16, 2006 at 12:57 pm
It looks like y ou're filling in null in for fillers. I'm not able to do that in mine becasue the # of columns and length of columns vary so differently from the 3 tables that I cannot match up and create fillers for some columns that exist in one table but not the other simply because the number of columns is so drastically different both in number and size
March 16, 2006 at 1:00 pm
not there yet, still working on it
SELECT h.mnt_HeaderID as hdr_HeaderID,
h.BatchDate +
h.NotUsed +
h.TransactionCode +
h.GrossBatchTotal +
h.NetBatchTotal +
h.BatchTransactionCount +
h.PNet_ID +
h.PartnerCode +
h.Filler as HeaderRow,
h.PNet_ID as FilePath
FROM mnt_Header h
UNION ALL
SELECT m.mnt_HeaderID as MAINT_HeaderID,
m.TransactionDate +
m.TransactionTime +
m.AccountNumber +
m.TransactionCode +
m.FieldCode +
m.NewValue +
m.InternalExternalFlag +
m.PNetID +
m.RecovererID +
m.LoanCode +
m.NotUsed as mnt_Row,
' '
FROM mnt_MaintenanceRows m
UNION ALL
SELECT p.mnt_HeaderID as PMT_HeaderID,
p.TransactionDate +
p.TransactionTime +
p.AccountNumber +
p.TransactionCode +
p.TransactionAmount +
p.InterestFlag +
p.SelfDirectedFlag +
p.TransactionDesc +
p.NetPaymentAmount +
p.CommissionPercent +
p.InternalExternalFlag +
p.PNetID +
p.RecovererID +
p.RMSLoanCode +
p.Filler as pmt_Row,
' '
FROM mnt_PaymentRows p
LEFT JOIN mnt_MaintenanceRows mr ON mr.mnt_HeaderID = h.mnt_HeaderID
LEFT JOIN mnt_PaymentRows pr ON pr.mnt_HeaderID = h.mnt_HeaderID
March 16, 2006 at 1:04 pm
forgot a few things
SELECT 1 as col,
h.mnt_HeaderID as hdr_HeaderID,
h.BatchDate +
h.NotUsed +
h.TransactionCode +
h.GrossBatchTotal +
h.NetBatchTotal +
h.BatchTransactionCount +
h.PNet_ID +
h.PartnerCode +
h.Filler as HeaderRow,
h.PNet_ID as FilePath
FROM mnt_Header h
UNION ALL
SELECT 2 as col,
m.mnt_HeaderID as MAINT_HeaderID,
m.TransactionDate +
m.TransactionTime +
m.AccountNumber +
m.TransactionCode +
m.FieldCode +
m.NewValue +
m.InternalExternalFlag +
m.PNetID +
m.RecovererID +
m.LoanCode +
m.NotUsed as mnt_Row,
' '
FROM mnt_MaintenanceRows m
UNION ALL
SELECT 3 as col,
p.mnt_HeaderID as PMT_HeaderID,
p.TransactionDate +
p.TransactionTime +
p.AccountNumber +
p.TransactionCode +
p.TransactionAmount +
p.InterestFlag +
p.SelfDirectedFlag +
p.TransactionDesc +
p.NetPaymentAmount +
p.CommissionPercent +
p.InternalExternalFlag +
p.PNetID +
p.RecovererID +
p.RMSLoanCode +
p.Filler as pmt_Row,
' '
FROM mnt_PaymentRows p
LEFT JOIN mnt_MaintenanceRows mr ON mr.mnt_HeaderID = h.mnt_HeaderID
LEFT JOIN mnt_PaymentRows pr ON pr.mnt_HeaderID = h.mnt_HeaderID
ORDER BY h.mnt_HeaderID, col
ERROR:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "h.mnt_HeaderID" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "h.mnt_HeaderID" could not be bound.
March 16, 2006 at 1:08 pm
I'm not sure why it's not allowing the ORDER BY like yours had
FYI...took out the aliases
SELECT 1 as col,
mnt_HeaderID as hdr_HeaderID,
BatchDate +
NotUsed +
TransactionCode +
GrossBatchTotal +
NetBatchTotal +
BatchTransactionCount +
PNet_ID +
PartnerCode +
Filler as HeaderRow,
PNet_ID as FilePath
FROM mnt_Header h
UNION ALL
SELECT 2 as col,
mnt_HeaderID as MAINT_HeaderID,
TransactionDate +
TransactionTime +
AccountNumber +
TransactionCode +
FieldCode +
NewValue +
InternalExternalFlag +
PNetID +
RecovererID +
LoanCode +
NotUsed as MaintenceDetailRow,
' '
FROM mnt_MaintenanceRows
UNION ALL
SELECT 3 as col,
mnt_HeaderID as PMT_HeaderID,
TransactionDate +
TransactionTime +
AccountNumber +
TransactionCode +
TransactionAmount +
InterestFlag +
SelfDirectedFlag +
TransactionDesc +
NetPaymentAmount +
CommissionPercent +
InternalExternalFlag +
PNetID +
RecovererID +
RMSLoanCode +
Filler as PaymentDetailRow,
' '
FROM mnt_PaymentRows p
LEFT OUTER JOIN mnt_MaintenanceRows m ON m.mnt_HeaderID = h.mnt_HeaderID
LEFT OUTER JOIN mnt_PaymentRows pr ON pr.mnt_HeaderID = h.mnt_HeaderID
ORDER BY h.mnt_HeaderID, col
ERROR:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
March 16, 2006 at 1:18 pm
THE PROBLEM:
I cannot have the same # of rows, so UNION is not going to work for me. The end result must have certain columns for the header, maintenance, and payment rows...they're all different. I cannot split up any of the sequence of these rows with nulls or spaces because this ultimately is goign to be put in a 100 byte txt file just like the incoming one. If you notice the incoming file, you can't just put in a space for the field NotUsed in for a maintenance row because you cannot disrupt the maintenance row format. I have set formats (0-8 is this column, 9-15 is this column) that my txt output file has to maintain.
March 16, 2006 at 1:19 pm
check this out to help you understand. The format of the rows in the original txt file is this
headerrow:
http://www.webfound.net/headerrow_fixedlength.jpg
maintenance rows:
http://www.webfound.net/manitrow_fixedlength.jpg
payment rows:
http://www.webfound.net/paymentrow_fixedlength.jpg
because of the variations in field length, there is no way I can just put in the same # of columns using fillers
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
