Problems With Nodenum( )

[logic4fun]

Hi all,
I am trying to execute a query like the following and it throws me a SQL901 error which is severe system error and doesnt let me proceed..any suggestions..please...
*********************************************
Select a.A,a.B
from
Table1 a
inner join Table2 b ON
a.x = b.x
nodenumber(b.blah) = current node
where a.time between <start> and <end>
nodenumber(a.blah) = current node;
*********************************************

It throws me an error when i use &quot;nodenumber&quot; in the Join and it works fine when i use &quot;nodenumber&quot; in Where clause;
ANY Suggestions Please..
thanks in advance
Logic4fun

 

[MarcLodge]

Hi Logic,
I think you need to add an AND so that it reads:
**************************************************
Select a.A,a.B
from
Table1 a
inner join Table2 b ON
a.x = b.x
AND nodenumber(b.blah) = current node
where a.time between <start> and <end>
AND nodenumber(a.blah) = current node;
***************************************************

Although admittedly I've not tested this out. Hope it works!
Marc

 

[logic4fun]

Hi Marclodge,
thanks for responding my question, but it was a typo in my question..i have actually used AND in the query..it was an error when i am Using AND.

thanks
logic

 

[MarcLodge]

Hi Logic,
I've tried to find out a bit of info on the Nodenumber function and have drawn mainly blanks. I've come across a couple of articles that seem to imply that an export to a temporary table takes place when this is specified, but I can't find anything in depth to confirm this.

It is possible that you could rewrite the sql as such:
Select a.A,a.B
from
Table1 a
,table2 b
where a.time between <start> and <end>
and nodenumber(a.blah) = current node
and a.x = b.x
and nodenumber(b.blah) = current node
;

Marc

 

[pkganapathi]

I agree with Marc's query above.
One does not really need an ON clause for Inner joins, it might as well as be rewritten in a Table A, Table B format.

However, when it comes to LEFT or RIGHT Joins, this may be a problem with Node Numbers.

-PK

I.T.Analyst
Tata Consultancy Services