March 17, 2006 at 8:27 am
How can I take this query and order it so all the mnt_HeaderIDs that match are in sequence
right now, it produces this: http://www.webfound.net/current.txt
What I want it to do is to look like this: http://www.webfound.net/ordered.txt
As far as I've seen, doing an ORDER BY doesn't do it for me
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
March 17, 2006 at 8:35 am
"ORDER BY hdr_HeaderID, col " doesn't work? If not, I suggest that you make each of your headerid columns the same name. I think that SQL Server will only use names from the first query, but perhaps having different columns names confuses the Order by clause.
If not, wrap your query in another query with its own order by:
Select * from ( <your query> ) ORDER BY hdr_HeaderID, col
Hope this helps
Mark
March 17, 2006 at 8:36 am
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
ORDER BY 2 asc ,1 as
March 17, 2006 at 8:42 am
Hi,
You can use the following Query to solve This kind of Problem :
Select * from
(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
) ORDER BY 2 asc
I think it will be fine for you.
Regards,
Amit Gupta
March 17, 2006 at 8:59 am
Amit, right on man! thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply