June 27, 2011 at 8:50 am
My query is a union all, the result set returns a "Header" row which comes from the invoice table, the "Detail" rows are returned from Invoice Expense and Invoice tax (both tables are joined on invoice key from the invoice table, and a "Trailer" row which is a count of only the "detail" rows. The "Trailer" query returns the invoice key and a count, however, once the group by is added, any "Detail" rows that return NULL do not create the "Trailer" row.
What I'm trying to get, is a "Trailer" record for each invoice key, and if there are NO Detail rows, than return a "Trailer" row with a count of zero.
I have tried left joins to no avail. Not sure if you need to see all the code including the header row or just the code for the detail and trailer rows, to help me with this, so just copying the code for the trailer and detail rows.
UNION ALL
SELECT I.InvoiceKey AS InvoiceKey
,'T^' AS RecordType
,@Delimeter AS H1
,@Delimeter AS H2
,@Delimeter AS H3
,@Delimeter AS H4
,@Delimeter AS H5
,@Delimeter AS H6
,@Delimeter AS H7
,@Delimeter AS H8
,@Delimeter AS H9
,@Delimeter AS H10
,@Delimeter AS H11
,@Delimeter AS H12
,@Delimeter AS H13
,@Delimeter AS H14
,@Delimeter AS H15
,@Delimeter AS D1
,@Delimeter AS D2
,@Delimeter AS D3
,@Delimeter AS D4
,RIGHT('00000' + CAST(COUNT(*) AS VARCHAR(6)), 6) AS TRC
,4 AS sort
FROM @HINFO H
INNER JOIN Invoice I WITH (NOLOCK) ON H.SCK = I.SalesContractKey
INNER JOIN InvoiceExpense IE WITH (NOLOCK) ON I.InvoiceKey = IE.InvoiceKey
INNER JOIN InvoiceTax IT WITH (NOLOCK) ON I.InvoiceKey = IT.InvoiceKey
WHERE I.InvoiceStatusKey <> 1
GROUP BY I.InvoiceKey
The sort column is a way for me to sort the rows so I always get the Header 1st then details, 2 and/or 3 and the trailer last a 4
Thanks for any help!!
Don
June 27, 2011 at 8:52 am
Try the ISNULL function:
DECLARE @nullablecolumn INT = NULL
SELECT ISNULL(@nullablecolumn, 0)
gsc_dba
June 27, 2011 at 8:58 am
Have tried ISNULL , doesn't work, still returns null
June 27, 2011 at 9:00 am
when no rows are returned for invoice expense and invoice tax, I still need a row to be returned with the invoice key from invoice and a count of zero
June 27, 2011 at 9:14 am
Difficult to answer your question with the data you've provided. See the below examples for how to work it out yourself.
DECLARE @TABLE AS TABLE(The_Group INT)
INSERT INTO @TABLE
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
DECLARE @DATA_TABLE AS TABLE (ID INT IDENTITY, The_Group INT, Data CHAR(1))
INSERT INTO @DATA_TABLE
SELECT 1, 'A'
UNION ALL SELECT 2, 'A'
--How you're doing it --> misses out the "0" for The_Group 3
SELECT a.The_Group, COUNT(*) AS cnt
FROM @TABLE a
INNER JOIN @DATA_TABLE b ON a.The_Group = b.The_Group
GROUP BY a.The_Group
--Include the "0"
SELECT a.The_Group, COALESCE(b.Cnt,0) AS Cnt
FROM (SELECT
The_Group
FROM @TABLE
GROUP BY The_Group) a
LEFT JOIN (SELECT a.The_Group, COUNT(*) AS cnt
FROM @TABLE a
INNER JOIN @DATA_TABLE b ON a.The_Group = b.The_Group
GROUP BY a.The_Group) b ON a.The_Group = b.The_Group
June 27, 2011 at 9:18 am
Pretty sure that nobody is going to be able to help you with such a vague question. We need some ddl and sample data along with the desired output from the sample data. This probably pretty straight forward but I am not at all clear what you are looking for. Please see the link in my signature for best practices on posting a question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2011 at 9:19 am
Edit: As mentioned above - need a little more info to be able to help...
Can you elaborate on the table structures?
gsc_dba
June 27, 2011 at 11:19 am
Invoice Key 159021 returns 1 invoice row, Invoice expense and Invoice Tax which have 0 rows with invoice key 159021 where invoice key is a foreign key in both those tables.
Invoice key 159078 returns 1 invoice row and has 1 tax row, hence a detail row
I am tasked with creating "header" "H" rows for all invoices, "detail" "D" rows when either invoice expense and invoice tax have an amount > 0 and a "trailer" "T" row with a count of all "D" rows, so if no "D" rows are present for an invoice, I still need to produce the "T" row with a count of zero.
output data from invoice key 159021: H^ is header & T^ is trailer record both start with invoice key
159021 H^ 260101^ Zale Corporation^ 035-200600001-OAO^ 035-200600001^ 01/26/2006^ 200601^ CT^ United States^ Broomfield^ 30^ US$^ United States^ Broomfield^ 30^ 13000.00^ ^ ^ ^ ^ ^ 1
159021 T^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ 000001 4
output date from invoice key 159078:
159078 H^ 181529^ RBC Financial Group^ 063-200600013-OAO^ 063-200600013^ 02/28/2006^ 200603^ CT^ United States^ Detroit^ 64^ FRF^ United States^ Detroit^ 43^ 3081.60^ ^ ^ ^ ^ ^ 1
159078 D^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ TAX^ ^ ^ 201.60^ ^ 3
159078 T^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ 000001 4
Hope this helps!
June 27, 2011 at 11:27 am
I'm guessing you didn't actually read the article I pointed you to. If you post some ddl, sample data (insert statements) and desired output with a clear explanation you will there are lots of people willing and able to help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2011 at 11:39 am
Please take the time to read the article previously referenced (happens to be the first article I reference below in my signature block). Following those guidelines will get you plenty of help, plus the benefit of tested code in return.
Also, based on your code, if there are no records in some of the child tables you will not get any data. Your code is using inner joins in places where it is apparent that you need to use left outer joins.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply