Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gschwab
Starting Member
7 Posts |
 Posted - 2014-04-09 : 15:02:03
|
| I'm not sure if I can explain this properly, but I'll try. I need to create an output file with a detailed record. I've been able to create a query that will give me the format and fields of what I need. However, the file layout calls for an additional "B" record if certain criteria is met in the detail immediately following the detail record. For example, if the PaymentMethodID = 10, then I need to add a "B" record that displays the PayeeName. I'm not sure if this would be a separate Select or a Union or maybe an Insert? All of our current layout are just Detail records and a Control Record.Here is my current Select for the Detail:SELECT DISTINCT CONVERT(CHAR(10), ba.AccountNumber) + (REPLICATE('0', 10 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(10), p.CheckNumber)) + REPLACE(CONVERT(VARCHAR(6), p.CheckDate, 1), '/', '') + (REPLICATE('0', 10 - LEN(replace(p.checkamount,'.',''))) + CONVERT(VARCHAR(10), case p.paymentmethodid WHEN 10 THEN REPLACE(p.checkamount,'.','') WHEN 50 THEN REPLACE(p.checkamount,'.','') else 0 END))+ CONVERT(CHAR(2), '')+ CASE p.PaymentMethodID WHEN 60 THEN '1' WHEN 50 THEN '1' ELSE '0' END + CASE p.PaymentMethodID WHEN 60 THEN 'T' WHEN 50 THEN 'T' ELSE 'A' ENDAS exportpaymenthsbc Does anyone have any advise on what I would need to add a separate B record?Thanks for any help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-04-13 : 14:41:21
|
| Sounds like a UNION ALL query to me. But with given limited info cant give a suggestion. Can you post some sample data and give expected output from it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|
|
Adding a "B" Record to my output file