SELECT ifnull(ohordt,ord.ohscdt) theDate , ifnull(SUM(ord.Amount), 0) Amount
FROM kivalib/orhdrpf
FULL OUTER JOIN (
SELECT ohscdt , SUM(OHMTO$) Amount
FROM kivalib/orhdrpf
WHERE OHSTAT = 'F'
AND OHTYP = 1
AND OHCOCD = 'D'
GROUP BY ohscdt
) ord ON ord.ohscdt = ohordt
WHERE ohordt > 20140911
GROUP BY ifnull(ohordt,ord.ohscdt)
ORDER BY theDate
;
SQL = "" .
"SELECT ifnull(ohordt,ord.ohscdt) theDate " .
" , ifnull(SUM(ord.Amount), 0) Amount " .
"FROM kivalib/orhdrpf " .
"FULL OUTER JOIN " .
" ( SELECT ohscdt " .
" , SUM(OHMTO$) Amount " .
" FROM kivalib/orhdrpf " .
" WHERE OHSTAT = 'F' " .
" AND OHTYP = 1 " .
" AND OHCOCD = 'D' " .
" GROUP BY ohscdt " .
" ) ord ON ord.ohscdt = ohordt " .
"WHERE ohordt > 20140911 " .
"GROUP BY ifnull(ohordt,ord.ohscdt) " .
"ORDER BY theDate"
$SQL = "";
$SQL .= "SELECT ifnull(ohordt,ord.ohscdt) theDate ";
$SQL .= " , ifnull(SUM(ord.Amount), 0) Amount ";
$SQL .= "FROM kivalib/orhdrpf ";
$SQL .= "FULL OUTER JOIN ";
$SQL .= " ( SELECT ohscdt ";
$SQL .= " , SUM(OHMTO$) Amount ";
$SQL .= " FROM kivalib/orhdrpf ";
$SQL .= " WHERE OHSTAT = 'F' ";
$SQL .= " AND OHTYP = 1 ";
$SQL .= " AND OHCOCD = 'D' ";
$SQL .= " GROUP BY ohscdt ";
$SQL .= " ) ord ON ord.ohscdt = ohordt ";
$SQL .= "WHERE ohordt > 20140911 ";
$SQL .= "GROUP BY ifnull(ohordt,ord.ohscdt) ";
$SQL .= "ORDER BY theDate";
<?php
error_reporting(E_ALL);
$sql = <<<EOD
SELECT ifnull(ohordt,ord.ohscdt) theDate
, ifnull(SUM(ord.Amount), 0) Amount
FROM kivalib/orhdrpf
FULL OUTER JOIN
( SELECT ohscdt
, SUM(OHMTO$) Amount
FROM kivalib/orhdrpf
WHERE OHSTAT = 'F'
AND OHTYP = 1
AND OHCOCD = 'D'
GROUP BY ohscdt
) ord ON ord.ohscdt = ohordt
WHERE ohordt > 20140911
GROUP BY ifnull(ohordt,ord.ohscdt)
ORDER BY theDate
EOD;
var_dump($sql);
SQL = "" .
"WITH DAT1 AS " .
" (SELECT DISTINCT OHORDT ORDAT " .
" FROM KIVALIB/ORHDRPF " .
" ORDER BY OHORDT DESC " .
" FETCH FIRST 8 ROWS ONLY " .
" ) " .
" , DAT2 AS " .
" (SELECT OHSCDTSHPDAT " .
" , SUM(OHMTO$) SALES " .
" 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 " .
" ) " .
" AND OHCOCD = 'D' " .
" GROUP BY OHSCDT " .
" ORDER BY OHSCDT " .
" ) " .
"SELECT ORDAT " .
" , IFNULL(SALES,0) SALES " .
"FROM DAT1 " .
"LEFT OUTER JOIN DAT2 ON ORDAT = SHPDAT " .
"ORDER BY ORDAT"
If however you wish to continue with double quotes in the SQL then you have to contend with the issues that PHP also needs double quotes and to retain them in the SQL requires "escaping" of each double quote used by the SQL. Forming PHP with the SQL containing double quotes will look like this:SQL = "" .
"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$)\"SALES\" " .
" 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 " .
" ) " .
" AND OHCOCD = 'D' " .
" GROUP BY OHSCDT " .
" ORDER BY OHSCDT " .
" ) " .
"SELECT \"ORDAT\" " .
" , IFNULL(\"SALES\",0) \"SALES\" " .
"FROM DAT1 " .
"LEFT OUTER JOIN DAT2 ON \"ORDAT\" = \"SHPDAT\" " .
"ORDER BY \"ORDAT\""
<?php
error_reporting(E_ALL);
$qry = <<<EOD
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$)"SALES"
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)
AND OHCOCD = 'D'
GROUP BY OHSCDT ORDER BY OHSCDT)
SELECT "ORDAT", IFNULL("SALES",0) "SALES"
FROM DAT1 LEFT OUTER JOIN DAT2 ON "ORDAT" = "SHPDAT"
ORDER BY "ORDAT"
EOD;
var_dump($qry);
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!