Oracle query found issue

Question

select product.id,product.name,product.itemcode,sum(product.amount)as total_amount
from product where product.account in
select G.account from  report_results G 
where     G.status='Y')
group by product.id,product.name,product.itemcode

Consider the above as QUERY1

Now in order to perform an operation i modified the QUERY1 as below(i am using a new table called proc_temp

 (select product.id,product.name,product.itemcode,sum(product.amount)as total_amount
   from product where product.account in
   (select G.account from  report_results G 
   where     G.status='Y'))as Input,proc_temp
   where Input.id=proc_temp.id

The above query is not correctly formed.I want to join query1 with a new table proc_temp and compare both of its id column.Please help me in correcting this syntactically.

Please provide sample data and desired output.

D'Arcy Rittich
– D'Arcy Rittich

2012-09-04 15:03:21 +00:00
Commented Sep 4, 2012 at 15:03 — D'Arcy Rittich
– D'Arcy Rittich, Commented Sep 4, 2012 at 15:03

D'Arcy Rittich · Accepted Answer · 2012-09-04 14:59:39Z

1

You need to add a SELECT clause. I can't vouch for this query since I don't know what you want to do, but something like:

select i.*
from (
    select product.id, product.name, product.itemcode, sum(product.amount) as total_amount
    from product
    where product.account in (
            select G.account
            from report_results G
            where G.status = 'Y'
            )
) i
inner join proc_temp t on i.id = t.id

answered Sep 4, 2012 at 14:59

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

D'Arcy Rittich Over a year ago

@paul i.id comes from the derived table immmediately after the first from keyword

Carlos Grappa · Accepted Answer · 2012-09-04 15:05:06Z

1

I would get rid of the subquery, and use an inner join. Besides that, the following query assumes that Input.id is G.account

select p.id,p.name,p.itemcode,sum(p.amount)as total_amount
from product p
inner join report_results g on (p.account = g.account)
inner join proc_temp pt on (g.account = pt.id)
where g.status='Y'
group by product.id,product.name,product.itemcode

answered Sep 4, 2012 at 15:05

Carlos Grappa

2,38115 silver badges19 bronze badges

1 Comment

user472625 Over a year ago

@Carlos.Thanks for the response

Collectives™ on Stack Overflow

Oracle query found issue

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related