dinesh_bali
asked on
Numeric Check MSSQL 2005
Hi,
I am working on MSSQL 2005
I want in table TMPMATERIALORDERED field Quantity should be numeric and greater than 0, if not than the
STATUS should be set to FAILURE and REMARKS should be set to Quantity is not numeric or Quantity is less than 0
In table TMPMATERIALORDERED my filed Quantity is nvarchar(100)
My query gives error says
Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near 'Then'.
Please find my query below and help me in correcting it
Update TMPMATERIALORDERED
Set STATUS = Case
When Not IsNumeric(Quantity) Then 'FAILURE'
When IsNumeric(Quantity) And Quantity <=0 Then 'FAILURE'
Else NULL
End,
Set REMARKS = Case
When Not IsNumeric(Quantity) Then 'Quantity is not numeric'
When IsNumeric(Quantity) And Quantity <=0 Then 'Quantity is less than 0'
Else NULL
End
WHERE
QUANTITY IS NULL
AND STATUS IS NULL
Please Help
I am working on MSSQL 2005
I want in table TMPMATERIALORDERED field Quantity should be numeric and greater than 0, if not than the
STATUS should be set to FAILURE and REMARKS should be set to Quantity is not numeric or Quantity is less than 0
In table TMPMATERIALORDERED my filed Quantity is nvarchar(100)
My query gives error says
Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near 'Then'.
Please find my query below and help me in correcting it
Update TMPMATERIALORDERED
Set STATUS = Case
When Not IsNumeric(Quantity) Then 'FAILURE'
When IsNumeric(Quantity) And Quantity <=0 Then 'FAILURE'
Else NULL
End,
Set REMARKS = Case
When Not IsNumeric(Quantity) Then 'Quantity is not numeric'
When IsNumeric(Quantity) And Quantity <=0 Then 'Quantity is less than 0'
Else NULL
End
WHERE
QUANTITY IS NULL
AND STATUS IS NULL
Please Help
Last Comment
ASKER
Thanks for your reply,
Error says
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'Set'.
at line
Set REMARKS = Case
Error says
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'Set'.
at line
Set REMARKS = Case
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
indeed. the SET keyword only needs to be specified once:
Update TMPMATERIALORDERED
Set STATUS = Case
When IsNumeric(Quantity) <> 1 Then 'FAILURE'
When IsNumeric(Quantity) = 1And Quantity <=0 Then 'FAILURE'
Else NULL
End
, REMARKS = Case
When IsNumeric(Quantity) <> 1 Then 'Quantity is not numeric'
When IsNumeric(Quantity) = 1 And Quantity <=0 Then 'Quantity is less than 0'
Else NULL
End
WHERE
QUANTITY IS NULL
AND STATUS IS NULL
Update TMPMATERIALORDERED
Set STATUS = Case
When IsNumeric(Quantity) <> 1 Then 'FAILURE'
When IsNumeric(Quantity) = 1And Quantity <=0 Then 'FAILURE'
Else NULL
End
, REMARKS = Case
When IsNumeric(Quantity) <> 1 Then 'Quantity is not numeric'
When IsNumeric(Quantity) = 1 And Quantity <=0 Then 'Quantity is less than 0'
Else NULL
End
WHERE
QUANTITY IS NULL
AND STATUS IS NULL
ASKER
Hi Thanks for your mail
I seriously did not understood this
IsNumeric(Quantity) will be either 1 or not 1
in both cases you have added status as failure.
I am confused, Please guide me
Thanks
If When IsNumeric(Quantity) <> 1 Then 'FAILURE'
When IsNumeric(Quantity) = 1And Quantity <=
I seriously did not understood this
IsNumeric(Quantity) will be either 1 or not 1
in both cases you have added status as failure.
I am confused, Please guide me
Thanks
If When IsNumeric(Quantity) <> 1 Then 'FAILURE'
When IsNumeric(Quantity) = 1And Quantity <=
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
ohhhh
Ok, Thanks allot
Ok, Thanks allot
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Set STATUS = Case
When IsNumeric(Quantity) <> 1 Then 'FAILURE'
When IsNumeric(Quantity) = 1And Quantity <=0 Then 'FAILURE'
Else NULL
End,
Set REMARKS = Case
When IsNumeric(Quantity) <> 1 Then 'Quantity is not numeric'
When IsNumeric(Quantity) = 1 And Quantity <=0 Then 'Quantity is less than 0'
Else NULL
End
WHERE
QUANTITY IS NULL
AND STATUS IS NULL