How to use select statement to query only negative value in oracle sql

Question

table_a 

billno    desc        qty    amount
10324    orange       -1     -1.00
10324    apple         1      3.00
10324    papaya        1      2.00

10431    water melon   1      2.00
10431    kiwi          1      3.00   

10233    banana       -1     -2.50
10233    mango         1      4.00
..
.. 
total 378 records

Hi, how to use select statement query desc,qty and amount have negative qty in the billno, if i don't know the billno ?

I had tried use sign(number) syntax, but result incorrect.

select desc,qty,amount from table_a where sign(qty)='-1';
orange       -1     -1.00
banana       -1     -2.50

my goal

orange       -1     -1.00
apple         1      3.00
papaya        1      2.00  

banana       -1     -2.50
mango         1      4.00

your help is much appreciated.

Answer 1

You can use EXISTS to look in the same table for the same billno to see if there are any items with a negative quantity

WITH
    table_a (billno,
             item_desc,
             qty,
             amount)
    AS
        (SELECT 10324, 'orange', -1, -1.00 FROM DUAL
         UNION ALL
         SELECT 10324, 'apple', 1, 3.00 FROM DUAL
         UNION ALL
         SELECT 10324, 'papaya', 1, 2.00 FROM DUAL
         UNION ALL
         SELECT 10431, 'water melon', 1, 2.00 FROM DUAL
         UNION ALL
         SELECT 10431, 'kiwi', 1, 3.00 FROM DUAL
         UNION ALL
         SELECT 10233, 'banana', -1, -2.50 FROM DUAL
         UNION ALL
         SELECT 10233, 'mango', 1, 4.00 FROM DUAL)
SELECT *
  FROM table_a a1
 WHERE EXISTS
           (SELECT 1
              FROM table_a a2
             WHERE a2.billno = a1.billno AND a2.qty < 0)