Running pass through query

[miller1975]

Hello All,
What I am tryin to accomplish is run a DB2 SQL query and save the results to a Access table.
I was suggested to use a pass through query.
What are my options to do a insert in a pass through query.
Please advise.

Thanks,
Miller 1975

 

[PHV]

If you already have the passthru query working the simply create an append (or maketable) query based on it.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

Hello PHV,
My sql query is only doing select.
How do I make it a append table. Is that allowed with pass through..
Note my pass through is in ANSI..and I am running it from Access..which you know uses JET..
How will it work in this situation..
This is sample of the code in ANSI (pass through)

SELECT A.CUSTOMER_NBR, A.CONTRACT_ID, A.COMM_CREATE_USER,
       A.COMM_CREATE_DT, A.COMM_ACTION_CDE
  FROM ACRP1.CONTRACT_COMMENT A
 WHERE A.COMM_CREATE_USER NOT IN ('ZZCTAV','SYSTEM')
   AND A.COMM_ACTION_CDE
       IN ('TPTP','SM','EXP','WCB','CFP','EP','PAM','PRS',
       'LM','LMR','ICS','DISP','SICS','CKLB','APS','TPLC','WLC')

Thanks..

 

[PHV]

Reread my previous post.
I suggested you to create a NEW append query with the passthru query as source and your access table as destination.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

So what you are saying is make a new new blank append query
say APPD_QRY

INSERT INTO TBL_01

and then add the pass through qry to this append qry..
But how will it work..I mean how will access run the ANSI version as a regular append query..

 

[PHV]

INSERT INTO TBL_01 (fields list)
SELECT fields list
FROM yourPassthruQueryName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

Hmm..Ok
If if we are going by the scenario mentioned here..it should look like this

INSERT INTO TBL_01 field1, field2.....
SELECT A.CUSTOMER_NBR, A.CONTRACT_ID, A.COMM_CREATE_USER,
       A.COMM_CREATE_DT, A.COMM_ACTION_CDE
  FROM ACRP1.CONTRACT_COMMENT A
 WHERE A.COMM_CREATE_USER NOT IN ('ZZCTAV','SYSTEM')
   AND A.COMM_ACTION_CDE
       IN ('TPTP','SM','EXP','WCB','CFP','EP','PAM','PRS',
       'LM','LMR','ICS','DISP','SICS','CKLB','APS','TPLC','WLC')

or

INSERT INTO TBL_01 field1, field2.....
SELECT CUSTOMER_NBR, CONTRACT_ID, COMM_CREATE_USER,
       COMM_CREATE_DT, COMM_ACTION_CDE
WHERE COMM_CREATE_USER NOT IN ('ZZCTAV','SYSTEM')
   AND COMM_ACTION_CDE
       IN ('TPTP','SM','EXP','WCB','CFP','EP','PAM','PRS',
       'LM','LMR','ICS','DISP','SICS','CKLB','APS','TPLC','WLC')
FROM yourPassthruQueryName

I am sure missing something here..Please advice

 

[fredericofonseca]

Miller1975,

You need to learn to TRY things.

When you elect to create a new query you have the option of selecting a table, a query or both.

So if you already had a pass through query created this query will show on the list of available queries every time you try and create a new one.


So assuming you already have a passthrough query named myDB2qry, with the following SQL

"select f1, f2, f3 from my DB2_table"

You can now create a "standard" access query as

select f1 from myDB2qry.

And if you can do a select like the above then you can also do a "insert into ... select f1 from myDB2qry".


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[miller1975]

If we use this method
INSERT INTO TBL_01 (fields list)
SELECT fields list
FROM yourPassthruQueryName

then wont we have 2 select statements..I mean the PassthruQueryName also has a select statement right..

 

[miller1975]

Got it..let me try this..
Thanks Frederico..

 

[fredericofonseca]

Miller1975,


You either use linked tables and on that case you can use only one SQL, or you used pass through queries, and on that case you have two SQL's.

With a pass through query you can execute Stored Procedures on the remote server, and you have therefore more options to you, and processing speed may increase greatly.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com