|
|
Posted by Jim Michaels on 01/09/06 22:04
sorry. it jumbled my examples together. Let me try this again.
SELECT CITY FROM OFFICES WHERE target > (SELECT SUM(quota) FROM salesreps
WHERE rep_office=office)
SELECT company FROM customers WHERE cust_num IN
(SELECT DISTINCT cust FROM orders WHERE mfr='aci' AND product LIKE '4100%'
AND order_date BETWEEN '01-JAN-90' AND '30-JUN-90')
SELECT DISTINCT description FROM products WHERE EXISTS (SELECT order_num
FROM orders WHERE
product=product_id AND mfr=mfr_id AND amount>=25000.00)
WHERE X < ANY (SELECT
Y...) reads as "where, for some Y, X < Y"
WHERE X < ALL (SELECT Y...)
SELECT company, CASE WHEN credit_limit > 60000 THEN 'a' WHEN credit_limit >
30000
THEN 'b' ELSE 'c' FROM customers
SELECT city,SUM(sales) FROM offices,salesreps WHERE office=CASE WHEN
(rep_office IS NOT NULL) THEN
rep_office ELSE (SELECT rep_office FROM salesreps AS mgrs WHERE
mgrs.empl_num-manager)
the ELSE in a CASE statement is not necessary of
course.
COALESCE(,,,,,) with as many commas as you want, selects the first
non-NULL column in the comma-separated list.This eliminates your need for
2-columns results if you want it that way.
SELECT name,COALESCE(quota,sales,0.00) FROM salesreps
Here are some useful manual pages:
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN
result ...] [ELSE result] END , CASE WHEN [condition] THEN result [WHEN
[condition] THEN result ...] [ELSE result] END
Navigation:
[Reply to this message]
|