How to replace subquery with condition

Question

Hello I have below query to find the sum of days from different cases.

Tables used :

Table ZBK              Table ZLF        ZT05
Inv_Dt    Vendor  |  Vendor   Term |  TAG  FAEL MONA Term  TAG1
7/02/23   100428  |  100428   AA5  |  16   20    00   AA5   00
17/3/23   100428  |  100429   CA2  |  25   20    01   AA5   07
26/3/23   100428  |                |  31   10    02   AA5   15
22/4/23   100429  |                |  00   07    02   CA2   5

Desired Output (sum_days column) - List column just for reference.
inv_dt     term  tag tag1   fael  mona  sum_days  list
2023-02-07  AA5  16   00     20    00     13      13
2023-03-17  AA5  25   07     20    01     41      14+20   + 07(tag1)
2023-03-26  AA5  31   15     10    02     60      5+30+10 + 15(tag1) 
2023-04-22  CA2  00   05      7    02     51      8+31+7  + 05(tag1)

SELECT ZBK.INV_DT
, ZLF.TERM
, T1.TAG
, T1.FAEL
, T1.MONA
, DATEDIFF(
    'DAYS',
    ZBK.INV_DT,
    ADD_MONTHS(
      DATE_FROM_PARTS(
        YEAR(ZBK.INV_DT), 
        MONTH(ZBK.INV_DT),
        T1.FAEL
      ),
      T1.MONA
    )
  ) "SUM_DAYS"

FROM ZBK
  INNER JOIN ZLF ON ZBK.VENDOR = ZLF.VENDOR
  INNER JOIN ZT05 T1 ON ZLF.TERM = T1.TERM
                    AND T1.TAG = (
                      SELECT MIN(T2.TAG)
                      FROM ZT05 T2
                      WHERE ZLF.TERM = T2.TERM
                        AND T2.TAG > DAY(ZBK.INV_DT)
                    )

ORDER BY ZBK.INV_DT
;

How can I replace subquery of last join...

  INNER JOIN ZT05 T1 ON ZLF.TERM = T1.TERM
                    AND T1.TAG = (
                          SELECT MIN(T2.TAG) 
                          FROM ZT05 T2
                          WHERE ZLF.TERM = T2.TERM
                            AND T2.TAG > DAY(ZBK.INV_DT)
                        )

...to have the same result ?
It is fine if I have to create an extra table

Also, If is there other option to have the same logic without DATE_FROM_PARTS function as it is not working in Vertica.

Answer 1

Most of my experience relates to SQL Server. So, much of this is a guess based on a 10-minute sweep through the Vertica documentation. It may require a bit of massage.

The CTE named T2 is your subquery. Technically, a CTE is evaluated at runtime, so it's still a subquery. This technique helps tidy your code and make it more readable. If you have performance issues, you'll want to use a table. I think what I have done here is equivalent to what you provided, just written differently. Although, it may perform more poorly than the subquery you had written because it may require more table scans. It's a tradeoff between readability (developer hours) and performance (query milliseconds).

Also notice I used TO_DATE() to replace DATE_FROM_PARTS().

I have included your tables as common table expressions (CTEs) for future reference. Next time, include DDL rather than data tables in your question. That makes it easier for a potential answerer to create the initial conditions.

WITH 
-- your in data, don't use in query ...
ZBK (
    Inv_Dt
  , Vendor
) AS (
            SELECT TO_DATE('07/02/23', 'DD/MM/YYYY'), 100428 FROM DUAL
  UNION ALL SELECT TO_DATE('17/03/23', 'DD/MM/YYYY'), 100428 FROM DUAL
  UNION ALL SELECT TO_DATE('26/03/23', 'DD/MM/YYYY'), 100428 FROM DUAL
  UNION ALL SELECT TO_DATE('22/04/23', 'DD/MM/YYYY'), 100429 FROM DUAL
),
ZLF (
    Vendor
  , Term
) AS (
            SELECT 100428, 'AA5' FROM DUAL
  UNION ALL SELECT 100429, 'CA2' FROM DUAL
),
ZT05 (
    TAG
  , FAEL
  , MONA
  , Term
  , TAG1
) AS (
            SELECT '16', '20', '00', 'AA5', '00' FROM DUAL
  UNION ALL SELECT '25', '20', '01', 'AA5', '07' FROM DUAL
  UNION ALL SELECT '31', '10', '02', 'AA5', '15' FROM DUAL
  UNION ALL SELECT '00', '07', '02', 'CA2', '5'  FROM DUAL
), 
-- end of input, real query starts here
-- Don't forget the WITH statement, above.


T2 AS (
  SELECT MIN(ZT05.TAG) TAG
  FROM ZT05 
    INNER JOIN ZLF ON ZLF.TERM = ZT05.TERM
    INNER JOIN ZBK ON ZBK.VENDOR = ZLF.VENDOR
  WHERE ZT05.TAG > DAY(ZBK.INV_DT)
)

SELECT ZBK.INV_DT
, ZLF.TERM
, T1.TAG
, T1.FAEL
, T1.MONA
, DATEDIFF(
    'DAYS',
    ZBK.INV_DT,
    ADD_MONTHS(
      TO_DATE(
          TO_CHAR(YEAR(ZBK.INV_DT), 'FM0999') || 
          TO_CHAR(MONTH(ZBK.INV_DT), 'FM09') || 
          TO_CHAR(T1.FAEL, 'FM09'), 
        'YYYYMMDD'
      ),
      T1.MONA
    )
  ) "SUM_DAYS"

FROM ZBK
  INNER JOIN ZLF ON ZBK.VENDOR = ZLF.VENDOR
  INNER JOIN ZT05 T1 ON ZLF.TERM = T1.TERM
  INNER JOIN T2 ON T2.TAG = T1.TAG

ORDER BY ZBK.INV_DT