Tom,
Just a thought... I have the impression that users of Bizman can't
make schema changes. If that's true, then you could project a
permanent table for your sifter to reference, and just replace it as a
part of your updates that include schema changes.
And what does "tautological" mean anyway? <g>
Ben Petersen
On 3 May 2001, at 11:49, Tom Grimshaw wrote:
> G'day,
>
> I started writing this up and in doing so solved my problem. So thanks
> for being there! I still have a couple of questions remaining so if anyone
> knows one or more of the answers I would be pleased to hear.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> I have developed a you-beaut SuperSifter (trade mark pending) module that
> allows a user to select a table, then with a CHOOSE a column in that table
> by which to select records then a Where builder type form (but with
> explanations and samples) and the code creates a multi-table SQL SELECT
> statement from the menu options.
>
> Trouble is, (I assumed) because there are several views based on variables
> that are not always set at start-up the routine crashes on Win95 (works fine
> on NT).
>
> As per Bill Downall's recommendation all views using var have the var
> enclosed in parentheses.
>
> "No problem!" I hear someone say, "Declare your var prior to running the
> routine so there is no problem at the engine level."
>
> Tried that. Setting the var required by the views causes the results of the
> CHOOSE to take 14.5 seconds longer to display than with the var unset.
> Anyone know why? This is the CHOOSE:
>
> CHOOSE vColName +
> FROM #VALUES +
> FOR (LJS(sys_comment,50) & LJS(sys_column_name,20)),sys_column_name +
> FROM SYS_COLUMNS +
> WHERE sys_table_id = .vSysTableID +
> ORDER BY sys_comment +
> AT 12,10 +
> TITLE 'Column Descriptions and Names' +
> CAPTION 'Click a column to sift on or press [Esc] when done' +
> LINES 20 +
> FORMATTED
>
> I have tried Dennis McGrath's suggestion of basing the CHOOSE on a table
> projected from SYS_COLUMNS but the PROJECT takes 14.5 seconds too
> with the var set! That is unacceptable even as part of a start-up routine.
>
> One potential workaround I tried is to delete the 11 views based on var
> prior to calling the SuperSifter then recreating those views after the
> CHOOSE command has executed.
>
> This cut the wait time down to 6 seconds but I still get the message:
> -ERROR- Unrecognized global variable in expression. (2161)
> with no views in existence based on var.
>
> I do not know why I would get this error message after the following code:
>
> SELECT SYS_TYPE_NAME INTO +
> vDataType IND vi1 +
> FROM sys_columns +
> WHERE sys_table_id = .vSysTableID +
> AND sys_column_name = .vColName
>
> when both var have values.
>
> On writing this I relooked at the code and noticed the tautological nature
> of the SELECT. Because R:Base enforces referential integrity at the
> engine level then for each column name there can only be one data type.
> So I changed the above to:
>
> SELECT SYS_TYPE_NAME INTO +
> vDataType IND vi1 +
> FROM sys_columns +
> WHERE sys_column_name = .vColName +
> AND LIMIT=1
>
> and ceased getting the error message re vSysTableID AND CEASED
> EXPERIENCING THE CRASHES!
>
> I would be pleased to learn why I would get an error message number 2161
> saying that there was an unrecognized global variable in the expression when
> there wasn't.
>
> CONCLUSION
> Dennis McGrath's suggestion of PROJECTing system tables and using the
> projected table in lieu of the system table would appear to resolve a bug in
> using variables with the system tables.
>
> Warmest regards,
>
>
> Tom Grimshaw
> coy: Just For You Software
> tel: 61 (0)2 9552 3311
> fax: 61 (0)2 9566 2164
> email: [EMAIL PROTECTED]
> mobile: 0414 675 903
> street: 3/66 Wentworth Park Rd Glebe NSW 2037
> post: PO Box 470 Glebe NSW 2037 Australia
>
>
