access columns from subquery
Posted on 2004-10-21
Is there a way to get the columns from a subquery? My query (tested and it works) is this:
IF(LEFT(REPLACE(REPLACE(b.Pro,'-',''),' ',''),1)=0,SUBSTRING(REPLACE(REPLACE(b.Pro,'-',''),' ',''),2),REPLACE(REPLACE(b.Pro,'-',''),' ','')) as Pro2,
b.shipper, RIGHT(b.shipper,2) as shipper_state,
b.consignee, RIGHT(b.consignee,2) as consignee_state,
FROM oo_ODFL_Tracking_1 as b
b.ststerm LIKE 'Call%'
( SELECT c.pronum
FROM carriersettlement as c, masterquotes a
WHERE c.webquotecontrol = a.webquotecontrol AND
(c.pronum = b.Pro OR
c.pronum = Pro2 OR
a.webquotecontrol = b.bl OR
a.usercontrol = b.po) AND
( IF(c.provider_approved IS NOT NULL,
c.provider_approved BETWEEN b.charges-5 AND b.charges+5,
c.provider_estcost BETWEEN b.charges-5 AND b.charges+5) ) AND
a.shipstate = shipper_state AND
a.consstate = consignee_state)
I want to pull the columns from the subquery, so that I can why the "b" table failed to show up in the subquery. Example: if the c.pronum was not found, I want to create a column in the return result that says failed_pronum=Y/N.
I wouldn't be to worried about understanding the last part. The basic question is if there is a way to "show" c.pronum in the returned query?