?
Solved

I need help with this SQL statement written in php

Posted on 2014-09-19
14
Medium Priority
?
81 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 111

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
Independent Software Vendors: 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!

 
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 2000 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

Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …
Suggested Courses

770 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