?
Solved

SQL Server 2000 View - cannot format a column to 2 decimals

Posted on 2011-10-28
3
Medium Priority
?
198 Views
Last Modified: 2012-05-12
I am trying to format the InvoiceControllingGrossPriceUnit to 2 decimals but when I try putting this in FORMAT(field,"0.00") it comes back as format is not a recognized function.  Is it something else in sql server 2000?

The code is attached.  Thanks!
SELECT     TOP 100 PERCENT MIN(dbo.FS_ARInvoiceHeader.InvoiceDate) AS INV_Date, dbo.FS_ARInvoiceLine.LineItemNumber AS Inv_Item, 
                      MIN(dbo.FS_ARInvoiceHeader.CommissionCode) AS ComCode, dbo.FS_Item.ItemDescription, dbo.FS_ARInvoiceLine.ShipQuantity, 
                      dbo.FS_ARInvoiceLine.CommissionCode, dbo.FS_ARInvoiceLine.CustomerKey, dbo.FS_ARInvoiceHeader.CustomerID, 
                      dbo.FS_ARInvoiceLine.InvoiceControllingGrossUnitPrice, dbo.SA_Customer.CustomerName, dbo.MCC_StdPriceBookJoin.PB_FORMULA
FROM         dbo.FS_ARInvoiceLine INNER JOIN
                      dbo.FS_ARInvoiceHeader ON dbo.FS_ARInvoiceLine.ARInvoiceHeaderKey = dbo.FS_ARInvoiceHeader.ARInvoiceHeaderKey INNER JOIN
                      dbo.FS_Item ON dbo.FS_ARInvoiceLine.ItemKey = dbo.FS_Item.ItemKey INNER JOIN
                      dbo.SA_Customer ON dbo.FS_ARInvoiceLine.CustomerKey = dbo.SA_Customer.CustomerKey INNER JOIN
                      dbo.MCC_StdPriceBookJoin ON dbo.FS_ARInvoiceLine.LineItemNumber = dbo.MCC_StdPriceBookJoin.ItemNumber
GROUP BY dbo.FS_ARInvoiceLine.LineItemNumber, dbo.FS_Item.ItemClass4, dbo.FS_Item.ItemDescription, dbo.FS_ARInvoiceLine.ShipQuantity, 
                      dbo.FS_ARInvoiceLine.CommissionCode, dbo.FS_ARInvoiceLine.CustomerKey, dbo.FS_ARInvoiceHeader.CustomerID, 
                      dbo.FS_ARInvoiceLine.InvoiceControllingGrossUnitPrice, dbo.SA_Customer.CustomerName, dbo.MCC_StdPriceBookJoin.PB_FORMULA
HAVING      (dbo.FS_Item.ItemClass4 = 'F') AND (MIN(dbo.FS_ARInvoiceHeader.InvoiceDate) 
                      > CONVERT(DATETIME, '2011-08-31 00:00:00', 102))

Open in new window

0
Comment
Question by:tgfo4927
3 Comments
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 1000 total points
ID: 37045560
try casting it

cast(dbo.FS_ARInvoiceLine.InvoiceControllingGrossUnitPrice as decimal(18,2))
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1000 total points
ID: 37046186
try this

...


format is a MS-ACCESS function ... in ms sql server use convert/cast either as a decimal or money datatype

you shouldn't use top 100 percent it isnt required (and from sql 2008 its behaviour is corrected)

avoid putting conditions in the Having clause which should be performed in the where clause
that can greatly reduce the set of data which needs to be processed..


SELECT    MIN(IH.InvoiceDate) AS INV_Date, IL.LineItemNumber AS Inv_Item, 
          MIN(IH.CommissionCode) AS ComCode, Itm.ItemDescription, IL.ShipQuantity, 
          IL.CommissionCode, IL.CustomerKey, IH.CustomerID, 
          convert(decimal(18,2),IL.InvoiceControllingGrossUnitPrice) as InvoiceControllingGrossUnitPrice
         , C.CustomerName, PB.PB_FORMULA
FROM         dbo.FS_ARInvoiceLine as IL
INNER JOIN   dbo.FS_ARInvoiceHeader as IH ON IL.ARInvoiceHeaderKey = IH.ARInvoiceHeaderKey 
INNER JOIN   dbo.FS_Item as ITM ON IL.ItemKey = Itm.ItemKey 
INNER JOIN   dbo.SA_Customer as C ON IL.CustomerKey = C.CustomerKey 
INNER JOIN   dbo.MCC_StdPriceBookJoin as PB ON IL.LineItemNumber = PB.ItemNumber
where (Itm.ItemClass4 = 'F')
GROUP BY IL.LineItemNumber, Itm.ItemDescription, IL.ShipQuantity, 
                      IL.CommissionCode, IL.CustomerKey, IH.CustomerID, 
                      IL.InvoiceControllingGrossUnitPrice, C.CustomerName, PB.PB_FORMULA
HAVING   (MIN(IH.InvoiceDate) 
                      > CONVERT(DATETIME, '2011-08-31 00:00:00', 102))

Open in new window

0
 

Author Closing Comment

by:tgfo4927
ID: 37046848
Thanks!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question