Setting a Flag when a set of conditions meet

Question

Setting a Flag when a set of conditions meet

noel.moore

Valued Member

Points: 55
More actions
February 8, 2018 at 11:45 pm

#333449

I am trying to set a Flag if a number of conditions in an IIF statement are meet; after calculating a value in a CASE construction. I have tried a number of CAST constructions on the calculated "PAreaRaponi" , without success. Error message is: "Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric."
The Query is below: Thanks in advance, I am learning slowly after decades away from coding!
SELECT AgriculturalProductDetailID AS Prod_ID, APD.HouseHoldID, APD.AgriculturalProductTypeID AS Ag_ID,
APD.PlantationArea,
"PAreaRaponi" =
CASE APD.UnitID
        WHEN '1' THEN APD.PlantationArea*1.00
        WHEN '2' THEN APD.PlantationArea*13.31
  WHEN '3' THEN (APD.PlantationArea*13.31)/20
  WHEN '4' THEN (APD.PlantationArea*13.31)/400
  WHEN '5' THEN (APD.PlantationArea*0.0625)
    END,
IIF((AgriculturalProductTypeID = 1
  OR AgriculturalProductTypeID = 2
  OR AgriculturalProductTypeID = 3
  OR AgriculturalProductTypeID = 14)
  AND ('PAreaRaponi' < 4.34
   OR 'PAreaRaponi' > 4.66)
AND 'PAreaRaponi' > 1.0
, 'is One/2/3/14','? ' ) AS "FlagA"
--SoldAmount/NULLIF(SoldQuantity,0 ) AS "ICValue_perKg",
FROM AgriculturalProductHouseholdDetail AS APD
WHERE HouseHoldID<1200
-- and UnitID = xx for testing
ORDER BY HouseHoldID;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76944 More actions · Answer 1

The problem is here:
AND ('PAreaRaponi' < 4.34 OR 'PAreaRaponi' > 4.66) AND 'PAreaRaponi' > 1.0

'PAreaRaponi' is a string and cannot be converted to a number. It looks like you meant to refer to the field PAreaRaponi rather than the string 'PAreaRaponi'. Simply removed the single quotes.

I almost never quote field names, but when I do, I always use brackets instead of single or double quotes. It's unambiguous. In this case that would be [PAreaRaponi].

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

noel.moore Valued Member Points: 55 More actions · Answer 2

Thank you. Certainly a step forward! I still have an error---
with 3 variations, I have errors.......
AND (PAreaRaponi < 4.34
OR [PAreaRaponi] > 4.66)
AND (PAreaRaponi) > 1.0
, 'is One/2/3/14','? ' ) AS "FlagA"
Msg 207, Level 16, State 1, Line 16
Invalid column name 'PAreaRaponi'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'PAreaRaponi'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'PAreaRaponi'

Are you able to help again. Thanks

drew.allen SSC Guru Points: 76944 More actions · Answer 3

noel.moore - Friday, February 9, 2018 8:27 PM
Thank you. Certainly a step forward! I still have an error---
with 3 variations, I have errors.......
AND (PAreaRaponi < 4.34
OR [PAreaRaponi] > 4.66)
AND (PAreaRaponi) > 1.0
, 'is One/2/3/14','? ' ) AS "FlagA"
Msg 207, Level 16, State 1, Line 16
Invalid column name 'PAreaRaponi'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'PAreaRaponi'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'PAreaRaponi'
Are you able to help again. Thanks

It's your database. I have no idea what PAreaRaponi means.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

noel.moore Valued Member Points: 55 More actions · Answer 4

Thank you for your help Drew. In the remotes of Nepal help is always appreciated!