Given that placeholders can only represent data values in query statement, a distinct statement must be prepared for each query that differs in items other than data values. If the differences from one query to the next involve the conditions used in the "where" clause, you just need to organize your code to provide for the various combinations of "where" conditions, and prepare a statement for each of those.

The OP is pretty vague about what you're trying to do (and some of the idioms in your SQL syntax are unfamiliar to me), but the following example might be relevant: 

my %where_type = (
    type1 => { _1_job => 'LIKE', _2_host_name => 'LIKE', _3_server => 
+'LIKE' },
    type2 => { _1_job => '=', _2_host_name => '=', _3_time_stamp => 'L
+IKE' },
    type3 => { _1_user_id => '!=', _2_user_id => 'LIKE', _3_time_stamp
+ => '>' },
    );

sub build_sql
{
    my ( $type ) = @_;
    my $sql = "SELECT job,time_stamp,host_name,ip_address,server FROM 
+cf_status_log";
    my @where_clauses = ();
    if ( exists( $where_type{$type} )) {
        for my $key ( sort keys %{$where_type{$type}} ) {
            ( my $fld = $key ) =~ s/^_\d_//;
            push @where_clauses, "$fld $where_type{$type}{$key} ?";
        }
    }
    $sql .= " where ".join( " and ", @where_clauses ) if @where_clause
+s;
}

[download]
If you think that sort of approach is worth trying, you'll want to elaborate it to include some consideration of the values that are going to be passed along when these constructed queries are executed. At the very least, you want to avoid confusion about how many placeholder/parameter values get passed along with a given statement handle when it's executed. (If number of parameters passed on execution doesn't match the number of "?" placeholders, that can be fatal. -- UPDATE: and you want to make damn sure that you don't get confused about the ordering of parameters for a given statement. -- I updated the code snippet to avoid that sort of mistake; actually, I had to make a second update, to include "sort" in the for loop.)

Also, depending on how many times you have to build/prepare statements in your code, you may want to look at the "prepare_cached" function in DBI, so that you can avoid re-preparing a statement that was already used in a previous iteration. (You don't need to call "finish" on a statement handle until the process is completely done - but don't forget to do that before you disconnect.)

In reply to Re: Simplifying queries in DBI by graff
in thread Simplifying queries in DBI by neilwatson

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.