Linking Tables Problem

[JCook23]

CR10.0
SqlServer

Can anyone tell me how I would do this link within Crystal?

SELECT SHITEM.Fpartno,SHITEM.Frev,SHMAST.Fcbcompany,SHMAST.Fcsono,;
SHITEM.Fenumber,SOITEM.Fquantity,SHITEM.Fshipqty,SOITEM.Fduedate,;
SHMAST.Fshipdate,SHITEM.Fshipno;
FROM M2MDATA!SOITEM SOITEM INNER JOIN (M2MDATA!SHITEM SHITEM INNER JOIN M2MDATA!SHMAST SHMAST ON SHITEM.FSHIPNO = SHMAST.FSHIPNO);
ON SOITEM.FSONO = LEFT(SHITEM.FSOKEY,6) AND SOITEM.FINUMBER = PADL(SUBSTR(SHITEM.FSOKEY,7,3),3,'0');
ORDER BY SHITEM.Fpartno, SHMAST.Fcsono, SHITEM.Fenumber, SOITEM.Fduedate, SHMAST.Fshipdate;
INTO CURSOR ZPSHOT

I hope this helps. The problem is the link from SOITEM table to the SHITEM table. Any help would be great!

Jeff

 

[synapsevampire]

SHITEM? Great name...

After establishing your connection, rather than selecting tables/views, use the Add Command function and paste in your SQL, one of the big pluses to CR 9/10 (don't use a cursor):

SELECT SHITEM.Fpartno,SHITEM.Frev,SHMAST.Fcbcompany,SHMAST.Fcsono,
SHITEM.Fenumber,SOITEM.Fquantity,SHITEM.Fshipqty,SOITEM.Fduedate,
SHMAST.Fshipdate,SHITEM.Fshipno
FROM
M2MDATA!SOITEM SOITEM INNER JOIN (M2MDATA!SHITEM SHITEM INNER JOIN M2MDATA!SHMAST SHMAST ON SHITEM.FSHIPNO = SHMAST.FSHIPNO)
ON SOITEM.FSONO = LEFT(SHITEM.FSOKEY,6) AND SOITEM.FINUMBER = PADL(SUBSTR(SHITEM.FSOKEY,7,3),3,'0')
ORDER BY SHITEM.Fpartno, SHMAST.Fcsono, SHITEM.Fenumber, SOITEM.Fduedate, SHMAST.Fshipdate

If any of the columns referenced are parameters you can generate parameters there as well.

-k

 

[JCook23]

k,

I had no idea this could be done. My only other question is what would be the proper syntax in Crystal for this sql statement? I am getting this error:

Line 5: Incorrect syntax near '!'

Thanks so much.....

 

[JCook23]

Actually, I got that corrected (the ! problem).

However, it is saying 'SUBSTR' is not a recognized function name.