Some 4GL and SQL problems
I have recently switched from developing in Oracle where I have 10
years experience, to a project developing in Informix, where I have now
got about 2 months experience.
I have found a couple of things that I was used to doing in Oracle,
where Informix doesn't work quite the same. I have found work-arounds,
but I would like some advice as to whether there is a better way of
doing things.
1) Handling NULL values in 4GL
Let's say I have a 4GL program where I have a cursor reading rows from
one table, and for each row I want to find if there is a corresponding
row in another table where the joining condition is by a number of
columns. The combination of the columns will always be unique in the
other table, and some of them may be null.
Ignoring the fact that the columns may be null, the program would be
something like
declare my_cursor cursor for select (a,b,c) from table1;
foreach my_cursor into v_t1_a, v_t1_b, v_t1_c
select id into v_t2_id from table2
where a = v_t1_a
and b = v_t1_b
and c = v_t1_c;
...
end foreach;
Now to take account of the fact that the columns might be null, in
Oracle I would have simply rewritten the select as
select id into v_t2_id from table2
where (a = v_t1_a or (a is null and v_t1_a is null))
and (b = v_t1_b or (b is null and v_t1_b is null))
and (c = v_t1_c or (c is null and v_t1_c is null));
but Informix rejects this and says it will only test "is null" against
a simple column by which I assume it means just a database column, and
not a variable.
To get around this I have replaced the select with a series of
statements to dynamically build up the select statement in a char
string with either the test for equality or the test for is null as is
required. This is then prepared, a cursor declared, opened and fetched
from, closed and the statement free'd.
This work around seems extremely messy and I am sure it must waste a
lot of time reparsing and binding the statement. I have considered
trying to prepare all possible versions of the select and then picking
the relevant one so as to avoid all the reparsing. This would be more
efficient but is even more messier, and when there are more columns to
join it would just make the code unreasonably large.
2) Subqueries on multiple columns.
In Oracle you can write a statement like:
select id from table1
where (a, b, c) in (
select a, b, c from table2
);
Informix seems to reject this syntax.
There are many ways to rewrite this type of statement by completely
changing it. Ie either joining the tables in the main select, or using
an exists clause, etc.
Do I have to rewrite it like that, or can Informix really do a subquery
on multiple columns and I just can't find the right syntax.
3) Finding the value of a serial column that you have just inserted.
Fianlly a simple one that I am sure everyone must be familiar with and
everyone except me must know the best answer to.
Oracle has sequences that you select a value from. The select from the
sequence returns you a unique value very quickly. You can then use
this value as a key for subsequent inserts into master and detail
records.
Informix has serial columns, and as far as I can see, you have to
insert your master record and then do another select to see what value
was assigned before you can use the value to put in the detail
records. This select will have to specify enough columns in the where
clause to uniquely identify the row that was just inserted. With large
tables this may take a long time.
I have considered just selecting the max of the serial column, since it
will typically be the primnary key and be indexed, but this may fail if
there are other processes writing to the table and I don't know what
will happen to the value when it eventually exceeds it's maximum value.
Any advice would be much appreciated.
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
neil_smith@my-deja.com wrote:
>
> I have recently switched from developing in Oracle where I have 10
> years experience, to a project developing in Informix, where I have now
> got about 2 months experience.
Welcome. Once you get over trying to do thing Oracle's way you will
enjoy using Informix.
> I have found a couple of things that I was used to doing in Oracle,
> where Informix doesn't work quite the same. I have found work-arounds,
> but I would like some advice as to whether there is a better way of
> doing things.
>
> 1) Handling NULL values in 4GL
[SNIPPED]
lot of time reparsing and binding the statement. I have considered
> trying to prepare all possible versions of the select and then picking
> the relevant one so as to avoid all the reparsing. This would be more
> efficient but is even more messier, and when there are more columns to
> join it would just make the code unreasonably large.
Personally I'd go with the N prepared statements, at least for the most
common cases and build dynamically for the fringe cases.
> 2) Subqueries on multiple columns.
>
> In Oracle you can write a statement like:
>
> select id from table1
> where (a, b, c) in (
> select a, b, c from table2
> );>
> Informix seems to reject this syntax.
>
> There are many ways to rewrite this type of statement by completely
> changing it. Ie either joining the tables in the main select, or using
> an exists clause, etc.
>
> Do I have to rewrite it like that, or can Informix really do a subquery
> on multiple columns and I just can't find the right syntax.
You will have to rewrite the query to adhere to ANSI standards which
prohibits the kind of multi-column sub-query that you want to perform.
The syntax is an Oracle extension to SQL which Informix does not
support. As I have said before Informix was one of the driving forces
behind the original ANSI SQL standard to wrench syntax control away
from IBM and so tends to stick to the standard pretty closely.
Truth is the query you want to perform SHOULD be a join the sub-query
is a correlated sub-query and ALL CORRELATED SUB-QUERIES CAN BE
REWRITTEN AS SIMPLE JOINS! They run faster, if the indexes are
present anyway. Sub-queries result from trying to compose SQL using
a programmers view of the world. However, SQL is a user language and
to formulate efficient clean SQL you need to think like a user not like
a programmer. A user would express the query above as: "Show me all
the rows in table1 that have a record in table2 where the columns a, b,
and c have the same values in the two tables". That reads like a join
to me! But the programmer in us wants to say, instead: "For every row
in table1 see if there is a corresponding row in table2 that has the
matching values of columns a, b, and c". That sounds like a sub-query
now doesn't it?
Bottom line? Clean up your SQL and start making it ANSI compliant,
simply elegant, and efficient. Then I guarantee you never run into a
feature Informix does not have!
> 3) Finding the value of a serial column that you have just inserted.
>
> Fianlly a simple one that I am sure everyone must be familiar with and
> everyone except me must know the best answer to.
>
> Oracle has sequences that you select a value from. The select from the
> sequence returns you a unique value very quickly. You can then use
> this value as a key for subsequent inserts into master and detail
> records.
>
> Informix has serial columns, and as far as I can see, you have to
> insert your master record and then do another select to see what value
> was assigned before you can use the value to put in the detail
> records. This select will have to specify enough columns in the where
> clause to uniquely identify the row that was just inserted. With large
> tables this may take a long time.
>
> I have considered just selecting the max of the serial column, since it
> will typically be the primnary key and be indexed, but this may fail if
> there are other processes writing to the table and I don't know what
> will happen to the value when it eventually exceeds it's maximum value.
OK Here is another Oraclese misunderstanding. Informix returns the
serial number it assigns to the row just inserted in the sqlca
structure that is set after ANY SQL command. The field
sqlca.sqlerrd[1] contains the serial number and you can use it to
insert child records that need to reference it. If you prefer to use
the newer ANSI error handling, GET DIAGNOSTICS, you either still have
to look into the sqlca structure or use the DBINFO function to do it
for you, but that requires a query again (select
DBINFO('sqlca.sqlerrd1') from systables where tabid = 99;) or you canuse it directly in the INSERT statements of the child tables: INSERT
INTO kid_table values (DBINFO( 'sqlca.sqlerrd1' ), 12, 13, "HI" );.
Art S. Kagel
"Art S. Kagel" wrote: > > neil_smith@my-deja.com wrote: > > [SNIP] > structure that is set after ANY SQL command. The field > sqlca.sqlerrd[1] contains the serial number and you can use it to Oops forgot we were talking about 4GL. In 4GL the serial number comes back in sqlca.sqlerrd[2]. Forgot to take off my ESQL/C head and put my 4GL head on. Art S. Kagel
Just a note on what I think is a typing mistake ............ it is sqlca.sqlerrd[2] which contains the serial value Mariusz Art S. Kagel <kagel@bloomberg.net> wrote in article <376807C7.F2CFA83A@bloomberg.net>... > neil_smith@my-deja.com wrote: > > ................ > > OK Here is another Oraclese misunderstanding. Informix returns the > serial number it assigns to the row just inserted in the sqlca > structure that is set after ANY SQL command. The field > sqlca.sqlerrd[1] contains the serial number and you can use it to > ................
Thanks again for a clear, and concise answer.
I have changed my code to use sqlerrd[2], and I have also chosen to
hardcode the common cases of my select against possible null values and
have dynamic generation of the not so common cases as you suggested.
However, I have some queries regarding your statement below:
In article <376807C7.F2CFA83A@bloomberg.net>,
kagel@bloomberg.net wrote:
> Truth is the query you want to perform SHOULD be a join, the
> sub-query is a correlated sub-query and ALL CORRELATED
> SUB-QUERIES CAN BE REWRITTEN AS SIMPLE JOINS! They run faster,
> if the indexes are present anyway.
The query in question was:
>> select id from table1
>> where (a, b, c) in (
>> select a, b, c from table2
>> );
How is this correlated? There is no reference to the tables of the
main query within the subquery. Is a correlated subquery something
different in the Informix world to the Oracle world? As an example of
what I would consider to be a correlated subquery, the statement could
be rewritten as:
select id from table1
where exists (
select 9 from table2
where table2.a = table1.a
and table2.b = table1.b
and table3.c = table1.c
);
Also, I am dubious about your claim that all correlated subqueries can
be rewritten as simple queries. Is there a proof of this somewhere?
The case that springs straight to mind as requiring a correlated
subquery is that of deleting duplicates from a table. For instance,
consider table X with columns a, b, and c. The statememnt would be
delete from X where rowid in (
select rowid from X x_1
where exists (
select 9
from X x_2
where x_2.a = x_1.a
and x_2.b = x_1.b
and x_2.c = x_1.c
group by x_2.a, x_2.b, x_2.c
having count(*) > 1
) and rowid != (
select max(rowid)
from X x_3
where x_3.a = x_1.a
and x_3.b = x_1.b
and x_3.c = x_1.c
)
);
Now, rewrite that without a subquery.
Also, this brings to mind another usefull feature of which Informix is
deficient. Why can you not correlate the table of the delete clause?
In Oracle, the statement above could be:
delete from X x_1
where exists (
select 9
from X x_2
where x_2.a = x_1.a
and x_2.b = x_1.b
and x_2.c = x_1.c
group by x_2.a, x_2.b, x_2.c
having count(*) > 1
) and rowid != (
select max(rowid)
from X x_3
where x_3.a = x_1.a
and x_3.b = x_1.b
and x_3.c = x_1.c
);
On the plus side for Informix, it's performance seems similar if not
better than Oracle, and being able to just simply display stuff from
4GL is so much simpler than the dbms_output rubbish that you get with
PL/SQL. The calling of stired procedures from 4GL could do with some
work though.
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.