Solved

I need help with this SQL statement written in php

Posted on 2014-09-19
14
80 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
[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
14 Comments
 
LVL 110

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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
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 49

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to implement server side field validation and display customized error messages to the client.
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…
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.

690 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