-- table_to_query.sql   Generate SPT Query Code From an SCT Banner Table
--                      For creating a MS Access Pass-Through Query

--**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:) 

SET ECHO OFF

-- This program helps you create the Oracle SQL Code for an MS Access (SPT)
-- SQL Pass-Through Query
-- All fields are extracted retaining the original table's column names
-- Requires SELECT privileges to ALL_TAB_COLUMNS and ALL_TAB_COMMENTS
-- You may need to change all ! to $ for Host OS other than AIX

-- The user must review and make changes as required to select the appropriate records.
-- All DATE fields are Truncated to strip the time
-- inserts WHERE Statements for COLUMN_NAMEs ending with _EFF_DATE, _NCHG_DATE, _TERM_DATE,
-- N_CHANGE_IND and comment statements for the most likely to be used COLUMN_NAMEs for
-- additional user code
-- Date Format Matches MS Access Standard Date for four digit year display for ease of use

-- change log
-- 04/25/2000 bknox Created by bknox @t uaex.edu

SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK OFF
SET HEADING ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET TERMOUT ON
SET RECSEP OFF
SET LINESIZE 512
TTITLE OFF
BTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

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

ACCEPT TABLE_FROM CHAR PROMPT ' Enter Table for Selection --(xxxxxxx)-> '

COLUMN TABLE_FROM NEW_VALUE TABLE_FROM FORMAT A7
SELECT UPPER('&&TABLE_FROM') TABLE_FROM FROM DUAL;

COLUMN COL_ID NOPRINT

COLUMN Table_Desc NEW_VALUE Table_Desc FORMAT A65 TRUNC
SELECT SUBSTR(comments,1,65) Table_Desc
  FROM ALL_TAB_COMMENTS
 WHERE table_name='&&TABLE_FROM';

COLUMN Query_Type NEW_VALUE Query_Type FORMAT A11
SELECT TABLE_TYPE Query_Type FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = '&&TABLE_FROM';

!echo
!echo Creating Code for Query &&TABLE_FROM From &&Query_Type &&Table_Desc

SET TERMOUT OFF
-- Write out file for transmission
SET HEADING OFF
SET PAGESIZE 0   
-- no page breaks
SET WRAP OFF
SET SPACE 0      
-- no space(s) between columns
SET TIME OFF
SET TIMING OFF   
-- prevent time stamp at end of report
SET FEEDBACK OFF
SET TERMOUT OFF
SET ECHO OFF
SET HEADING OFF
SET TRIMSPOOL ON

!rm -f table_to_query.txt

SPOOL table_to_query.txt

-- This Section will Generate Code for Tables or Views (with Warning)

-- Output SELECT and Table Description
SELECT 0 COL_ID,'SELECT'||'   -- '||DECODE('&&Query_Type','VIEW','Warning Created from VIEW ','Created from TABLE ')||'&&TABLE_FROM'||'  '||'&&Table_Desc' FROM DUAL
UNION
-- Output Column Names
SELECT COLUMN_ID COL_ID, RPAD(DECODE(DATA_TYPE,'DATE','  TRUNC('||COLUMN_NAME||')'),37)||'  '||COLUMN_NAME||','
FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('&&TABLE_FROM') AND COLUMN_ID NOT IN 
(SELECT MAX(COLUMN_ID) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('&&TABLE_FROM'))
UNION
SELECT COLUMN_ID COL_ID, RPAD(DECODE(DATA_TYPE,'DATE','  TRUNC('||COLUMN_NAME||')'),37)||'  '||COLUMN_NAME
FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('&&TABLE_FROM') AND COLUMN_ID = 
(SELECT MAX(COLUMN_ID) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('&&TABLE_FROM'))
UNION
-- Output FROM table
SELECT 90000 COL_ID, '  FROM '||TABLE_NAME
FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
AND ROWNUM < 2
UNION
-- Output WHERE
SELECT 90001 COL_ID,' WHERE '
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND (COLUMN_NAME LIKE '%_EFF_DATE' OR COLUMN_NAME LIKE '%_NCHG_DATE' OR COLUMN_NAME LIKE '%_TERM_DATE')
    OR (COLUMN_NAME LIKE '%N_CHANGE_IND' AND TABLE_NAME = UPPER('&&TABLE_FROM'))
   AND ROWNUM < 2
UNION
SELECT 90001 COL_ID,'-- WHERE ' 
  FROM DUAL
 WHERE NOT EXISTS
 (SELECT 'X'   FROM ALL_TAB_COLUMNS
   WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
     AND (COLUMN_NAME LIKE '%_EFF_DATE'
      OR COLUMN_NAME LIKE '%_NCHG_DATE' 
      OR COLUMN_NAME LIKE '%_TERM_DATE')
     OR  COLUMN_NAME LIKE '%N_CHANGE_IND')
UNION
-- Output Critical Date Checking Code
SELECT 90002 COL_ID,'       TRUNC('||COLUMN_NAME||') <= SYSDATE'
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND COLUMN_NAME LIKE '%_EFF_DATE'
UNION
SELECT 90003 COL_ID,'   AND      ('||COLUMN_NAME||'  > TRUNC(SYSDATE) OR '||COLUMN_NAME||' IS NULL)'
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND COLUMN_NAME LIKE '%_NCHG_DATE'
UNION
SELECT 90004 COL_ID,'   AND      ('||COLUMN_NAME||'  > TRUNC(SYSDATE) OR '||COLUMN_NAME||' IS NULL)'
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND COLUMN_NAME LIKE '%_TERM_DATE'
UNION
-- Output comment code for most likely to be used fields to help "Access Programmers" with Oracle
-- > Last Year End
SELECT TO_NUMBER(TO_CHAR(91000+COLUMN_ID)||'1') COL_ID, '--'||' AND       '||COLUMN_NAME||'  >  TO_DATE('||''''||'06/30/'||TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-1)||' 00:00:00'||''''||','||''''||'MM/DD/YYYY HH24:MI:SS'||''''||')' 
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND COLUMN_NAME LIKE '%_DATE'
   AND NOT (COLUMN_NAME LIKE '%_EFF_DATE' OR COLUMN_NAME LIKE '%_NCHG_DATE' OR COLUMN_NAME LIKE '%_TERM_DATE')
UNION
-- <= Today
SELECT TO_NUMBER(TO_CHAR(91000+COLUMN_ID)||'2') COL_ID, '--'||' AND TRUNC('||COLUMN_NAME||')'||' >= TO_DATE('||''''||TO_CHAR(SYSDATE,'MM/DD/YYYY')||' 00:00:00'||''''||','||''''||'MM/DD/YYYY HH24:MI:SS'||''''||')' 
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND COLUMN_NAME LIKE '%_DATE'
   AND NOT (COLUMN_NAME LIKE '%_EFF_DATE' OR COLUMN_NAME LIKE '%_NCHG_DATE' OR COLUMN_NAME LIKE '%_TERM_DATE')
UNION
-- = Today
SELECT TO_NUMBER(TO_CHAR(91000+COLUMN_ID)||'3') COL_ID, '--'||' AND TRUNC('||COLUMN_NAME||')'||' =  TO_DATE('||''''||TO_CHAR(SYSDATE,'MM/DD/YYYY')||' 00:00:00'||''''||','||''''||'MM/DD/YYYY HH24:MI:SS'||''''||')' 
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND COLUMN_NAME LIKE '%_DATE'
   AND NOT (COLUMN_NAME LIKE '%_EFF_DATE' OR COLUMN_NAME LIKE '%_NCHG_DATE' OR COLUMN_NAME LIKE '%_TERM_DATE')
UNION
-- < Today
SELECT TO_NUMBER(TO_CHAR(91000+COLUMN_ID)||'4') COL_ID, '--'||' AND TRUNC('||COLUMN_NAME||')'||' <  TO_DATE('||''''||TO_CHAR(SYSDATE,'MM/DD/YYYY')||' 00:00:00'||''''||','||''''||'MM/DD/YYYY HH24:MI:SS'||''''||')' 
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND COLUMN_NAME LIKE '%_DATE'
   AND NOT (COLUMN_NAME LIKE '%_EFF_DATE' OR COLUMN_NAME LIKE '%_NCHG_DATE' OR COLUMN_NAME LIKE '%_TERM_DATE')
UNION
-- Between Dates
SELECT TO_NUMBER(TO_CHAR(91000+COLUMN_ID)||'5') COL_ID, '--'||' AND TRUNC('||COLUMN_NAME||')'||' BETWEEN TO_DATE('||''''||'07/01/'||TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-1)||' 00:00:00'||''''||','||''''||'MM/DD/YYYY HH24:MI:SS'||''''||')'||' AND '||'TO_DATE('||''''||'06/30/'||TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')))||' 23:59:59'||''''||','||''''||'MM/DD/YYYY HH24:MI:SS'||''''||')'||''''
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND COLUMN_NAME LIKE '%_DATE'
   AND NOT (COLUMN_NAME LIKE '%_EFF_DATE' OR COLUMN_NAME LIKE '%_NCHG_DATE' OR COLUMN_NAME LIKE '%_TERM_DATE')
UNION
SELECT 91000+COLUMN_ID COL_ID, '--'||' AND '||COLUMN_NAME||' = '||''''||'?'||''''
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND COLUMN_NAME LIKE '%_IND'
UNION
-- Output comments for Fields that may have Relationships to Set in MS Access
SELECT 920000+COLUMN_ID COL_ID, '--'||RPAD(COLUMN_NAME,30) ||'NOT NULLABLE Consider Setting Relationships' FROM  ALL_TAB_COLUMNS WHERE
TABLE_NAME = UPPER('&&TABLE_FROM') AND NULLABLE = 'N'   
UNION
SELECT 930000+COLUMN_ID COL_ID, 'ORDER BY '||COLUMN_NAME
  FROM ALL_TAB_COLUMNS 
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND SUBSTR(TABLE_NAME,4,7) = SUBSTR(COLUMN_NAME,9,4) AND NULLABLE = 'N'
UNION
SELECT 940000+COLUMN_ID COL_ID, 'ORDER BY '||COLUMN_NAME
  FROM ALL_TAB_COLUMNS 
 WHERE TABLE_NAME = UPPER('&&TABLE_FROM')
   AND SUBSTR(COLUMN_NAME,8,5) = '_PIDM' AND NULLABLE = 'N'
ORDER BY 1;

SPOOL OFF

!echo Table Query Created'
!echo
SET FEEDBACK ON
SET TERMOUT ON
SET HEADING ON
SET SPACE 1      
-- 1 space(s) between columns
SET TIME ON
SET TIMING ON   
SET FEEDBACK ON
SET ECHO ON

-- This script is available "as is".  See Disclaimer http://www.uaex.edu/bknox/ms_access.htm
SET TERMOUT ON