decimal datatpe with less than or equal

3 Nov 2005 7:28 PM

microsoft.public.dotnet.languages.vb

Hi All,

I have a field whose datatype is decimal.

I am trying to see if the value is less than or equal to zero. I am
getting Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Here is what I was doing

select field1=
CASE WHEN table2.MaxLabrHrs<=0.00
THEN table1.field2* table2.LaborAmount
end

I am not sending DDL hoping that it will be easy fix.
Problem is here when I put : table2.MaxLabrHrs<=0.00

Field MaxLabrHrs is decimal datatype (9,2).
If I put only less than then it works.

Thanks a million in advance.

Best regards,

mamun

3 Nov 2005 7:32 PM

Anith Sen

>> I am not sending DDL hoping that it will be easy fix.

It may not be. The error might be to generated from another section of the
code. It might be worthwhile to check the datatypes & data in the field2
column as well as the LaborAmount column.

--
Anith

3 Nov 2005 7:42 PM

microsoft.public.dotnet.languages.vb

Thanks a ton.

Yes, the field2 in the table1 is numeric.

Is there anything I can do without changing the numeric datatype in the
table1?
If not then I will change that to decimal.

Thanks again,

best regards,
mamun

3 Nov 2005 7:46 PM

Anith Sen

NUMERIC & DECIMAL are well compatible and so that is a non-issue. You might
want to check the data in all involved columns. Also check the datatype of
the LaborAmount as well.

If you still find it a problem, consider posting the table DDLs & some
sample data.

--
Anith

3 Nov 2005 8:59 PM

microsoft.public.dotnet.languages.vb

The following codes were written from Mr. Anith Sen's help probably two
years ago (or more). Then it became necessity to use another field
MaxLabrHrs in the criteria table.

This is the code I am using:

select V_LABCST=
CASE WHEN t2.LaborIndicator = 'R' AND t2.LaborAmount > 0.00 and
t2.MaxLabrHrs <=0.00
            THEN t1.V_APPLBRHRS * t2.LaborAmount

        WHEN t2.LaborIndicator = 'R' and (t2.LaborAmount=0.00 or
t2.LaborAmount='' or t2.LaborAmount is null) and t2.MaxLabrHrs <=0.00
            THEN t1.V_APPLBRHRS * t3.Labor_Rate
        WHEN t2.LaborIndicator = 'R' and t2.MaxLabrHrs>0.00
        THEN CASE
        WHEN t1.V_APPLBRHRS<t2.MaxLabrHrs THEN
            t1.V_APPLBRHRS * t3.Labor_Rate
             WHEN t1.V_APPLBRHRS>t2.MaxLabrHrs THEN
            t2.MaxLabrHrs * t3.Labor_Rate
        END
       END
from Filtered_Data_Hold t1
JOIN Criteria t2
    ON t1.Supplier_ID = t2.SupplierID
join Claims t3 on t1.Claim_Number=t3.Claim_Number
where t1.Claim_Number='1I23456'

CREATE TABLE [dbo].[Criteria] (
    [Supplier_ID] [varchar] (10) NOT NULL ,
    [LaborIndicator] [char] (2) ,
    [LaborAmount] [decimal](8, 2) NULL ,
    [MaxLabrHrs] [decimal](9, 2) NOT NULL

)

Insert into criteria('AX812', 'R', 67.00 , .66)

CREATE TABLE [dbo].[Filtered_Data_Hold] (
    [Claim_Number] [char] (9) NOT NULL ,
    [Supplier_ID] [char] (7) NULL ,
    [Req_Lab_Hrs] [numeric](6, 1) NOT NULL ,
    [V_APPLBRHRS] [numeric](6, 1) NOT NULL ,
    [V_LABCST] [numeric](10, 2) NOT NULL
) ON [PRIMARY]
GO

insert Filtered_Data_Hold('1I23456', 'AX812', 1.0, 1.0, 67.00)

CREATE TABLE [dbo].[Claims] (
    [Claim_Number] [char] (9) NOT NULL ,
    [VAppLabr_Hrs] [numeric](6, 1) NOT NULL

)

Insert Claims ('1I23456', 1.0)

I want the result to be 67.00*.66=44.22 (when there is max labor hours
in the criteria table greater than 0.00, I want to multiply max labour
hours with the labor rate).

Thanks a ton for your help.

best regards,
mamun

3 Nov 2005 10:04 PM

Trey Walpole

comparing t2.LaborAmount to the empty string is the problem

(t2.LaborAmount=0.00 or
----->>>> t2.LaborAmount=''
or t2.LaborAmount is null)

microsoft.public.dotnet.languages.vb wrote:

Show quote

>The following codes were written from Mr. Anith Sen's help probably two
>years ago (or more). Then it became necessity to use another field
>MaxLabrHrs in the criteria table.
>
>This is the code I am using:
>
>select V_LABCST=
>CASE WHEN t2.LaborIndicator = 'R' AND t2.LaborAmount > 0.00 and
>t2.MaxLabrHrs <=0.00
>            THEN t1.V_APPLBRHRS * t2.LaborAmount
>
>         WHEN t2.LaborIndicator = 'R' and (t2.LaborAmount=0.00 or
>t2.LaborAmount='' or t2.LaborAmount is null) and t2.MaxLabrHrs <=0.00
>            THEN t1.V_APPLBRHRS * t3.Labor_Rate
>        WHEN t2.LaborIndicator = 'R' and t2.MaxLabrHrs>0.00
>        THEN CASE
>        WHEN t1.V_APPLBRHRS<t2.MaxLabrHrs THEN
>            t1.V_APPLBRHRS * t3.Labor_Rate
>             WHEN t1.V_APPLBRHRS>t2.MaxLabrHrs THEN
>            t2.MaxLabrHrs * t3.Labor_Rate
>        END
>       END
>from Filtered_Data_Hold t1
> JOIN Criteria t2
>    ON t1.Supplier_ID = t2.SupplierID
>join Claims t3 on t1.Claim_Number=t3.Claim_Number
>where t1.Claim_Number='1I23456'
>
>
>CREATE TABLE [dbo].[Criteria] (
>    [Supplier_ID] [varchar] (10) NOT NULL ,
>    [LaborIndicator] [char] (2) ,
>    [LaborAmount] [decimal](8, 2) NULL ,
>    [MaxLabrHrs] [decimal](9, 2) NOT NULL
>
>)
>
>Insert into criteria('AX812', 'R', 67.00 , .66)
>
>
>CREATE TABLE [dbo].[Filtered_Data_Hold] (
>    [Claim_Number] [char] (9) NOT NULL ,
>    [Supplier_ID] [char] (7) NULL ,
>    [Req_Lab_Hrs] [numeric](6, 1) NOT NULL ,
>    [V_APPLBRHRS] [numeric](6, 1) NOT NULL ,
>    [V_LABCST] [numeric](10, 2) NOT NULL
>) ON [PRIMARY]
>GO
>
>insert Filtered_Data_Hold('1I23456', 'AX812', 1.0, 1.0, 67.00)
>
>
>
>CREATE TABLE [dbo].[Claims] (
>    [Claim_Number] [char] (9) NOT NULL ,
>    [VAppLabr_Hrs] [numeric](6, 1) NOT NULL
>
>)
>
>Insert Claims ('1I23456', 1.0)
>
>
>
>
>
>I want the result to be 67.00*.66=44.22 (when there is max labor hours
>in the criteria table greater than 0.00, I want to multiply max labour
>hours with the labor rate).
>
>
>
>Thanks a ton for your help.
>
>best regards,
>mamun
>
>
>

3 Nov 2005 7:41 PM

Trey Walpole

I don't think it's that column that's giving the error.
What types are the "field2" and "LaborAmount" columns you mention?
It's most likely that some row where MaxLabrHrs=0 has an unconvertible
varchar column.

And it would be easier to fix with DDL, btw ;)

microsoft.public.dotnet.languages.vb wrote:

Show quote

>Hi All,
>
>I have a field whose datatype is decimal.
>
>I am trying to see if the value is less than or equal to zero. I am
>getting Server: Msg 8114, Level 16, State 5, Line 1
>Error converting data type varchar to numeric.
>
>Here is what I was doing
>
>select field1=
>CASE WHEN table2.MaxLabrHrs<=0.00
>THEN table1.field2* table2.LaborAmount
>end
>
>
>I am not sending DDL hoping that it will be easy fix.
>Problem is here when I put : table2.MaxLabrHrs<=0.00
>
>Field MaxLabrHrs is decimal datatype (9,2).
>If I put only less than then it works.
>
>Thanks a million in advance.
>
>Best regards,
>
>mamun
>
>
>