Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Help with Order By

    I have a list of part numbers that are alphanumeric, and quantities. I need to sort this list by:

    1.Partnumber with Qty <>0
    1a. First positive Qty then negative Qty
    2. Partnumber with Qty 0

    Example:
    Partnumber 1234567 with Qty 2
    Partnumber 1234567 with Qty -1
    Partnumber 1234568 with Qty 1
    Partnumber 0123456 with Qty 0

    How do I do this?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    looks like you want to start by ordering by partnumber and then qty desc.
    write a query like that, post it, and then let us know what else you need
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select partnumber, qty, 1 As Sort
    from table
    where qty <> 0
    union all
    select partnumber, qty, 2 As Sort
    from table
    where qty = 0
    order by Sort, qty desc
    or

    Code:
    select partnumber, qty, (case when qty = 0 then -9999999999 else qty end) as sort
    from table
    order by (case when qty = 0 then -9999999999 else qty end) desc
    -cf

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    I think I am close, but the 1 and the 2 both show up in the results, and I don't want that.

    Code:
    SELECT 
     p.ver,
     p.loc,
     p.splrcode_dlr,
     REPLACE (REPLACE (p.item, '@', '.'), '#', ' ') item,                   -- replace @ -> . & # -> _ to undo the substitution made earlier
     DECODE((SIGN(NVL(p.sofqty, 0))), 
               -1,  '-' || LPAD( TO_CHAR(   ABS(ROUND(p.sofqty, 2)*100))   , 7, '0'),
                1,  LPAD( TO_CHAR(   ROUND(p.sofqty, 2)     *100), 8, '0'),
                0,  LPAD('0', 8, '0')
             ) sofqty,
     p.ordertype,
     SUBSTR(p.SOFMSG,1,29) SOFMSG
     ,1 AS SORT
    FROM pac.SOF p, 
            (           -- compare the loc and p_SOFName columns, check for switch p_SOFAvailSw. 
                        -- If all the switch'es are 1 then run scrip above
            SELECT 
             l.p_SOFName,
             SUM(SIGN(l.p_SOFAvailSw)) su,
             COUNT(l.p_SOFName) cn
            FROM loc l,
               (SELECT 
                 l.loc,
                 l.p_SOFName,
                 l.p_SOFAvailSw
                FROM stsc.loc l
                 WHERE l.loc = '&1' -- =======> dealer
                  ) sl 
              WHERE l.p_SOFName = sl.p_SOFName
              GROUP BY l.p_SOFName
             ) i
     WHERE p.sofloc IN  -- spool out data for the location group --------
                        (SELECT l.loc FROM loc l,
                            (SELECT l.loc, l.p_SOFName, l.p_SOFAvailSw 
                           FROM stsc.loc l WHERE l.loc = '&1' ) sl 
                          WHERE l.p_SOFName = sl.p_SOFName)
       AND p.sofdate = TO_DATE('&3', 'dd/mm/yy')
       AND p.difdate = TO_DATE('&4', 'dd/mm/yy')
       AND i.cn = i.su
       AND p.sofqty <> 0
    UNION ALL 
    SELECT 
     p.ver,
     p.loc,
     p.splrcode_dlr,
     REPLACE (REPLACE (p.item, '@', '.'), '#', ' ') item,                   -- replace @ -> . & # -> _ to undo the substitution made earlier
     DECODE((SIGN(NVL(p.sofqty, 0))), 
               -1,  '-' || LPAD( TO_CHAR(   ABS(ROUND(p.sofqty, 2)*100))   , 7, '0'),
                1,  LPAD( TO_CHAR(   ROUND(p.sofqty, 2)     *100), 8, '0'),
                0,  LPAD('0', 8, '0')
             ) sofqty,
     p.ordertype,
     SUBSTR(p.SOFMSG,1,29) SOFMSG
     ,2 AS SORT
    FROM pac.SOF p, 
            (           -- compare the loc and p_SOFName columns, check for switch p_SOFAvailSw. 
                        -- If all the switch'es are 1 then run scrip above
            SELECT 
             l.p_SOFName,
             SUM(SIGN(l.p_SOFAvailSw)) su,
             COUNT(l.p_SOFName) cn
            FROM loc l,
               (SELECT 
                 l.loc,
                 l.p_SOFName,
                 l.p_SOFAvailSw
                FROM stsc.loc l
                 WHERE l.loc = '&1' -- =======> dealer
                  ) sl 
              WHERE l.p_SOFName = sl.p_SOFName
              GROUP BY l.p_SOFName
             ) i
     WHERE p.sofloc IN  -- spool out data for the location group --------
                        (SELECT l.loc FROM loc l,
                            (SELECT l.loc, l.p_SOFName, l.p_SOFAvailSw 
                           FROM stsc.loc l WHERE l.loc = '&1' ) sl 
                          WHERE l.p_SOFName = sl.p_SOFName)
       AND p.sofdate = TO_DATE('&3', 'dd/mm/yy')
       AND p.difdate = TO_DATE('&4', 'dd/mm/yy')
       AND i.cn = i.su
       AND p.sofqty = 0
    ORDER BY SORT, sofqty DESC

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try:

    COL SORT NOPRINT


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    Thanks to everyone for your feedback. It is truly appreciated.

    The only issue I have now is that the item number is Varchar(2) and does not get sorted correctly.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Replace:
    Code:
    ...
    DECODE((SIGN(NVL(p.sofqty, 0))), 
               -1,  '-' || LPAD( TO_CHAR(   ABS(ROUND(p.sofqty, 2)*100))   , 7, '0'),
                1,  LPAD( TO_CHAR(   ROUND(p.sofqty, 2)     *100), 8, '0'),
                0,  LPAD('0', 8, '0')
             ) sofqty,
    With:
    Code:
    ...
    DECODE(NVL(p.sofqty, 0),0,'00000000',TO_CHAR(ROUND(p.sofqty,2)*100,'S0000000')) sofqty,

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Sep 2005
    Posts
    220
    This is VERY close. What I need is this:

    format for numbers >= 0 should not have a sign
    format for numbers < 0 should have a negative sign

    QTY FORMAT

    3 00000300

    0 00000000

    -3 -0000300

  9. #9
    Join Date
    Sep 2005
    Posts
    220
    This is VERY close. What I need is this:

    format for numbers >= 0 should not have a sign
    format for numbers < 0 should have a negative sign

    Code:
    QTY            FORMAT
    
    3               00000300
    
    0               00000000
    
    -3              -0000300

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It is sad there is no sample data and output wanted in this problem.. but how about something like.. select qty, decode( greatest( qty, 0 ), 0, '-' ) || to_char( qty*100, 'fm000000' ) format ....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •