See the latest updates to the Gemini Apps Privacy HubOpens in a new window
Conversation with Gemini
profile picture
FAILED: ParseException line 22:0 mismatched input 'on' expecting ) near 'd' in subquery source (state=42000,code=40000)
The error message indicates a syntax issue in the subquery within your Sales_Performance_With_B CTE. It seems there's an extra closing parenthesis before the ON clause that joins table_a and the subquery.

Here's the corrected version of the Sales_Performance_With_B CTE:

SQL
Sales_Performance_With_B AS (
  SELECT a.*, d.*
  FROM table_a a
  LEFT JOIN (
    select distinct cast(party_id as bigint) as party_id, immigration_cat_cd, start_date,
      date_sub(coalesce(LEAD(start_date) OVER (partition by party_id order by start_date ), current_date),1) as end_date
    from  
    (select
      party_id,
      immigration_cat_cd,
      businesseffectivedate as start_date,
      rank() over (partition by party_id, immigration_cat_cd order by businesseffectivedate) as rank
    from table_b) d
  ON lpad(a.prim_own_cif_id,15,'0') = lpad(d.party_id,15,'0')
  WHERE 
    trim(a.channel) not in ('Branch','CCC') 
    and trim(a.type) not in ('US$','GTSP') 
    and a.opn_dt > '2023-10-31' 
    and a.opn_dt <'2023-12-01'
  AND
    (
      d.party_id is null  
      OR (a.opn_dt between d.start_date and d.end_date)
    )
)
Use code with caution.
Changes made:

Removed the extra closing parenthesis before the ON clause in the subquery.
This corrected query should now execute without the parsing error.

profile picture
FAILED: ParseException line 22:29 cannot recognize input near ')' 'd' 'ON' in subquery source (state=42000,code=40000)
The error message "ParseException line 22:29 cannot recognize input near ')' 'd' 'ON' in subquery source" persists, indicating the issue is still within the subquery that defines Sales_Performance_With_B. Here are two possible reasons and solutions:

1. Missing Comma:

Double-check that there's a comma separating the selection of columns (party_id, immigration_cat_cd, etc.) and the subquery definition within the parentheses in the SELECT statement of the subquery.

Here's the corrected version assuming a missing comma:

SQL
Sales_Performance_With_B AS (
  SELECT a.*, d.*
  FROM table_a a
  LEFT JOIN (
    select distinct cast(party_id as bigint) as party_id, 
                   immigration_cat_cd, 
                   start_date,
                   date_sub(coalesce(LEAD(start_date) OVER (partition by party_id order by start_date ), current_date),1) as end_date
    from  
    (select
      party_id,
      immigration_cat_cd,
      businesseffectivedate as start_date,
      rank() over (partition by party_id, immigration_cat_cd order by businesseffectivedate) as rank
    from table_b) d  -- Add alias 'd' here
  ON lpad(a.prim_own_cif_id,15,'0') = lpad(d.party_id,15,'0')
  WHERE 
    trim(a.channel) not in ('Branch','CCC') 
    and trim(a.type) not in ('US$','GTSP') 
    and a.opn_dt > '2023-10-31' 
    and a.opn_dt <'2023-12-01'
  AND
    (
      d.party_id is null  
      OR (a.opn_dt between d.start_date and d.end_date)
    )
)
Use code with caution.
2. Incorrect Subquery Alias:

Make sure you've assigned an alias (d) to the subquery within the parentheses. This alias is used in the ON clause to join with table_a.

The corrected version (assuming missing alias) is provided above.

Verification Steps:

Ensure there's a comma separating the column selection and the subquery definition in the subquery's SELECT statement.
Verify that you've assigned an alias (d in this case) to the subquery within the parentheses.
By addressing one of these potential issues, the parsing error should be resolved, allowing your query to execute successfully.

profile picture
with Sales_Performance_With_B AS (
 SELECT a.*, d.*
 FROM table_a a
 LEFT JOIN (
  select distinct cast(party_id as bigint) as party_id, 
          immigration_cat_cd, 
          start_date,
          date_sub(coalesce(LEAD(start_date) OVER (partition by party_id order by start_date ), current_date),1) as end_date
  from  
  (select
   party_id,
   immigration_cat_cd,
   businesseffectivedate as start_date,
   rank() over (partition by party_id, immigration_cat_cd order by businesseffectivedate) as rank
  from table_b ) d -- Add alias 'd' here
 ON lpad(a.prim_own_cif_id,15,'0') = lpad(d.party_id,15,'0')
 WHERE 
  trim(a.channel) not in ('Branch','CCC') 
  and trim(a.type) not in ('US$','GTSP') 
  and a.opn_dt > '2023-10-31' 
  and a.opn_dt <'2023-12-01'
 AND
  (
   d.party_id is null  
   OR (a.opn_dt between d.start_date and d.end_date)
  )
)
 SELECT * FROM Sales_Performance
UNION ALL
SELECT * FROM Sales_Performance_With_B;

FAILED: ParseException line 16:0 mismatched input 'ON' expecting ) near 'd' in subquery source (state=42000,code=40000)
I'd be glad to help you create the correct query based on the feedback and identified issues. Here's the revised version incorporating the necessary corrections:

SQL
WITH Sales_Performance AS (
  SELECT DISTINCT CAST(a.acct_num AS BIGINT) AS acct_num,
                  CAST(a.prim_own_cif_id AS BIGINT) AS cust_cid,
                  SUBSTR(TRIM(a.opn_dt), 1, 7) AS year_month,
                  a.opn_dt AS open_date,
                  a.type AS products,
  CASE WHEN d.immigration_cat_cd IN ('000','999','C1','FC1','FC2','FC3','EN2','PV1','PV2','SW1','SW2','SW3','SE2','STP','C2','NV5') THEN 'PR'
       WHEN d.immigration_cat_cd IN ('FW','FW0') THEN 'FW'
       WHEN d.immigration_cat_cd IN ('S','S00') THEN 'IS'
       ELSE 'Regular_Customers'
  END AS MCB_Category
  FROM table_a a
  LEFT JOIN (
    SELECT DISTINCT CAST(party_id AS BIGINT) AS party_id,
                   immigration_cat_cd,
                   start_date,
                   date_sub(coalesce(LEAD(start_date) OVER (PARTITION BY party_id ORDER BY start_date), CURRENT_DATE), 1) AS end_date
    FROM (
      SELECT party_id,
             immigration_cat_cd,
             businesseffectivedate AS start_date,
             RANK() OVER (PARTITION BY party_id, immigration_cat_cd ORDER BY businesseffectivedate) AS rank
      FROM table_b
    ) d
    ON lpad(a.prim_own_cif_id, 15, '0') = lpad(d.party_id, 15, '0')
  ) d
  WHERE trim(a.channel) NOT IN ('Branch','CCC')
    AND trim(a.type) NOT IN ('US$','GTSP')
    AND a.opn_dt > '2023-10-31'
    AND a.opn_dt < '2023-12-01'
    AND (
      d.party_id IS NULL
      OR (a.opn_dt BETWEEN d.start_date AND d.end_date)
    )
),
Sales_Performance_With_B AS (
  SELECT a.*, d.*
  FROM table_a a
  LEFT JOIN (
    SELECT DISTINCT CAST(party_id AS BIGINT) AS party_id,
                   immigration_cat_cd,
                   start_date,
                   date_sub(coalesce(LEAD(start_date) OVER (PARTITION BY party_id ORDER BY start_date), CURRENT_DATE), 1) AS end_date
    FROM (
      SELECT party_id,
             immigration_cat_cd,
             businesseffectivedate AS start_date,
             RANK() OVER (PARTITION BY party_id, immigration_cat_cd ORDER BY businesseffectivedate) AS rank
      FROM table_b
    ) d
    ON lpad(a.prim_own_cif_id, 15, '0') = lpad(d.party_id, 15, '0')
  ) d
  WHERE trim(a.channel) NOT IN ('Branch','CCC')
    AND trim(a.type) NOT IN ('US$','GTSP')
    AND a.opn_dt > '2023-10-31'
    AND a.opn_dt < '2023-12-01'
    AND (
      d.party_id IS NULL
      OR (a.opn_dt >= d.start_date AND a.opn_dt <= d.end_date)  -- Inclusive date comparison
    )
)
SELECT * FROM Sales_Performance
UNION ALL
SELECT * FROM Sales_Performance_With_B;
Use code with caution.
Key Changes:

Corrected ON Clause: The ON clause in both CTEs now correctly references the subquery alias d.
Inclusive Date Comparison: The WHERE clause in Sales_Performance_With_B uses >= and <= for the date comparison to ensure accounts opened on the start and
profile picture
here is my query - fix this
The provided query seems to be almost correct based on the previous discussions. However, there's one potential issue and some formatting inconsistencies that can be addressed:

1. Potential Issue with DISTINCT:

In the Sales_Performance CTE, you're using DISTINCT on all selected columns. This might not be necessary depending on your intended outcome. If you want to remove duplicate rows entirely, using DISTINCT is fine. However, if you only want to ensure unique values for certain columns (e.g., acct_num and cust_cid), you can remove DISTINCT or use SELECT DISTINCT acct_num, cust_cid, ...

2. Formatting:

Consider adding line breaks after each FROM, WHERE, and JOIN clause for better readability.
Ensure consistent indentation throughout the query.
Here's the revised version incorporating these suggestions (assuming you only need unique acct_num and cust_cid):

SQL
WITH Sales_Performance AS (
  SELECT 
    CAST(a.acct_num AS BIGINT) AS acct_num,
    CAST(a.prim_own_cif_id AS BIGINT) AS cust_cid,
    SUBSTR(TRIM(a.opn_dt), 1, 7) AS year_month,
    a.opn_dt AS open_date,
    a.type AS products,
  CASE WHEN d.immigration_cat_cd IN ('000','999','C1','FC1','FC2','FC3','EN2','PV1','PV2','SW1','SW2','SW3','SE2','STP','C2','NV5') THEN 'PR'
       WHEN d.immigration_cat_cd IN ('FW','FW0') THEN 'FW'
       WHEN d.immigration_cat_cd IN ('S','S00') THEN 'IS'
       ELSE 'Regular_Customers'
  END AS MCB_Category
  FROM table_a a
  LEFT JOIN (
    SELECT DISTINCT CAST(party_id AS BIGINT) AS party_id,
                   immigration_cat_cd,
                   start_date,
                   date_sub(coalesce(LEAD(start_date) OVER (PARTITION BY party_id ORDER BY start_date), CURRENT_DATE), 1) AS end_date
    FROM (
      SELECT party_id,
             immigration_cat_cd,
             businesseffectivedate AS start_date,
             RANK() OVER (PARTITION BY party_id, immigration_cat_cd ORDER BY businesseffectivedate) AS rank
      FROM table_b
    ) d
    ON lpad(a.prim_own_cif_id, 15, '0') = lpad(d.party_id, 15, '0')
  ) d
  WHERE trim(a.channel) NOT IN ('Branch','CCC')
    AND trim(a.type) NOT IN ('US$','GTSP')
    AND a.opn_dt > '2023-10-31'
    AND a.opn_dt < '2023-12-01'
    AND (
      d.party_id IS NULL
      OR (a.opn_dt >= d.start_date AND a.opn_dt <= d.end_date)  -- Inclusive date comparison
    )
),
Sales_Performance_With_B AS (
  SELECT a.*, d.*
  FROM table_a a
  LEFT JOIN (
    SELECT DISTINCT CAST(party_id AS BIGINT) AS party_id,
                   immigration_cat_cd,
                   start_date,
                   date_sub(coalesce(LEAD(start_date) OVER (PARTITION BY party_id ORDER BY start_date), CURRENT_DATE), 1) AS end_date
    FROM (
      SELECT party_id,
             immigration_cat_cd,
             businesseffectivedate AS start_date,
             RANK() OVER (PARTITION BY party_id, immigration_cat_cd ORDER BY businesseffectivedate) AS rank
      FROM table_b
    ) d
    ON lpad(a.prim_own_cif_id, 15, '0') = lpad(d.party_id, 15, '0')
  ) d
  WHERE trim(a.channel) NOT IN ('Branch','CCC')
    AND trim(a.type) NOT IN ('US$','GTSP')
    
Use code with caution.
profile picture
here is my query - fix this

WITH Sales_Performance AS
(
  SELECT DISTINCT CAST(a.acct_num AS BIGINT) AS acct_num,
      CAST(a.prim_own_cif_id AS BIGINT) AS cust_cid,
      SUBSTR(TRIM(a.opn_dt), 1, 7) AS year_month,
      a.opn_dt AS open_date,
      a.type AS products,
      CASE 
        WHEN d.immigration_cat_cd IN ('000','999','C1','FC1','FC2','FC3','EN2','PV1','PV2','SW1','SW2','SW3','SE2','STP','C2','NV5') THEN 'PR'
        WHEN d.immigration_cat_cd IN ('FW','FW0') THEN 'FW'
        WHEN d.immigration_cat_cd IN ('S','S00') THEN 'IS' 
        ELSE 'Regular_Customers'
      END AS MCB_Category
  FROM table_a a
left join (select distinct cast(party_id as bigint) as party_id, immigration_cat_cd, start_date,
date_sub(coalesce(LEAD(start_date) OVER (partition by party_id order by start_date ), current_date),1) as end_date
from 
(select
party_id,
immigration_cat_cd,
businesseffectivedate as start_date,
rank() over (partition by party_id, immigration_cat_cd order by businesseffectivedate) as rank
from table_b) d 
where rank = 1) d
on lpad(a.prim_own_cif_id,15,'0') = lpad(d.party_id,15,'0')
WHERE 
trim(a.channel) not in ('Branch','CCC') and trim(a.type) not in ('US$','GTSP') and a.opn_dt > '2023-10-31' and a.opn_dt <'2023-12-01'
AND
(
  d.party_id is null 
  OR (a.opn_dt between d.start_date and d.end_date)
))
select * from Sales_Performance a; 
The issue with your query lies in the LEFT JOIN within the Sales_Performance CTE. You're using a subquery with a WHERE rank = 1 condition that filters the table_b data to include only the first ranked record (based on rank() for each party_id). This might not be the intended behavior for a LEFT JOIN.

Here's a breakdown of two possible scenarios and how to adjust the query accordingly:

Scenario 1: Include All Records from table_b (if Applicable):

If you want the LEFT JOIN to include all matching records from table_b (not just the first ranked record for each party_id), remove the WHERE rank = 1 condition from the subquery. Here's the adjusted CTE:

SQL
WITH Sales_Performance AS (
  SELECT DISTINCT CAST(a.acct_num AS BIGINT) AS acct_num,
                  CAST(a.prim_own_cif_id AS BIGINT) AS cust_cid,
                  SUBSTR(TRIM(a.opn_dt), 1, 7) AS year_month,
                  a.opn_dt AS open_date,
                  a.type AS products,
  CASE WHEN d.immigration_cat_cd IN ('000','999','C1','FC1','FC2','FC3','EN2','PV1','PV2','SW1','SW2','SW3','SE2','STP','C2','NV5') THEN 'PR'
       WHEN d.immigration_cat_cd IN ('FW','FW0') THEN 'FW'
       WHEN d.immigration_cat_cd IN ('S','S00') THEN 'IS'
       ELSE 'Regular_Customers'
  END AS MCB_Category
  FROM table_a a
  LEFT JOIN (
    SELECT DISTINCT CAST(party_id AS BIGINT) AS party_id,
                   immigration_cat_cd,
                   start_date,
                   date_sub(coalesce(LEAD(start_date) OVER (PARTITION BY party_id ORDER BY start_date), CURRENT_DATE), 1) AS end_date
    FROM (
      SELECT party_id,
             immigration_cat_cd,
             businesseffectivedate AS start_date,
             RANK() OVER (PARTITION BY party_id, immigration_cat_cd ORDER BY businesseffectivedate) AS rank
      FROM table_b
    ) d
    ON lpad(a.prim_own_cif_id, 15, '0') = lpad(d.party_id, 15, '0')
  ) d
  WHERE trim(a.channel) NOT IN ('Branch','CCC')
    AND trim(a.type) NOT IN ('US$','GTSP')
    AND a.opn_dt > '2023-10-31'
    AND a.opn_dt < '2023-12-01'
    AND (
      d.party_id IS NULL
      OR (a.opn_dt >= d.start_date AND a.opn_dt <= d.end_date)  -- Inclusive date comparison
    )
),
...  -- Rest of your query remains the same
Use code with caution.
Scenario 2: Include Only the Most Recent Record from table_b (if Applicable):

If you specifically want to include only the most recent record (based on the start_date) for each party_id from table_b, you can modify the subquery to use ROW_NUMBER() instead of RANK(). Here's the adjusted CTE:

SQL
WITH Sales_Performance AS (
  SELECT DISTINCT CAST(a.acct_num AS BIGINT) AS acct_num,
                  CAST(a.prim_own_cif_id AS BIGINT) AS cust_cid,
                  SUBSTR(TRIM(a.opn_dt), 1, 7) AS year_month,
                  a.opn_dt AS open_date,
                  a.type AS products,
  CASE WHEN d.immigration_cat_cd IN ('000','999','C1','FC1','FC2','FC3','EN2','PV1','PV2','SW1','SW2','SW3','SE2','STP','C2','NV5') THEN 'PR'
       WHEN d.immigration_cat_cd IN ('FW','FW0') THEN 'FW'
       WHEN d.immigration_cat_cd IN ('S','S00
Use code with caution.
profile picture
why dont you provide the union all
I apologize for not including the UNION ALL in the previous response. Here's the complete query with the UNION ALL clause assuming you want to include all records from table_a regardless of data availability in table_b:

SQL
WITH Sales_Performance AS (
  SELECT DISTINCT CAST(a.acct_num AS BIGINT) AS acct_num,
                  CAST(a.prim_own_cif_id AS BIGINT) AS cust_cid,
                  SUBSTR(TRIM(a.opn_dt), 1, 7) AS year_month,
                  a.opn_dt AS open_date,
                  a.type AS products,
  CASE WHEN d.immigration_cat_cd IN ('000','999','C1','FC1','FC2','FC3','EN2','PV1','PV2','SW1','SW2','SW3','SE2','STP','C2','NV5') THEN 'PR'
       WHEN d.immigration_cat_cd IN ('FW','FW0') THEN 'FW'
       WHEN d.immigration_cat_cd IN ('S','S00') THEN 'IS'
       ELSE 'Regular_Customers'
  END AS MCB_Category
  FROM table_a a
  LEFT JOIN (
    SELECT DISTINCT CAST(party_id AS BIGINT) AS party_id,
                   immigration_cat_cd,
                   start_date,
                   date_sub(coalesce(LEAD(start_date) OVER (PARTITION BY party_id ORDER BY start_date), CURRENT_DATE), 1) AS end_date
    FROM (
      SELECT party_id,
             immigration_cat_cd,
             businesseffectivedate AS start_date,
             RANK() OVER (PARTITION BY party_id, immigration_cat_cd ORDER BY businesseffectivedate) AS rank
      FROM table_b
    ) d
    ON lpad(a.prim_own_cif_id, 15, '0') = lpad(d.party_id, 15, '0')
  ) d
  WHERE trim(a.channel) NOT IN ('Branch','CCC')
    AND trim(a.type) NOT IN ('US$','GTSP')
    AND a.opn_dt > '2023-10-31'
    AND a.opn_dt < '2023-12-01'
    AND (
      d.party_id IS NULL
      OR (a.opn_dt >= d.start_date AND a.opn_dt <= d.end_date)  -- Inclusive date comparison
    )
),
Sales_Performance_With_B AS (
  SELECT a.*, d.*
  FROM table_a a
  LEFT JOIN (
    SELECT DISTINCT CAST(party_id AS BIGINT) AS party_id,
                   immigration_cat_cd,
                   start_date,
                   date_sub(coalesce(LEAD(start_date) OVER (PARTITION BY party_id ORDER BY start_date), CURRENT_DATE), 1) AS end_date
    FROM (
      SELECT party_id,
             immigration_cat_cd,
             businesseffectivedate AS start_date,
             RANK() OVER (PARTITION BY party_id, immigration_cat_cd ORDER BY businesseffectivedate) AS rank
      FROM table_b
    ) d
    ON lpad(a.prim_own_cif_id, 15, '0') = lpad(d.party_id, 15, '0')
  ) d
  WHERE trim(a.channel) NOT IN ('Branch','CCC')
    AND trim(a.type) NOT IN ('US$','GTSP')
    AND a.opn_dt > '2023-10-31'
    AND a.opn_dt < '2023-12-01'
    AND (
      d.party_id IS NULL
      OR (a.opn_dt BETWEEN d.start_date AND d.end_date)  -- Inclusive date comparison (optional)
    )
)
SELECT * FROM Sales_Performance
UNION ALL
SELECT * FROM Sales_Performance_With_B;
Use code with caution.
This query ensures that all records from table_a are included in the final result set. Records with matching data from table_b will have additional information in the columns joined from `Sales_Performance_With_

profile picture
Here is my query - Right now it gets me 7981 records instead of 8244 records  which is basically sale.
After joiniing it comes down to 7981. I used the Left Outer Join and Full Join too but I am still getting 7981 records instead of 8244.
Here is what My understanding is, because I am saying in the where clause to get all the record between Nov 1 and Nov 30, and I am also using where it say opn_dt between start and end date. Since I am using AND statement, the output looks into both conditions and get me lower records.
One of the reason is, if the acct open in sales table or table somwehere in november then it gets me the data from the table b. but if the table b showing the same customer number in some next months which does not qualify the condition and therefore exclude the records and showing 7981.

I would like to know from you - how do I ensure to get all 8244 records as well as the opn_dt should also match between start and end date and if they do not - then still provide the full data regardless of data is available in table b for the same month or not.


here is my query - fix this in hive sql

WITH Sales_Performance AS
(
  SELECT DISTINCT CAST(a.acct_num AS BIGINT) AS acct_num,
      CAST(a.prim_own_cif_id AS BIGINT) AS cust_cid,
      SUBSTR(TRIM(a.opn_dt), 1, 7) AS year_month,
      a.opn_dt AS open_date,
      a.type AS products,
      CASE 
        WHEN d.immigration_cat_cd IN ('000','999','C1','FC1','FC2','FC3','EN2','PV1','PV2','SW1','SW2','SW3','SE2','STP','C2','NV5') THEN 'PR'
        WHEN d.immigration_cat_cd IN ('FW','FW0') THEN 'FW'
        WHEN d.immigration_cat_cd IN ('S','S00') THEN 'IS' 
        ELSE 'Regular_Customers'
      END AS MCB_Category
  FROM table_a a
left join (select distinct cast(party_id as bigint) as party_id, immigration_cat_cd, start_date,
date_sub(coalesce(LEAD(start_date) OVER (partition by party_id order by start_date ), current_date),1) as end_date
from 
(select
party_id,
immigration_cat_cd,
businesseffectivedate as start_date,
rank() over (partition by party_id, immigration_cat_cd order by businesseffectivedate) as rank
from table_b) d 
where rank = 1) d
on lpad(a.prim_own_cif_id,15,'0') = lpad(d.party_id,15,'0')
WHERE 
trim(a.channel) not in ('Branch','CCC') and trim(a.type) not in ('US$','GTSP') 
and a.opn_dt > '2023-10-31' and a.opn_dt <'2023-12-01'
AND
(
  d.party_id is null 
  OR (a.opn_dt between d.start_date and d.end_date)
))
select * from Sales_Performance a;