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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy