- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ODAG - how to make bindings optional
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')