enter image description hereWorking with Jaspersoft and SQL : I have a table called Claims which has several fields such as ClaimNo, ShippingNo, ShippingAddress, TrackingNo and 5 other fields. Now a user can upload into that same table a subset of data so just say ClaimNo and ShippingNo . We are able to track when the upload was done by the date as well as the user who created the upload.
I have to now create a SQL report where the user on the front end can select the upload date ,user that uploaded data and 9 filters with a yes or no selection and the joins in the backed have to created based on the filters they select. This is to find if the upload that they created has any matching data in the existing table already .
This is the query I came up with on the condition that there is one matching field so I have a condition to join them . But I was wrong in my assumption . The join conditions need to be created based on user selection
select
main.ClaimNo,
main.ShippingNo,
main.ShippingAddress,
main.TrackingNo
from ClaimsTable Main
inner join
(
select
ClaimNo,
ShippingNo,
ShippingAddress,
TrackingNo
from ClaimsTable
where uploaddate between date1 and date2
and uploadedby ='user1'
) Temp
on (join condition based on filters selected)
I initially assumed that I can join with an id field but clearly that cant be done because they dont have matching ID's. I created two parameters in Jasper one was the front end called ShippingNo which has a dropdown of yes or no ,
($P{ShippingNo}.equals("Yes") ? "AND main.ShippingNo= temp.ShippingNo" : " ")
if they selected yes then it will be appended to the query above . But without a set join condition how do I add these statements as they always have an AND attached to it.
So lets says the data looked like this on the Claim table before upload:
**ID** **ClaimNo** **ShippingNo** **ShippingAddress** **TrackingNo** **UDate**
==================================================================================
1 101 s1 1501 Peters T1 1/23/2020
2 102 s2 5 Lombard Ave T2 1/23/2020
3 103 s3 23 Granville St T3 1/23/2020
4 104 s4 101 Park T4 1/24/2020
And then the next day user uploads data into the same table
**ID** **ClaimNo** **ShippingNo** **ShippingAddress** **TrackingNo** **UDate**
==================================================================================
1 101 s1 1501 Peters T1 1/23/2020
2 102 s2 5 Lombard Ave T2 1/23/2020
3 103 s3 23 Granville St T3 1/23/2020
4 104 s4 101 Park T4 1/24/2020
5 s3 T3 1/25/2020
6 s2 T2 1/25/2020
Now if user runs the report on the front end : The have the option of selecting if they want to match by any of the below filters along with date and user. So in our case user now wants to see if the two uploads they did recently matches with any values that already exist (we can see Shipping No and Tracking No are a match )
Claim No -> Yes or No
Shipping No -> Yes or No
Shipping Address-> Yes or no
TrackingNo -> Yes or No
They select Yes on Shipping No and TrackingNo
so now the sql needs to be constructed as below
select
main.ClaimNo,
main.ShippingNo,
main.ShippingAddress,
main.TrackingNo
from ClaimsTable Main
inner join
(
select
ClaimNo,
ShippingNo,
ShippingAddress,
TrackingNo
from ClaimsTable
where uploaddate between date1 and date2
and uploadedby ='user1'
) Temp
on (main.ShippingNo = temp.Shipping no
and main.TrackingNo= temp.TrackingNo)
**ID** **ClaimNo** **ShippingNo** **ShippingAddress** **TrackingNo** **UDate**
==================================================================================
2 102 s2 5 Lombard Ave T2 1/23/2020
3 103 s3 23 Granville St T3 1/23/2020
How do I get this to work? Do I need to write 81 combinations of the user filter selection or is there an easier way? Any insight would be helpful.
Modify your ON condition to include a tautology as a fixed predicate then build the remainder by AND each each of the selected conditions to it. This then avoids checking each condition for being the first in order by skip the AND for that condition. So
select main.ClaimNo "main ClaimNo"
, main.ShippingNo "main ShippingNo"
, main.ShippingAddress "main ShippingAddress"
, main.TrackingNo "main TrackingNo"
, upload.ClaimNo "upload ClaimNo"
, upload.ShippingNo "upload ShippingNo"
, upload.ShippingAddress "upload ShippingAddress"
, upload.TrackingNo "upload TrackingNo"
from claimstable main
join claimsupload upload
on ( TRUE
...
Where "..." is replace by the AND for each condition where the user selected 'YES'. For the conditions the user selects 'NO' then do not change the building query. Then append the following:
)
where uploaddate between date1 and date2
and uploadedby ='user1';
I combined into a more standard from although the way you have it should also work except perhaps for the position on the where clause. Also in your current query you are actually joining the CalimsTable to itself. Just because you alised one doesn't make it different. For that I used a name of ClaimsUpload. You will need to correct to the actual table.
Lets assume user selected Yes for ClaimNo and TrackingNO and selected NO for all other conditions. The resulting query would be:
select main.ClaimNo "main ClaimNo"
, main.ShippingNo "main ShippingNo"
, main.ShippingAddress "main ShippingAddress"
, main.TrackingNo "main TrackingNo"
, upload.ClaimNo "upload ClaimNo"
, upload.ShippingNo "upload ShippingNo"
, upload.ShippingAddress "upload ShippingAddress"
, upload.TrackingNo "upload TrackingNo"
from claimstable main
join claimsupload upload
on ( TRUE
AND main.claimno = upload.claimno
AND main.trackingno = upload.trackingno
)
where uploaddate between date1 and date2
and uploadedby ='user1';