>I was wondering why an SQL statement works in a view but not in SPT. I created a view with the following using the view designer and this is the SQL statement that it created. And got the desired result this way.
>
>SELECT Invc.USER_INVC_NO, Seg.USER_SEG_NO, Cust.USER_CUST_NO,;
> Cust.CUST_NAME, Invc.CUST_TYPE_CD, Invc.INVC_STATUS_CD,;
> Invc.INVC_TYPE_CD, Invc.ACCT_DT, Invc.INVC_DT, Invc.CURR_CD,;
> Invc.INVC_DUE_DT, Seg.SEG_NO, Invc.ACCT_PER, Invc.ACCT_YR,;
> Invc.BILL_TO_CUST_NO, Invc.CLOSE_DT, Invc.CURR_RATE, Invc.FRGHT_AMT,;
> Invc.INSUR_AMT, Invc.INVC_NO, Invc.INVC_SRC_CD, Invc.JE_NO,;
> Invc.PMT_TERMS_CD, Invc.PRINT_INVC_STATUS, Invc.RECUR_INVC_NO,;
> Invc.SEG_NO, Invc.SHIP_TO_ADDR_NO, Invc.TOTALS_UPDT_DT_TIME,;
> Invc.UPDT_DT_TIME, Invc.UPDT_PERS_NO, Invc.REMIT_TO_ADDR_NO;
> FROM PROD.INVC Invc, PROD.SEG Seg, PROD.CUST Cust;
> WHERE Seg.SEG_NO = ?the_seg_no;
> AND Seg.SEG_NO = Invc.SEG_NO;
> AND Cust.CUST_NO = Invc.BILL_TO_CUST_NO;
> ORDER BY Invc.USER_INVC_NO
>
>But, when I tried to use it in SPT. I got an error message saying unrecognized phrase/keyword ???
>
>SQLEXEC(nhandle, ' my sql statement ' , 'invc_cursor' )
>
>If I replace all the fields that I want with SELECT * FROM all is ok. Is there a specific way to choose the desired fields ?
>
>Thanks in advance
>Mike McHugh

---

I found this to happen all the time in FP2.6 What I did was break that sucker up into smaller pieces, bind them together and then pass them.

t1=">SELECT Invc.USER_INVC_NO, Seg.USER_SEG_NO, Cust.USER_CUST_NO,;
Cust.CUST_NAME, Invc.CUST_TYPE_CD, Invc.INVC_STATUS_CD,;
Invc.INVC_TYPE_CD, Invc.ACCT_DT, Invc.INVC_DT, Invc.CURR_CD,;
Invc.INVC_DUE_DT, Seg.SEG_NO, Invc.ACCT_PER, Invc.ACCT_YR,;
Invc.BILL_TO_CUST_NO, Invc.CLOSE_DT, Invc.CURR_RATE, Invc.FRGHT_AMT,;
Invc.INSUR_AMT, Invc.INVC_NO, Invc.INVC_SRC_CD, Invc.JE_NO,"

t2= " > Invc.PMT_TERMS_CD, Invc.PRINT_INVC_STATUS, Invc.RECUR_INVC_NO,;
Invc.SEG_NO, Invc.SHIP_TO_ADDR_NO, Invc.TOTALS_UPDT_DT_TIME,;
Invc.UPDT_DT_TIME, Invc.UPDT_PERS_NO, Invc.REMIT_TO_ADDR_NO;
FROM PROD.INVC Invc, PROD.SEG Seg, PROD.CUST Cust"

t3= ' ..... '

sql_t= t1 +t2+t....

nSuccess = SQLEXEC(nhandle, sql_t , 'invc_cursor' )

Depending on the back end the ? used before the view_parameter will get you into trouble. The back end won't come forward and ask for a value now will it?

__Stephen