translate IIFs in a where clause to CASE statements

M

Mittle

Member
Local time
Today, 06:39
Joined
Dec 2, 2020
Messages
105
hi

Could someone please help.
this is an access query with IIF's statements , as you can see I have translated to CASE WHEN
but am having trouble translating the WHERE part of this clause .Thanks


Code: 
SELECT
Capability_Q.IDQ,
Capability_Q.Capability,
Capability_Q.OtherDesc,
--IIf([CapNow]="0",Null,[CapNow]) AS CpNow,
CASE
    WHEN [CapFuture]='0' THEN NULL ELSE [CapNow]

    END

    AS CpNow,
--IIf([CapFuture]="0",Null,[CapFuture]) AS CpFuture,
CASE
    WHEN [CapFuture]='0' THEN NULL ELSE [CapFuture]

END

 AS CpFuture
INTO UpldCapability
FROM Capability_Q
WHERE
(((Capability_Q.IDQ) Is Not Null)
AND ((IIf([CapNow]="0",Null,[CapNow])) Is Not Null)
AND ((IIf([CapFuture]="0",Null,[CapFuture])) Is Not Null));


 could someone please help
 
I would do the WHERE without the CASE. You don't want 0 values nor Null values for CapNow, so just add 2 criteria:

Code: 
...AND (CapNow<>0) AND (CapNow Is Not Null)...

Doing that also negates the need to do it in the SELECT. 0 values won't be passing through the WHERE so the SELECT won't have to handle them.

Also, is the first CASE correct?

Code: 
WHEN [CapFuture]='0' THEN NULL ELSE [CapNow]

You test one field, but output another.
 
Mittle said:
hi

Could someone please help.
this is an access query with IIF's statements , as you can see I have translated to CASE WHEN
but am having trouble translating the WHERE part of this clause .Thanks


Code: 
SELECT
Capability_Q.IDQ,
Capability_Q.Capability,
Capability_Q.OtherDesc,
--IIf([CapNow]="0",Null,[CapNow]) AS CpNow,
CASE
    WHEN [CapFuture]='0' THEN NULL ELSE [CapNow]

    END

    AS CpNow,
--IIf([CapFuture]="0",Null,[CapFuture]) AS CpFuture,
CASE
    WHEN [CapFuture]='0' THEN NULL ELSE [CapFuture]

END

AS CpFuture
INTO UpldCapability
FROM Capability_Q
WHERE
(((Capability_Q.IDQ) Is Not Null)
AND ((IIf([CapNow]="0",Null,[CapNow])) Is Not Null)
AND ((IIf([CapFuture]="0",Null,[CapFuture])) Is Not Null));


could someone please help
Click to expand...
Each IIF becomes its own little CASE (one way), including the ones near the end...
 
thank you for responding so quickly

1.true suggesting the WHERE doesnt need the CASE . I will test this code and see. Many Thanks




2.Oops thanks you are right the first case is wrong its typo

meant to be
WHEN [Capnow] = '0' THEN NULL ELSE [Capnow]
 
it's a very odd looking thing to have in the where clause in the first place, actually, now that I look more at it and think.
"where, this-if capnow is 0, null, else capnow-is not null" ??? Why didn't the original person just say, "where capnow <>0 or where isnull(capnow,0) is not null

clearly this person was trying to handle the possibility of null, but it would have been clearer to just say:

where capnow is not null and capnow <>0
 
I know your very right . its like going round the blocks in circles when their is a straight line option too.probably why I couldn't get my head round it

anyway code was inherited from access database .

thanks anyway for your help
 
Mittle said:
I know your very right . its like going round the blocks in circles when their is a straight line option too.probably why I couldn't get my head round it

anyway code was inherited from access database .

thanks anyway for your help
Click to expand...
Sure thing.

Sometimes even the shortest statements are confusing, if they're written in a confusing way.
Probably why I am big on readability in coding--because I'm easily confused :)
 
Mittle said:
thank you for responding so quickly

1.true suggesting the WHERE doesnt need the CASE . I will test this code and see. Many Thanks




2.Oops thanks you are right the first case is wrong its typo

meant to be
WHEN [Capnow] = '0' THEN NULL ELSE [Capnow]
Click to expand...
quick question please this code works very well but I have a slight error message probably to do with the data . any help will be much appreciated

Error
Conversion failed when converting the varchar value 'N/A' to data type INT


Troubleshooting
when I comment out the line below I dont get the error . so I found out the CapNow and CapFuture columns have values of 'N/A' but what I dont understand is why the conversion to data type INT

.--AND (CapNow<>0) AND (CapNow Is Not Null).
 
Last edited:
What is the code now - please can you post it up with the offending line highlighted/indicated?

As a guess If CapNow and CapFuture have N/A in them they are Varchar's so you can't use <> 0
 
AND (CapNow<>'0') AND (CapNow Is Not Null)
 
Minty said:
What is the code now - please can you post it up with the offending line highlighted/indicated?

As a guess If CapNow and CapFuture have N/A in them they are Varchar's so you can't use <> 0
Click to expand...
arrh thanks thats probably why . yes the CapNow and Capfuture have values "N/A" in them and they are varchar datatype
but its funny the access code worked in access though
 
Access doesn't care, SQL does because it's properly fussy about datatypes and implicit conversations.

Equally, SQL will let you join on a text field to a number field without issue.
Which can be jolly useful especially when dealing with importing data from various sources.
 
Hi Minty



what would you suggest the solution to this issue please. here's an example of the data values in the CapFuture and Capnow Columns

Capfuture
Green
Red
Green
Green
N/A

Thank you
 
What are you trying to get as an output from those values?
Best route is two sets of data - your starting data and what you want as an output, with the rules that get to that result.

We would need enough sample data to cover all the potential desired results.
 
ok will get back to you on that .
thats exactly the. question am asking the Project owners that

why are we comparing to '0' when the data values in the field are archer data types e.g Green, Red, N/A etc
 
just a quick question please and a bit confusing

is SQL Having a problem with the value 'N/A' because the other values in those columns are 'Green', 'Amber' etc



as you can see from the code . I only get the error in the WHERE Clause but no error in the CASE part of the code before the where clause even though we are still comparing to 0 . if I commit out the WHERE Clause I get no conversion errors

Code: 
SELECT
Capability_Q.IDQ,
Capability_Q.Capability,
Capability_Q.OtherDesc,
--IIf([CapNow]="0",Null,[CapNow]) AS CpNow,
CASE
    WHEN [CapFuture]='0' THEN NULL ELSE [CapNow]

    END

    AS CpNow,
--IIf([CapFuture]="0",Null,[CapFuture]) AS CpFuture,
CASE
    WHEN [CapFuture]='0' THEN NULL ELSE [CapFuture]

END

AS CpFuture
INTO UpldCapability
FROM Capability_Q
WHERE
Capability_Q.IDQ Is Not Null
AND [CapNow]<>0 AND [CapNow] Is Not Null
AND [CapFuture]<>0 AND [CapFuture] Is Not Null;
 
Last edited:
That code wouldn't run SQL. Doesn't understand IIf() or the " " around the 0

What is the actual code you ae using?
 
Apologies , ive now modified the code and this is the response I got from my colleague that wrote the Access query .

this is the response I got

the aim of the CODE is to find '0' and change to NULL . the data values might be coming in from the Source Application as null or blank but when used in Excel, the link formula always put '0' so he would remove it to keep things Clean
 
In that case your existing code
CASE
WHEN [CapFuture]='0' THEN NULL ELSE [CapFuture]
is all you need.

The Where clause is still using <>0 instead of <>'0' however that would exclude '0' values rendering it useless?
 
You must log in or register to reply here.

Similar threads

D
Problem with IIF statement and filtering a query
Replies
4
Views
906
Pat Hartman
P
S
Complex T-SQL Statement Conversion to MySQL
Replies
14
Views
918
stanhook
S
N
Solved Progress meter for reports & queries in MS Access
Replies
16
Views
790
tvanstiphout
T
N
Solved Caching recordset in MS Access VBA
2
Replies
26
Views
1,757
tvanstiphout
T
N
Solved Count iif in a query
Replies
2
Views
528
nirmal
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom