Query working in Oracle SQL developer but PHP replicating second part of UNION dataset
Im preforming a union and getting my required results set when I run the query in Oracle SQL developer. But when it come to iterating through the results set with I am getting only the output from the second part of the UNION using
while($row = oci_fetch_row($query)){
//Print results
}
Can anybody help me as to why this is?
actual.JPG
while($row = oci_fetch_row($query)){
//Print results
}
Can anybody help me as to why this is?
SELECT * FROM ( SELECT c.check_id AS check_id,
c.doc_id, trim(c.revision) AS revision,
trim(c.ORDER_NO) AS order_no,
trim(d.doc_line) AS doc_line,
trim(d.doc_desc) AS doc_desc,
trim(o.CUSTOMR_ID) AS customer_id,
trim(cu.CUST_NAME) AS customer_name,
TO_CHAR(c.check_requested, 'DD/MM/YY') AS creq,
TO_CHAR(c.check_returned, 'DD/MM/YY') AS cret,
c.check_requested reqsort,
c.check_returned AS retsort,
s.status_id AS status_id,
c.custref AS cust_ref,
c.revref AS rev_ref,
TO_CHAR(ch.chase_date, 'DD/MM/YY')AS last_chase,
n.order_no AS ordernote,
c.note AS linenote,
n.body AS onotebody,
LAG(c.status_id,1,'NA') OVER(PARTITION BY c.doc_id, c.order_no order by to_number(c.revision) NULLS FIRST) as prev_rev,
ROW_NUMBER() OVER(PARTITION BY c.doc_id, c.order_no order by to_number(c.revision) desc NULLS LAST, ch.chase_date DESC NULLS LAST) rn
FROM docsChecks c
INNER JOIN docsDocuments d ON d.doc_id = c.doc_id
INNER JOIN OPORDHD o ON c.order_no = o.ORDER_NUM
INNER JOIN SLCUSTX cu ON cu.CUSTOMR_ID = o.CUSTOMR_ID
INNER JOIN docsstatus s ON s.status_id = c.status_id
LEFT JOIN docschases ch ON ch.check_id = c.check_id
LEFT JOIN docsnotes n ON trim(n.order_no) = trim(o.ORDER_NUM)
LEFT JOIN docsmanagement m ON trim(m.order_no) = trim(c.order_no)
WHERE UPPER(o.ORDER_NUM) LIKE '%' || :amsearchterm || '%' AND cu.ledger_id = 'SL' ORDER BY order_no ASC, lpad(doc_line,10) ASC, revision ASC NULLS FIRST ) WHERE rn = 1
UNION
SELECT * FROM ( SELECT c.check_id AS check_id,
c.doc_id, trim(c.revision) AS revision,
trim(c.ORDER_NO) AS order_no,
trim(d.doc_line) AS doc_line,
trim(d.doc_desc) AS doc_desc,
trim(o.CUSTOMR_ID) AS customer_id,
trim(cu.CUST_NAME) AS customer_name,
TO_CHAR(c.check_requested, 'DD/MM/YY') AS creq,
TO_CHAR(c.check_returned, 'DD/MM/YY') AS cret,
c.check_requested reqsort,
c.check_returned AS retsort,
s.status_id AS status_id,
c.custref AS cust_ref,
c.revref AS rev_ref,
TO_CHAR(ch.chase_date, 'DD/MM/YY')AS last_chase,
n.order_no AS ordernote,
c.note AS linenote,
n.body AS onotebody,
LAG(c.status_id,1,'NA') OVER(PARTITION BY c.doc_id, c.order_no order by to_number(c.revision) NULLS FIRST) as prev_rev,
ROW_NUMBER() OVER(PARTITION BY c.doc_id, c.order_no order by to_number(c.revision) desc NULLS LAST, ch.chase_date DESC NULLS LAST) rn
FROM docsChecks c
INNER JOIN docsDocuments d ON d.doc_id = c.doc_id
INNER JOIN OPORDHD o ON c.order_no = o.ORDER_NUM
INNER JOIN SLCUSTX cu ON cu.CUSTOMR_ID = o.CUSTOMR_ID
INNER JOIN docsstatus s ON s.status_id = c.status_id
LEFT JOIN docschases ch ON ch.check_id = c.check_id
LEFT JOIN docsnotes n ON trim(n.order_no) = trim(o.ORDER_NUM)
LEFT JOIN docsmanagement m ON trim(m.order_no) = trim(c.order_no)
WHERE UPPER(o.ORDER_NUM) LIKE '%' || :amsearchterm2 || '%' AND cu.ledger_id = 'SL' ORDER BY order_no ASC, lpad(doc_line,10) ASC, revision ASC NULLS FIRST ) WHERE rn = 1
actual.JPG
ASKER
i put the blank lines in the code window just to make it clearer where the UNION was, the query itself is one continuous string when I echo it out in PHP before parsing it with oci_parse.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With SQLPLUS for example, you would have to set sqlblanklines on
Maybe the PHP oci api has an issue with it, so remove empty lines and try again.