"The Text is too long to be edited" (1 Viewer)

Erin M 2021

Member
Local time
Today, 04:48
Joined
Apr 14, 2021
Messages
77
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)
 
Last edited:

plog

Banishment Pending
Local time
Today, 04:48
Joined
May 11, 2011
Messages
11,613
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

Well-known member
Local time
Today, 05:48
Joined
Apr 9, 2015
Messages
4,339
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:48
Joined
Feb 19, 2002
Messages
42,981
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

Active member
Local time
Today, 12:48
Joined
Jan 28, 2022
Messages
310
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

Code:
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

Super Moderator
Staff member
Local time
Today, 09:48
Joined
Feb 19, 2013
Messages
16,553
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

Active member
Local time
Today, 12:48
Joined
Jan 28, 2022
Messages
310
Code:
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
 

Users who are viewing this thread

Top Bottom