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

For a Banner Table you start the program and enter the Table name (e.g., FTVORGN). 

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Mar 5 09:24:15 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name: bknox
Enter password:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production

BKNOX: ACES> @table_to_query


This program will Generate the Code Required to Create a Query based upon an Existing Table or View

Enter Table for Selection --(xxxxxxx)-> FTVORGN

TABLE_FROM
------------------------------
FTVORGN

TABLE_DESC
-----------------------------------------------------------------
Organization Validation Table

QUERY_TYPE
-----------
TABLE


Creating Code for Query FTVORGN From TABLE Organization Validation Table

Table Query Created

SELECT -- Created from TABLE FTVORGN Organization Validation Table
FTVORGN_COAS_CODE,
FTVORGN_ORGN_CODE,
TRUNC(FTVORGN_EFF_DATE) FTVORGN_EFF_DATE,
TRUNC(FTVORGN_ACTIVITY_DATE) FTVORGN_ACTIVITY_DATE,
FTVORGN_USER_ID,
TRUNC(FTVORGN_NCHG_DATE) FTVORGN_NCHG_DATE,
TRUNC(FTVORGN_TERM_DATE) FTVORGN_TERM_DATE,
FTVORGN_TITLE,
FTVORGN_STATUS_IND,
FTVORGN_ORGN_CODE_PRED,
FTVORGN_FUND_CODE_DEF,
FTVORGN_PROG_CODE_DEF,
FTVORGN_ACTV_CODE_DEF,
FTVORGN_LOCN_CODE_DEF,
FTVORGN_DATA_ENTRY_IND,
FTVORGN_FMGR_CODE_PIDM,
FTVORGN_ENCB_POLICY_IND,
FTVORGN_ORGN_CODE_NSF,
FTVORGN_HIERARCHY_TABLE_IND,
FTVORGN_ALT_POOL_IND
FROM FTVORGN
WHERE
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)
-- AND FTVORGN_STATUS_IND = '?'
-- AND FTVORGN_DATA_ENTRY_IND = '?'
-- AND FTVORGN_ENCB_POLICY_IND = '?'
-- AND FTVORGN_HIERARCHY_TABLE_IND = '?'
-- AND FTVORGN_ALT_POOL_IND = '?'
-- AND FTVORGN_ACTIVITY_DATE > TO_DATE('06/30/2002 00:00:00','MM/DD/YYYY HH24:MI:SS')
-- AND TRUNC(FTVORGN_ACTIVITY_DATE) >= TO_DATE('03/05/2003 00:00:00','MM/DD/YYYY HH24:MI:SS')
-- AND TRUNC(FTVORGN_ACTIVITY_DATE) = TO_DATE('03/05/2003 00:00:00','MM/DD/YYYY HH24:MI:SS')
-- AND TRUNC(FTVORGN_ACTIVITY_DATE) < TO_DATE('03/05/2003 00:00:00','MM/DD/YYYY HH24:MI:SS')
-- 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')'
--FTVORGN_COAS_CODE NOT NULLABLE Consider Setting Relationships
--FTVORGN_ORGN_CODE NOT NULLABLE Consider Setting Relationships
--FTVORGN_EFF_DATE NOT NULLABLE Consider Setting Relationships
--FTVORGN_ACTIVITY_DATE NOT NULLABLE Consider Setting Relationships
--FTVORGN_USER_ID NOT NULLABLE Consider Setting Relationships
--FTVORGN_NCHG_DATE NOT NULLABLE Consider Setting Relationships
--FTVORGN_TITLE NOT NULLABLE Consider Setting Relationships
--FTVORGN_STATUS_IND NOT NULLABLE Consider Setting Relationships
--FTVORGN_DATA_ENTRY_IND NOT NULLABLE Consider Setting Relationships
ORDER BY FTVORGN_ORGN_CODE

(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

 


 Disclaimer

Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences.  The scripts and information are provided "as is" without warranty, implied or otherwise.  Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)

 

 Hit Counter visits

 

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

http://www.uaex.edu/bknox/