Hi all,

I'm trying to run a remote view from an SQL Server database, using the same table more than once. When I use the View Designer, the query works fine. When I try to run the query in a program, it doesn't like the same table used twice. How is the language different when I want to multi-use a table in the same remote view query?


View Designer SQL

SELECT Card.ENTP_CARD_NBR, Cl.CL_VAL, Ip.ENTP_IP_CODE, Ar.ENTP_AR_CODE,;
Cl_a.NM, PD.ENTP_Pd_CODE;
FROM dbo.CARD Card, dbo.CL Cl, dbo.IP Ip, dbo.AR Ar, dbo.AR_HIST Ar_hist,;
dbo.CL Cl_a, dbo.PD Pd;
WHERE Cl.CL_ID = Card.CARD_TP_ID;
AND Card.CST_ID = Ip.IP_ID;
AND Card.PRIM_AR_CODE = Ar.AR_ID;
AND Ar.AR_ID = Ar_hist.AR_ID;
AND Ar_hist.AR_LCS_ID = Cl_a.CL_ID;
AND Ar.PD_ID = Pd.PD_ID;
AND Pd.ENTP_PD_CODE IN ('L55','L57','L60','L61','L65','L66')


Program SQL

password = "market"
store sqlconnect('Data Warehouse','dawsonr',password) to dw
= sqlexec(dw, "SELECT A.ENTP_CARD_NBR, B.CL_VAL, C.ENTP_IP_CODE, D.ENTP_AR_CODE, G.NM, F.ENTP_PD_CODE ;
FROM CARD A, CL B, IP C, AR D, AR_HIST E, PD F, CL_A G ;
WHERE A.CARD_TP_ID = B.CL_ID ;
AND A.CST_ID = C.IP_ID ;
AND A.PRIM_AR_CODE = D.AR_ID ;
AND D.AR_ID = E.AR_ID ;
AND D.PD_ID = F.PD_ID ;
AND E.AR_LCS_ID = G.CL_ID ;
AND INLIST(G.ENTP_PD_CODE,'L55','L57','L60','L61','L65','L66') ", 'result')
= SQLDISCONNECT(dw)

Thanks very much for any help.