Good morning:
Quite by accident I discovered that I had a view that had been inadvertently
defined with duplicate column names. The data in the columns (both named
GAUGE) was coming from two different tables. This lead to some confusion
when a user tried to query the view with criteria against the GAUGE column.
Which GAUGE column was being referenced? I assume R:Base probably uses the
first GAUGE column it encounters. At any rate, if I had done this once I
knew there was a possibility that I had done it in other views. In fact I
discovered two other views with this situation.
To make a long story short, I created a quick and dirty command file that
assembles a table with all view names and column names so that you can
easily check to see if any views have duplicate column names. Granted there
MAY be a valid reason for having duplicate names but I can't think what it
might be. I hereby share my code in case anyone else wants to check their
databases. I make no guarantees or assume any liability for any damage that
may occur as a result of using this code. The code ran fine on my machine
and it's been run through R:Code. You will need to customize the command
file to your particular database name, etc.
As an aside, should R:Base allow such a thing to happen or should there be
some kind of check for this? Just curious.
Hope this helps someone.
Thanks
Mike Ramsour
Voice: 740-829-4340
-- COMMAND FILE TO CHECK FOR DUPLICATE COLUMN NAMES IN VIEW DEFINITIONS
-- WRITTEN: OCTOBER 15, 2002
-- AUTHOR: MIKE RAMSOUR
-- VOICE: 740-829-4340
--
CONNECT maindata -- CONNECT TO THE DATABASE. PROVIDE YOUR OWN DATABASE
NAME.
--
-- SET UP THE VARIABLES
--
SET VAR v_viewname TEXT,vcolumns TEXT,vcol_count INTEGER,vcol_name TEXT
--
SET VAR vcol_cntr INTEGER,vlen INTEGER
--
-- SET AN ERROR VARIABLE AND A VARIABLE TO TRAP THE VALUE
--
SET ERROR VAR verrcode
--
SET VAR verrtrap = .verrcode
--
-- CREATE A VIEW TO DRIVE THE PROCEDURE THAT HAS VIEW NAMES AND THEIR COLUMN
-- LIST
--
DROP VIEW view_names
--
CREATE VIEW view_names (sys_table_id,sys_table_name,sys_column_list, +
sys_query) +
AS SELECT t1.sys_table_id,t2.sys_table_name,t1.sys_column_list,t1.sys_query
+
FROM sys_views t1,sys_tables t2 +
WHERE t1.sys_table_id=t2.sys_table_id
--
-- CREATE A TEMPORARY TABLE TO HOLD THE TABLE AND COLUMN COMBINATIONS
--
CREATE TEMPORARY TABLE column_test (table_name TEXT 18,column_name TEXT 18)
--
-- DELCARE A CURSOR TO STEP THROUGH THE VIEW_NAMES VIEW
--
DECLARE vstep_cursor CURSOR FOR SELECT sys_table_name,sys_column_list +
FROM view_names ORDER BY sys_table_name
--
OPEN vstep_cursor
--
FETCH vstep_cursor INTO v_viewname,vcolumns
--
SET VAR verrtrap = .verrcode
--
-- OUTSIDE WHILE LOOP STEPS THROUGH THE VIEW NAMES
--
WHILE verrtrap = 0 THEN
--
SET VAR vcol_count = (itemcnt(.vcolumns))
--
SET VAR vcol_cntr = 1
--
SET VAR vlen = (SLEN(.vcolumns))
--
SET VAR vlist TEXT = (SGET(.vcolumns,(.vlen - 2),2))
--
-- INSIDE WHILE LOOP STEPS THROUGH THE COLUMN LIST FOR A PARTICULAR VIEW
-- AND POPULATES THE COLUMN_TEST TABLE
--
WHILE vcol_cntr <= .vcol_count THEN
--
SET VAR vcol_name = (SSUB(.vlist,.vcol_cntr))
--
INSERT INTO column_test * VALUES .v_viewname,.vcol_name
--
SET VAR vcol_cntr = (.vcol_cntr + 1)
--
ENDWHILE
--
FETCH vstep_cursor INTO v_viewname,vcolumns
--
SET VAR verrtrap = .verrcode
--
ENDWHILE
--
DROP CURSOR vstep_cursor
--
-- ELIMINATES EXTRA SPACE AT BEGINNING OF SOME COLUMN NAMES JUST IN CASE
--
UPDATE column_test SET column_name =
(SGET(column_name,(SLEN(column_name)),2)) +
WHERE column_name LIKE ' %'
--
-- SELECTION TO SHOW ANY VIEWS THAT HAVE DUPLICATE COLUNN NAMES IN THEIR
-- DEFINITION
--
SELECT table_name,column_name,COUNT(*) FROM column_test +
GROUP BY table_name,column_name ORDER BY 3 DESC
--
RETURN
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
