SQL Works In Oracle, when put into foxpro table 1 column displays NULL

[mdav2]

I am amending some SQL to create a report (see below). The SQL work fine when executed in Oracle. However, when I run the query from Foxpro (using SQLEXEC) all the data returns correctly apart from the last column (nperiod). This just shows as a series of NULL's.

The links between the tables used are

INVHD - Invoice header table (links using ninvref)
INVHD - Invoice line table (links using ninvref)
SUPPLIER - Supplier View (links through csupplref)
PAYDETAIL - Payments details table (links through payref)
OAPPARAM - Holds parameters for finacial year periods (no direct link to any of the above. Looks up value based upon date field in the PAYDETAIL table)

Unfortunately I cannot change the structure of the tables. The original code created a cartesian join in order to get this query to work but that slows down the database and caused errors by using all tempoary tablespace.

I can't see why a statement would work if run in a querying tool against the oracle database and wouldn't work if run via VFP. Any ideas anyone?

/* Formatted on 2009/04/17 08:40 (Formatter Plus v4.8.7) */
SELECT   ID.crespoff crespoff, ih.ninvref ninvref, ih.cinvoiceno cinvref,
         ih.ccurrency,
         SUBSTR (TO_CHAR (ih.dinvdate, 'DD/MM/YYYY'), 1, 10) dinvdate,
         SUBSTR (TO_CHAR (ID.dauthdt, 'DD/MM/YYYY'), 1, 10) dauthdate,
         ih.nvatamount nvatamount, ih.nvate nvate, ih.ninvtot ninvtot,
         ih.ninvtote ninvtote, ih.cmultioff cmultioff, ih.coapsrce cdispind,
         ih.nseqnum nseqnum, ID.nlinetot nlinetot, ID.nlinetote nlinetote,
         su.csuppname csuppname, su.csupptype csupptype,
         DECODE (ih.nsuppterm, 0, su.nsuppterm, ih.nsuppterm) nsuppterm,
         SUBSTR (TO_CHAR (pd.dpaydate, 'DD/MM/YYYY'), 1, 10) dpaydate,
         SUBSTR (TO_CHAR (pd.deffectdte, 'DD/MM/YYYY'), 1, 10) deffdate,
         DECODE (SIGN (ih.dreceived - ih.dinvdate),
                 1, (TRUNC (pd.deffectdte) + 1) - TRUNC (ih.dreceived),
                 (TRUNC (pd.deffectdte) + 1
                 ) - (TRUNC (ih.dinvdate) + 2)
                ) nelapsed,
         (  DECODE (SIGN (ih.dreceived - ih.dinvdate),
                    1, (TRUNC (pd.deffectdte) + 1) - TRUNC (ih.dreceived),
                    (TRUNC (pd.deffectdte) + 1
                    ) - (TRUNC (ih.dinvdate) + 2)
                   )
          - DECODE (ih.nsuppterm, 0, su.nsuppterm, ih.nsuppterm)
         ) ndelay,
         0 nactelapse, 0 nactlate,
         SUBSTR (TO_CHAR (ih.dreceived, 'DD/MM/YYYY'), 1, 10) dreceived,
         ID.cfinyear cfinyear,
         (SELECT ncode
            FROM oapall.oapparam
           WHERE csection = 'FP'
             AND TO_DATE (pd.deffectdte, 'DD/MM/YYYY') BETWEEN ddatevalid
                                                           AND ddateend)
                                                                      nperiod
    FROM euop2.invhd ih, 
         euop2.invdt ID, 
         euop2.supplier su,
         euop2.paydetail pd
   WHERE ID.ninvref = ih.ninvref
     AND pd.cpayref = ih.cpayref
     AND su.csupplref = ih.csupplref
     AND ih.cdoctype = 'I'
     AND ih.coapsrce <> 'S'
     AND (pd.deffectdte >= TO_DATE ('01/03/2008', 'DD/MM/YYYY'))
     AND (pd.deffectdte <= TO_DATE ('31/03/2008', 'DD/MM/YYYY'))
ORDER BY 1, 2, 3, 18, 5

Mark Davies
Warwickshire County Council

 

[mdav2]

My only real thoughts at the moment is to put the base query into a view and then have the user apply the criteria they like to it.

Mark Davies
Warwickshire County Council

 

[Mike Lewis]

Mark,

Like you, I can't see why it would run within Oracle but not via SQLEXEC(). It's clearly something to do with the inner SELECT on ncode, but I don't know Oracle well enough to know.

However, I don't think making it into a view would help. A view is just a wrapper for the SQL, and the same code will be sent to the server.

Hope someone else can give you the answer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[mdav2]

I've spoken with my colleague and we think the view option is the way to go. The problem looks to be the way ODBC is taking to the oracle database, although I can't prove it.

Will set up the view and see if that works.

Mark Davies
Warwickshire County Council

 

[Mike Lewis]

Mark,

Let us know what you find.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[mdav2]

Fixed it! The problem was with the oracle TO_DATE function. I had applied this to an character date in testing which I then replace with a date field for live. Removing the function fixed my problem.

Mike was right, I create the statement as a view which worked in the oracle database but not via ODBC as a straight select from view.

Its great when you can fix your own problems!

Mark Davies
Warwickshire County Council

 

[Mike Lewis]

Mark,

Good to hear you fixed it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business