asked on

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');

Ray Paseur

Please post the CREATE TABLE statements and the test data so we can recreate the issue, thanks.

Ganapathi

What help you want? You haven't stated your problem/issue.

Kimberly Minarik

ASKER

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.

Gary

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

Should be

. $company .

Kimberly Minarik

ASKER

I will try that thanks Gary...

Kimberly Minarik

ASKER

I am still getting an internal server error. This does run correctly on the AS/400.

Kimberly Minarik

ASKER

This is the company part..

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

Gary

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.

Kimberly Minarik

ASKER

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?

Kimberly Minarik

ASKER

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');

Gary

In the first one you use order and in the second one you use orders
Cannot see anything else wrong with your first sql

Kimberly Minarik

ASKER

Thats just the differnect between the AS/400 execution and the php. It is not an actual table on the AS/400.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kimberly Minarik

ASKER

This guy is the greatest..