Using SQLPass-Though Queries for Banner Reporting

 by Bruce Knox  bknox @t uaex.edu     Oracle Database Programmer/Analyst                                                                   date last updated 05/13/2009

Where do you get the code?  Let Oracle Create your Code from the Data Dictionary.

(Yes, the commented date is an error.  We will get to that soon.)
 

Paste the generated code.
 

Yep, we knew that.  BTW, this is one of the very most informative "ODBC -- call failed." messages I have seen.  Most give no clue to the cause.  I run my generated Queries in SQL*Plus to get a (usually) much better error message, but in this case the messages were the same.

-- AND TRUNC(FTVORGN_ACTIVITY_DATE) BETWEEN TO_DATE('07/01/2002 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('06/30/2003 23:59:59'
,'MM/DD/YYYY HH24:MI:SS')'

We didn't remove the word wrap and the date was broken by the editor.

-- AND TRUNC(FTVORGN_ACTIVITY_DATE) BETWEEN TO_DATE('07/01/2002 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('06/30/2003 23:59:59','MM/DD/YYYY HH24:MI:SS')'

     {On a single line.}

Testing it:
 

The automatic Sort (ORDER BY) is great for a Banner table you will only be viewing, but mostly we will be combining our Queries with other Queries.  In that case, sorting the component tables only slows you down (and everyone else on the server).  There is no advantage in MS Access to having these component tables sorted.

So, go back into Design and comments out the statement:
-- ORDER BY FTVORGN_ORGN_CODE

Note the many other commented statements.  Some are for reference and others are just misguided suggestions:)

I never use defaulted Relationships in MS Access, because it slows down your startup processing.  It seems like a great idea, but it doesn't work well with a complex database such as Banner.  It is especially slow when used with SQL Pass-Through Queries.

This seems like a great deal to do, so it takes a long time anyway, right?  Not at all.  You can create a new Query using the above methods in 5-10 minutes.  The routines that do this are generic and do not use specific Banner code.  That means that it will completely work for SPRIDEN and will only give you a starting place for the most complex tables.  But, it is a great starting point.  Note that the routine takes care of the 

TRUNC(FTVORGN_EFF_DATE) <= SYSDATE
AND (FTVORGN_NCHG_DATE > TRUNC(SYSDATE) OR FTVORGN_NCHG_DATE IS NULL)
AND (FTVORGN_TERM_DATE > TRUNC(SYSDATE) OR FTVORGN_TERM_DATE IS NULL)

logic that is seen in many of the validation tables.  The default is to use the current date (<=SYSDATE) records, which is the most commonly used Query.  For specific future or past dates, you will need to change the code.

The Date Columns are all TRUNC(date_column) date_column
That makes all the dates have "as of beginning of day" time and displays only the MM/DD/YYYY.

There are some cases where the Query must know the time; however, each of these requires special knowledge (i.e., the pattern is difficult to predict for Banner dates).

Next: At some point you need the Oracle Guys!

Back

Top

page mirror http://www.uaex.edu/bknox/Summit_2003_p08.htm  Let Oracle Create your Code from the Data Dictionary.