Jet/ACE SQL, iif statement, 2 false values same column

Question

I have the following iif statement in my sql query within excel.

iif(master.[Canada] = '0' or master.[Canada] = ' ' or master.[Canada] IS NULL,master.[USA], master.[Canada]) as Stackoverflow

but I want to add something like iff([Stackoverflow] is not null, [Stackoverflow], "n/a") as Stackoverflow but cannot have duplicate alias'.

Does anyone know how to do two false conditions? I need to have my blanks in Stackoverflow column show as N/a.

the logic is like this,

+--------+--------+---------------+
| Canada |  USA   | Stackoverflow |
+--------+--------+---------------+
|  12345 |  35262 |         12345 |
|  15678 |  52367 |         15678 |
|        |  97536 |         97536 |
|  17893 |  35252 |         17893 |
|      0 | 120321 |        120321 |
+--------+--------+---------------+

But what if stackoverflow is null, I want to put n/a's there

Trying something like:

iff(master.[Canada] <> ' ', iif(master.[Canada] = '0' or master.[Canada] = ' ' or master.[Canada] IS NULL,master.[USA], master.[Canada]), "n/a") as best_id

But cannot get it to work. undefined funcation iif

Two false conditions? Can you explain all of the conditions and your desired results? You can nest `iif()` statements, but if we nest your new `iif()` then there is no change for `master.[ISIN]` to come through (or it's not clear what you are wanting). Perhaps share some sample data and your desired results so we can help write this up. — JNevill, Oct 31 '18 at 14:50
Use `iif([Stackoverflow] is not null, [Stackoverflow], "n/a") as Stackoverflow1` — Santosh, Oct 31 '18 at 15:06
I want it in the same column lol, not a new one. I added some sample data. — excelguy, Oct 31 '18 at 15:08

Parfait · Accepted Answer · 2018-10-31T16:33:46.783

1

When using nested IIF(), you must equate the number of closing parenetheses with open parenetheses. Below indents the expression, truth part, false part of each IIF() for emphasis:

IIF(master.[Canada] <> '0' AND TRIM(master.[Canada]) <> '' AND master.[Canada] IS NOT NULL, 
    master.[Canada], 
    IIF(master.[USA] <> '0' AND TRIM(master.[USA]) <> '' AND master.[USA] IS NOT NULL, 
        master.[USA], 
        'n/a'
       )
   ) as Stackoverflow

edited Oct 31 '18 at 16:33

answered Oct 31 '18 at 16:04

Parfait

104,375
17
94
125

thanks for this, query executes fine, however I am still getting blank cells as a result. Perhaps instead of the 2nd iif, can we just do a blank cell = n/a type of query? – excelguy Oct 31 '18 at 16:24
See update logic. It is because of `USA` being empty/null. – Parfait Oct 31 '18 at 16:34

Santosh · Answer 2 · 2018-10-31T15:22:04.627

0

Try this

iif([Stackoverflow] is not null, iif(Nz(master.[Canada],'0') = '0' or master.[Canada] = ' ',master.[USA], master.[Canada]) , "n/a") as Stackoverflow1

edited Oct 31 '18 at 15:22

answered Oct 31 '18 at 15:14

Santosh

12,175
4
41
72

circular reference error. I think it is because stackoverflow is being used in the beggining when it is not yet formed. – excelguy Oct 31 '18 at 15:20
@excelguy Yes, just change column name as Stackoverflow1 – Santosh Oct 31 '18 at 15:22
But i want it to be stackoverflow, in that same column, not a newly created column – excelguy Oct 31 '18 at 15:24
@excelguy The above expression has both the conditions you were looking for and you will have 1 column as output i.e. StackOverflow1 – Santosh Oct 31 '18 at 15:27
Can we name the output Stackoverflow somehow? – excelguy Oct 31 '18 at 15:29
I don't think so, as it will refer to existing field. – Santosh Oct 31 '18 at 15:34

Jet/ACE SQL, iif statement, 2 false values same column

2 Answers2