Post reply

joins & qry

  • 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

  • October 21, 2003 at 2:36 pm

    #479002 

    

    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



    --Jonathan

  • October 21, 2003 at 3:10 pm

    #479005

    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"

  • October 21, 2003 at 3:23 pm

    #479006

    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



    --Jonathan

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

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