Can you tell, What do i have wrong in this select sql please ?

[titoneon]

Hello everyone,
Need to know what is wrong on the below select ?
I want to be able to display the vendor name in the cursor below, in this case the field for the vendor name is called "company"

oK, I have the vendno in table potran04 but no the company field in that table, so table apvend04 will have vendno field and company field as well, i just want to pull the company value from apvend04 table when potran04.vendno = apvend04.vendno
so what is wrong in the below code, i am getting error "unrecognized verb"

lcjob_no= "119677" 
path_1="S:\PRO50\APEX04\POTRAN04"  &&VFP 5.0 TABLE TYPE
path_2="F:\MFG\ENG_JOBS"           && FOXPRO DOS 2.0 TABLE        
path_3="F:\MFG\INS_LOG"            && FOXPRO DOS 2.0 TABLE  
path_4 ="s:\pro50\apex04\apvend04"


Select INT(VAL(t1.dept)) as ball_no, sht as Sheet, INT(t1.QtyOrd) as QTYORD, INT(t1.Qtyrec) as QTYREC, t4.company, t2.part_type as  PRC, t2.draw_no, t1.item as ItemNo, t1.vpartno as vendorpartno, ;
t1.descrip, t1.Recdate, t1.purno;
 From  &path_1 t1 ; 		 
	INNER Join &path_2 t2;
	INNER JOIN &path_4 t4;
	ON INT(VAL(t2.ball_no)) = INT(VAL(t1.dept));
	ON t1.vendno = t4.vendno;
	 WHERE  t1.reqno= lcJob_no AND t2.job_no=lcJob_no ; 
		ORDER BY 1,2,7 INTO Cursor RESULS1

Thanks in advance

 

[TamarGranor]

Step 1 in figuring this out is to get rid of the macros. Using & when specifying a variable with a path is really risk, because it will fail if there are any embedded spaces in the path. Use parens instead, like (path_1).

Second is what I think is actually your problem. You have the join conditions in the wrong order. You've written this using the nested syntax in which the first ON should match the last JOIN. It's much easier to write and debug if you use the sequential syntax, where every JOIN is immediately followed by its ON.

Apply those two changes, you get:

Select INT(VAL(t1.dept)) as ball_no, sht as Sheet, INT(t1.QtyOrd) as QTYORD, INT(t1.Qtyrec) as QTYREC, t4.company, t2.part_type as  PRC, t2.draw_no, t1.item as ItemNo, t1.vpartno as vendorpartno, ;
t1.descrip, t1.Recdate, t1.purno;
 From  (path_1) t1 ; 		 
	INNER Join (path_2) t2;
	ON INT(VAL(t2.ball_no)) = INT(VAL(t1.dept));
	INNER JOIN (path_4) t4;
	ON t1.vendno = t4.vendno;
	 WHERE  t1.reqno= lcJob_no AND t2.job_no=lcJob_no ; 
		ORDER BY 1,2,7 INTO Cursor RESULS1

If that doesn't do it, let us know.

Tamar

 

[Olaf Doschke]

I think Tamar has spotted it already.

If you want the nested joins, then also nest your code this way:

...
    INNER Join (path_2) [u]t2[/u];
        INNER JOIN (path_4) [i]t4[/i];
        ON INT(VAL([u]t2[/u].ball_no)) = INT(VAL(t1.dept));
    ON t1.vendno = [i]t4[/i].vendno;

This shows you, how your ON conditions are in the wrong order.

Since both join conditions are joinning to t1, you would rather use the sequential syntax, each join should have the nesting on the level of t1, so actually you don't even have the choice to nest the joins, both are from the base t1 level:

...
From (path_1) [b]t1[/b] ; 
    INNER Join (path_2) [u]t2[/u];
    ON INT(VAL([u]t2[/u].ball_no)) = INT(VAL([b]t1[/b].dept)); 
    INNER JOIN (path_4) [i]t4[/i];
    ON [i]t4[/i].vendno == [b]t1[/b].vendno;

Also see, I like to always put fields/expression on fields of the joined table first in the join condition. That's a matter of taste and if you need a partial match (begins with) you may have to put the join condition in the other order.

Your where condition also points out, there is a secondary join condition for t1 and t2, and that should be made a join condition this way:

...
From (path_1) [b]t1[/b] ; 
    INNER Join (path_2) [u]t2[/u];
    ON INT(VAL([u]t2[/u].ball_no)) = INT(VAL([b]t1[/b].dept)) [b]AND t1.reqno=t2.job_no[/b]; 
    INNER JOIN (path_4) [i]t4[/i];
    ON [i]t4[/i].vendno == [b]t1[/b].vendno;

The WHERE condition then only needs the condition t2.job_no = lcJob_No, the condition for t1.reqno = lcJob_No then is already fulfilled via the join condition.

Bye, Olaf.

 

[titoneon]

Tmmar, Olaf
Thanks it works, Thanks for the lesson