Solved

sql is not working correctly

Posted on 2014-09-19
23
368 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
[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
  • 10
  • 5
  • 4
  • +2
23 Comments
 

Author Comment

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

Expert Comment

by:Sharath
ID: 40333914
It would be helpful if you provide some sample data and expected result.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40334010
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40334014
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
ID: 40334239
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 110

Expert Comment

by:Ray Paseur
ID: 40334338
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
ID: 40336491
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
ID: 40336506
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
ID: 40336526
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 110

Expert Comment

by:Ray Paseur
ID: 40337070
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
 

Author Comment

by:Kimberly Minarik
ID: 40337879
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
ID: 40337886
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 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40337928
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 110

Expert Comment

by:Ray Paseur
ID: 40337962
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 49

Expert Comment

by:PortletPaul
ID: 40337991
@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 110

Expert Comment

by:Ray Paseur
ID: 40338064
@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
ID: 40338217
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
ID: 40339079
I took out all the double quote, and I am still getting an internal server error.
0
 

Author Comment

by:Kimberly Minarik
ID: 40339236
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
ID: 40339240
Thank you for sticking with me on my first experts exchange php question.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40339302
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

623 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