Left Join problem.

[CRANSTON]

I have a detail table that contains line items for sales orders. I also have a current line items cursor (same structure) that contains 1 or more line items for the current order being processed. The current line items could also exist in the line items table. My problem is this. I need to exclude any rows that exist in both the table and the cursor for some balance calculations. I can't use the construct NOT IN (Select ... from the cursor records) in my SQL statement due to design rules of the accounting package
(They have their own SQL processing objects which allow the package to be used with a VFP or SQL backend). I have tried using a right outer join with the exclusion table on right hand side and it will work correctly with 1 record in the exclusion table. More than one record, it doesn't work.

 

[alvechurchdata]

it doesn't work

What happens? No records selected? Blue screen of death? Could you give us some more symptoms.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Cranston,

Why exactly can't you use NOT IN (SELECT .... ) ?

You mentioned "design rules in the accounting package". How exactly do they limit the syntax of your SQL?

You can see why I'm asking this. If these "design rules" don't allow a subquery, what else do they disallow? If someone here comes up with a some code, how will they know whether that code will be permitted under your regime?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[CRANSTON]

The query fails by not excluding the records in the current items cursor.

SELECT ;
    * ;
  FROM ;
    sllines a1 ;
  WHERE ;
    INLIST(a1.doctype, "SO", "SR" ;
  INTO 
    TABLE coretest

SELECT ;
    a1.doctype, ;
    a1.doctype, ;
    a1.linenum, ;
    a1.item  ;
  FROM ;
    coretest a1 ;
    RIGHT OUTER JOIN lineitemt l1 ;
      ON a1.docid <> li.docid  ;
  INTO ;
    CURSOR junk READWRITE

The above code works if there is only 1 record in lineitemt.
I can change the join codition to equal, it includes every left hand record that matches the right hand side. If lineitemt has more than one record, the equal join condition still works and the unequal condition includes all records from the left hand side. I have also tried using multiple fields on the join conditon and still get the same results.

 

[Mike Lewis]

Cranston,

I still think using NOT IN (SELECT .... ) is the easiest way to achieve this.

However, if you prefer to use an outer join, you should include a test for NOT ISNULL() on the appropriate field; this will exclude the records in the curent cursor.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[CRANSTON]

About the design rules: The accounting product is Alere 4.2 from TIW.

1. In/not in/exists/not exists for the WHERE clause are not supported in Client Server where a table and cursor are joined.
2. Memory variables must be proceded by a quesion mark and must be private, they can't be local.
3. It would appear that TIW's rules follow what is legal for MS SQL and exclude the VFP extensions.

 

[CRANSTON]

Thank you for the help. See attached for the solution. The last select could be deleted and just use l1docid field as a flag field.

PROCEDURE coretest

SET DELETED OFF

SELECT ;
    Sllines.descrip, ;
    Sllines.originli, ;
    Sllines.doctype, ;
    Sllines.docid, ;
    Sllines.item, ;
    Sllines.linenum, ;
    Sllines.delmark ;
 FROM ;
     sllines ;
 WHERE  ; 
   INLIST(sllines.doctype, "SO", "SR") ;
 ORDER BY ;
   Sllines.docid ;
 INTO ;
   TABLE coretest

* Select a flag field.
SELECT ;
    a1.docid,   ;
    a1.doctype, ;
    a1.linenum, ;
    a1.item, ;
    l1.docid AS l1docid ;
  FROM   ;
    coretest a1 ;
    LEFT OUTER JOIN lineitemt l1 ;
      ON  (A1.docid = l1.docid) AND (a1.doctype = l1.doctype) AND (a1.linenum = l1.linenum) ;
  INTO ;
    CURSOR c_junk readwrite

* Select all but flagged records.
SELECT ;
    a1.* ;
  FROM   ;
    c_junk a1;
  WHERE    ;
    ISNULL( a1.l1docid) ;
  INTO ;
    CURSOR c_coreout
               
INDEX ON docid + doctype TAG idtype
BROWSE

 

[Stella740pl]

Mike,

I still think using NOT IN (SELECT .... ) is the easiest way to achieve this.
However, if you prefer to use an outer join...

Your post reminded me that recently I went through an older program and, besides other things, replaced JOINs in there (mostly INNER) with subqueries like <NOT> IN (SELECT ...) where applicable.

I haven't tested it yet for performance, but made the changes because it seemed simpler and more readable. I expect it to be faster, too, but will test only probably next week or so.
Do you think it's a right thing to do where outcome is the same?

 

[Mike Lewis]

Stella,

I generally prefer subqueries rather than complex joins because I find them easier to write and read. I've tried to do performance tests, but I've never been able to get any firm results.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

Thanks, Mike.
Will tell you if I get something new from the testing.

 

[TamarGranor]

Cranston:

The accounting product is Alere 4.2 from TIW.

I was talking to one of the developers of Alere last night at our user group meeting. He recommended you talk to their tech support to try to solve your problem.

Tamar