• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 641
  • Last Modified:

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

Open in new window

desired.JPG
actual.JPG
0
wellso
Asked:
wellso
  • 2
1 Solution
 
mrjoltcolaCommented:
Nothing wrong with your syntax that I can see, except use of blank lines can cause problems in some tools.

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.
0
 
wellsoAuthor Commented:
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.
0
 
wellsoAuthor Commented:
Turned out to be something strange with the way OCI was binding the variables, thanks for the help anyway
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now