I need help with this SQL statement written in php

$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');
Kimberly MinarikDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Please post the CREATE TABLE statements and the test data so we can recreate the issue, thanks.
0
GanapathiFacets DeveloperCommented:
What help you want? You haven't stated your problem/issue.
0
Kimberly MinarikDeveloperAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

GaryCommented:
Only problem I see is here
'. $company . ' .

Should be

. $company .
0
Kimberly MinarikDeveloperAuthor Commented:
I will try that thanks Gary...
0
Kimberly MinarikDeveloperAuthor Commented:
I am still getting an internal server error.  This does run correctly on the AS/400.
0
Kimberly MinarikDeveloperAuthor Commented:
This is the company part..

if(isset($args['filters'][0]) && strlen($args['filters'][0]) > 0) {
                        $company = ' and ohcocd = \'' . $args['filters'][0] . '\' ';
                  } else {
                        $company = ' ';
                  }
0
GaryCommented:
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
Kimberly MinarikDeveloperAuthor Commented:
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
Kimberly MinarikDeveloperAuthor Commented:
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
GaryCommented:
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
Kimberly MinarikDeveloperAuthor Commented:
Thats just the differnect between the AS/400 execution and the php.  It is not an actual table on the AS/400.
0
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kimberly MinarikDeveloperAuthor Commented:
This guy is the greatest..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.