a small SQL help regrading a criteria

mackenzo · #1 (**permalink**) 10-07-07, 21:03

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

r937 · #2 (**permalink**) 10-07-07, 21:06

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

mackenzo · #3 (**permalink**) 10-07-07, 21:10

sorry
im not sure where to ask
im using this in sql server and access

mackenzo · #4 (**permalink**) 10-07-07, 21:12

doesn't the star calls out all the rows of the tables?
i'll try

r937 · #5 (**permalink**) 10-07-07, 21:14

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

mackenzo · #6 (**permalink**) 10-07-07, 21:14

hey it works!
why does the * makes it work?

mackenzo · #7 (**permalink**) 10-07-07, 21:16

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
__________________

r937 · #8 (**permalink**) 10-07-07, 21:17

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

r937 · #9 (**permalink**) 10-07-07, 21:17

as d was not a typo

mackenzo · #10 (**permalink**) 10-07-07, 21:18

i took the as d out and it works too
and what's as d
sorry for being a noob

Peter.Vanroose · #11 (**permalink**) 10-08-07, 14:32

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 !

r937 · #12 (**permalink**) 10-08-07, 15:39

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

Peter.Vanroose · #13 (**permalink**) 10-08-07, 15:45

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...

Internet Services

Web Development

Digital Marketing

Consumer Tech