in reply to Re: Sort in multilevel hash
in thread Sort in multilevel hash

Thank you for fast response. It's hard to know where to look for the problem... 

 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]
This is the code I build the hash from. I would like to sort on 'shiptofax' but it doesn't get this way... Regards,

Replies are listed 'Best First'.
Re^3: Sort in multilevel hash
by Anonymous Monk on Jan 09, 2012 at 09:46 UTC

    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.

[reply]
[d/l]
[select]
Re^3: Sort in multilevel hash
by Anonymous Monk on Jan 09, 2012 at 08:58 UTC

    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)

[reply]
[d/l]
      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...
[reply]

        queriniere:

        Try wrapping your SQL query inside another, and sort the outer one, something like:

        select foo, bar, baz from (
   select 'a' as foo, 'b' as bar, 'c' as baz from dual
   union
   select 'f', 'q', 'z' from dual
   union 
   select 'x', 'y', 'z' from dual
)
order by bar

        [download]

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]

        ORDER BY must be after WHERE (or GROUP BY or HAVING) of the last statement. You effectively have multiple statements thanks to the unions.

[reply]
[d/l]
[select]
        append means add to the end :)
[reply]
Re^3: Sort in multilevel hash
by TJPride (Pilgrim) on Jan 09, 2012 at 23:30 UTC
    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.

[reply]
[d/l]

In Section Seekers of Perl Wisdom