Link to home
Start Free TrialLog in
Avatar of ammartahir1978
ammartahir1978Flag for United Kingdom of Great Britain and Northern Ireland

asked on

how to fix sql query

hi guys i have this query which works fine until i put the following lines in select statement.

i am trying to caculate cost here which is the last statement in select statment.

select
A.Aud_receipt_no as RECECIPT_NO,
A.Aud_prod_code as PRODUCT_CODE,
A.Aud_prod_var as VARIENCE_CODE,
A.Aud_EAN as EAN,
A.Aud_desc as DESCRIPTIONS,
A.Aud_quantity as QUANTITY,
A.Aud_value as SOLD_VALUE,
A.Aud_VAT as VAT,
A.Aud_weight as WEIGHT,
A.Aud_short_desc as PRODUCT_SHORT_DESC,
A.Aud_Date_time as TRANS_DATE_TIME,
A.Aud_trans_type as TRANSACTION_TYPE,
b.i_lat_cost = ((b.i_lat_cost/b.i_supp_unit)* a.aud_quantity) as COST


from [uks-1-1].[stree].[dbo].[prods] as B
inner join [eretail1-1].[pos audit].[dbo].[_audit] as A
on B.i_prod_code = A.aud_prod_code collate SQL_Latin1_General_CP1_CI_AS
and B.I_promo = A.aud_prod_var collate SQL_Latin1_General_CP1_CI_AS
WHERE A.AUD_PROD_CODE != 'null'
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ammartahir1978

ASKER

hi Kent, tried that already get this error

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals divide, type equals char.
Ok.  What's the data type of b.i_supp_unit?

From your query, it looks like it should be a numeric type, but the error says that it's a character type.  And that probably means that it's the wrong variable.


Kent
its character field but it always have numbers can i convert it in my query instead of changing table field.
i tried converting it to int and get this error

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '    75.000' to a column of data type int.
Ok.  That field has both spaces and a period in it.  Clearly not at integer.

Convert it to a float or double.  (You can recast to an int if you need to.)  You may have to trim the leading spaces.


Kent
hi kent my knownledge ends here can  you please let me know how or if you can please twik it below thank you

select
A.Aud_receipt_no as RECECIPT_NO,
A.Aud_prod_code as PRODUCT_CODE,
A.Aud_prod_var as VARIENCE_CODE,
A.Aud_EAN as EAN,
A.Aud_desc as DESCRIPTIONS,
A.Aud_quantity as QUANTITY,
A.Aud_value as SOLD_VALUE,
A.Aud_VAT as VAT,
A.Aud_weight as WEIGHT,
A.Aud_short_desc as PRODUCT_SHORT_DESC,
A.Aud_Date_time as TRANS_DATE_TIME,
A.Aud_trans_type as TRANSACTION_TYPE,
((b.i_lat_cost/(convert(int,b.i_supp_unit)))* a.aud_quantity) as COST


from [uks-cafe1-1].[streetwise].[dbo].[prods] as B
inner join [eretail1-1].[pos audit].[dbo].[_audit] as A
on B.i_prod_code = A.aud_prod_code collate SQL_Latin1_General_CP1_CI_AS
and B.I_promo = A.aud_prod_var collate SQL_Latin1_General_CP1_CI_AS
WHERE A.AUD_PROD_CODE != 'null'
Sure.  :)

((b.i_lat_cost/(convert(int,b.i_supp_unit)))* a.aud_quantity) as COST

becomes

((b.i_lat_cost/(convert(double,ltrim (b.i_supp_unit))))* a.aud_quantity) as COST

Then the only real issues would be bad data, or division by zero.


Kent
thanks kent :)

error:
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near ','.
Too many databases, too many proprietary formats...

Replace 'double' with 'real' or 'float'.


Kent
when i replace with either real or float i get this:

erver: Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'as'.

sorry to be a pain
okay i put ( and it ran with error

(2 row(s) affected)

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
excellent
Can't tell you how many times I counted the parenthesis wrong....


:)