Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Inline Queries - Urgent
Multiple Inline Queries - Urgent [message #36252] Tue, 13 November 2001 07:11 Go to next message
Tina
Messages: 38
Registered: November 2001
Member
I'm having problems with the joins in my multiple inline query. As a correlated subquery on SQL:

SELECT
' GROUP_TYPE' = PROD.01_PRODUCT_NAME,
' ACC_TYPE_DESC' = PROD.02_PRODUCT_NAME,
'BFTP' = (SELECT COUNT(DATES.ACCOUNT_NUMBER)
FROM A.DEPOSIT_DATES
WHERE DATEPART(YY,DATES.DATE_OPEN) = 2001
AND (DATES.DATE_CLOSED IS NULL
OR DATES.DATE_CLOSED = '')),
'OPTP' = (SELECT COUNT(DATES.ACCOUNT_NUMBER)
FROM A.DEPOSIT_DATES
WHERE DATEPART(YY,DATES.DATE_OPEN) = 2001)
FROM A.Prod,
A.Product,
A.Dates
WHERE Prod.Product_Id = Product.Product_Id
AND Product.Account_Number = Dates.Account_Number
GROUP BY PROD.01_PRODUCT_NAME,
PROD.02_PRODUCT_NAME

But, trying to recode this in an Oracle Inline query, my joins are invalid. How do I count the relevant number of accounts for each product, depending on the inline criteria?

SELECT
PROD.01_PRODUCT_NAME GROUP_TYPE,
PROD.02_PRODUCT_NAME ACC_TYPE_DESC,
TB1.BFTP,
TB2.OPTP
FROM A.Prod,
A.Product,
A.DATES,
(SELECT COUNT(DATES.ACCOUNT_NUMBER) AS BFTP
FROM A.DATES
WHERE TO_CHAR(DATES.DATE_OPEN,'YYYY') = '2001'
AND DATES.DATE_CLOSED IS NOT NULL) TB1,
(SELECT COUNT(DATES.ACCOUNT_NUMBER) AS OPTP
FROM A.DATES
WHERE TO_CHAR(DATES.DATE_OPEN,'YYYY') = '2001') TB2
WHERE Prod.Product_Id=Product.Product_Id
AND Product.Account_Number=Dates.Account_Number
GROUP BY PROD.01_PRODUCT_NAME,
PROD.02_PRODUCT_NAME,
TB1.BFTP,
TB2.OPTP

This produces the following:

GROUP_TYPE ACC_TYPE_DESC BFTP OPTP
----------- ------------- ---- ----
1 001 25 45
1 002 25 45
2 006 25 45
2 009 25 45

The inline select statements are providing me with overall totals, but not totals for each GROUP_TYPE and ACC_TYPE_DESC

Any ideas, thanks?

----------------------------------------------------------------------
Re: Multiple Inline Queries - Urgent [message #36270 is a reply to message #36252] Wed, 14 November 2001 03:32 Go to previous message
Hans
Messages: 42
Registered: September 2000
Member
Here is my suggestion for your problem. I renamed 01_product_name to product_name_01 and
02_product_name to product_name_02 because they are invalid column identifiers. How could
you work with this column names in oracle without enclosing them with double quotes ?

connect test/test
 
drop table dates;
create table dates (
   date_open         date,
   date_closed       date,
   account_number    varchar2(10)
);
 
drop table prod;
create table prod (
   product_id        number(10),
   product_name_01   varchar2(30),
   product_name_02   varchar2(30)
);
 
drop table product;
create table product (
   product_id        number(10),
   account_number    varchar2(10)
);
 
-- dates
insert into dates values ( to_date('01.01.2001','DD.MM.YYYY'), null, 'ACC001' );
insert into dates values ( to_date('02.01.2001','DD.MM.YYYY'), to_date('03.01.2001','DD.MM.YYYY'), 'ACC001' );
insert into dates values ( to_date('03.01.2001','DD.MM.YYYY'), to_date('04.01.2001','DD.MM.YYYY'), 'ACC002' );
insert into dates values ( to_date('06.01.2001','DD.MM.YYYY'), null, 'ACC002' );
insert into dates values ( to_date('09.01.2001','DD.MM.YYYY'), null, 'ACC002' );
insert into dates values ( to_date('11.01.2001','DD.MM.YYYY'), null, 'ACC003' );
 
insert into dates values ( to_date('23.12.2000','DD.MM.YYYY'), null, 'ACC001' );
insert into dates values ( to_date('23.12.2000','DD.MM.YYYY'), to_date('24.12.2000','DD.MM.YYYY'), 'ACC001' );
 
-- prod
insert into prod values ( 1, '1', '001');
insert into prod values ( 2, '1', '002');
insert into prod values ( 3, '2', '006');
insert into prod values ( 4, '2', '009');
 
-- product
insert into product values ( 1, 'ACC001' );
insert into product values ( 1, 'ACC002' );
insert into product values ( 2, 'ACC003' );
insert into product values ( 3, 'ACC001' );
insert into product values ( 4, 'ACC034' );
 
 
 
select
   a.group_type,
   a.acc_type_desc,
   a.bftp,
   b.optp
from
   (
      select 
         prod.product_name_01          group_type,
         prod.product_name_02          acc_type_desc,
         count(dates.account_number)   bftp 
      from
         dates,
         prod, 
         product
      where prod.product_id         =  product.product_id 
      and   product.account_number  =  dates.account_number(+)
      and   to_char(dates.date_open(+),'yyyy') = '2001'
      and   dates.date_closed(+) is not null
      group by prod.product_name_01, prod.product_name_02
   ) a,
   (
      select 
         prod.product_name_01          group_type,
         prod.product_name_02          acc_type_desc,
         count(dates.account_number)   optp 
      from
         dates,
         prod, 
         product
      where prod.product_id         =  product.product_id 
      and   product.account_number  =  dates.account_number(+)
      and   to_char(dates.date_open(+),'yyyy') = '2001'
      group by prod.product_name_01, prod.product_name_02
   ) b
where a.group_type    = b.group_type
and   a.acc_type_desc = b.acc_type_desc;
 
 
GROUP_TYPE                     ACC_TYPE_DESC                        BFTP       OPTP
------------------------------ ------------------------------ ---------- ----------
1                              001                                     2          5
1                              002                                     0          1
2                              006                                     1          2
2                              009                                     0          0


----------------------------------------------------------------------
Previous Topic: Unique list without using DISTINCT
Next Topic: leading 0's trimmed from number column
Goto Forum:
  


Current Time: Sun May 31 19:09:16 CDT 2020