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 advancethis 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|||sorry
im not sure where to ask
im using this in sql server and access|||doesn't the star calls out all the rows of the tables?
i'll try|||doesn't the star calls out all the rows of the tables?
i'll tryno, 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|||hey it works!
why does the * makes it work?|||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
__________________|||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|||as d was not a typo|||i took the as d out and it works too
and what's as d
sorry for being a noob|||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:
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: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 asSELECT 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 !|||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|||i call it a derived table instead of nested table expressionNTE is the DB2 terminology.
I'm probably a bit biased...