CrossJoin with InStr function won't bring in value from column

slc1axj

The code below runs fine and brings in the correct Provider Lookup; however, if I change a."Provider Lookup" to a."Provider", it does not bring back the Provider but plugs in the "N". I can't figure out what's happening... I have confirmed that the HCPC in the WFMGMT_PRD.map_auto_provider table and zip code match to the same instance within the wfmgmt_prd.open_report_hourly

select o."zip code",
MAX(CASE WHEN INSTR(o.'provider hcpc/revenue code', a.HCPC) >= 1 and cast(cast(cast(o.'zip code' as float) as int) as varchar)= a.zip THEN a."Provider Lookup" ELSE 'N' END) "Auto-Provider"
from wfmgmt_prd.open_report_hourly o
CROSS JOIN WFMGMT_PRD.map_auto_provider a
where "Report Interval" >= (select max("Report Interval") from wfmgmt_prd.open_report_hourly)
and o."queue type" = 'Provider Staffing'
and o."intake id"='10969508'
group by o."zip code",o."provider hcpc/revenue code"

Hibiki

Hi @slc1axj,

Do you mean you hit the issue if you changed a."Provider Lookup" in CASE clause to a."Provider"? If possible, please provide the simple DDLs and data for the reproducer.

My trial worked well. But I think this may be different from yours.

=> DROP TABLE a CASCADE;
=> CREATE TABLE a (
->   "provider hcpc/revenue code" varchar(100),
->   "zip code" integer,
->   "Provider Lookup" varchar(10),
->   "Provider" varchar(10)
-> );
=>COPY a FROM STDIN;
a11-1|1000000|aaalup|aaa
b22-2|2000000|bbblup|bbb
c33-3|3000000|ccclup|ccc
\.

=> DROP TABLE b CASCADE;
=> CREATE TABLE b (
->   HCPC varchar(10),
->   zip varchar(10)
-> );
=> COPY b FROM STDIN;
a11|1000000
b22|1000000
c33|3000000
\.

=> SELECT a."zip code",
->        MAX(CASE WHEN INSTR(a.'provider hcpc/revenue code', b.HCPC) >= 1 AND CAST(CAST(CAST(a.'zip code' as float) as int) as varchar) = b.zip THEN a."Provider Lookup" ELSE 'N' END) "Auto-Provider"
-> FROM a
-> CROSS JOIN b
-> GROUP BY a."zip code", a."provider hcpc/revenue code";
 zip code | Auto-Provider
----------+---------------
  1000000 | aaalup
  2000000 | N
  3000000 | ccclup
(3 rows)

=> SELECT a."zip code",
->       MAX(CASE WHEN INSTR(a.'provider hcpc/revenue code', b.HCPC) >= 1 AND CAST(CAST(CAST(a.'zip code' as float) as int) as varchar) = b.zip THEN a."Provider" ELSE 'N' END) "Auto-Provider"
->FROM a
->CROSS JOIN b
->GROUP BY a."zip code", a."provider hcpc/revenue code";
 zip code | Auto-Provider
----------+---------------
  1000000 | aaa
  2000000 | N
  3000000 | ccc
(3 rows)

slc1axj

I've attached an Excel document showing the 2 tables queried separately (2 separate tabs) - then the crossjoin that will not bring in the provider name (3rd tab).

Hibiki

Hi @slc1axj,

Thank you for providing this information.
I tried to reproduce your issue but it failed.

Which Vertica version are you using? Can you try the attached scenario and see if the issue happens?

Best regards,

slc1axj

Vertica Analytic Database v10.1.1-1

We all got together to try to figure this out and we think the issue is Vertica is not bringing back all the rows to match as it must have a limit.

Hibiki

Hi @slc1axj,

Can you share the actual DDLs for these tables and the data to reproduce the issue in our environment? If yes, please send an email to me. If the data is huge, I can prepare the temporary sftp site for you. As for the data, any data you can see this issue is OK for me.

Your environment is 10.1.1-1. How many nodes does your cluster have?

Best regards,