concatenate numeric and varchar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

concatenate numeric and varchar

Author

Topic

jullgunit
Starting Member

4 Posts

Posted - 2009-02-11 : 02:07:03

hi.. i have code as below

SELECT DISTINCT
SLRXX_ProductGroup.ProductGroupName ,
SUM(SLDXX_InvoiceDetail.Qty) AS Quantity ,
'RM' + (CAST(CAST(SUM(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty) AS NUMERIC(9,2)) AS VARCHAR(10))) AS Amount ,
FROM
SLDXX_InvoiceDetail

INNER JOIN SLDXX_Invoice ON
SLDXX_InvoiceDetail.InvoiceId = SLDXX_Invoice.InvoiceId

INNER JOIN SLRXX_Product ON
SLDXX_InvoiceDetail.ProductId = SLRXX_Product.ProductId

INNER JOIN SLRXX_ProductGroup ON
SLRXX_Product.ProductGroupId = SLRXX_ProductGroup.ProductGroupId

INNER JOIN SLDXX_PaymentDetail ON
SLDXX_Invoice.InvoiceNo = SLDXX_PaymentDetail.InvoiceNo

FULL OUTER JOIN SLRXX_PackageProduct ON
SLDXX_InvoiceDetail.PackageProductID >= SLRXX_PackageProduct.PackageProductId
WHERE
SLDXX_Invoice.InvoiceStatus = '1' AND
SLDXX_PaymentDetail.PaymentDate = CONVERT(VARCHAR,GETDATE()-1, 101)

GROUP BY
SLRXX_ProductGroup.ProductGroupName

ORDER BY
SLRXX_ProductGroup.ProductGroupName

COMPUTE SUM(SUM(SLDXX_InvoiceDetail.Qty))

the code is right, but how i want to get the total sum from this statement 'RM' + (CAST(CAST(SUM(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty) AS NUMERIC(9,2)) AS VARCHAR(10))) using COMPUTE

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-02-11 : 09:48:15

Do this concatenation at the front end.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:53:05

you can very well get RM on display by choosing currency formatting functions available at front end.

jullgunit
Starting Member

4 Posts

Posted - 2009-02-11 : 10:01:52

maybe my subject is wrong. i already do a concatenation in this statement
'RM' + (CAST(CAST(SUM(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty) AS NUMERIC(9,2)) AS VARCHAR(10)))

below is the output from the code

ProductGroupName Quantity Amount
Media 69 RM11568.00
Directory 6 RM184.90
Registration 733 RM92650.00
Reader 18 RM2709.45

sum
826

i manage to get the total sum for the Quantity column using this statement COMPUTE SUM(SUM(SLDXX_InvoiceDetail.Qty))
my problem is, how to get the total sum for the Amount column without remove the 'RM'?

below is the statement to get the total sum of Amount column without 'RM' in front of it.
COMPUTE SUM(convert(decimal(12,2), sum(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty)))

thanks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 10:20:30

try
SUM(REPLACE(Amount,'RM','')*1.0)

jullgunit
Starting Member

4 Posts

Posted - 2009-02-11 : 10:28:09

quote:
Originally posted by visakh16

try
SUM(REPLACE(Amount,'RM','')*1.0)

thanks, but Amount is not a valid column name
'RM' + (CAST(CAST(SUM(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty) AS NUMERIC(9,2)) AS VARCHAR(10))) AS Amount

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 11:14:55

is it so difficult to replace amount by your actual columnname?

jullgunit
Starting Member

4 Posts

Posted - 2009-02-12 : 00:14:36

thanks, but i am not sure where to put SUM(REPLACE(Amount,'RM','')*1.0) in my code

SELECT DISTINCT
SLRXX_ProductGroup.ProductGroupName ,
SUM(SLDXX_InvoiceDetail.Qty) AS Quantity ,
'RM' + (CAST(CAST(SUM(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty) AS NUMERIC(9,2)) AS VARCHAR(10))) AS Amount

FROM
SLDXX_InvoiceDetail

INNER JOIN SLDXX_Invoice ON
SLDXX_InvoiceDetail.InvoiceId = SLDXX_Invoice.InvoiceId

INNER JOIN SLRXX_Product ON
SLDXX_InvoiceDetail.ProductId = SLRXX_Product.ProductId

INNER JOIN SLRXX_ProductGroup ON
SLRXX_Product.ProductGroupId = SLRXX_ProductGroup.ProductGroupId

INNER JOIN SLDXX_PaymentDetail ON
SLDXX_Invoice.InvoiceNo = SLDXX_PaymentDetail.InvoiceNo

FULL OUTER JOIN SLRXX_PackageProduct ON
SLDXX_InvoiceDetail.PackageProductID >= SLRXX_PackageProduct.PackageProductId
WHERE
SLDXX_Invoice.InvoiceStatus = '1' AND
SLDXX_PaymentDetail.PaymentDate = CONVERT(VARCHAR,GETDATE()-1, 101)

GROUP BY
SLRXX_ProductGroup.ProductGroupName

ORDER BY
SLRXX_ProductGroup.ProductGroupName

COMPUTE SUM(SUM(SLDXX_InvoiceDetail.Qty))
COMPUTE SUM(REPLACE('RM' + (CAST(CAST(SUM(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty) AS NUMERIC(9,2)) AS VARCHAR(10))),'RM','')*1.0)

and it gives error 'COMPUTE clause #2, aggregate expression #1 is not in the select list.'

sorry for my stupidity, but i am newbie in T-SQL

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources