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(REPLACE(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
bptacekAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
crimson117Connect With a Mentor Commented:
You can usually link a subreport to its parent based on a field in the query.  You can also usually do multiple levels of subreports, eg subreports of subreports of subreports.
0
 
crimson117Commented:
No, especially not with a NOT EXISTS query.  The entire subquery will evaluate to (TRUE) or (FALSE), and MySQL won't remember what was in it.

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.
0
 
bptacekAuthor Commented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

0
 
bptacekAuthor Commented:
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
0
 
bptacekAuthor Commented:
Havent had the time to try crimson117's idea, but in theory, it should work just fine.

Thanks crimson!
0
 
crimson117Commented:
No problem!  Good luck with your project.
0
All Courses

From novice to tech pro — start learning today.