CODEWITHSUNDEEP
sqlms-accessquery-optimization
Dan Champagne
Dan Champagne

Reputation: 920

MS Access SQL IIF optimization

Given this table, I have a query that uses IIF to pull out the value from one column when the other one is NULL.

COL_A,COL_B
NULL,10
12,NULL
14,NULL
NULL,16

SELECT iif(COL_A IS NULL, COL_B, COL_A);

Results:
10
12
14
16

What I'm trying to work out is how to do this in straight SQL. Is there a way this can be done? Normally I wouldn't mind using this a few times, but we have a massive query with cough 86 IIF statements and it's getting prohibitive to run. Any ideas?

Upvotes: 1

Views: 440

Answers (3)

KenDGreen
KenDGreen

Reputation: 21

Assuming only one column will have an actual value, You could just Concatenate them, since the nulls would compress. Select COL_A||COL_B||COL_C||COL_D||....

Upvotes: 1

Matt Donnan
Matt Donnan

Reputation: 4993

If your query needs to carry out 86 different test's in one query then I would suggest the data architecture is flawed, you could shorten down your SQL by writing a custom function using the "Case" method which could step through all the validation and return a result. However this will only improve the look of your SQL and won't really improve performance. Difficult to say without a list of the 86 IIF's but I think you need to look at your data structure to see if you can improve things from table level.

Upvotes: 1

Alex K.
Alex K.

Reputation: 175816

You can use NZ()

 select nz(COL_A, COL_B)

Upvotes: 4

Related Questions