my $query = qq|SELECT
                   s.shiptofax, s.shiptocountry, s.shiptophone, s.ship
+toaddress1, s.shiptoaddress2,
          s.shiptocity, s.shiptostate, s.shiptozipcode,
          s.shiptocontact, s.shiptoname,
          s.shiptoemail, o.waybill, o.shipvia
          FROM shipto s, oe o
          WHERE ((trans_id =  $form->{"$form->{vc}_id"}) AND ( o.id = 
+$form->{"$form->{vc}_id"})
                  AND ((to_date( s.shiptophone, 'YYYY-MM-DD') >= to_da
+te( to_char(current_timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD'))
                  ))
          UNION
          SELECT
           s.shiptofax, s.shiptocountry,  s.shiptophone, s.shiptoaddre
+ss1, s.shiptoaddress2,
          s.shiptocity, s.shiptostate, s.shiptozipcode,
          s.shiptocontact, s.shiptoname,
          s.shiptoemail, o.waybill, o.shipvia
          FROM shipto s
          JOIN oe o ON (o.id = s.trans_id)
          WHERE ((o.$form->{vc}_id = $form->{"$form->{vc}_id"})
                  AND ((to_date( s.shiptophone, 'YYYY-MM-DD') >= to_da
+te( to_char(current_timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD'))
                  ))
          UNION
          SELECT
           s.shiptofax, s.shiptocountry, s.shiptophone, s.shiptoaddres
+s1, s.shiptoaddress2,
          s.shiptocity, s.shiptostate, s.shiptozipcode,
          s.shiptocontact, s.shiptoname,
          s.shiptoemail, a.waybill, a.shipvia
          FROM shipto s
          JOIN $table a ON (a.id = s.trans_id)
          WHERE ((a.$form->{vc}_id = $form->{"$form->{vc}_id"})
                  AND ((to_date( s.shiptophone, 'YYYY-MM-DD') >= to_da
+te( to_char(current_timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD'))
                  ))|;


  if ($form->{id}) {
    $query .= qq|
                 EXCEPT
         SELECT
          s.shiptofax, s.shiptocountry, s.shiptophone, s.shiptoaddress
+1, s.shiptoaddress2,
         s.shiptocity, s.shiptostate, s.shiptozipcode,
         s.shiptocontact, s.shiptoname,
         s.shiptoemail, o.waybill, o.shipvia
         FROM shipto s, oe o
         WHERE ((s.trans_id = '$form->{id}') AND ( o.id = '$form->{id}
+'))
         |;
  }

  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);
  
  my $ref;
  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_shipto} }, $ref;
  }
  $sth->finish;

  $dbh->disconnect;
[download]

Just wanted to point out that you can make that SQL quite a bit less repetitive. Depending on the data stored in t2 and t3 this may or may not hold true

SELECT t1.col1, t2.col5
  FROM t1
  JOIN t2 ON t2.tid = t1.tid
  WHERE t2.col3 = 99 AND ...
UNION
SELECT t1.col1, t3.col5
  FROM t1
  JOIN t3 ON t3.tid = t1.tid
  WHERE t3.col4 = 42 AND ...
[download]

is often equivalent to

SELECT t1.col1, coalesce(t2.col5, t3.col5) AS col5
FROM t1
LEFT JOIN t2 ON t2.tid = t1.tid AND t2.col3 = 99
LEFT JOIN t3 ON t3.tid = t1.tid AND t3.col4 = 42
WHERE ...
[download]

which is probably much easier for the database server to execute. The EXCEPT can be done with a left anti-join.

But do heed the other nameless monk's advice. Taking column names directly from a form is a security hole.

Well, you should use DBI placeholders or quote, otherwise you'll fall for http://bobby-tables.com/

To sort through SQL by shiptofax, its usually a murder of appending

ORDER BY s.shiptofax
[download]

And then the results will be sorted, and you won't have to sort your array (you have an array of hashes , stored in a hash, you'd be sorting the array, which you won't have to do if you adjust your query with an ORDER BY clause)

The problem is to combine ORDER BY and JOIN. Wherever I put it in the query I get syntax error... I am not sure exactly what the join is making so I have not dared to change it into some other query...

Not sure what variant of SQL you're using, but you ought to be able to simplify the query:

SELECT s.*, o.waybill, o.shipvia

FROM shipto s, oe o 

WHERE trans_id = $form->{"$form->{vc}_id"} AND
      to_date(s.shiptophone, 'YYYY-MM-DD') >= to_date(to_char(current_
+timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD')

    UNION

SELECT s.*, o.waybill, o.shipvia 

FROM shipto s
JOIN oe o ON (o.id = s.trans_id)

WHERE o.$form->{vc}_id = $form->{"$form->{vc}_id"} AND
      to_date(s.shiptophone, 'YYYY-MM-DD') >= to_date(to_char(current_
+timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD')

    UNION

SELECT s.*, a.waybill, a.shipvia

FROM shipto s
JOIN $table a ON (a.id = s.trans_id)

WHERE a.$form->{vc}_id = $form->{"$form->{vc}_id"} AND
      to_date(s.shiptophone, 'YYYY-MM-DD') >= to_date(to_char(current_
+timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD')
[download]

If you explain what each of those tables has in it and what you're trying to generate, I may be able to suggest a better query structure. Incidently, is there a particular reason why you're storing your dates as timestamps? Dates are easy to manipulate using INTERVAL commands.