How to use select statement insert first row of dummy dash value with 2 table combination

user_14328183 0
Viewed 44
table_A
no   desciption         
1     Apple
2     orange
3     banana
4     kiwi
5     papaya

table_B
no     price      mydate
1      10.00      20210801
2       8.00      20210802
3       5.00      20210803
4      12.00      20210804
5       4.00      20210805

Hi, I try to use this SQL and union all but show an error.

select '-' a.description, '-' b.price from dual union all select a.description,sum(b.price) from table_A a, table_B b where a.no=b.no and b.mydate='20210801' group by a.description;

ORA-00923 : FROM keyword not found where expected

I need result

a.description    sum(b.price)
   -                  -       <-----dummy dash always on first row
   Apple            10.00

Anyone help is much appreciated.

sqlplus oracle
2 Answers

One option is to

  • use a subquery
  • it contains additional rn column (where 1 means that those values will represent "row number 1" in the result)
  • apply to_char function to sum(b.price) because - with union - queries have to match in number of columns and their datatypes

So:

  SELECT description, price
    FROM (SELECT 1 rn, '-' description, '-' price FROM DUAL
          UNION ALL
            SELECT 2 rn, a.description, TO_CHAR (SUM (b.price))
              FROM table_A a, table_B b
             WHERE     a.no = b.no
                   AND b.mydate = '20210801'
          GROUP BY a.description)
ORDER BY rn

which - applied to your sample data - returns

DESCRIPTION     PRICE
--------------- ----------------------------------------
-               -
apple           10

Note that:

  • mydate should be a true date datatype column; don't use strings because nothing prevents you from entering e.g. 2f$8-23 into a varchar2 column, and that certainly isn't a valid date value
  • try to switch to joins; leave where clause for conditions data should meet in order to be returned

Something like this:

from table_a a join table_b b on a.no = b.no   --> JOIN
where b.mydate = date '2021-08-01'             --> MYDATE being DATE datatype
user_90979060
  1. There is no aliases a and b in your first part of union all, so just remove a. and b.
  2. Don't use implicit conversion from char literals to date, use literal dates instead:
select '-' as description, '-' as price from dual 
union all 
select a.description,to_char(sum(b.price))
from 
    table_A a
    join table_B b 
        on a.no=b.no
where b.mydate=date'2021-08-01' 
group by a.description;
user_4291000
Related