SELECT with sapdb-subquery

[Danny Tramnitzke]

Hi all again

I have a SELECT, that doesn't work. But there comes no Error or somthing 
like that, when executing..

SELECT
   p.prod_no,
   p.artist,
   CHAR (DATE( VALUE (p.release_date, '10000101010101000000')) , USA) as 
release_date_txt ,
   count_B + count_aa  as thumbnail,
   0 as order_quantity

FROM
table001 p,
  (select
         count(B.prod_no) count_B
   from
         table001 p,
         view102 B,
         view001 A,
         view001 C
   where
         A.prod_no = p.prod_no
     AND A.version_no = C.version_no
     AND C.prod_no = B.prod_no
     AND B.media_key = 'Thumbnail'
     AND B.cust_id = '0000') tab1,
  (select
         count(aa.prod_no) count_aa
   from
         table001 p,
         view102 aa
   where
         aa.prod_no = p.prod_no
     AND media_key = 'Thumbnail'
     AND cust_key = '0000') tab2
WHERE
   1=1
   AND artist LIKE 'Blackhawk'
ORDER by
   p.prod_no
If I want to execute that SELECT, there is a sapdb kernel process, which 
takes ca. 40 % CPU performance, and takes ca. 9 minutes. Then the process 
is gone, without any result or Errormessage ... (maybe a timeout? )

I guess, that is caused by the selection of : "count_B + count_aa  as 
thumbnail" , because
when I execute the same SELECT without the subquery that it looks like :

SELECT
   p.prod_no,
   p.artist,
   CHAR (DATE( VALUE (p.release_date, '10000101010101000000')) , USA) as 
release_date_txt ,
   0 as thumbnail,
   0 as order_quantity

FROM
table001 p,
WHERE
   1=1
   AND artist LIKE 'Blackhawk'
ORDER by
   p.prod_no
it workes fine. So might the subquery in the FROM Part cause a bug with the 
sapdb kernel ?

I have started the two SELECT's with the newest SQL-Studio W2k in INTERNAL 
Mode, and the newest Linux Kernel Version.

Best regards,
Danny
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general