Home » SQL & PL/SQL » SQL & PL/SQL » simple =(+)
simple =(+) [message #2185] Wed, 26 June 2002 09:46 Go to next message
Aulo Aasmaa
Messages: 3
Registered: June 2002
Junior Member
Hello

I have a set of statuses: "I","V","W" and I would like to query the number each of these statuses exists in my table.

It would be done with:

select
trn_stat_typ,
count(trn_stat_typ)
from
TABEL
WHERE
PROCESSDT >= to_date('01.01.2000','dd.MM.yyyy')
and PROCESSDT <= to_date('01.01.2004','dd.MM.yyyy')
and TRN_STAT_TYP IN ('I','A','W','V','E','P','N')
GROUP BY
trn_stat_typ

But for those statuses, that do not exist in the table, zero should be displayed.

Another thing is that the statuses exist in a memory, meaning they can not be queried, therefore making the use of =(+) more difficult.

Can I use DUAL to get a dbresult like:

STATUS
------
I
P
N
...

And then somehow make =(+) ?

I would be very thankful for all the help that takes me closer to the solution. Thank you!

Sincerely,
Aulo Aasmaa
Re: simple =(+) [message #2186 is a reply to message #2185] Wed, 26 June 2002 10:04 Go to previous messageGo to next message
Aulo Aasmaa
Messages: 3
Registered: June 2002
Junior Member
I could get the statuses from DUAL with this:

select 'I' AS BLA FROM DUAL
union
select 'A' AS BLA FROM DUAL
union
select 'W' AS BLA FROM DUAL
union
select 'V' AS BLA FROM DUAL
union
select 'E' AS BLA FROM DUAL
union
select 'P' AS BLA FROM DUAL
union
select 'N' AS BLA FROM DUAL

How would I use it in the =(+) syntax?

This does not work:

select
trn_stat_typ,
NVL(count(trn_stat_typ),0)
from
vegas.hb_t_atoim
WHERE
PROCESSDT >= to_date('01.01.2000','dd.MM.yyyy')
and PROCESSDT <= to_date('01.01.2004','dd.MM.yyyy')
and TRN_STAT_TYP =(+) (
select 'I' AS BLA FROM DUAL
union
select 'A' AS BLA FROM DUAL
union
select 'W' AS BLA FROM DUAL
union
select 'V' AS BLA FROM DUAL
union
select 'E' AS BLA FROM DUAL
union
select 'P' AS BLA FROM DUAL
union
select 'N' AS BLA FROM DUAL
)
GROUP BY
trn_stat_typ
Re: simple =(+) [message #2189 is a reply to message #2185] Wed, 26 June 2002 11:25 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Note the outer join (+) on both lines of the WHERE clause:

select trn_stat_typ, count(trn_stat_typ)
  from vegas.hb_t_atoim,
       (select 'I' bla from dual
        union
        select 'A' from dual
        union
        select 'W' from dual
        union
        select 'V' from dual
        union
        select 'E' from dual
        union
        select 'P' from dual
        union
        select 'N' from dual) d
 where trn_stat_typ (+)= d.bla
   and processdt (+) between to_date('01.01.2000','dd.mm.yyyy') and to_date('01.01.2004','dd.mm.yyyy') 
 group by trn_stat_typ;
Re: simple =(+) [message #2196 is a reply to message #2185] Thu, 27 June 2002 03:24 Go to previous message
Aulo Aasmaa
Messages: 3
Registered: June 2002
Junior Member
This solved the problem. Thank you very much for a helping hand!

Regards,
AUlo
Previous Topic: two counts???
Next Topic: Question
Goto Forum:
  


Current Time: Wed Oct 16 10:38:39 CDT 2019