ODAG - how to make bindings optional

[micklesj]

Hello!

Does anyone by chance know how to make it so my bindings do not all have to be selected?

Right now the on demand app is only generated correctly if all 3 “filters” have a selection in them.  If I only use 1 or 2 of them, it still turns green and meets the criteria but fails.

So far can only get to work if all of them have something selected.

Let vPostMonth = $(od_POSTING_EOM);

Let vBranchName = $(od_ACCOUNT_ASSIGNED_BRANCH_COST_CENTER_NM);

Let vRegion = $(od_RETAIL_REGION_CD);

I have tried odo and odso  but it doesn’t like those.  Also odag didn't work for me.

I think it’s trying to put a blank in the variable and then bombs down at the bottom in the where clause.  Is there a way around that?  I would like them to be able to select as many as they want or none at all.

It does work if a branch is selected.

Thank you!

[Or · Accepted solution]

If you don't select a branch, that part of the WHERE condition becomes COST_CENTER IN (), which isn't valid SQL. You'll need to rephrase either your variable setting or your code to handle the situation where no value is passed.

[micklesj]

thank you!

[micklesj]

Ok yes, that was helpful thank you!  This is what we had to do to make this work: 

(and you have to do it for every single binding)

Let vRegion = $(od_RETAIL_REGION_CD);

[Temp_RETAIL_REGION_CD]:
SQL SELECT ''''||LISTAGG(DISTINCT RETAIL_REGION_CD, ''',''')||'''' as REGIONS
FROM XXX.XXXX.XXXXXXXXX_VW
WHERE RETAIL_REGION_CD IS NOT NULL
;

Let vRegionAll = peek('REGIONS',0,'Temp_RETAIL_REGION_CD') ;
Trace vRegionAll;

IF ('$(vRegion)') ='' THEN;
let vRegionSQL = vRegionAll;
ELSE;
let vRegionSQL=vRegion;
END IF;
Drop Table Temp_RETAIL_REGION_CD;

and then later at the bottom of the main query 

Where  RETAIL_REGION_CD In ($(vRegionSQL))

[Or]

That is indeed one option.

You could also use something like:

If Len(vRegion) > 0 Then

Original Code Goes Here

Else

Same Code Goes Here But without the condition on Region

Or you could write it straight into your SQL, something like:

Let vRegion = Coalesce($(od_RETAIL_REGION_CD),'ZZZZZ');

Select Something From Somewhere

Where (Region IN ('$(vRegion)')  OR Region = 'ZZZZZ')