bptacek
asked on
access columns from subquery
Hi,
Is there a way to get the columns from a subquery? My query (tested and it works) is this:
========================== ========== ====
SELECT
b.Pro,
IF(LEFT(REPLACE(REPLACE(b. Pro,'-','' ),' ',''),1)=0,SUBSTRING(REPLA CE(REPLACE (b.Pro,'-' ,''),' ',''),2),REPLACE(REPLACE(b .Pro,'-',' '),' ','')) as Pro2,
b.po, b.bl,
b.shipper, RIGHT(b.shipper,2) as shipper_state,
b.consignee, RIGHT(b.consignee,2) as consignee_state,
b.origin, b.dest,
b.pieces, b.weight,
b.charges, b.estdeldte,
b.ststerm, b.pickupdte,
b.colppd, b.appointment
FROM oo_ODFL_Tracking_1 as b
WHERE
b.ststerm LIKE 'Call%'
OR
NOT EXISTS
( 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?
Ben
Is there a way to get the columns from a subquery? My query (tested and it works) is this:
==========================
SELECT
b.Pro,
IF(LEFT(REPLACE(REPLACE(b.
b.po, b.bl,
b.shipper, RIGHT(b.shipper,2) as shipper_state,
b.consignee, RIGHT(b.consignee,2) as consignee_state,
b.origin, b.dest,
b.pieces, b.weight,
b.charges, b.estdeldte,
b.ststerm, b.pickupdte,
b.colppd, b.appointment
FROM oo_ODFL_Tracking_1 as b
WHERE
b.ststerm LIKE 'Call%'
OR
NOT EXISTS
( 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?
Ben
ASKER
I am using JasperReports with iReport. If you don't know what that is, it is a report designer that will allow you to create reports.
It uses Java, so I could code some Java. The only problem is that I am not sure how extensive I can get with iReport.
I did notice that you could do this
SELECT AVG(sum_column1)
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;
http://dev.mysql.com/doc/mysql/en/Unnamed_views.html
Something to that effect??? Is there a way to use that in conjunction with a GROUP BY yaddayadda HAVING something = "Y"?
Ben
It uses Java, so I could code some Java. The only problem is that I am not sure how extensive I can get with iReport.
I did notice that you could do this
SELECT AVG(sum_column1)
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;
http://dev.mysql.com/doc/mysql/en/Unnamed_views.html
Something to that effect??? Is there a way to use that in conjunction with a GROUP BY yaddayadda HAVING something = "Y"?
Ben
Using a "SELECT FROM (SELECT FROM)" (unnamed view) syntax is different from using EXISTS, so I don't know if you could even implement the business logic with an unnamed view.
Does JasperReports let you create subreports embedded within the main report, as MS Access does?
Can you please describe in plain language your business requirement (eg: "I need to list all suppliers that have fewer than 5 open orders in a given state")? We might need to take a step back from the SQL you've got so far and see if there's a better approach, but to help with that I'll need to know the gist of the ogirinal requirement.
Does JasperReports let you create subreports embedded within the main report, as MS Access does?
Can you please describe in plain language your business requirement (eg: "I need to list all suppliers that have fewer than 5 open orders in a given state")? We might need to take a step back from the SQL you've got so far and see if there's a better approach, but to help with that I'll need to know the gist of the ogirinal requirement.
ASKER
Yes, JasperReports does let you create subreports. Currently that query IS running in subreport..... hmmm... but maybe I could have a subreport with a subreport??? That might work.
As far as what I am trying to do... I am trying to compare tracking info to a local database. So, pretend you download a tab file rom UPS (with all your shipments and their info), dump it into a table and then try to see if those shipments are different than the ones in the local DB. For example.... the charges may be off in the local db, so I would like that shipment to show in the list and show why it is showing up... so it might say something like "charges flagged" or something.
I am starting to think that I could maybe do that subreport of a subreport. The hardest part will be on determining how to get the values from the subreport so that I can use it on it's parent report.
ben
As far as what I am trying to do... I am trying to compare tracking info to a local database. So, pretend you download a tab file rom UPS (with all your shipments and their info), dump it into a table and then try to see if those shipments are different than the ones in the local DB. For example.... the charges may be off in the local db, so I would like that shipment to show in the list and show why it is showing up... so it might say something like "charges flagged" or something.
I am starting to think that I could maybe do that subreport of a subreport. The hardest part will be on determining how to get the values from the subreport so that I can use it on it's parent report.
ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Havent had the time to try crimson117's idea, but in theory, it should work just fine.
Thanks crimson!
Thanks crimson!
No problem! Good luck with your project.
Can I ask what type of application are you writing - like what language is sending this query to the database? If you need to perform the kind of procedural logic you described for failed_pronum, you'll need more than SQL.