So where do you hide

Post #157,063 by lincoln 5/26/04 2:21:39 PM Reply	So where do you hide the file containing the original CREATE VIEW code, so that anybody can see what you're SELECTing, FROM what table(s), and what conditions are in the WHERE clause? Or do databases like Oracle and DB2 allow a complete display of the view somehow? Even though I know where the files containing the views are, an unfortunate reality of NonStop SQL is that they can't be opened in an editor or copyed for displaying. Inside the SQL, I can do an INVOKE, but it only gives me the columns being selected - no WHERE clause logic whatsoever. So what's so hard about having a complete SELECT statement within a string buffer within the program so that people besides that author can see what's going on? Oh, right -- job security. 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]
Post #157,065 by ChrisR 5/26/04 2:41:24 PM 5/26/04 2:44:30 PM Reply	The view itself is defined in the database if you have god rights to the database, reconstructing the views should be but a query away. Unfortunately, you have to trundle through the Oracle docs to find the right tables/views for reconstruction. Seems like there's some freeware to do that - probably from the Toad people. [edit doh: for that matter doesn't Toad just let you view the View definitions directly? Realizes that i is missing some piece of the puzzle] Edited by ChrisR May 26, 2004, 02:44:30 PM EDT Expand All History
Post #157,087 by admin 5/26/04 4:37:53 PM Reply	Re: So where do you hide Our views are defined in .sql files and loaded by the build system. Or, with Oracle at least, TOAD or TOra will show you the view definition. Dunno about NonStop. Seems pretty silly that you can't get the full definition. So what's so hard about having a complete SELECT statement within a string buffer within the program so that people besides that author can see what's going on? 1) A view is precompiled. 2) A view can have different permissions than the base table. 3) A view can be used with the same built-in logic by more than one program. No, it isn't "job security". Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #157,178 by lincoln 5/27/04 9:13:20 AM Reply	Re: So where do you hide Dunno about NonStop. Seems pretty silly that you can't get the full definition. and 3) A view can be used with the same built-in logic by more than one program. Totally agree with both points. 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]
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!