General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Using same table multiple times in remote view
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.
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only