sql - 加入即时创建

Question

在此处输入图像描述使用 Jaspersoft 和 SQL：我有一个名为 Claims 的表，其中有几个字段，例如 ClaimNo、ShippingNo、ShippingAddress、TrackingNo 和其他 5 个字段。现在用户可以将数据子集上传到同一个表中，所以只需说 ClaimNo 和 ShippingNo 。我们能够跟踪上传完成的日期以及创建上传的用户。

我现在必须创建一个 SQL 报告，前端的用户可以选择上传日期，上传数据的用户和 9 个过滤器，选择是或否，并且必须根据他们选择的过滤器创建支持的连接。这是为了查找他们创建的上传是否已经在现有表中有任何匹配的数据。

这是我在有一个匹配字段的条件下提出的查询，因此我有条件加入它们。但我的假设是错误的。需要根据用户选择创建加入条件

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)

我最初假设我可以使用 id 字段加入，但显然不能这样做，因为它们没有匹配的 ID。我在 Jasper 中创建了两个参数，一个是名为 ShippingNo 的前端，它有一个 yes 或 no 的下拉菜单，

($P{ShippingNo}.equals("Yes") ? "AND main.ShippingNo= temp.ShippingNo"   : " ") 

如果他们选择是，那么它将被附加到上面的查询中。但是如果没有设置连接条件，我如何添加这些语句，因为它们总是附加一个 AND。

所以让我们说数据在上传之前在 Claim 表上看起来像这样：

 **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

现在，如果用户在前端运行报告：可以选择是否要通过以下任何过滤器以及日期和用户进行匹配。因此，在我们的案例中，用户现在想查看他们最近所做的两次上传是否与任何已经存在的值匹配（我们可以看到 Shipping No 和 Tracking No 是匹配的）

Claim No -> Yes or No
Shipping No -> Yes or No
Shipping Address-> Yes or no
TrackingNo -> Yes or No

他们在 Shipping No 和 TrackingNo 上选择 Yes

所以现在需要如下构造sql

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

我怎样才能让它工作？我需要编写用户过滤器选择的 81 种组合还是有更简单的方法？任何见解都会有所帮助。

标签： sqlpostgresqljasper-reports

Answer 1

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';