Better Structured Case Or Coalesce

[Ringers]

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

 

[markros]

You don't need ELSE NULL in all your cases, as by default ELSE always evaluates to NULL. Other than that, I'd leave it as is.

PluralSight Learning Library

 

[Olaf Doschke]

Looks like you could simply put RowID=1 in a outer join condition instead of the whole CASE, if you join that to some other table.

The whole thing doesn't make much sense to me, if I assume you're actually just picking one record via RowID.

Bye, Olaf.