August 7, 2011 at 6:47 pm
Hi All,
I was wondering if there was a better way to structure my case statement, as you can see it is a bit long 🙂 maybe with Coalesce?
SELECT CASE WHEN RowId = 1 THEN Invoice_id
ELSE null
END AS [Invoice_id],
CASE WHEN RowId = 1 THEN IssueDate
ELSE NULL
END AS [Inovice Date],
CASE WHEN RowId = 1 THEN optional_1
ELSE NULL
END AS [Division\Buyer],
CASE WHEN RowId = 1 THEN ScanDate
ELSE NULL
END AS [Scan Date],
CASE WHEN RowId = 1 THEN Supplier
ELSE NULL
END AS [Supplier\Vendor],
CASE WHEN RowId = 1 THEN optional_2
ELSE NULL
END AS [PONumber],
CASE WHEN RowId = 1 THEN invoicenumber
ELSE NULL
END AS [Invoice Number],
CASE WHEN RowId = 1 THEN rejected
ELSE NULL
END AS [Rejected],
CASE WHEN RowId = 1 THEN completed
ELSE NULL
END AS [Transaction Date],
CASE WHEN RowId = 1 THEN Notified
ELSE NULL
END AS [Notified],
CASE WHEN RowId = 1 THEN Comments
ELSE NULL
END AS [Comments],
CASE WHEN RowId = 1 THEN Transferred
ELSE NULL
END AS [Transferred],
CASE WHEN RowId = 1 THEN Optional_3
ELSE NULL
END AS [InvoiceType],
CASE WHEN RowId = 1 THEN InvoiceTotal
ELSE NULL
END AS [Total Invoice Amount],
CASE WHEN RowId = 1 THEN Optional_4
ELSE NULL
END AS [TFN],
CASE WHEN RowId = 1 THEN Optional_5
ELSE NULL
END AS [Orginator],
CASE WHEN RowId = 1 THEN Amount_1
ELSE NULL
END AS [GST Total Amount],
CASE WHEN RowId = 1 THEN Responsibleh
ELSE NULL
END AS [Responsible 1],
CASE WHEN RowId = 1 THEN creditnote
ELSE NULL
END AS [Credit Note],
CASE WHEN RowId = 1 THEN Currency
ELSE NULL
END AS [Currency],
CASE WHEN RowId = 1 THEN PaymentDate
ELSE NULL
END AS [BCC Upload Date 1],
CASE WHEN RowId = 1 THEN Optional_7
ELSE NULL
END AS [Supplier Code],Account,Optional_6 AS [Job Number], Optional_8 AS [Cost Type Code],Department AS [Department Code],Optional_7l AS [Employee Code],
optional_1l AS [Product Code],optional_9l AS [Campaign Code], linetext AS [Description], Approver AS [Completed By],
optional_3l AS [Division Code], approvedatel AS [BCC Upload Date], optional_5l AS [Client Code],remarks,
amount_2l AS [Total Excl GST], amount3l AS [GST], linetotal AS [Total Incl GST], line_id
August 7, 2011 at 7:15 pm
Only 2 things can be happening here. Either you're doing a pivot (and missing the max() function)
or you could perharps just move this in the where clause (on the left join cause depending on the exact requirements)
WHERE RowId = 1
Can you explain in a little more details what the ouput needs to be and maybe post the rest of the query so we have the whole picture?
August 10, 2011 at 1:57 pm
could try this maybe:
WITH cte AS
(
SELECT
[SomeID],
[Invoice_id],
[Invoice Date],
[Division\Buyer],
[etc...]
FROM [SomeTable]
WHERE RowID = 1
)
SELECT
[SomeID],
[Invoice_id],
[Invoice Date],
[Division\Buyer],
[etc...]
[other fields...]
FROM [SomeTable]
LEFT JOIN cte ON [SomeID] = [SomeOtherID]
August 11, 2011 at 2:32 pm
I think the code already posted is a good reply. As an addendum, you might also check out the nullif() function.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply