in reply to wild cards in Vertica

What is the SQL you want to end up with in $Where?

Update: part of the issue may be your program cannot tell what is a prefix (and needs the % appended) and what is a complete value that should be matched verbatim.

Dum Spiro Spero

Replies are listed 'Best First'.
Re^2: wild cards in Vertica
by pragov (Novice) on Jun 30, 2015 at 18:26 UTC
    The SQL is as follows $sql = $select.$from.$Where; $from is the list of fields from the table. and $Where is the filters. How do I tweak my program, to tell what is a prefix and what is a complete value? Thanks, Pragov
[reply]

      What value do you want to be in $Where?

      As to how to tell what is prefix, I don't know enough about your data. Is a 1 letter entry always a prefix?

      You can always use LIKE and % but it will hurt the efficiency of your queries. That may not matter to you.

      Dum Spiro Spero
[reply]
        Yes..the prefix is always like A or B or M etc. I tried this way 
        
if ($clnm  ne ""){
        $cnt = @parts = split(/\,/, $clnm );
       # $clnm  = "'";
        $WHERE = $WHERE ." AND (";
        for ($i = 0; $i < $cnt; $i++) {
             $WHERE = $WHERE . "m.CLNAME LIKE '$parts[$i]%' ";
             ($WHERE = $WHERE .  " OR ") if ($i < $cnt - 1);
        }
       $WHERE = $WHERE . ")";

}

        [download]
        but still did not work. getting output as: AND m.CLNAME IN (A,BDO-CL1) Should I modify here too? 
        if ($clnm ne "") { $Where = $Where . "AND m.CLNAME IN ($clnm) "; }

        [download]
        Thanks,
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom