Avatar of dinesh_bali
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
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
dinesh_bali
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Update TMPMATERIALORDERED
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
Avatar of dinesh_bali
dinesh_bali

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
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
Avatar of dinesh_bali
dinesh_bali

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 <=
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of dinesh_bali
dinesh_bali

ASKER

ohhhh

Ok, Thanks allot
Microsoft SQL Server 2005
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo