Help me to translate this code to be a view in ingres

[zhaoxu710]

Hi all,

I need your help for translate the code below to be a view:

ifnull(
    (select first 1 t_ID from table1 t,view1 v
    where  t.t_ID=v.t_ID
    and t.m=v.m
    and t.n=v.m),
    (select first 1 t_ID from table1 t,view1 v
    where  t.t_ID=null
    and t.m=v.m
    and t.n=v.m),
    (select first 1 t_ID from table1 t,view1 v
    where   t.t_ID=v.t_ID
    and t.m=null
    and t.n=v.m),
    (select first 1 t_ID from table1 t,view1 v
    where   t.t_ID=v.t_ID
    and t.m=v.m
    and t.n=null)
)

Now I want to create a view contain 3 column "col1", "col2" and "col3" to record the results of each "select first 1...".
Is it possible? And how?

And I'm so sad that "select first 1" can't be used in view in Ingres. Is it a bug of Ingres?

Plz help me.

Thx for all of you!

[kasrnk]

Create view v_veiw2
As
Select * From table1 t,view1 v
where ( (t.t_ID=v.t_ID and
t.m=v.m and
t.n=v.m) Or
(t.t_ID=null and
t.m=v.m and
t.n=v.m) Or
(t.t_ID=v.t_ID and
t.m=null and
t.n=v.m) Or
(t.t_ID=v.t_ID and
t.m=v.m and
t.n=null) );
Commit;

[tyler.mcgraw]

NULLS NULLS NULLS... yikes.

Do you have to use all those NULLs? Not saying it won't work however I hate creating views, joins, indexes etc. that key off a NULL. Not wanting to open a big can of worms here re: 'the correct way to use NULL values' however in my past NULL usage as you propose can create problems/unexpected results.

Keep in mind all the aggregate functions are adversely affected by NULLs also your application needs to be able to trap NULLs throughout, how good is your error handling? I think every programmer I know abuses NULLs.

To be 'syntaxtically' <is that a word?> correct a NULL represents the 'absence of a value' and therefore testing for NULL in the 'where clause' isn't correct syntax.

Anyone else have input on the correct usage of NULLs? Come on I know there of plenty of you out there who have abused them. Time to come clean...

Tyler

[rhann]

I have been abusive about NULLs. Does that count?

Just to elaborate slightly on your comment about flagging absence of value; if only it were that simple. SQL treats nulls differently depending on context. In an aggregate they are treated as "does not apply" and are ignored (i.e. SQL arbitrarily assumes you have conflated fact-types in one table). Elsewhere they are treated as "expected value not supplied" and are noted, and prevent a result from being returned. So if you can frame a query both ways you get different answers depending on which formulation you used.

However my main, serious, insurmountable objection to them is that they permit database designers to ignore a load of analysis that they should be doing, leaving the programmers to discover what is really going on, write voluminous code to deal with it, and then we incur the run-time cost of reverse-engineering the real logical model from the physical model every time we touch the data. (Database designers; what a bunch of heroes.)

[tyler.mcgraw]

Haha... abusive "about" NULLs. That's funny.

Yes agree with your comments. My concern is the test for a value being 'equal' to NULL. In my mind you should use the 'IS NULL' predicate to test for a missing value. (or IS NOT NULL as it where) Can't say you could ever have anything 'equal' to NULL- Then again I could be mistaken and 'rejecting your reality for my own'.
:-)

Tyler

[denjo02]

You can't query for a value "=null":

select * from n1 where k = null;
Executing . . .

E_US0836 line 1, Column 'null' not found in any specified table.

This SQL script inserts two identical rows into two identical tables, with one row (in each) having a null specified for one of the columns.

create table n1 (k i4,d varchar(20));\p\g
create table n2 (k i4,d varchar(20));\p\g
insert into n1 (k,d) values (1,'one');\p\g
insert into n1 (d) values ('null');\p\g
insert into n2 (k,d) values (1,'one');\p\g
insert into n2 (d) values ('null');\p\g
select * from n1 where k is null;\p\g
select * from n2 where k is null;\p\g
select * from n1 where k = null;\p\g
select n1.* from n1, n2 where n1.k=n2.k and n1.d=n2.d; \p\g


The last select, joining on the columns which have a null value, only returns the single matching row with a value, despite the two rows in the tables being identical. Even in a join, null does not equal null.

+-------------+--------------------+
|k |d |
+-------------+--------------------+
| 1|one |
+-------------+--------------------+
(1 row)

An equality condition where one value is null is automatically false, no matter whether the other side of the condition is also null.

So zhaoxu710 needs to replace his "=null" with IS NULL - that might help, providing there is no expectation that a=b if both are NULL.

[tyler.mcgraw]

From the 'Official' Ingres documentation-
_______________________________________
Nulls and Comparisons
Because a null is not a value, it cannot be compared to any other value (including another null value). For example, the following WHERE clause evaluates to false if one or both of the columns is null:

where columna = columnb
Similarly, the WHERE clause:

where columna < 10 or columna >= 10
is true for all numeric values of columna, but false if columna is null.
_________________________________
Nulls and Aggregate Functions
If an aggregate function against a column that contains nulls is executed, the function ignores the nulls. This prevents unknown or inapplicable values from affecting the result of the aggregate. For example, if the aggregate function, avg(), is applied to a column that holds the ages of your employees, be sure that any ages that have not been entered in the table are not treated as zeros by the function. This distorts the true average age. If a null is assigned to any missing ages, the aggregate returns a correct result: the average of all known employee ages.

Aggregate functions, except count(), return null for an aggregate that has an argument that evaluates to an empty set. (Count() returns 0 for an empty set.) In the following example, the select returns null, because there are no rows in the table named test.

create table test (col1 integer not null);
select max(col1) as x from test;
In the above example, use the ifnull function to return a zero (0) instead of a null:

select ifnull(max(coll),0) as x from test;
For more information, see ifNull function in the chapter "Elements of SQL Statements."

When specifying a column that contains nulls as a grouping column (that is, in the group by clause) for an aggregate function, nulls in the column are treated as equal for the purposes of grouping. This is the one exception to the rule that nulls are not equal to other nulls. For information about the group by clause, see the chapter "SQL Statements".
_______________

Hope this helps.