Solved

I need help with this SQL statement written in php

Posted on 2014-09-19
14
75 Views
Last Modified: 2014-09-25
$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$)"Amount"' .
                               '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("Amount",0) "Amount" ' .
                               'FROM DAT1 ' .
                               'LEFT OUTER JOIN DAT2 ON "ORDAT" = "SHPDAT" ' .
                               'ORDER BY "ORDAT"', 'filled_placed_amounts.api.php');
0
Comment
Question by:Kimberly Minarik
14 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40332504
Please post the CREATE TABLE statements and the test data so we can recreate the issue, thanks.
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40332513
What help you want? You haven't stated your problem/issue.
0
 

Author Comment

by:Kimberly Minarik
ID: 40332515
I just need to know if I have placed a ' in there to cause it not to run.  I know it run the AS/400.
0
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.

 
LVL 58

Expert Comment

by:Gary
ID: 40332680
Only problem I see is here
'. $company . ' .

Should be

. $company .
0
 

Author Comment

by:Kimberly Minarik
ID: 40332700
I will try that thanks Gary...
0
 

Author Comment

by:Kimberly Minarik
ID: 40332706
I am still getting an internal server error.  This does run correctly on the AS/400.
0
 

Author Comment

by:Kimberly Minarik
ID: 40332712
This is the company part..

if(isset($args['filters'][0]) && strlen($args['filters'][0]) > 0) {
                        $company = ' and ohcocd = \'' . $args['filters'][0] . '\' ';
                  } else {
                        $company = ' ';
                  }
0
 
LVL 58

Expert Comment

by:Gary
ID: 40332735
Disable Friendly HTTP errors in the browser and add error reporting to your php page.
Copy your SQL bit into a variable and echo the result of the var and post here.
0
 

Author Comment

by:Kimberly Minarik
ID: 40332807
I have tried to echo the sql.  It is contained in an php api.  I inherited this mess.    I cannot turn on debugging because it will turn it on for production, and we cannot have that.  

I just want to see where I have messed up the sql on the .php side.  It shoudl be returning values.  Would it help to give the AS/400 SQL query that works?
0
 

Author Comment

by:Kimberly Minarik
ID: 40333139
Okay, here is another query that work with the data on the AS\400

                              Enter SQL Statements                              
                                                                               
 Type SQL statement, press Enter.                                              
 ===> with order as (select ohscdt "Date", sum(OHMTO$) "Amount"                
      from kivalib/orhdrpf                                                      
      where OHSTAT = 'F' AND OHTYP = 1 and OHCOCD = 'D'                        
      group by ohscdt order by ohscdt)                                          
      select ohordt,ifnull(sum(ord."Amount"),0)                                
      from kivalib/orhdrpf full outer join order ord on ord."Date" =            
      ohordt where ohordt > 20140911                                            
      group by ohordt order by ohordt                                          
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                        Bottom  
 F3=Exit   F4=Prompt   F6=Insert line   F9=Retrieve   F10=Copy line            
 F12=Cancel            F13=Services     F24=More keys                          
                                                                               

But when I place it in the php; it give me a server error..

//$filledAmounts = default_Model_As400::ExecuteSelect(' ' .
                   //            'with Orders as (select ohscdt "Date", SUM(OHMTO$) "Amount" ' .
                   //            'from kivalib.orhdrpf ' .
                   //            'where OHSTAT = \'F\' AND OHTYP = 1 ' . $company . ' ' .
                   //            ' group by ohscdt order by ohscdt ' .
                   //            ') ' .
                   //            'SELECT ohordt, ifnull(sum(ord."Amount"),0) ' .
                   //            'FROM KIVALIB.ORHDRPF full outer join Orders ord on ord."Date" = ohordt ' .
                   //            'WHERE ohordt > ' . $ymd  . ' ' .
                   //            'GROUP BY ohordt, ORDER BY ohordt ', 'filled_placed_amounts.api.php');
0
 
LVL 58

Expert Comment

by:Gary
ID: 40333152
In the first one you use order and in the second one you use orders
Cannot see anything else wrong with your first sql
0
 

Author Comment

by:Kimberly Minarik
ID: 40333155
Thats just the differnect between the AS/400 execution and the php.  It is not an actual table on the AS/400.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40334016
Experts, a part of the sql under discussion here is also subject to another question:
sql is not working correctly

I have proposed a re-write to that piece and made some recommendations that may affect this question:
don't use unsafe sql terms like "order" or "date"
don't use table or column aliases that require double quotes
don't use order by inside with
and, don't use with unless there is a compelling reason to do so

Oh, and the dbms is DB2 I'm pretty sure
(AS/400 and IFNULL() both point to that)
0
 

Author Closing Comment

by:Kimberly Minarik
ID: 40344514
This guy is the greatest..
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
How can I make this form submit to itself? 10 35
two ways encryption with php 3 37
What does != "" mean in programming 8 78
Wordpress Pagination 1 28
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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 …

856 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