Different data type columns - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Different data type columns

Author

Topic

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 11:00:12

Hello

I'm trying to multiply two columns with different data types.
For example in one column data type is varchar "box.20"(quantity per unit of measure) and other is numeric "10"(quantity). This means that 1 box has a total of 20 bottles(for example) and so multiplying for "10" gives me the total of bottles(200).

So how can i multiply these two columns without giving me this error "Error converting data type varchar to numeric." ?

Here's the code:

CREATE TABLE #TEMP (
DocN varchar,
Num numeric,
Desc1 varchar,
Desc2 varchar,
Um varchar,
QtdP numeric,
QtdE numeric
)

-- Insert statements for procedure here
INSERT INTO #Temp
SELECT [Malaquias$Sales Line].[Document No_], [Malaquias$Sales Line].[No_],
[Malaquias$Sales Line].[Description], [Malaquias$Sales Line].[Description 2],
[Malaquias$Sales Invoice Line].[Unit of Measure], ([Malaquias$Sales Line].[Quantity]*[Malaquias$Sales Line].[Qty_ per Unit of Measure]) as qtdpedida,
sum([Malaquias$Sales Invoice Line].[Quantity]*[Malaquias$Sales Invoice Line].[Qty_ per Unit of Measure])as qtdenviada
FROM [Malaquias$Sales Line], [Malaquias$Sales Invoice Line]
WHERE [Malaquias$Sales Line].[No_]=[Malaquias$Sales Invoice Line].[No_] and
[Malaquias$Sales Line].[Sell-to Customer No_]= [Malaquias$Sales Invoice Line].[Sell-to

Customer No_] and
[Malaquias$Sales Line].[Shipment Date] <= [Malaquias$Sales Invoice Line].[Shipment Date]

and
[Malaquias$Sales Line].[Document No_]= 'FP.09.00296'

GROUP BY
[Malaquias$Sales Line].[Document No_], [Malaquias$Sales Line].[No_],
[Malaquias$Sales Line].[Description], [Malaquias$Sales Line].[Description 2],
[Malaquias$Sales Invoice Line].[Unit of Measure],
[Malaquias$Sales Line].[Quantity], [Malaquias$Sales Line].[Qty_ per Unit of Measure]

SELECT Docn, Num, Desc1, Desc2, Um, QtdP, QtdE
From #TEMP

END
---------
The final goal is to see the quantity ordered and quantity shiped.

Please help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-24 : 11:16:24

You should have two columns:
Unit varchar(50) -- to hold info like 'box' or 'container' or what ever
Qty_per_Unit int -- to hold the qty for that unit (maybe you need decimal instead of int)

But to answer your question:
I can imagine many different values in your [Qty_ per Unit of Measure] and 'box.20' is only one of that stuff.
Do you have a clue on what rules whe can do the extract of the numeric part from [Qty_ per Unit of Measure]?

No, you're never too old to Yak'n'Roll if you're too young to die.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-07-24 : 11:30:29

[code]
try this

CREATE TABLE #TEMP
(
DocN varchar,
Num numeric,
Desc1 varchar,
Desc2 varchar,
Um varchar,
QtdP numeric,
QtdE numeric
)

-- Insert statements for procedure here
INSERT INTO #Temp
SELECT
[Malaquias$Sales Line].[Document No_],
[Malaquias$Sales Line].[No_],
[Malaquias$Sales Line].[Description],
[Malaquias$Sales Line].[Description 2],
[Malaquias$Sales Invoice Line].[Unit of Measure],
([Malaquias$Sales Line].[Quantity]*PARSENAME([Malaquias$Sales Line].[Qty_ per Unit of Measure],1)) as qtdpedida,
SUM([Malaquias$Sales Invoice Line].[Quantity]*PARSENAME([Malaquias$Sales Invoice Line].[Qty_ per Unit of Measure],1))as qtdenviada
FROM
[Malaquias$Sales Line], [Malaquias$Sales Invoice Line]
WHERE [Malaquias$Sales Line].[No_]=[Malaquias$Sales Invoice Line].[No_]
AND [Malaquias$Sales Line].[Sell-to Customer No_]= [Malaquias$Sales Invoice Line].[Sell-to Customer No_]
AND [Malaquias$Sales Line].[Shipment Date] <= [Malaquias$Sales Invoice Line].[Shipment Date]
AND [Malaquias$Sales Line].[Document No_]= 'FP.09.00296'
GROUP BY
[Malaquias$Sales Line].[Document No_],
[Malaquias$Sales Line].[No_],
[Malaquias$Sales Line].[Description],
[Malaquias$Sales Line].[Description 2],
[Malaquias$Sales Invoice Line].[Unit of Measure],
[Malaquias$Sales Line].[Quantity],
[Malaquias$Sales Line].[Qty_ per Unit of Measure]

SELECT Docn, Num, Desc1, Desc2, Um, QtdP, QtdE
From #TEMP
[/code]

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 11:45:06

In Qty_ per Unit Of Measure only the numbers change. It allways starts with box and the numbers can go since 000 to 999 for what i realise.

About PARSENAME i've got the same error "error converting data type varchar to numeric".

Any ideas?

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 11:47:00

Maybe changing the content of qty_ per unit of measure to a temp table (only the numbers would pass).. but i have no idea how.

For example box.20 -> 20

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-24 : 11:54:19

Try:
select * from [Malaquias$Sales Line]
where
isnumeric(PARSENAME([Malaquias$Sales Line].[Qty_ per Unit of Measure],1))=0

to get an idea of what kind of values appear in that column.

No, you're never too old to Yak'n'Roll if you're too young to die.

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 12:07:32

Query executed successfully but no values appeared...

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 12:08:47

0 row(s) affected.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 12:39:50

quote:
Originally posted by Rikarddo

0 row(s) affected.

that means you have only numeric values coming after . so you can use something like:-



.......

([Malaquias$Sales Line].[Quantity]*
PARSENAME([Malaquias$Sales Line].[Qty_ per Unit of Measure],1)) as qtdpedida,
sum([Malaquias$Sales Invoice Line].[Quantity]*PARSENAME([Malaquias$Sales Line].[Qty_ per Unit of Measure],1))as qtdenviada
..........

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 12:47:57

I've tried that already as raky said.
Got the same error "error converting data type varchar to numeric"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 12:49:47

what about this?


.......

([Malaquias$Sales Line].[Quantity]*
(PARSENAME([Malaquias$Sales Line].[Qty_ per Unit of Measure],1)*1)) as qtdpedida,
sum([Malaquias$Sales Invoice Line].[Quantity]*(PARSENAME([Malaquias$Sales Line].[Qty_ per Unit of Measure],1)*1))as qtdenviada
..........

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 12:56:01

Same error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:00:41

and this?



.......

([Malaquias$Sales Line].[Quantity]*
(RIGHT([Malaquias$Sales Line].[Qty_ per Unit of Measure],charindex('.',reverse([Malaquias$Sales Line].[Qty_ per Unit of Measure]))-1)*1)) as qtdpedida,
sum([Malaquias$Sales Invoice Line].[Quantity]*(RIGHT([Malaquias$Sales Line].[Qty_ per Unit of Measure],charindex('.',reverse([Malaquias$Sales Line].[Qty_ per Unit of Measure]))-1)*1))as qtdenviada
..........

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 13:06:49

Not working..same error... I dont know if it will interfere but in qty_ unit of measure there are box.(number) between 000 and 999 as i said before and UN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:09:18

do you mean it has number wrapped in () ?

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 13:10:32

No .. just box.number
And other descriptions such as Un etc etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:11:45

after number? please can you clearly post some of real values?

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 13:13:54

Example 1 : Qty_ per unit of measure of the product is box.10
Example 2: Qty_ per unit of measure of the product is Un.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:18:44

so in second case what you want to return?

Rikarddo
Starting Member

14 Posts

Posted - 2009-07-24 : 13:21:09

In that case only the [Malaquias$Sales Line].[Quantity]
and not ([Malaquias$Sales Line].[Quantity]*[Malaquias$Sales Line].[Qty_ per Unit of Measure]).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:30:18

then make a derived column like below and use it in place of [Malaquias$Sales Line].[Qty_ per Unit of Measure]in above query at all the places
[Malaquias$Sales Line].[Qty_ per Unit of Measure] + case when charindex('.',[Malaquias$Sales Line].[Qty_ per Unit of Measure])>0 then '' else '.1' end

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources