Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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"

Tagged:

Answers

  • HibikiHibiki Employee

    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)
    
  • 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).

  • HibikiHibiki Employee
    edited 5:05AM

    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,

  • 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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.