How to get a union of two data sets?

Andrew2009

Well Known Member
I have a business view that have two date fields (SRPDJ and CDJ). Users will enter into data selection whatever they want and I want to use whatever they entered and get all records that match that criteria and fall within date ranges for SRPDJ and CDJ

For example

Let's say users enter Community = 'OTT' into the data selection then

I want to get all records where Community is 'OTT' and SRPDJ is between 1/1/2016 and 12/31/2016

And

All records where Community is 'OTT' and CDJ is between 1/1/2017 and 12/31/2017

So it's a union of two data sets. Users will enter the date ranges into processing options but I hard code it here for simplicity. Here's my code

Set Selection Append Flag( <Yes>)

Set User Selection(BC Sales Date (Julian) (F44H501)(SRPDJ), <Greater Than or Equal To>, 1/1/2016, <And>)

Set User Selection(BC Sales Date (Julian) (F44H501)(SRPDJ), <Less Than or Equal To>, PO 12/31/2016, <And>)


Set User Selection(BC Sales Date (Julian) (F44H501)(CDJ), <Greater Than or Equal To>, 1/1/2017, <Or>)

Set User Selection(BC Sales Date (Julian) (F44H501)(CDJ), <Less Than or Equal To>, 12/31/2017, <And>)


But I only get all the records for SRPDJ and NOT CDJ.


Do you know why?

Thanks

JDE 9.2
 
Andrew,

Is there any data that satisfies the community = 'OTT' and CDJ between 1/1/2017 and 12/31/2017 ?

Get a debug log and look at the SQL it produces.
 
Yes there are data for community = 'OTT' and CDJ between 1/1/2017 and 12/31/2017. So my code is correct?
 
Andrew,

What is the SQL that is produced? You can get it from a debug log, which is best because it includes actual values, or you can get it from the execution detail, which uses the bind variables, but does not require a debug log.
 
Hi Andrew,

I may have spotted it, but I maybe missing something

You said:

"I want to get all records where Community is 'OTT' and SRPDJ is between 1/1/2016 and 12/31/2016
And
All records where Community is 'OTT' and CDJ is between 1/1/2017 and 12/31/2017"

So you said AND yet you coded something different. You added an OR

Set Selection Append Flag( <Yes>)
Set User Selection(BC Sales Date (Julian) (F44H501)(SRPDJ), <Greater Than or Equal To>, 1/1/2016, <And>)
Set User Selection(BC Sales Date (Julian) (F44H501)(SRPDJ), <Less Than or Equal To>, PO 12/31/2016, <And>)

Set User Selection(BC Sales Date (Julian) (F44H501)(CDJ), <Greater Than or Equal To>, 1/1/2017, <Or>)
Set User Selection(BC Sales Date (Julian) (F44H501)(CDJ), <Less Than or Equal To>, 12/31/2017, <And>)"

So is that the issue???

Also maybe try saying the first line is <NONE> Shouldn't make a difference but who knows
 
Last edited:
Back
Top