Help needed for nested query without "with" clause! urgent

Transact-SQL
1

Hi everyone! I have to take an SQL exam the next week and I have a problem with nested subqueries with oracle. The exercise say:

/* 11. Give routes for which the total monetary sales of products in the category 'Hand Ice' exceeded
total monetary sales of products in the category 'Scoop Ice'. */

The problem is that i'm trying to solve this without "WITH" clause. My version is:
/*
SELECT hvroutetemplate_nrid, sum(quantityprice)
FROM hvc_so0 a, hvc_hvvisitresult b, hvc_visitresultdetails c, hvc_ar0 d
WHERE a.so0_nrid = b.so0_nrid AND b.hvvisitresult_nrid = c.hvvisitresult_nrid AND d.ar0_nrid = c.ar0_nrid
AND upper(family) = 'HAND ICE'
GROUP BY hvroutetemplate_nrid
HAVING sum(quantityprice) > (SELECT sum(quantity*price)
FROM hvc_so0 a, hvc_hvvisitresult b, hvc_visitresultdetails c, hvc_ar0 d
WHERE a.so0_nrid = b.so0_nrid AND b.hvvisitresult_nrid = c.hvvisitresult_nrid AND d.ar0_nrid = c.ar0_nrid
AND upper(family) = 'SCOOP ICE'); */

My version return only 0 rows!! But i can't understand why...Do you have any ideas? It's really urgent. I thank you in advance for the help.

The solution using "WITH" clause is like:
/*
WITH
detailed_purchases AS(
SELECT a.family, a.price, b.quantity, d.hvroutetemplate_nrid
FROM hvc_ar0 a, hvc_visitresultdetails b, hvc_hvvisitresult c, hvc_so0 d
WHERE a.ar0_nrid = b.ar0_nrid AND b.hvvisitresult_nrid = c.hvvisitresult_nrid AND c.so0_nrid = d.so0_nrid)
SELECT DISTINCT hvroutetemplate_nrid
FROM detailed_purchases a
WHERE UPPER(family) = 'HAND ICE'
GROUP BY hvroutetemplate_nrid
HAVING SUM(pricequantity) > (SELECT SUM(pricequantity)
FROM detailed_purchases b
WHERE b.hvroutetemplate_nrid = a.hvroutetemplate_nrid
AND UPPER(b.family) = 'SCOOP ICE');
*/

2

Hi

Hope this helps​:slightly_smiling_face::slightly_smiling_face:

I think

With clause is a temporary table created
Used again below

One idea is to create a select using with clause logic
And join

Example

With ABC as
( Select name from #data)
Select*from ABC where name = 'pam'

Select a.* from
( Select name from #data ) a
Where name = 'pam'

1 Like
3

For the purpose of speed and readability, please use join(s).
Couldn't figure out where the pricequantity field came from (the field you sum), so I assume it should be price multiplied by quantity.
I would have done something like this:

select d.hvroutetemplate_nrid
  from hvc_ar0 as a
       inner join hvc_visitresultdetails as b
               on b.ar0_nrid=a.ar0_nrid
       inner join hvc_hvvisitresult as c
               on c.hvvisitresult_nrid=b.hvvisitresult_nrid
       inner join hvc_so0 as d
               on d.so0_nrid=c.so0_nrid
 where upper(a.family) in ('HAND ICE','SCOOP ICE')
 group by d.hvroutetemplate_nrid
 having sum(case when a.family='HAND ICE' then a.price*a.quantity else 0 end)
       >sum(case when a.family='SCOOP ICE' then a.price*a.quantity else 0 end)
;
1 Like