query on linked sevrer tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query on linked sevrer tables

Author

Topic

bsheikh
Starting Member

3 Posts

Posted - 2005-12-29 : 12:43:27

i am using a Host intigration server 2004 and then i created a linked server for DB2/AS400 .
i need to run the query.
this query is composed of multiple joins, groups and order based on tables in linked server, and then insert data on local sql 2000 table.
if just start working only on select statement. and then after viewing what we need we can work on inserting .
my query which is working in access 2000 is as follow.

" "......INSERT INTO OPENQUERY(GP,'SELECT PSF1PF.F1LOC, PSF1PF.F1DEPT, Sum(PSF1PF.F1CV) AS SumOfF1CV,

Sum(PSF1PF.F1SV) AS SumOfF1SV, RTSTPF.STNAMS, RTDPPF.DPDPNM, Sum(F1SV-F1CV) AS GP$,

PSF1PF.F1DATE, Sum(PSF1PF.F1QTY) AS SumOfF1QTY

FROM (PSF1PF INNER JOIN RTDPPF ON (PSF1PF.F1DEPT = RTDPPF.DPDEPT) AND

(PSF1PF.F1CO = RTDPPF.DPCO)) INNER JOIN RTSTPF ON (PSF1PF.F1LOC = RTSTPF.STLOC) AND

(PSF1PF.F1CO = RTSTPF.STCO)

GROUP BY PSF1PF.F1LOC, PSF1PF.F1DEPT, RTSTPF.STNAMS, RTDPPF.DPDPNM, PSF1PF.F1DATE

HAVING (((PSF1PF.F1LOC)<>"009" And (PSF1PF.F1LOC)<>"015" AND ((PSF1PF.F1DEPT)<>19 And

(PSF1PF.F1DEPT)<>70 And (PSF1PF.F1DEPT)<>20 And (PSF1PF.F1DEPT)<>21 And

(PSF1PF.F1DEPT)<>31 And (PSF1PF.F1DEPT)<>22 And (PSF1PF.F1DEPT)<>26 And

(PSF1PF.F1DEPT)<>27 And (PSF1PF.F1DEPT)<>23 And (PSF1PF.F1DEPT)<>24) AND

((Sum(PSF1PF.F1CV))<>0) AND ((Sum(PSF1PF.F1SV))<>0) AND ((PSF1PF.F1DATE)>=1051128 And

(PSF1PF.F1DATE)<=1051204))

ORDER BY PSF1PF.F1LOC, PSF1PF.F1DEPT, PSF1PF.F1DATE )') ........"

Regards
Bilal

Kristen
Test

22859 Posts

Posted - 2005-12-29 : 13:57:26

Hi bsheikh, Welcome to SQL Team!

Should it be something like:

INSERT INTO MyTable SELECT * FROM OPENQUERY

??

Kristen

bsheikh
Starting Member

3 Posts

Posted - 2005-12-29 : 14:14:56

yeah u r right but its not picking fields from multiple tables .
meaning if i have multiple tables in linked server. and right now i am not interested in inserting as 1st step is to get data but i am unable to get.
SELECT * FROM OPENQUERY(GP,' SELECT F1LOC, Sum(F1SV) AS SALES ,Sum(F1CV)AS COST FROM RMSPROD.PSF1PF WHERE (F1DATE >= 1051219 AND F1DATE <= 1051225) GROUP BY F1LOC ')the above mentioned query is working but as u can see it is only from 1 table. i need to pick multiple fields from multiple tables with in linked server.

regards
bilal

Kristen
Test

22859 Posts

Posted - 2005-12-29 : 14:25:35

Sorry if completely basic, but the syntax in the OPENQUERY needs to be what will run on the "GP" server, which I understand to be DB2/AS400 - does that support the JOIN syntax?

Can you try running the guts of the query directly on that Server / Database and checking that it works OK?

(But I would have expected you to get some sort of syntax error, rather than "no data"

)

Kristen

bsheikh
Starting Member

3 Posts

Posted - 2005-12-29 : 14:38:46

basically i have all that query working fine in access 2000 and linked table. i need to upgrade that sys into sql 2000
and obviously i need to complete ly transform the queries in access to sql 2000
here is the query working in access 2000

" SELECT RMSPROD_PSF1PF.F1LOC, RMSPROD_PSF1PF.F1DEPT, Sum(RMSPROD_PSF1PF.F1CV) AS SumOfF1CV, Sum(RMSPROD_PSF1PF.F1SV) AS SumOfF1SV, RMSPROD_RTSTPF.STNAMS, RMSPROD_RTDPPF.DPDPNM, Sum([F1SV]-[F1CV]) AS [GP$], RMSPROD_PSF1PF.F1DATE, Sum(RMSPROD_PSF1PF.F1QTY) AS SumOfF1QTY INTO Sales
FROM (RMSPROD_PSF1PF INNER JOIN RMSPROD_RTDPPF ON (RMSPROD_PSF1PF.F1DEPT = RMSPROD_RTDPPF.DPDEPT) AND (RMSPROD_PSF1PF.F1CO = RMSPROD_RTDPPF.DPCO)) INNER JOIN RMSPROD_RTSTPF ON (RMSPROD_PSF1PF.F1LOC = RMSPROD_RTSTPF.STLOC) AND (RMSPROD_PSF1PF.F1CO = RMSPROD_RTSTPF.STCO)
GROUP BY RMSPROD_PSF1PF.F1LOC, RMSPROD_PSF1PF.F1DEPT, RMSPROD_RTSTPF.STNAMS, RMSPROD_RTDPPF.DPDPNM, RMSPROD_PSF1PF.F1DATE
HAVING (((RMSPROD_PSF1PF.F1LOC)<>"009" And (RMSPROD_PSF1PF.F1LOC)<>"015") AND ((RMSPROD_PSF1PF.F1DEPT)<>19 And (RMSPROD_PSF1PF.F1DEPT)<>70 And (RMSPROD_PSF1PF.F1DEPT)<>20 And (RMSPROD_PSF1PF.F1DEPT)<>21 And (RMSPROD_PSF1PF.F1DEPT)<>31 And (RMSPROD_PSF1PF.F1DEPT)<>22 And (RMSPROD_PSF1PF.F1DEPT)<>26 And (RMSPROD_PSF1PF.F1DEPT)<>27 And (RMSPROD_PSF1PF.F1DEPT)<>23 And (RMSPROD_PSF1PF.F1DEPT)<>24) AND ((Sum(RMSPROD_PSF1PF.F1CV))<>0) AND ((Sum(RMSPROD_PSF1PF.F1SV))<>0) AND ((RMSPROD_PSF1PF.F1DATE)>=[SDate] And (RMSPROD_PSF1PF.F1DATE)<=[EDate]))
ORDER BY RMSPROD_PSF1PF.F1LOC, RMSPROD_PSF1PF.F1DEPT, RMSPROD_PSF1PF.F1DATE;

i need the same thing in sql 2000.

regards
bilal

Kristen
Test

22859 Posts

Posted - 2005-12-30 : 13:58:41

Yup, I saw your bit about Access and assumed you had it working there.

I don't know diddly squat about Access, but my understanding is that there are two types of query - an "Access style" one and a "Pass through" one.

If you are using an Access Style one then it will have to be translated into native AS400/DB2 speak before it will work with OPENQUERY. If it is already a Pass Through query then I reckon that is already in native AS400/DB2 speak, so should be OK.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources