Cast as

[Ringers]

Hi,

I am using a case statement to check for row numbers. But I want to change the format of a date and number fields.

1.
Current: June 29 2011 12:00 AM
Expected: dd/mm/yyyy(no seconds)

Code: CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20))
ELSE ''

2.
Current: 145671.39
--------------0
Expected:145671.39
---------null\blank

using float but it is adding a zero to the next row? it should be null.

Code: CASE WHEN RowId = 1 THEN CAST(InvoiceTotal AS FLOAT)
ELSE ''

Thanks for helping

 

[Rhys666]

The else should be ELSE NULL not an empty string and depending where you're viewing the output depends where the issue might be. Can you clarify if the output is being viewed in the results window in Management Studio, in SSRS, a UI or anything else?


Also, I'm not sure why you're doing this, but I would strongly suggest you change your logic and format the output at point of display, not in the SQL.



Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[Ringers]

Hey Rhys666

Thanks for the reply.

not sure what you mean by "Also, I'm not sure why you're doing this, but I would strongly suggest you change your logic and format the output at point of display, not in the SQL."?

I have designed a package to extract some data and then I will add the package to a daily job.

here is my full code to put it in context:

WITH cte ( Invoice_id, IssueDate,optional_1,scandate,supplier,optional_2,invoicenumber,rejected,completed,notified,comments,transferred,optional_3,invoiceTotal,optional_4,optional_5, amount_1,responsible,creditnote,currency,paymentdate,optional_7,
Account, Optional_6, optional_8,department,optional_7l,optional_1l,linetext,approver,optional_3l,approvedatel,optional_5l,responsiblel,remarks,amount_2l,amount3l,linetotal,RowId)
AS (
SELECT h.Invoice_id,h.IssueDate,h.optional_1,h.scandate,h.supplier,h.optional_2,h.invoicenumber,h.rejected,h.completed,h.notified,h.comments,h.transferred,h.optional_3,h.InvoiceTotal,h.optional_4,h.optional_5,h.amount_1,h.responsible,h.iscredit,h.currency,h.paymentdate,h.optional_7,
l.Account,l.optional_6,l.optional_8,l.Department,l.Optional_7,l.optional_1,l.linetext,l.approver,l.optional_3,l.approveDate,l.optional_5,l.Responsible,remarks,l.amount_2,l.amount_3,l.linetotal,
ROW_NUMBER() OVER ( PARTITION BY h.Invoice_id ORDER BY h.Invoice_id ASC )

FROM Companies c
JOIN Invoice_head h ON h.company_id = c.company_id
JOIN Invoice_lines l ON l.invoice_id = h.invoice_id

WHERE ( c.company_id = 35 )
AND ( l.ApproveDate >= ( CASE WHEN DATEPART(dw, GETDATE()) = 2 THEN ( GETDATE() - 3 )
ELSE ( GETDATE() - 1 )
END ) )
)

SELECT CASE WHEN RowId = 1 THEN CAST(Invoice_id AS VARCHAR(20))
ELSE ''
END AS [Invoice_id],
CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20))
ELSE ''
END AS [Inovice Date],
CASE WHEN RowId = 1 THEN CAST(optional_1 AS VARCHAR(50))
ELSE ''
END AS [Division\Buyer],
CASE WHEN RowId = 1 THEN CAST(ScanDate AS VARCHAR(20))
ELSE ''
END AS [Scan Date],
CASE WHEN RowId = 1 THEN CAST(Supplier AS VARCHAR(50))
ELSE ''
END AS [Supplier\Vendor],
CASE WHEN RowId = 1 THEN CAST(optional_2 AS VARCHAR(50))
ELSE ''
END AS [PONumber],
CASE WHEN RowId = 1 THEN CAST(invoicenumber AS VARCHAR(50))
ELSE ''
END AS [Invoice Number],
CASE WHEN RowId = 1 THEN CAST(rejected AS VARCHAR(20))
ELSE ''
END AS [Rejected],
CASE WHEN RowId = 1 THEN CAST(completed AS VARCHAR(20))
ELSE ''
END AS [Transaction Date],
CASE WHEN RowId = 1 THEN CAST(Notified AS VARCHAR(20))
ELSE ''
END AS [Notified],
CASE WHEN RowId = 1 THEN CAST(Comments AS VARCHAR(500))
ELSE ''
END AS [Comments],
CASE WHEN RowId = 1 THEN CAST(Transferred AS VARCHAR(20))
ELSE ''
END AS [Transferred],
CASE WHEN RowId = 1 THEN CAST(Optional_3 AS VARCHAR(50))
ELSE ''
END AS [InvoiceType],
CASE WHEN RowId = 1 THEN CAST(ScanDate AS VARCHAR(20))
ELSE ''
END AS [Scan Date],
CASE WHEN RowId = 1 THEN CAST(InvoiceTotal AS VARCHAR(20))
ELSE ''
END AS [Total Invoice Amount],
CASE WHEN RowId = 1 THEN CAST(Optional_4 AS VARCHAR(20))
ELSE ''
END AS [TFN],
CASE WHEN RowId = 1 THEN CAST(Optional_5 AS VARCHAR(50))
ELSE ''
END AS [Orginator],
CASE WHEN RowId = 1 THEN CAST(Amount_1 AS VARCHAR(20))
ELSE ''
END AS [GST Total Amount],
CASE WHEN RowId = 1 THEN CAST(Responsible AS VARCHAR(20))
ELSE ''
END AS [Responsible],
CASE WHEN RowId = 1 THEN CAST(creditnote AS VARCHAR(20))
ELSE ''
END AS [Credit Note],
CASE WHEN RowId = 1 THEN CAST(Currency AS VARCHAR(20))
ELSE ''
END AS [Currency],
CASE WHEN RowId = 1 THEN CAST(PaymentDate AS VARCHAR(20))
ELSE ''
END AS [BCC Upload Date 1],
CASE WHEN RowId = 1 THEN CAST(Optional_7 AS VARCHAR(20))
ELSE ''
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],linetext AS [Description], Approver AS [Completed By],
optional_3l AS [Division Code], approvedatel AS [BCC Upload Date], optional_5l AS [Client Code], Responsiblel AS [Responsible],remarks,
amount_2l AS [Total Excl GST], amount3l AS [GST], linetotal AS [Total Incl GST]
FROM cte

 

[Rhys666]

not sure what you mean by "Also, I'm not sure why you're doing th...is, but I would strongly suggest you change your logic and format the output at point of display, not in the SQL."?

What you are doing is not changing the format of your data but changing its type, and what I mean is that it is usually unnecessary, (and therefore an overhead that can be avoided,) to change the data type in this way.

The first thing I would do is change the CASE statements to not change the data type and output NULL not an empty string as the two things are completely different, i.e.,;

CASE
    WHEN RowId = 1 THEN IssueDate
    ELSE NULL
END AS [Invoice Date],

...and format display of the output in whatever application or report is being used to view this data. How are you viewing/displaying the output? i.e., Excel, SSRS, a.n.other etc. etc.

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[Ringers]

I have done what you suggested that seems to be a good idea.

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]

I am trying to put this a package designed in Visual Studio 2005.

 

[Ringers]

Hi,

I am getting an error when I try to use the code in BIDS\Visual studio 2005 as part of a package design. "The OVER SQL construct or statement is not support".

My coding is not that strong, does anyone know of a way to modify the code above so that it doesn't user the 'over'command?

 

[Rhys666]

What is the taget SQL Server version, 2000, 2005, 2008?

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[Ringers]

SQL 2005