Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

access columns from subquery

Posted on 2004-10-21
7
Medium Priority
?
2,281 Views
Last Modified: 2007-11-27
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
0
Comment
Question by:bptacek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:crimson117
ID: 12374744
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
 

Author Comment

by:bptacek
ID: 12374878
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
 
LVL 5

Expert Comment

by:crimson117
ID: 12375046
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:bptacek
ID: 12383826
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
 
LVL 5

Accepted Solution

by:
crimson117 earned 2000 total points
ID: 12383915
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
 

Author Comment

by:bptacek
ID: 12616166
Havent had the time to try crimson117's idea, but in theory, it should work just fine.

Thanks crimson!
0
 
LVL 5

Expert Comment

by:crimson117
ID: 12616226
No problem!  Good luck with your project.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question