January 3, 2013 at 4:53 pm
I'm really new in here
I'd like to merge into one and add text between two
oi.ItemCode
X ( X is text)
oi.Quantity
so the result should be like this
SKU_A X 1
How to change this below?
--------------------------------------------------
SELECT oi.ItemCode, oi.ItemDescription, oi.Quantity, oi.UnitPrice, oi.UnitWeight, os.Ref1, os.Ref2, os.Ref3, os.Ref4, os.Ref5, os.ORDER_KEY, os.NameOrCompany,
os.Attention, os.ResidentialIndicator, os.REF_NUMBER_WEB, os.SADDR_FIRST_NM, os.SADDR_LAST_NM, os.SADDR_COMPANY_NM, os.SADDR_PHONE,
os.SADDR_EMAIL, os.SADDR_CITY, os.SADDR_LINE1, os.SADDR_LINE2, os.SADDR_LINE3, os.SADDR_PROVINCE, os.SADDR_STATE, os.SADDR_ZIP,
os.SADDR_COUNTRY, os.ServiceType, os.ShipVia, os.SHIP_SHIPMENT_WEIGHT, os.ENABLE_QVN_FLAG, os.QVN_SHIP_FROM, os.QVN_SUBJECT_LINE,
os.QVN_FAILED_EMAIL_ADDR, os.DELIVER_CONF_FLAG, os.DELIVER_CONF_SIG_FLAG, os.ADULT_SIGNATURE_FLAG, os.QVN_SHIP_FLAG,
os.QVN_EXCEPTION_FLAG, os.QVN_DELIVERY_FLAG, os.qvnCCName, os.qvnCCEmail, os.qvnCCFlag, os.BillingOption, os.PackageType, os.ShippingWeight,
os.SHIP_INSURANCE_AMOUNT, os.DeclaredValueOption, os.ORDER_REF_NUMERIC, os.qvnCCFromName
FROM dbo.OrdersToShip AS os INNER JOIN
dbo.OrderItems AS oi ON os.ORDER_KEY = oi.OrderId
WHERE (os.ShipVia = 'FedEx') AND (os.ORDER_KEY NOT IN
(SELECT ORDER_KEY
FROM dbo.ShipmentTracking
WHERE (ORDER_KEY IS NOT NULL)))
January 3, 2013 at 8:15 pm
Try adding the code in bold, adjusting the length of the quantity CAST to VARCHAR as necessary:
SELECT oi.ItemCode + ' X ' + CAST(oi.Quantity AS VARCHAR(5)),
oi.ItemCode, oi.ItemDescription, oi.Quantity, oi.UnitPrice, oi.UnitWeight, os.Ref1, os.Ref2, os.Ref3, os.Ref4, os.Ref5, os.ORDER_KEY, os.NameOrCompany,
os.Attention, os.ResidentialIndicator, os.REF_NUMBER_WEB, os.SADDR_FIRST_NM, os.SADDR_LAST_NM, os.SADDR_COMPANY_NM, os.SADDR_PHONE,
os.SADDR_EMAIL, os.SADDR_CITY, os.SADDR_LINE1, os.SADDR_LINE2, os.SADDR_LINE3, os.SADDR_PROVINCE, os.SADDR_STATE, os.SADDR_ZIP,
os.SADDR_COUNTRY, os.ServiceType, os.ShipVia, os.SHIP_SHIPMENT_WEIGHT, os.ENABLE_QVN_FLAG, os.QVN_SHIP_FROM, os.QVN_SUBJECT_LINE,
os.QVN_FAILED_EMAIL_ADDR, os.DELIVER_CONF_FLAG, os.DELIVER_CONF_SIG_FLAG, os.ADULT_SIGNATURE_FLAG, os.QVN_SHIP_FLAG,
os.QVN_EXCEPTION_FLAG, os.QVN_DELIVERY_FLAG, os.qvnCCName, os.qvnCCEmail, os.qvnCCFlag, os.BillingOption, os.PackageType, os.ShippingWeight,
os.SHIP_INSURANCE_AMOUNT, os.DeclaredValueOption, os.ORDER_REF_NUMERIC, os.qvnCCFromName
FROM dbo.OrdersToShip AS os INNER JOIN
dbo.OrderItems AS oi ON os.ORDER_KEY = oi.OrderId
WHERE (os.ShipVia = 'FedEx') AND (os.ORDER_KEY NOT IN
(SELECT ORDER_KEY
FROM dbo.ShipmentTracking
WHERE (ORDER_KEY IS NOT NULL)))
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 4, 2013 at 10:06 am
Error Comes out
Executed SQL Statement: SELECT oi.ItemCode + ' X ' + CAST(oi.Quantity AS VARCHAR(5)) AS Expr1, oi.ItemCode, oi.ItemDescription, oi.Quantity, oi.UnitWeight, os.Ref1....
Error Source: .Net SqlClient Data Provider
Error Message: Arithmetic overflow error converting numeric to data type varchar.
How to fix this?
January 4, 2013 at 10:09 am
suchang (1/4/2013)
Error Comes outExecuted SQL Statement: SELECT oi.ItemCode + ' X ' + CAST(oi.Quantity AS VARCHAR(5)) AS Expr1, oi.ItemCode, oi.ItemDescription, oi.Quantity, oi.UnitWeight, os.Ref1....
Error Source: .Net SqlClient Data Provider
Error Message: Arithmetic overflow error converting numeric to data type varchar.
How to fix this?
I am totally guessing here but is ItemCode numeric?
It would be far easier to help if you can post ddl so we know what we are working with. The first link in my signature contains best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2013 at 11:05 am
ItemCode is not numberic. Quantity is numberic
January 4, 2013 at 12:08 pm
suchang (1/4/2013)
ItemCode is not numberic. Quantity is numberic
How big are the numbers? You may have to increase the varchar size as Dwain suggested.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2013 at 1:30 pm
quantity numberic digit is like this 1.000 2.000
After changed to 5 to 10, there is no more error form the statement
Thank you ALL : )
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply