Informix 4GL Construct Statement and ANSI Databases 
Author Message
 Informix 4GL Construct Statement and ANSI Databases

ANSI Databases require the table owner's name to be attached to the
table references in sql statements, as in

        SELECT * from "joe".data WHERE id > 100

The use of these table names works fine evereywhere within I4GL
(I'm running version 4.1) except in the CONSTRUCT statement. When
a query by example form is filled in and the Accept key pressed,
CONSTRUCT returns a WHERE clause for a select statement, and includes
the tablename with the data element, as in

        "WHERE data.id > 100"

When appended to the select statement and executed, an error occurs.
Only if I append the owner name to this table name, so that the resulting
query becomes :

        SELECT * from "joe".data WHERE "joe".data.id > 100

Does the query work. I have had to write a subroutine to parse the "Where"
clause and add owner names to table names, but this is very non-portable.
Is there any other way around this problem?
Inquiring minds want to know!
--

----------------------------------------------------------------
Joe Ryburn |  CIM Manager               | Intergraph Corporation
Ext 5639   |  Manufacturing Integration | Huntsville, AL 35894
----------------------------------------------------------------



Sat, 20 Apr 1996 22:10:49 GMT
 Informix 4GL Construct Statement and ANSI Databases

Quote:

>Subject: Informix 4GL Construct Statement and ANSI Databases
>Date: 2 Nov 1993 14:10:49 GMT
>X-Informix-List-Id: <news.4772>

>ANSI Databases require the table owner's name to be attached to the
>table references in sql statements, as in

>    SELECT * from "joe".data WHERE id > 100

>The use of these table names works fine everywhere within I4GL
>(I'm running version 4.1) except in the CONSTRUCT statement. When
>a query by example form is filled in and the Accept key pressed,
>CONSTRUCT returns a WHERE clause for a select statement, and includes
>the tablename with the data element, as in

>    "WHERE data.id > 100"

>When appended to the select statement and executed, an error occurs.
>Only if I append the owner name to this table name, so that the resulting
>query becomes :

>    SELECT * from "joe".data WHERE "joe".data.id > 100

>Does the query work. I have had to write a subroutine to parse the "Where"
>clause and add owner names to table names, but this is very non-portable.
>Is there any other way around this problem? Inquiring minds want to know!

Using 4.11.UC1 RDS on SunOS 4.1.3, I used the CONSTRUCT statement:

        CONSTRUCT wh ON "jl".t1.data_id FROM FORMONLY.a

and having entered =999 in the (type CHAR) field, got back:

        "jl".t1.data_id = "999"

which is correct.  What construct does is uses the names in the ON list
(or its implicit equivalent) as the names of the columns you wish to refer
to.  And it does not check for their existence in the database or anything.

Actually, this is entirely desirable.  In a MODE ANSI database, I strongly
recommend that you use the following paradigm for SELECT statements:

SELECT a.*, b.*
FROM "owner1".table1 a, "owner2".table2 b
WHERE a.col01 = b.col03
  AND ...

And you CONSTRUCT statement should then be:

        CONSTRUCT wh ON a.col01, b.col02, b.col03 FROM s_construct.*

I don't care what you use as the FROM list, but the ON list should use the
table aliases you are going to declare in your SELECT statement.  This
technique (which I use on non-ANSI databases too) cuts down the size of
the SELECT statement, and means that if the owner of a table changes, there
is only the one place in this SELECT that has to be changed.

Yours,



Mon, 22 Apr 1996 00:43:06 GMT
 Informix 4GL Construct Statement and ANSI Databases

->Subject: Informix 4GL Construct Statement and ANSI Databases
->Date: 2 Nov 1993 14:10:49 GMT

->Organization: Intergraph Corporation, Huntsville, AL
->
->ANSI Databases require the table owner's name to be attached to the
->table references in sql statements, as in
->
->   SELECT * from "joe".data WHERE id > 100
->
->The use of these table names works fine evereywhere within I4GL
->(I'm running version 4.1) except in the CONSTRUCT statement. When
->a query by example form is filled in and the Accept key pressed,
->CONSTRUCT returns a WHERE clause for a select statement, and includes
->the tablename with the data element, as in
->
->   "WHERE data.id > 100"
->
->When appended to the select statement and executed, an error occurs.
->Only if I append the owner name to this table name, so that the resulting
->query becomes :
->
->   SELECT * from "joe".data WHERE "joe".data.id > 100
->
->Does the query work. I have had to write a subroutine to parse the "Where"
->clause and add owner names to table names, but this is very non-portable.
->Is there any other way around this problem?
->Inquiring minds want to know!
->--
->----------------------------------------------------------------
->Joe Ryburn |  CIM Manager               | Intergraph Corporation
->Ext 5639   |  Manufacturing Integration | Huntsville, AL 35894
->----------------------------------------------------------------

One approach involves using synonyms:

CREATE SYNONYM data FOR "joe".data

Then reference the file as just 'data'.  If the program is only for your use
(how likely is that?), then this should work.  If it is for general use, this
might not work for you, because there are Informix version specific limitations
on synonyms:  public in some versions, private in others.  Jonathan Leffler

Regards,
Alan                   ___________________________
______________________| R. Alan Popiel            |__________________________
\  Internet:          | Martin Marietta, Tech Ops |                         /

  )Voice:             | Denver, CO 80201-0179 USA |                       (
 /   303-977-9998     |___________________________|  (But you knew that!)  \
/________________________)                     (____________________________\



Mon, 22 Apr 1996 01:48:05 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Informix 4gl "construct" statement broken

2. Informix 4gl - another construct statement bug

3. Informix 4gl - help in construct statements

4. 4GL Q: Popup window from within Construct statement

5. 4GL 4.10 and Interrupting a Construct Statement

6. CONSTRUCT Statement - Any experts in 4GL?

7. Question about Constructs and Prepares in Informix 4GL

8. Question about Constructs and Prepares in Informix 4GL

9. Informix 4gl construct and DBDATE

10. CONSTRUCT/END CONSTRUCT within INPUT statement

11. INFORMIX 4.1: CONSTRUCT Statement


 
Powered by phpBB® Forum Software