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.
| Author |
Topic |
|
krausr79
Starting Member
19 Posts |
 Posted - 2012-07-11 : 14:14:57
|
| I've run into a situation that could probably use some kind of temporary table: I am running several joins on the subset of the union of two tables. I figure if I find the subset and union just once instead of many times then run joins on that it will be better.Based on what I'm reading onling, I need to create a stored procedure on the server in order to create a temporary table. I'm working in excel vba like this:Set ISeriesDataCN = New ADODB.ConnectionISeriesDataCN.Open "Provider=IBMDA400;Data Source=[censored]; Force Translate=0", "", ""Set ISeriesDataCM = New ADODB.CommandISeriesDataCM.ActiveConnection = ISeriesDataCNcmdtext = "sql stuff"Set objRecordSet = New ADODB.RecordsetSet objRecordSet = ISeriesDataCN.Execute(cmdtext, varParms, adCmdText)Range("a6").CopyFromRecordset objRecordSetHow do I create a stored procedure on the server?How do I make a temporary table (No need to save it)?How do I call and get the results of the sql? |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-07-11 : 14:25:44
|
| according to your example:cmdtext = "CREATE view SCRIPT HERE"Anyways, I don't think you need to create a procedure in order to create a view. you can simply create your view.--------------------------Get rich or die trying-------------------------- |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-11 : 14:36:29
|
| For future reference, SQLTeam is a Microsoft SQL Server site, so Excel and DB2 questions are not our forte.That being said, you could write a pure SQL statement to do all the UNIONs and execute that from Excel VBA, but if you need to make it a stored procedure you're better off writing it with the iSeries query tool. Once you've written it and created it on the server then you simply execute it using the procedure name (i.e. "sql stuff" becomes "MyStoredProcedure"). The rest of the code you have should work, and CopyFromRecordSet should do exactly as it says. |
|
|
|
krausr79
Starting Member
19 Posts |
Posted - 2012-07-11 : 14:54:39
|
'View' does seem to be what I want, but when I try to use it it gives me the error [myusername] in QSYS *LIB not found... quote:
For future reference, SQLTeam is a Microsoft SQL Server site, so Excel and DB2 questions are not our forte.
Ah! ok. |
|
|
|
|
|
|
|
|
How to create and use stored procedures from excel