[Info-Ingres] Few questions concerning Ingres 2006

[Roy Hann]

<Marek.Patrzek at gmail.com> wrote in message 
news:1176764468.360683.212290 at y5g2000hsa.googlegroups.com...
> On 10 Kwi, 21:38, c... at usenet.invalid wrote:
>>
>> >INGRES TERMINAL MONITOR Copyright 2007 Ingres Corporation
>> >Ingres 2006 Release 2 Microsoft Windows Version II 9.1.0 (int.w32/119) 
>> >login

[snip]
> And Roy, I have no idea, how did you manage to do that,
> as I always end up with "Table "select" does not exists or is not
> owned by you

As I said earlier, Ingres only recently added support for subqueries in the 
FROM clause of a SELECT statement.  Make sure you are using a version at 
least as recent as the one I used (see above).

> A new issue - a bit off topic. I made a view which had casting
> function (from varchar to integer) in the select statement. Everything
> was fine, until I wanted to join my view with another table using
> table.column which was an integer to some view.column which - in
> theory was an integer too. Again - all I got was an error msg saying
> that I got incompatible data types... I can assure you, that all the
> data in my view was easily casted to integer. The thing is, I than
> created the view WITHOUT casting and than in my join clause I added
> casting function and everything was fine.

I'm reluctant to suggest that might be a bug without first running the 
following query to verify for-sure-for-sure that the data in table.column 
can be converted to an integer (it should return no rows):

SELECT *
FROM table
WHERE squeeze(table.column)
LIKE '%'+'\['+x'00'+'-'+x'2f'+','+x'3a'+'-'+x'7f'+'\]'+'%' ESCAPE '\'

> To sum up - it seems that Ingres omits somehow cast functions and
> execute the select without them, so the question is - why ? Imho,
> values AFTER the cast functions should be returned, or am I wrong ?

You are correct, that is what should happen in SQL.  (QUEL, which Ingres 
also supports, actually treats views a bit differently, but it should still 
work the way you expect.)

I assume you do realize that there is a howling design error in any database 
where you end up comparing integers to strings.  I hope that this was 
someone else's design that you are having to live with.  It would almost 
certainly be better to fix the database design even if you get this query to 
return the required rows without errors.

Roy