Solved

I need help with this SQL statement written in php

Posted on 2014-09-19
14
72 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 108

Expert Comment

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

Expert Comment

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

Author Comment

by:Kimberly Minarik
Comment Utility
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
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Only problem I see is here
'. $company . ' .

Should be

. $company .
0
 

Author Comment

by:Kimberly Minarik
Comment Utility
I will try that thanks Gary...
0
 

Author Comment

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

Author Comment

by:Kimberly Minarik
Comment Utility
This is the company part..

if(isset($args['filters'][0]) && strlen($args['filters'][0]) > 0) {
                        $company = ' and ohcocd = \'' . $args['filters'][0] . '\' ';
                  } else {
                        $company = ' ';
                  }
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 58

Expert Comment

by:Gary
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
This guy is the greatest..
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
This article discusses four methods for overlaying images in a container on a web page
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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 …

762 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

6 Experts available now in Live!

Get 1:1 Help Now