joins & qry

Question

joins & qry

mark00189

SSC Enthusiast

Points: 140
More actions
October 21, 2003 at 2:19 pm

#101726

hi guys,
need some help with sql..
i have 2 tbls 1-m,
cust(CId(pk), creditlimit, name) and ord(ordId(pk), Cid(fk), date, totalO)
want to display name , creditlimit, & Newcredit limit for those customer who had orders more than 1000 in total order. Newcredit is 20%increase if they had order above 1000.
I started like this but am getting error!
select name, cId, aa
from ord o, cust c
where o.cid =c.cid and total IN (select sum(total) aa > 1000 from ord)
group by name, cid
thnks
mark

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1



SELECT c.Name, c.CreditLimit, c.CreditLimit * 1.2 NewCreditLimit

FROM Cust c JOIN Ord o ON o.CId = c.CId

GROUP BY c.Name, c.CreditLimit

HAVING SUM(o.Total) > 1000

--Jonathan

mark00189 SSC Enthusiast Points: 140 More actions · Answer 2

thanks jonathan for yout help, but am getting this erro

" where c.Custid = o.Custid

ERROR at line 3:

ORA-00933: SQL command not properly ended"

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
thanks jonathan for yout help, but am getting this erro
" where c.Custid = o.Custid
ERROR at line 3:
ORA-00933: SQL command not properly ended"

You're using Oracle and this is a SQL Server site...

But that query is standard SQL-92 and should also work in Oracle; just add a semicolon to the end. Where did the "WHERE" come from? It's not in my query...

--Jonathan