Solved

sql is not working correctly

Posted on 2014-09-19
23
352 Views
Last Modified: 2014-09-23
I need this to only return the amount that I retrieved on the first select.  If I sum it again, the amount are wrong.  Unfortunately, this is the only sql that seems to work with PHP.

with Orders as (select ohscdt "Date", SUM(OHMTO$) "Amount" from kivalib.orhdrpf where OHSTAT = \'F\' AND OHTYP = 1 and ohscdt > \'' . $ymd  . '\' ' . $company . ' ' .
                               'group by ohscdt ORDER BY OHSCDT) ' .
                               'SELECT ohordt, sum(ord."Amount")   ' .
                               'FROM KIVALIB.ORHDRPF left outer join Orders ord on ord."Date" = ohordt ' .
                               'WHERE ohordt > ' . $ymd  . ' ' .
                               'GROUP BY ohordt ORDER BY OHORDT',
0
Comment
Question by:Kimberly Minarik
  • 10
  • 5
  • 4
  • +2
23 Comments
 

Author Comment

by:Kimberly Minarik
Comment Utility
Can someone help me please?  I have worked all day on this an I am not making any progress.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
It would be helpful if you provide some sample data and expected result.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Now I take off my Topic Advisor hat to discuss you query.

Yikes!
do NOT use "order" as a name ever, never ever, this will error somewhere at sometime. It isn't "safe". Use something that will never cause heartache e.g.

WITH theOrders ( ... )

why is it a problem? well just consider this: ORDER BY ORDER.ID really going to bend somebodies brain including the AS/400

I suggest you stop  using aliases that require double quotes: e.g. select ohscdt "Date"

firstly "Date" is a reserved word in SQL had is also used by PHP, so you are making things much harder than they need to be.

Additionally by using double quotes you are adding an additional layer of complexity when you come to translate this into PHP

Do NOT use ORDER BY inside WITH
This is a normal SQL rule, and I'm surprised it is allowed at all, but in any case it is most probably wasteful.

and, Unless there is a compelling reason for it, I can see no point in using WITH for this piece of your SQL. Instead we can use a subquery (also known as "inline view" ) for this query.

Could you test if this alternative works in your DB2?

SELECT ifnull(ohordt,ord.ohscdt) theDate  , ifnull(SUM(ord.Amount), 0) Amount
FROM kivalib/orhdrpf
      FULL OUTER JOIN (
                  SELECT ohscdt , SUM(OHMTO$) Amount
                  FROM kivalib/orhdrpf
                  WHERE OHSTAT = 'F'
                        AND OHTYP = 1
                        AND OHCOCD = 'D'
                  GROUP BY ohscdt
            ) ord ON ord.ohscdt = ohordt
WHERE ohordt > 20140911
GROUP BY ifnull(ohordt,ord.ohscdt)
ORDER BY theDate
;

Open in new window


I am not sure that you really need a FULL OUTER JOIN by the way, but I simply cannot tell without more information about your tables and data (this join type is the "most expensive").
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Oh, and maybe one of these will work for your php (assuming my suggested re-write is OK

SQL = "" .
"SELECT ifnull(ohordt,ord.ohscdt) theDate " .
"   , ifnull(SUM(ord.Amount), 0) Amount " .
"FROM kivalib/orhdrpf " .
"FULL OUTER JOIN " .
"   ( SELECT ohscdt " .
"      , SUM(OHMTO$) Amount " .
"   FROM kivalib/orhdrpf " .
"   WHERE OHSTAT = 'F' " .
"   AND OHTYP = 1 " .
"   AND OHCOCD = 'D' " .
"   GROUP BY ohscdt " .
"   ) ord ON ord.ohscdt = ohordt " .
"WHERE ohordt > 20140911 " .
"GROUP BY ifnull(ohordt,ord.ohscdt) " .
"ORDER BY theDate"

Open in new window

$SQL  = "";
$SQL .= "SELECT ifnull(ohordt,ord.ohscdt) theDate ";
$SQL .= "   , ifnull(SUM(ord.Amount), 0) Amount ";
$SQL .= "FROM kivalib/orhdrpf ";
$SQL .= "FULL OUTER JOIN ";
$SQL .= "   ( SELECT ohscdt ";
$SQL .= "      , SUM(OHMTO$) Amount ";
$SQL .= "   FROM kivalib/orhdrpf ";
$SQL .= "   WHERE OHSTAT = 'F' ";
$SQL .= "   AND OHTYP = 1 ";
$SQL .= "   AND OHCOCD = 'D' ";
$SQL .= "   GROUP BY ohscdt ";
$SQL .= "   ) ord ON ord.ohscdt = ohordt ";
$SQL .= "WHERE ohordt > 20140911 ";
$SQL .= "GROUP BY ifnull(ohordt,ord.ohscdt) ";
$SQL .= "ORDER BY theDate";

Open in new window

0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
Without an explanation of what it's expected to do, it's hard to guess what any appropriate changes might be. The query isn't making sense to me.

It has a CTE (i.e., the "WITH" part) that sums amounts by date. Then it joins the same file to its summary by matching the dates. It's not clear how that could give meaningful results. You could almost as easily get the result with a straight SELECT of the file except that the two date fields will probably match from different rows. I'm not sure what the purpose of ['\' ' . $company . ' ' .] is in the query.

Apparently the query is over an 'Order' file containing header data. There doesn't seem to be anything that breaks the dates or amounts out by customer. (Is that what the "$company" should do?) So matching the two dates from different orders doesn't seem meaningful. (Is this some kind of high-level reconciliation query about total money amounts on a series of dates?)

What do the two dates mean? What is the "amount" stored in the header file?

The main SELECT is summing the amount from the CTE. But that amount is already a grouped SUM() based on date. As such there will only be a single row and "summing" it wouldn't make sense.

In short, a description of what's needed is going to be much more useful than a query that somehow isn't right, especially when we don't know what the incorrect results are.

Tom
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Probably the best way to approach a task like this one is to create the SSCCE (it's a useful practice with almost any programming question).  In this case the SSCCE would consist of the CREATE TABLE statements and the INSERT statements that would enable us to set up a test case that replicated your data set.  This is the most important part of the SSCCE, since without it we cannot test any proposed solutions.

Once we have that, you can show us by example what your query results set needs to contain.  And we can show you a query that will use your exact test data set to create the exact desired results set.  

You might also want to learn about PHP "HEREDOC" notation because it makes the punctuation and escape sequences much, much easier to get right.  I don't know whether this query makes any sense, but it's much easier to read if you use HEREDOC.  PHP variable substitution occurs inside HEREDOC, making it the perfect tool for templates of all sizes.
<?php
error_reporting(E_ALL);

$sql  = <<<EOD
SELECT ifnull(ohordt,ord.ohscdt) theDate
   , ifnull(SUM(ord.Amount), 0) Amount
FROM kivalib/orhdrpf
FULL OUTER JOIN
   ( SELECT ohscdt
      , SUM(OHMTO$) Amount
   FROM kivalib/orhdrpf
   WHERE OHSTAT = 'F'
   AND OHTYP = 1
   AND OHCOCD = 'D'
   GROUP BY ohscdt
   ) ord ON ord.ohscdt = ohordt
WHERE ohordt > 20140911
GROUP BY ifnull(ohordt,ord.ohscdt)
ORDER BY theDate
EOD;

var_dump($sql);

Open in new window

0
 

Author Comment

by:Kimberly Minarik
Comment Utility
Thanks everyone who wrote to me over the weekend... I sort of had this problem dumped on me.  I will get back to everyone after I have gone through options.
0
 

Author Comment

by:Kimberly Minarik
Comment Utility
The sql duplicates the amounts.  The orginal amount should be 538.00 for 9/12/.  This is the amount for 9/12.  My main problem is how to do this without getting outragous numbers, but I still need to get a 0 value for 9/13 and 9/14.  I am new to php.  I apologize....

 20,140,912                                  418,026.00
 20,140,913                                         .00
 20,140,914                                         .00
0
 

Author Comment

by:Kimberly Minarik
Comment Utility
This query works on the AS/400, but I do not know how to translate it to PHP.  That is the problem.

WITH DAT1 AS (SELECT DISTINCT OHORDT "ORDAT"                          
                        FROM KIVALIB/ORHDRPF                          
                        ORDER BY OHORDT DESC                          
                        FETCH FIRST 8 ROWS ONLY),                    
     DAT2 AS (SELECT OHSCDT"SHPDAT",  SUM(OHMTO$)"SALES"              
           FROM KIVALIB/ORHDRPF                                      
           WHERE OHSTAT = 'F' AND OHTYP = 1                          
           AND OHSCDT IN(SELECT DISTINCT OHORDT FROM KIVALIB/ORHDRPF  
                         ORDER BY OHORDT DESC                        
                         FETCH FIRST 8 ROWS ONLY)                    
           AND OHCOCD  =  'D'                                        
           GROUP BY OHSCDT ORDER BY OHSCDT)                          
 SELECT "ORDAT", IFNULL("SALES",0)     "SALES"                        
   FROM DAT1 LEFT OUTER JOIN DAT2 ON "ORDAT" = "SHPDAT"              
   ORDER BY "ORDAT"
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Am I on firm ground in understanding that you do not want to use the AS/400, but instead you want to use a web-enabled application that runs PHP?  What database do you want to be using ?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Kimberly Minarik
Comment Utility
Ray,

The database is from the AS/400.  The above query works on the AS/400 and I just need to be able to format in .php to fill an array in php.  The data is coming from the AS/400.  This query that I provided does work on the AS/400.  The problem is formatting it in .php to fill and array.
0
 

Author Comment

by:Kimberly Minarik
Comment Utility
This is what I have commented out.  It is giving an internal server error on the Apache server.  All I want to know is what am I missing a ' or something that makes it so it will not run in .php?  All the $company does is add the AND OHCOCD = 'D'.  It is just a variable to allow for another company.  My debug environment is non-exsistant..

//$filledAmounts = default_Model_As400::ExecuteSelect(' ' .
             //                  'WITH DAT1 AS (SELECT DISTINCT OHORDT "ORDAT" FROM KIVALIB/ORHDRPF ORDER BY OHORDT DESC FETCH FIRST 8 ROWS ONLY), ' .
             //                  'DAT2 AS (SELECT OHSCDT "SHPDAT",  SUM(OHMTO$) "SALES" FROM KIVALIB/ORHDRPF WHERE OHSTAT = \'F\' AND OHTYP = 1 ' .
             //                  'ORDER BY OHORDT DESC FETCH FIRST 8 ROWS ONLY) ' .
             //                  ' ' . $company . ' ' .
             //                  'GROUP BY OHSCDT ORDER BY OHSCDT) SELECT "ORDAT", IFNULL("SALES", 0) "SALES" FROM DAT1 LEFT OUTER JOIN DAT2 ON "ORDAT" = "SHPDAT" ' .
             //                  'ORDER BY "ORDAT"', 'filled_placed_amounts.api.php');
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
Kimberley, does that query (ID: 40336526) work on the AS/400 if you remove all double quotes?

If it does the conversion to PHP will be substantially easier for you, and it would look like this:

SQL = "" .
"WITH DAT1 AS " .
"   (SELECT DISTINCT OHORDT ORDAT " .
"      FROM KIVALIB/ORHDRPF " .
"      ORDER BY OHORDT DESC " .
"      FETCH FIRST 8 ROWS ONLY " .
"   ) " .
"   , DAT2 AS " .
"   (SELECT OHSCDTSHPDAT " .
"      , SUM(OHMTO$) SALES " .
"   FROM KIVALIB/ORHDRPF " .
"   WHERE OHSTAT = 'F' " .
"   AND OHTYP = 1 " .
"   AND OHSCDT IN " .
"                 (SELECT DISTINCT OHORDT " .
"                 FROM KIVALIB/ORHDRPF " .
"                 ORDER BY OHORDT DESC " .
"                 FETCH FIRST 8 ROWS ONLY " .
"                 ) " .
"   AND OHCOCD = 'D' " .
"   GROUP BY OHSCDT " .
"   ORDER BY OHSCDT " .
"   ) " .
"SELECT ORDAT " .
"   , IFNULL(SALES,0) SALES " .
"FROM DAT1 " .
"LEFT OUTER JOIN DAT2 ON ORDAT = SHPDAT " .
"ORDER BY ORDAT"

Open in new window

If however you wish to continue with double quotes in the SQL then you have to contend with the issues that PHP also  needs double quotes and to retain them in the SQL requires "escaping" of each double quote used by the SQL. Forming PHP with the SQL containing double quotes will look like this:
SQL = "" .
"WITH DAT1 AS " .
"   (SELECT DISTINCT OHORDT \"ORDAT\" " .
"      FROM KIVALIB/ORHDRPF " .
"      ORDER BY OHORDT DESC " .
"      FETCH FIRST 8 ROWS ONLY " .
"   ) " .
"   , DAT2 AS " .
"   (SELECT OHSCDT\"SHPDAT\" " .
"      , SUM(OHMTO$)\"SALES\" " .
"   FROM KIVALIB/ORHDRPF " .
"   WHERE OHSTAT = 'F' " .
"   AND OHTYP = 1 " .
"   AND OHSCDT IN " .
"                 (SELECT DISTINCT OHORDT " .
"                 FROM KIVALIB/ORHDRPF " .
"                 ORDER BY OHORDT DESC " .
"                 FETCH FIRST 8 ROWS ONLY " .
"                 ) " .
"   AND OHCOCD = 'D' " .
"   GROUP BY OHSCDT " .
"   ORDER BY OHSCDT " .
"   ) " .
"SELECT \"ORDAT\" " .
"   , IFNULL(\"SALES\",0) \"SALES\" " .
"FROM DAT1 " .
"LEFT OUTER JOIN DAT2 ON \"ORDAT\" = \"SHPDAT\" " .
"ORDER BY \"ORDAT\""

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
HEREDOC notation is useful here, too.  This will should put the query string into a PHP variable named $qry.  I have not tested it yet, but you can just cut and paste to see if it creates the right variable.
<?php
error_reporting(E_ALL);
$qry = <<<EOD
WITH DAT1 AS (SELECT DISTINCT OHORDT "ORDAT"                          
                        FROM KIVALIB/ORHDRPF                          
                        ORDER BY OHORDT DESC                          
                        FETCH FIRST 8 ROWS ONLY),                    
     DAT2 AS (SELECT OHSCDT"SHPDAT",  SUM(OHMTO$)"SALES"              
           FROM KIVALIB/ORHDRPF                                      
           WHERE OHSTAT = 'F' AND OHTYP = 1                          
           AND OHSCDT IN(SELECT DISTINCT OHORDT FROM KIVALIB/ORHDRPF  
                         ORDER BY OHORDT DESC                        
                         FETCH FIRST 8 ROWS ONLY)                    
           AND OHCOCD  =  'D'                                        
           GROUP BY OHSCDT ORDER BY OHSCDT)                          
 SELECT "ORDAT", IFNULL("SALES",0)     "SALES"                        
   FROM DAT1 LEFT OUTER JOIN DAT2 ON "ORDAT" = "SHPDAT"              
   ORDER BY "ORDAT" 
EOD;

var_dump($qry);

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
@Ray, v.useful indeed, but I would still encourage avoiding double quotes in the SQL (which I don't believe are necessary)

Oh and the dbms is DB2 as far as I can tell, there are some syntax clues for this
use of slash in table referencing e.g. FROM KIVALIB/ORHDRPF  
use of IFnull() which, while common with MySQL, is used by DB2
and of course the AS/400 hardware
I added DB2 to the question topics also.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
@PortletPaul:  I'm not sufficiently conversant in AS/400 to have a clue about the double quotes, and I agree that they look out of place.  The advantage of HEREDOC notation is (among other things) that quotes do not generally need to be escaped, allowing you to concentrate on the correct contents of the template, including variable substitution, without having to wrestle with PHP escape sequences.
0
 

Author Comment

by:Kimberly Minarik
Comment Utility
When I get in tomorrow, I will try this without the double quotes.  I inherited the issue, unfortunately.  I know this is simple.   Yes, the query without the quotes works on the AS/400.
0
 

Author Comment

by:Kimberly Minarik
Comment Utility
I took out all the double quote, and I am still getting an internal server error.
0
 

Author Comment

by:Kimberly Minarik
Comment Utility
I had / in from copying the AS/400 query.

$filledAmounts = default_Model_As400::ExecuteSelect( " " .
                               "WITH DAT1 AS " .
                               "   (SELECT DISTINCT OHORDT ORDAT " .
                               "      FROM KIVALIB.ORHDRPF " .
                               "      ORDER BY OHORDT DESC " .
                               "      FETCH FIRST 8 ROWS ONLY " .
                               "   ) " .
                               "   , DAT2 AS " .
                               "   (SELECT OHSCDT " .
                               "      , SUM(OHMTO$) SALES " .
                               "   FROM KIVALIB.ORHDRPF " .
                               "   WHERE OHSTAT = 'F' " .
                               "   AND OHTYP = 1 " .
                               "   AND OHSCDT IN " .
                               "                 (SELECT DISTINCT OHORDT " .
                               "                 FROM KIVALIB.ORHDRPF " .
                               "                 ORDER BY OHORDT DESC " .
                               "                 FETCH FIRST 8 ROWS ONLY " .
                               "                 ) " .
                               ' ' . $company . '  '.
                               "   GROUP BY OHSCDT " .
                               "   ORDER BY OHSCDT " .
                               "   ) " .
                               "SELECT ORDAT " .
                               "   , IFNULL(SALES,0) SALES " .
                               "FROM DAT1 " .
                               "LEFT OUTER JOIN DAT2 ON ORDAT = OHSCDT " .
                               "ORDER BY ORDAT", 'filled_placed_amounts.api.php');

this is the actual solution..
0
 

Author Closing Comment

by:Kimberly Minarik
Comment Utility
Thank you for sticking with me on my first experts exchange php question.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
No problem at all Kimberly, it's what we like to do. Thanks for the grading.

Cheers, Paul

ps: please make sure folks know you are using DB2 in future questions, choose that as a topic if it's relevant - this will attract specialists with DB2 expertise.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now