Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2007
    Location
    Toronto, Ontario
    Posts
    11

    Unanswered: a small SQL help regrading a criteria

    i want the ordline.qty * product.prodprice AS ordercost

    where ordercost = > 150
    but it does not work. Every time i type: where ordercost => 150, it would give me an error.
    this is my full query:

    select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno where (ordertbl.orddate = datevalue('01/23/2007')) and (making ordercost = > 150) ..................

    Thanks for help in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this has to be either microsoft access or sql server, it doesn't look very much like ANSI SQL

    in any case...

    one solution is to wrap the query in another SELECT

    select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) as d where orddate = datevalue('01/23/2007') and ordercost = > 150
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Toronto, Ontario
    Posts
    11
    sorry
    im not sure where to ask
    im using this in sql server and access

  4. #4
    Join Date
    Oct 2007
    Location
    Toronto, Ontario
    Posts
    11
    doesn't the star calls out all the rows of the tables?
    i'll try

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mackenzo
    doesn't the star calls out all the rows of the tables?
    i'll try
    no, the star "calls out" all the columns

    in this case it's all the columns of the derived table, show here in blue --

    select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) as d where orddate = datevalue('01/23/2007') and ordercost = > 150
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2007
    Location
    Toronto, Ontario
    Posts
    11
    hey it works!
    why does the * makes it work?

  7. #7
    Join Date
    Oct 2007
    Location
    Toronto, Ontario
    Posts
    11
    nm
    you have explained.

    select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) where orddate = datevalue('01/23/2007') and ordercost = > 150

    u had "as d" as typos =p
    __________________

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it works because of the derived table, which uses column names as defined in its SELECT

    the expression with the column alias ordercost becomes an actual column in the derived table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    as d was not a typo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2007
    Location
    Toronto, Ontario
    Posts
    11
    i took the as d out and it works too
    and what's as d
    sorry for being a noob

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by mackenzo
    i took the as d out and it works too
    and what's as d
    This sets "d" as the alias name (actually: table name) for the temporary "table" inside the parentheses.

    According to standard SQL, every table (or view or nested table expression) must have a name. By having the "AS d" after the definition, it's as if you created a view, viz:
    Code:
    CREATE VIEW d (custno, custfullname, ordno, orddate, empno,
                   empfullname, prodno, prodname, ordercost)
    AS
      SELECT customer.custno,
             customer.custfirstname || ' ' || customer.custlastname,
             ordertbl.ordno,
             ordertbl.orddate,
             employee.empno,
             employee.empfirstname + " " + employee.emplastname,
             product.prodno,
             product.prodname,
             ordline.qty * product.prodprice
      FROM   ordertbl inner join customer on ordertbl.custno = customer.custno
              inner join employee on employee.empno = ordertbl.empno
              inner join ordline on ordline.ordno=ordertbl.ordno
              inner join product on product.prodno=ordline.prodno
    Now this view can be interrogated:
    Code:
    SELECT *
    FROM   d
    WHERE  orddate = datevalue('01/23/2007')
      and  ordercost => 150
    Rudy's query is exactly this, except for the fact that no view with the name "d" is ever created, it's just temporarily available for the scope of the current query. Such a "view" is often called a "nested table expression". It's to be written as
    Code:
    SELECT d.whatever
    FROM   (SELECT whatever, ... -- the NTE
           ) AS d
    WHERE  ...
    Note the "d.whatever", where the table name "d" is used. That's why the "AS d" is needed: it's really the name of the NTE !
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks peter, i was away from the computer all day, but your answer was much better than mine would've been

    and i call it a derived table instead of nested table expression
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    i call it a derived table instead of nested table expression
    NTE is the DB2 terminology.
    I'm probably a bit biased...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •