this might be useful

I know how to find general info on a view; what I needed was to find the creation of the view.

Here's what I did last night:

1. Find the VIEWS table - since it has a unique filecode of 585, searched the system for any file with that filecode and a name like "views".
2. Opened it up and look for the view name that the code is using.
3. Curse up a storm, because the fuckwads at Tandem who designed this back in the 1970s decided that the actual view name, what you call it, will not be stored in the table, BUT THE ACTUAL 8 CHARACTER FILENAME OF THE FILE THAT CONTAINS THE CREATE VIEW CODE. So you have to figure out which file has the view that you want.
4. Get the right filename through guessing/trial and error, use that to go into the views table and display the CREATE VIEW code using this select:

SELECT SUBSTRING(viewtext FROM 1 FOR 70),
SUBSTRING(viewtext FROM 71 FOR 70),
SUBSTRING(viewtext FROM 141 FOR 70),
SUBSTRING(viewtext FROM 211 FOR 70),
SUBSTRING(viewtext FROM 281 FOR 70),
SUBSTRING(viewtext FROM 351 FOR 70),
SUBSTRING(viewtext FROM 421 FOR 70),
SUBSTRING(viewtext FROM 491 FOR 70),
SUBSTRING(viewtext FROM 561 FOR 70),
SUBSTRING(viewtext FROM 631 FOR 70),
SUBSTRING(viewtext FROM 701 FOR 70),
SUBSTRING(viewtext FROM 771 FOR 70),
SUBSTRING(viewtext FROM 841 FOR 70),
SUBSTRING(viewtext FROM 911 FOR 70),
SUBSTRING(viewtext FROM 981 FOR 70),
SUBSTRING(viewtext FROM 1051 FOR 70),
SUBSTRING(viewtext FROM 1121 FOR 70),
SUBSTRING(viewtext FROM 1191 FOR 70)
FROM views
WHERE viewname like '\\EAIDR.$CAT.SC01IDAT.VPRRI%'
browse access;

This gave me:

(EXPR)
-------------------------------------------------------------------------------

CREATE VIEW \\EAIDR.$CAT.SC01IDAT.VPRRI ( CUSTOMER_NO , SALES_ORGANIZAT
ION_CD , DISTRIBUTION_CHANNEL_CD , DIVISION_CD , PARTNER_FUNCTION_CD ,
PARTNER_COUNTER_NO , SOURCE_NM , BUSINESS_PARTNER_NO , PARTNER_CONTAC
T_NO , CENTRAL_DELETE_ACTIVE_FL , CENTRAL_DELETE_INACTIVE_FL , ISO_COU
NTRY_CD , SOURCE_DT , SYSTEM_UPDATE_DT ) AS SELECT A.CUSTOMER_NO , A.S
ALES_ORGANIZATION_CD , A.DISTRIBUTION_CHANNEL_CD , A.DIVISION_CD , A.P
ARTNER_FUNCTION_CD , A.PARTNER_COUNTER_NO , A.SOURCE_NM , A.BUSINESS_P
ARTNER_NO , A.PARTNER_CONTACT_NO , A.CENTRAL_DELETE_ACTIVE_FL , A.CENT
RAL_DELETE_INACTIVE_FL , A.ISO_COUNTRY_CD , A.SOURCE_DT , A.SYSTEM_UPD
ATE_DT FROM ( ( \\EAIDR.$CAT.SC01IDAT.CUSTPNFU A LEFT JOIN \\EAIDR.$CAT.
SC01IDAT.CUSTOMER B ON A.CUSTOMER_NO = B.CUSTOMER_NO ) LEFT JOIN \\EAID
R.$CAT.SC01IDAT.SLARSCCU C ON ( ( ( ( A.CUSTOMER_NO = C.CUSTOMER_NO )
AND ( A.SALES_ORGANIZATION_CD = C.SALES_ORGANIZATION_CD ) ) AND ( A.DI
STRIBUTION_CHANNEL_CD = C.DISTRIBUTION_CHANNEL_CD ) ) AND ( A.DIVISION
_CD = C.DIVISION_CD ) ) AND ( A.SOURCE_NM = C.SOURCE_NM ) ) WHERE ( (
A.CUSTOMER_NO = B.CUSTOMER_NO ) AND ( ( A.SOURCE_NM = B.SOURCE_NM ) OR
( A.SOURCE_NM = "PGWW79" ) ) ) AND ( ( A.SOURCE_NM = C.SOURCE_NM ) OR
( A.SOURCE_NM = "PGWW79" ) )

--- 1 row(s) selected.

NOW I know what the underlying logic is, so I know why the code is dropping records based upon specific conditions.

Post #157,103 by SpiceWare 5/26/04 5:41:22 PM 5/26/04 5:45:24 PM Reply	this might be useful A few minutes on Google turned up this - [link\|http://h71033.www7.hp.com/nsswdocs/sqlodbc/sqlmp_G0613/SQLMP_Install_&_Mgmt_429830.pdf\|NonStop SQL/MP Installation and Management Guide](3.3 MB PDF) Page 6-3(page 161 of PDF) is the beginning of the section Displaying Current Database Defintions page 6-4 thru 6-5(page 162-163 of PDF) is Displaying Information About Views and has 4 queries listed for extracting a view's tables, columns, ect. Also, page 3-13 thru 3-14 (69-70) lists some of the advantages to using views. Darrell Spice, Jr. [link\|http://www.spiceware.org/cgi-bin/spa.pl?album=./Artistic%20Overpass\|Artistic Overpass]\n[link\|http://www.spiceware.org/\|SpiceWare] - We don't do Windows, it's too much of a chore Edited by SpiceWare May 26, 2004, 05:45:24 PM EDT Expand All History
Post #157,126 by Another Scott 5/26/04 8:37:37 PM Reply	The IGM strikes again.
Post #157,173 by lincoln 5/27/04 8:43:07 AM Reply	Good find, but not what I needed I know how to find general info on a view; what I needed was to find the creation of the view. Here's what I did last night: 1. Find the VIEWS table - since it has a unique filecode of 585, searched the system for any file with that filecode and a name like "views". 2. Opened it up and look for the view name that the code is using. 3. Curse up a storm, because the fuckwads at Tandem who designed this back in the 1970s decided that the actual view name, what you call it, will not be stored in the table, BUT THE ACTUAL 8 CHARACTER FILENAME OF THE FILE THAT CONTAINS THE CREATE VIEW CODE. So you have to figure out which file has the view that you want. 4. Get the right filename through guessing/trial and error, use that to go into the views table and display the CREATE VIEW code using this select: SELECT SUBSTRING(viewtext FROM 1 FOR 70), SUBSTRING(viewtext FROM 71 FOR 70), SUBSTRING(viewtext FROM 141 FOR 70), SUBSTRING(viewtext FROM 211 FOR 70), SUBSTRING(viewtext FROM 281 FOR 70), SUBSTRING(viewtext FROM 351 FOR 70), SUBSTRING(viewtext FROM 421 FOR 70), SUBSTRING(viewtext FROM 491 FOR 70), SUBSTRING(viewtext FROM 561 FOR 70), SUBSTRING(viewtext FROM 631 FOR 70), SUBSTRING(viewtext FROM 701 FOR 70), SUBSTRING(viewtext FROM 771 FOR 70), SUBSTRING(viewtext FROM 841 FOR 70), SUBSTRING(viewtext FROM 911 FOR 70), SUBSTRING(viewtext FROM 981 FOR 70), SUBSTRING(viewtext FROM 1051 FOR 70), SUBSTRING(viewtext FROM 1121 FOR 70), SUBSTRING(viewtext FROM 1191 FOR 70) FROM views WHERE viewname like '\\EAIDR.$CAT.SC01IDAT.VPRRI%' browse access; This gave me: (EXPR) ------------------------------------------------------------------------------- CREATE VIEW \\EAIDR.$CAT.SC01IDAT.VPRRI ( CUSTOMER_NO , SALES_ORGANIZAT ION_CD , DISTRIBUTION_CHANNEL_CD , DIVISION_CD , PARTNER_FUNCTION_CD , PARTNER_COUNTER_NO , SOURCE_NM , BUSINESS_PARTNER_NO , PARTNER_CONTAC T_NO , CENTRAL_DELETE_ACTIVE_FL , CENTRAL_DELETE_INACTIVE_FL , ISO_COU NTRY_CD , SOURCE_DT , SYSTEM_UPDATE_DT ) AS SELECT A.CUSTOMER_NO , A.S ALES_ORGANIZATION_CD , A.DISTRIBUTION_CHANNEL_CD , A.DIVISION_CD , A.P ARTNER_FUNCTION_CD , A.PARTNER_COUNTER_NO , A.SOURCE_NM , A.BUSINESS_P ARTNER_NO , A.PARTNER_CONTACT_NO , A.CENTRAL_DELETE_ACTIVE_FL , A.CENT RAL_DELETE_INACTIVE_FL , A.ISO_COUNTRY_CD , A.SOURCE_DT , A.SYSTEM_UPD ATE_DT FROM ( ( \\EAIDR.$CAT.SC01IDAT.CUSTPNFU A LEFT JOIN \\EAIDR.$CAT. SC01IDAT.CUSTOMER B ON A.CUSTOMER_NO = B.CUSTOMER_NO ) LEFT JOIN \\EAID R.$CAT.SC01IDAT.SLARSCCU C ON ( ( ( ( A.CUSTOMER_NO = C.CUSTOMER_NO ) AND ( A.SALES_ORGANIZATION_CD = C.SALES_ORGANIZATION_CD ) ) AND ( A.DI STRIBUTION_CHANNEL_CD = C.DISTRIBUTION_CHANNEL_CD ) ) AND ( A.DIVISION _CD = C.DIVISION_CD ) ) AND ( A.SOURCE_NM = C.SOURCE_NM ) ) WHERE ( ( A.CUSTOMER_NO = B.CUSTOMER_NO ) AND ( ( A.SOURCE_NM = B.SOURCE_NM ) OR ( A.SOURCE_NM = "PGWW79" ) ) ) AND ( ( A.SOURCE_NM = C.SOURCE_NM ) OR ( A.SOURCE_NM = "PGWW79" ) ) --- 1 row(s) selected. NOW I know what the underlying logic is, so I know why the code is dropping records based upon specific conditions. lincoln "Windows XP has so many holes in its security that any reasonable user will conclude it was designed by the same German officer who created the prison compound in "Hogan's Heroes." - Andy Ihnatko, Chicago Sun-Times [link\|mailto:bconnors@ev1.net\|contact me]

Welcome to IWETHEY!