Forum Stats

  • 3,876,179 Users
  • 2,267,077 Discussions
  • 7,912,462 Comments

Discussions

Categories

WHERE ROWNUM <= 65 issue with php

user10127912
user10127912 Member Posts: 39
edited Aug 26, 2014 3:51PM in PHP

Hi,

We have a Oracle database server 11.2.0.1 that run in Oracle Linux version 3.8.13-16.2.3.el6uek.x86_64, and a Linux 32-bit client server that run in Linux CentOS 6.2.

From the 32-bit client server if we run the query below by sqlplus we have no problem. When we run the same query from the same client server but by php page, it does not return more than 65 row. But (WHERE ROWNUM <= 60) it returns the result fine. Please help.

Aqlplus connection:

sqlplus appsodbc/[email protected]:1521/pterpdb02.domain.net

Sqlplus query:

SELECT * FROM ( SELECT p.T$NAMA AS NAMA, o.T$ORNO AS ORNO, CASE CAST(o.T$CORG AS INT ) WHEN 1 THEN 'Contracts' WHEN 2 THEN 'Quotations' WHEN 3 THEN 'EDI' WHEN 4 THEN 'Manual' WHEN 5 THEN 'Phone' WHEN 6 THEN 'Fax' WHEN 7 THEN 'Mail' WHEN 8 THEN 'Opportunity' WHEN 9 THEN 'CRM' WHEN 10 THEN 'Consumption' WHEN 14 THEN 'Order Template' WHEN 21 THEN 'Service' WHEN 22 THEN 'Intercompany EDI' WHEN 25 THEN 'Retro-Billing' WHEN 30 THEN 'Planning' WHEN 35 THEN 'Purchase' WHEN 40 THEN 'Shipment' ELSE 'Unknown' END AS ORD_ORIGIN, CASE CAST(o.T$HDST AS INT ) WHEN 2 THEN 'Suspended' WHEN 5 THEN 'Free' WHEN 10 THEN 'Approved' WHEN 20 THEN 'In Process' WHEN 25 THEN 'Modified' WHEN 30 THEN 'Closed' WHEN 35 THEN 'Cancelled' WHEN 40 THEN 'Blocked' WHEN 45 THEN 'Released' WHEN 50 THEN 'Not Applicable' ELSE 'Unknown' END AS ORD_STATUS, o.T$ODAT AS ODAT, o.T$CORN AS CORN, SUM((l.T$OAMT)) AS ORD_TOT FROM INFOR.TTDSLS400104 o, INFOR.TTDSLS401104 l, INFOR.TTCCOM100104 p WHERE o.T$ORNO = l.T$ORNO AND p.T$BPID (+)= o.T$STBP AND o.T$OFBP = '210008000' AND o.T$ODAT BETWEEN to_date('07/14/2014', 'MM-DD-YYYY') and to_date('08/14/2014', 'MM-DD-YYYY') GROUP BY p.T$NAMA, o.T$ORNO, o.T$ODAT, o.T$CORN, o.T$STBP, o.T$CORG, o.T$HDST ORDER BY o.T$ODAT DESC ) WHERE ROWNUM <= 500

odbc.ini :

[PTERPDB02]

Application Attributes=T

Attributes=W

BatchAutocommitMode=IfAllSuccessful

CloseCursor=T

DisableDPM=F

DisableMTS=T

Driver=Oracle_ODBC_Driver_in_dmz3

DSN=palnerptest2

EXECSchemaOpt=

EXECSyntax=T

Failover=T

FailoverDelay=10

FailoverRetryCount=10

FetchBufferSize=64000

ForceWCHAR=F

Lobs=T

Longs=T

MetadataIdDefault=F

QueryTimeout=T

ResultSets=T

ServerName=PTERPDB02

SQLGetData extensions=F

Translation DLL=

Translation Option=0

UserID=appsodbc

Php query:

'SELECT * FROM (SELECT

        p.T$NAMA AS NAMA,

        o.T$ORNO AS ORNO,

        CASE CAST(o.T$CORG AS INT )

                WHEN 1 THEN \'Contracts\'

                WHEN 2 THEN \'Quotations\'

                WHEN 3 THEN \'EDI\'

                WHEN 4 THEN \'Manual\'

                WHEN 5 THEN \'Phone\'

                WHEN 6 THEN \'Fax\'

                WHEN 7 THEN \'Mail\'

                WHEN 8 THEN \'Opportunity\'

                WHEN 9 THEN \'CRM\'

                WHEN 10 THEN \'Consumption\'

                WHEN 14 THEN \'Order Template\'

                WHEN 21 THEN \'Service\'

                WHEN 22 THEN \'Intercompany EDI\'

                WHEN 25 THEN \'Retro-Billing\'

                WHEN 30 THEN \'Planning\'

                WHEN 35 THEN \'Purchase\'

                WHEN 40 THEN \'Shipment\'

                ELSE \'Unknown\'

                END AS ORD_ORIGIN,

        CASE CAST(o.T$HDST AS INT )

                WHEN 2 THEN \'Suspended\'

                WHEN 5 THEN \'Free\'

                WHEN 10 THEN \'Approved\'

                WHEN 20 THEN \'In Process\'

                WHEN 25 THEN \'Modified\'

                WHEN 30 THEN \'Closed\'

                WHEN 35 THEN \'Cancelled\'

                WHEN 40 THEN \'Blocked\'

                WHEN 45 THEN \'Released\'

                WHEN 50 THEN \'Not Applicable\'

                ELSE \'Unknown\'

                END AS ORD_STATUS,

        o.T$ODAT AS ODAT,

        o.T$CORN AS CORN,

        SUM((l.T$OAMT)) AS ORD_TOT

FROM

        INFOR.TTDSLS400104 o,

        INFOR.TTDSLS401104 l,

        INFOR.TTCCOM100104 p

WHERE

        o.T$ORNO = l.T$ORNO AND

        p.T$BPID (+)= o.T$STBP AND

        o.T$OFBP = \'210008000\' AND

        o.T$ODAT BETWEEN to_date(\'07/15/2014\', \'MM-DD-YYYY\') and to_date(\'08/15/2014\', \'MM-DD-YYYY\')

GROUP BY

        p.T$NAMA,

        o.T$ORNO,

        o.T$ODAT,

        o.T$CORN,

        o.T$STBP,

        o.T$CORG,

        o.T$HDST

ORDER BY

        o.T$ODAT DESC ) WHERE ROWNUM <= 65';

· Share on TwitterShare on Facebook

Answers

This discussion has been closed.