RE: [sqr-users] brackets in where clause when using outer join

[Bob Stone <bstone@fastenal.com>]

I recently had a problem that was only because there were dynamic variables
causing the problem (mine was in a nested select).  The not-so-intuitive (or
logical) work around is to create a variable '' or ' ', and stick it in as a
dynamic variable right after the word 'from'...that should signal to the SQR
compiler that it needs to wait until run time to parse that one.

let $necessary = ' '

begin-select 
  ...

FROM [$necessary] table1, table2, OUTER table3
WHERE table1.ID LIKE 'A%' AND 
table2.PARENT_ID = table1.ID AND
table3.Y = table1.X AND
[$date_clause]
end-select

I'm not 100% positive this will fix your problem, since I haven't used
Informix and I stay far away from all Cartesian joins.  (which, btw, might
be another problem to check on...does it have to be cartesian and
reeeeeeally slow?).  But it helped me fix my SQR parsing problem.

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Michael Pascual
Sent: Thursday, May 12, 2005 11:09 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] brackets in where clause when using outer join


This is a problem I saw someone post before, but no one posted a
solution. The problem is when I use an outer join and try to include a
clause that is dynamically created by using brackets.

I am using:
Informix 7
SQR 6.1.3
Windows XP pro

Eg.
FROM table1, table2, OUTER table3
WHERE table1.ID LIKE 'A%' AND 
table2.PARENT_ID = table1.ID AND
table3.Y = table1.X AND
[$date_clause]

I get the following error:

(SQR 5528) INFORMIX SQL PREPARE/DECLARE error -395 (ISAM: 0) in cursor
4:
   The where clause contains an outer Cartesian Product.
... the sql statement ...
Error on line 283:
   (SQR 3716) Error in SQL statement.
Errors were found in the program file.
SQR: Program Aborting.

When I remove the dynamic clause, it works. This looks like a bug in
sqr in analyzing the sqr code. If I replace the bracketed variable with
the contents of the variable it works. If I use brackets and set the
variable to an empty string, it still fails.

Is there some kind of work around to this problem? Is there another way
to include dynamic clauses in the WHERE and have it work with outer
joins?

Thanks,

Mike



                
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - Find what you need with new enhanced search. 
http://info.mail.yahoo.com/mail_250

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users