"The Text is too long to be edited"

[Erin M 2021]

Hi, I had to build this obnoxious report for accounting purposes. One gift can have many (10+) accounts (funds) associated with it. And the debit account can be one of many. Here's a snippet of what I came up with. It functions well, other than the fund count being higher than 8. Any suggestions?

UNION SELECT [Gift Clearing Report].Gf_CNBio_ID AS [Const ID], [Gift Clearing Report].Gf_Date AS [Gift Date], [Gift Clearing Report].Gf_Amount AS [Gift Amount], [Gift Clearing Report].Gf_Constit_Code AS Constituency, [Gift Clearing Report].Gf_Fnds_1_08_Amount AS SplitAmount, [Gift Clearing Report].Gf_Fnds_1_08_Fund_ID AS FundID, [Gift Clearing Report].Gf_Fnds_1_08_Fn_Fund_type AS FundType, [Gift Clearing Report].Gf_Fnds_1_08_FnAtrCat_1_01_Description AS FundCode, [Gift Date] & " " & [GiftID] AS [DESC], [Gift Clearing Report].Gf_Fnds_1_08_FnAtrCat_2_01_Description AS OU, [Gift Clearing Report].Gf_Fnds_1_08_FnAtrCat_3_01_Description AS DeptID, [Gift Clearing Report].Gf_Type AS GiftType, [Gift Clearing Report].Gf_Fnds_1_08_FnAtrCat_4_01_Description AS Class, [Gift Clearing Report].Gf_Fnds_1_08_FnAtrCat_5_01_Description AS ProjectID, [Gift Clearing Report].Gf_Fnds_1_08_FNAtrCat_6_01_Description AS ActivityID, [Gift Clearing Report].Gf_Fnds_1_08_FNAtrCat_7_01_Description AS BudRef, IIF([ActivityID] IS Null,"",

IIF([ActivityID] Is Not Null,"code","code")) AS PCBU, [Gift Clearing Report].Gf_Batch_Number AS Batch, [Gift Clearing Report].Gf_System_ID AS GiftID, [SplitAmount] AS Debit, [SplitAmount]*-1 AS Amount, IIF(([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620") AND ([Constituency]="Alumni"),'450440',
IIF(([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620") AND ([Constituency]="Parent" OR [Constituency]="Staff" OR [Constituency]="Student" OR [Constituency]="Other Individual"),'450430',
IIF(([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620") AND ([Constituency]="Foundation"),'450420',
IIF(([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620") AND ([Constituency]="Corporation"),'450410',
IIF(([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620") AND ([Constituency]="Fund-Raising Organization" OR [Constituency]="Religious Organization" OR [Constituency]="Other Organization"),'450450',
IIF(([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910") AND ([Constituency]="Alumni"),'450330',
IIF(([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910") AND ([Constituency]="Parent" Or [Constituency]="Staff" Or [Constituency]="Student" Or [Constituency]="Other Individual"),'450330',
IIF(([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910") AND ([Constituency]="Foundation"),'450320',
IIF(([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910") AND ([Constituency]="Corporation"),'450310',
IIF(([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910") AND ([Constituency]="Fund-Raising Organization" OR [Constituency]="Religious Organization" OR [Constituency]="Other Organization"),'450350',
0)))))))))) AS PSAccount1, IIF(([FundCode]="120" OR [FundCode]="130" OR [FundCode]="210" OR [FundCode]="220" OR [FundCode]="230" OR [FundCode]="233" OR [FundCode]="235" OR [FundCode]="240" OR [FundCode]="260" OR [FundCode]="310" OR [FundCode]="415" OR [FundCode]="418" OR [FundCode]="428" OR [FundCode]="440" OR [FundCode]="445" OR [FundCode]="448" OR [FundCode]="450" OR [FundCode]="455") AND ([Constituency]="Alumni"),'450070',
IIF(([FundCode]="120" OR [FundCode]="130" OR [FundCode]="210" OR [FundCode]="220" OR [FundCode]="230" OR [FundCode]="233" OR [FundCode]="235" OR [FundCode]="240" OR [FundCode]="260" OR [FundCode]="310" OR [FundCode]="415" OR [FundCode]="418" OR [FundCode]="428" OR [FundCode]="440" OR [FundCode]="445" OR [FundCode]="448" OR [FundCode]="450" OR [FundCode]="455") AND ([Constituency]="Parent" OR [Constituency]="Staff" OR [Constituency]="Student" OR [Constituency]="Other Individual"),'450050',
IIF(([FundCode]="120" OR [FundCode]="130" OR [FundCode]="210" OR [FundCode]="220" OR [FundCode]="230" OR [FundCode]="233" OR [FundCode]="235" OR [FundCode]="240" OR [FundCode]="260" OR [FundCode]="310" OR [FundCode]="415" OR [FundCode]="418" OR [FundCode]="428" OR [FundCode]="440" OR [FundCode]="445" OR [FundCode]="448" OR [FundCode]="450" OR [FundCode]="455") AND ([Constituency]="Foundation"),'450030',
IIF(([FundCode]="120" OR [FundCode]="130" OR [FundCode]="210" OR [FundCode]="220" OR [FundCode]="230" OR [FundCode]="233" OR [FundCode]="235" OR [FundCode]="240" OR [FundCode]="260" OR [FundCode]="310" OR [FundCode]="415" OR [FundCode]="418" OR [FundCode]="428" OR [FundCode]="440" OR [FundCode]="445" OR [FundCode]="448" OR [FundCode]="450" OR [FundCode]="455") AND ([Constituency]="Corporation" OR [Constituency]="Fund-Raising Organization"),'450010',
IIF(([FundCode]="120" OR [FundCode]="130" OR [FundCode]="210" OR [FundCode]="220" OR [FundCode]="230" OR [FundCode]="233" OR [FundCode]="235" OR [FundCode]="240" OR [FundCode]="260" OR [FundCode]="310" OR [FundCode]="415" OR [FundCode]="418" OR [FundCode]="428" OR [FundCode]="440" OR [FundCode]="445" OR [FundCode]="448" OR [FundCode]="450" OR [FundCode]="455") AND ([Constituency]="Religious Organization"),'450190',
IIF(([FundCode]="120" OR [FundCode]="130" OR [FundCode]="210" OR [FundCode]="220" OR [FundCode]="230" OR [FundCode]="233" OR [FundCode]="235" OR [FundCode]="240" OR [FundCode]="260" OR [FundCode]="310" OR [FundCode]="415" OR [FundCode]="418" OR [FundCode]="428" OR [FundCode]="440" OR [FundCode]="445" OR [FundCode]="448" OR [FundCode]="450" OR [FundCode]="455") AND ([Constituency]="Other Organization"),'450090',
0,)))))) AS PSAccount2, IIF([PSAccount1]="0",[PSAccount2],[PSAccount1]) AS PSAccount3, IIF([FundType]="Sales & Service",'470030',[PSAccount3]) AS Account, [Gift Clearing Report].Gf_Pay_Method AS [Pay Method], [Gift Clearing Report].Gf_gift_subtype AS Subtype, [Gift Clearing Report].Gf_AttrCat_1_01_Description AS PayrollCode, IIF([Subtype]="Development Entry" AND [Pay method]="Credit Card",'122321',
IIF([Subtype]="Development Entry" AND [Pay method]="Cash",'122323',
IIF([Subtype]="Global Payments",'122325',
IIF([Subtype]="Online Entry (bbnc)",'122321',
IIF([Subtype]="Online Entry (UTMB)",'122321',
IIF([Subtype]="Lockbox (bank)",'122326',
IIF([Subtype]="utso",'122324',

0))))))) AS ClearingAccount, " " AS [Trans ID], " " AS [Trans Date], "AJE" AS Source, " " AS Interface_Ref, "UTMBG" AS Business_Unit, "ACT" AS Ledger, "110" AS CLFundCode, "10098" AS CLOpUnit, "181100" AS CLDeptID, " " AS CLClass, " " AS CLProject, " " AS CLPCBU, " " AS CLActivityID, " " AS CLBudRef, " " AS Program_Code
FROM [Gift Clearing Report]
Where ([Gift Clearing Report].Gf_Fnds_1_08_Amount Is Not Null)

 

[theDBguy]

Any suggestions?

Use a joining table to replace all your IIF() statements.

 

[plog]

1. Only UNION in a UNION query. Don't try and cram logic into any of the SELECTS or criteria in the WHERE. Use a subquery if necessary to do all that logic. That way when things go wrong, you can better drill down to the issue and handle it.

2. Create a function to handle those FundCode IIfs. Pass it FundCode and Constinuency and have it return the correct value.

3. Create a table to transform Subtype into ClearingAccount.

4. You can't calculate a value and use it in the same query. You calculate PSAccount2 and then try and use it in PSAccount3. That's not going to work. You will need to do that in a subquery, or use algebraic replacement to recalculate PSAccount2 in PSAccount3.

Most importantly, divide, conquery and work gradually. Get something working, then add to it. If the new addition breaks it, you know where the error is and can better identify and fix it before you move on.

 

[Ranman256]

DO NOT build IFs in sql.
build a table (a lookup table) with these codes. Join the table to your data and this will convert them for you without using IF.

 

[jdraw]

You maybe able to adapt the approach in this thread ---Exclude Data using a table by Bob Larson.
Can also be used for Include.

 

[Pat Hartman]

IIF(([FundCode]="120" OR [FundCode]="130" OR [FundCode]="210" OR [FundCode]="220" OR [FundCode]="230" OR [FundCode]="233" OR [FundCode]="235" OR [FundCode]="240" OR [FundCode]="260" OR [FundCode]="310" OR [FundCode]="415" OR [FundCode]="418" OR [FundCode]="428" OR [FundCode]="440" OR [FundCode]="445" OR [FundCode]="448" OR [FundCode]="450" OR [FundCode]="455") AND ([Constituency]="Other Organization"),'450090',

Can be simplified to:

IIF(([FundCode] In ("120","130","210","220","230","233","235","240","260","310","415","418","428","440","445","448","450","455")) AND ([Constituency]="Other Organization"),'450090',

However, table changes would probably be better. For example, add a TypeCode to the table that defines FundCode so you can select TypeX and have that mean the whole list of values above. Then you can have a table that uses TypeCode and Constituency to return the account number.

 

[SHANEMAC51]

It functions well, other than the fund count being higher than 8.

at any time, you can get ACCESS restrictions on the number of nested IIF -
you already have 10 of the allowed 14

name replacements made to improve readability

iif((FC="460" OR FC="610" OR FC="620")
 AND (C="Alumni"),'450440',
iif((FC="460" OR FC="610" OR FC="620")
 AND (C="Parent" OR C="Staff" OR C="Student" OR C="Other Individual"),'450430',
iif((FC="460" OR FC="610" OR FC="620")
 AND (C="Foundation"),'450420',
iif((FC="460" OR FC="610" OR FC="620")
 AND (C="Corporation"),'450410',
iif((FC="460" OR FC="610" OR FC="620")
 AND (C="Fund-Raising Organization" OR C="Religious Organization" OR C="Other Organization"),'450450',

iif((FC="810" OR FC="815" OR FC="820" OR FC="870" OR FC="873" OR FC="874" OR FC="875" OR FC="877" OR FC="890" OR FC="910")
 AND (C="Alumni"),'450330',

iif((FC="810" OR FC="815" OR FC="820" OR FC="870" OR FC="873" OR FC="874" OR FC="875" OR FC="877" OR FC="890" OR FC="910")
 AND (C="Parent" Or C="Staff" Or C="Student" Or C="Other Individual"),'450330',

iif((FC="810" OR FC="815" OR FC="820" OR FC="870" OR FC="873" OR FC="874" OR FC="875" OR FC="877" OR FC="890" OR FC="910")
 AND (C="Foundation"),'450320',

iif((FC="810" OR FC="815" OR FC="820" OR FC="870" OR FC="873" OR FC="874" OR FC="875" OR FC="877" OR FC="890" OR FC="910")
 AND (C="Corporation"),'450310',

iif((FC="810" OR FC="815" OR FC="820" OR FC="870" OR FC="873" OR FC="874" OR FC="875" OR FC="877" OR FC="890" OR FC="910")
 AND (C="Fund-Raising Organization" OR C="Religious Organization" OR C="Other Organization"),'450350',
0)))))))))) AS PSAccount1,

 

[CJ_London]

you can also use IN to reduce the the amount of text. Not going to do the whole thing but this

iif((FC="810" OR FC="815" OR FC="820" OR FC="870" OR FC="873" OR FC="874" OR FC="875" OR FC="877" OR FC="890" OR FC="910")
AND (C="Alumni"),'450330',

could be reduced to
iif(FC IN ("810","815","820","870","873","874","875","877","890","910") AND (C="Alumni"),'450330',

 

[SHANEMAC51]

SELECT .....
"810-815-820-870-873-874-875-877-890-910") AS S810,
"910-...-945") AS S910,


Iif(INSTR(S810,FC)>0 AND="Alumni",'450330',
Iif(INSTR(S810,FC)>0 AND="REGION",'450530',
Iif(INSTR(S810,FC)>0 AND="CLIENT",'450360',
Iif(INSTR(S910,FC)>0 AND="Alumni",'450370',"000000") AS PARAM1,
....
FROM TABL1