Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Display Values from Sub Statement

976439Jan 7 2013 — edited Jan 7 2013
Hi, i have managed to write the following code to give me an output but for some reason I am not able to display the min(time) value as I get 0094 error which identifies that field does not exist.

the following is the code:
SELECT ID, username, order_num, count(*) AS Count, search_time, Owner,
       count(*) over (partition by Owner) AS Owner_Count,
       sum(decode(count(order_num),1,1,0)) over (partition by Owner) only_1,
       round(sum(decode(count(order_num),1,1,0)) over (partition by Owner)/ (count(*) over (partition by Owner)) * 100, 1) AS Answer
  FROM
  ( 
  SELECT distinct decode(Tbl1.Order_Num, 'NA', Tbl1.telephone_number, Tbl1.Order_Num) Order_Num, Tbl1.Telephone_Number, Tbl2.ID, Tbl2.username, min(Tbl2.search_time), Tbl1.Product, Tbl1.Owner
  FROM Tbl2 INNER JOIN Tbl1 ON Tbl2.telephoneno = Tbl1.telephone_number
  where Tbl1.data_date between '10-Dec-12 00:00:00' and '16-Dec-12 23:59:59'
  group by
  Tbl1.Order_Num, Tbl1.Telephone_Number, Tbl2.ID, Tbl2.username
, trunc(Tbl2.search_time), search_time -- to group just by day
, Tbl1.Product, Tbl1.Owner
  UNION ALL
  SELECT distinct decode(Tbl1.Order_Num, 'NA', Tbl1.telephone_number, Tbl1.Order_Num) Order_Num, Tbl1.Telephone_Number, Tbl2.ID, Tbl2.username, min(Tbl2.search_time), Tbl1.Product, Tbl1.Owner
  FROM Tbl2 INNER JOIN Tbl1 ON Tbl2.faultrefno = Tbl1.Order_Num
  where Tbl1.data_date between '10-Dec-12 00:00:00' and '16-Dec-12 23:59:59'
  group by
  Tbl1.Order_Num, Tbl1.Telephone_Number, Tbl2.ID, Tbl2.username
, trunc(Tbl2.search_time) -- to group just by day
, Tbl1.Product, Tbl1.Owner
  )
  group by ID, username, order_num, search_time, Owner;
When the above code is run I get the following error:
ORA-00904: "SEARCH_TIME": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 172 Column: 45
Can any1 help me understand how I can display the min Search_time in my final table?

Thanks in advance.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 4 2013
Added on Jan 7 2013
5 comments
153 views