IIF Help on complex query

[eerich]

Hi everyone,

I am hoping for help on an IIF statement within a query that is not producing results expected. Analyzing a security table where I self-joined table and I need the results to display on one row per user. The scenario is looking at security values where access is granted using security type A and actions are granted with security type C AND D.

The IIF comes into the equation bc not all users have action rights C and D, but they all have A So when there is no C or D rights I need the IIF to just insert a value of 'N/A', but if the rights do exist, the results should return the update rights values in 4 columns. Here is my query:

SELECT DISTINCT F.USER,, F.PROGRAM, F.SECTYPE, F.RUNAPP, 
IIF(F1.SECTYPE IN ('C', 'D'), (V1.ADD, V1.EDIT, V1.DLETE), 'N/A')
FROM RIGHTS AS F INNER JOIN RIGHTS AS F1 ON (F.USER = F1.USER) AND (F.PROGRAM = F1.PROGRAM) 
ORDER BY 1, 2;

Expected results would be:
USER // PROGRAM // SEC // RUNAPP // SEC // ADD // EDIT // DLETE //
SHAWN // OIL DRILL // A // YES // C // YES // YES // NO //
JAKE // ACCTS REC // A // YES // N/A // -- // -- // -- //

Results I receive instead is:
USER // PROGRAM // SEC // RUNAPP // SEC // ADD // EDIT // DLETE //
SHAWN // OIL DRILL // A // YES // C // YES // YES // NO //
JAKE // ACCTS REC // A // YES // A // // // //

The second 'A' value is incorrect. Any help is appreciated

 

[Andrzejek]

“I self-joined table” - [tt]RIGHTS AS [highlight #FCE94F]F[/highlight] INNER JOIN RIGHTS AS [highlight #FCE94F]F1[/highlight][/tt]
But in your Select you have: [tt][highlight #8AE234]V1[/highlight].ADD, [highlight #8AE234]V1[/highlight].EDIT, [highlight #8AE234]V1[/highlight].DLETE[/tt]
Where do the [tt]V1[/tt] come from?

Plus, your outcome from your Select statement will have:
IF(F1.SECTYPE IN ('C', 'D'):
F.USER, F.PROGRAM, F.SECTYPE, F.RUNAPP, V1.ADD, V1.EDIT, V1.DLETE (7 fields)
else
F.USER, F.PROGRAM, F.SECTYPE, F.RUNAPP, N/A (5 fields)

Try:
IIF(F1.SECTYPE IN ('C', 'D'), (V1.ADD, V1.EDIT, V1.DLETE), ('N/A', '--', '--'))

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[eerich]

The V1 is a typo. It should have been F1 which I corrected, but it still doesn't work.

It doesn't seem to like to have the multi values in the output based on the logical evaluation. For example, if I return just one value such as sectype it will give the correct results, but when I try return sectype, add, edit, etc it throws an error saying the commas are a problem even though I have them all enclosed in parenthesis as noted in my code above.Is it not possible to get multiple values returned in an Inline IF statement?

 

[eerich]

I've figured out a work around but it is not ideal. It seems it just doesn't like the multiple values pulled into separate columns so what I did was concatenate them all into one field using the '&' and that produced the results accurately. If there is no other solution anyone can help with that I might be missing I can try to use that and do a parsing within excel using a text to column separate (hopefully).

 

[combo]

So you can use three Iif: [tt]IIF(F1.SECTYPE IN ('C', 'D'), V1.ADD, 'N/A') AS ADD[/tt] etc.


combo

 

[Andrzejek]

I hate to point the obvious, but what about an additional table with the values you want to display?
[pre]

ID RunApp Add Edit Delete
1 C Yes Yes No
2 D Yes Yes No
3 A Yes Yes Yes
4 X No No No
[/pre]
And use it instead of IIF in your select statement.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson