Solved

NETEZZA - anyone have  a sample how to return a table from a sp ?

Posted on 2013-12-27
7
3,328 Views
Last Modified: 2014-01-04
Hi

We are migrating certain database and SPs to netezza.
We have an sp in Sql server 2008 which does a join between 2 tables and returns the join.

How can i write this in netezza as an sp.

As far as i read the doc, it says that it can return only an existing table. does it mean i have to create a permenant table and then insert into that table and then return ?

any sample which shows how to create an sp and return an ondemand query results from join of 2 tables would be useful .

            SELECT
                  P.POSITIONID,
                  S.VALUEID,
                  S.LEGID,
                  S.CURRENCY, --NULL AS CURRENCY,
                  S.CURVEID,
                  S.MATURITYPERIOD,
                  S.RESULTVALUE2 AS RESULTVALUE,
                  S.RESULT
            FROM
                  SIMRESULTBASE_D  S ,
                  POSITIONS_D P
            WHERE    S.SIMID  = pSIMID
                  AND  P.SIMULATIONID  = pSIMID
                  AND  P.SIMULATIONID  = S.SIMID
                  AND  P.POSITIONUID  = S.POSITIONUID
                  AND  P.POSITIONID >= pSTARTPID
                  AND  P.POSITIONID <= pENDPPID
                  AND  S.VALUEID  = pRESULTID
                  AND  LENGTH(S.CURRENCY) > 0


Dhaval
0
Comment
Question by:DhavalShah0824
  • 4
  • 3
7 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 39742810
0
 

Author Comment

by:DhavalShah0824
ID: 39744983
thank you.  i got the guide from the IBM site. will follow .
However,  direct communication with ibm suport tells me that  there might be the issues.

Once get more info, will submit here.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39745032
It should be relatively easy - Netezza SP can return a result set based on a table so you just have to create the (empty) table prior to the SP creation and populate it in the SP. The question then is if you really need to return this table as a SP result set... You can simply work on the table created by SP which returns just one scalar indicating the SP success...

I would guess IBM support knows nothing about SQL and stored procedures. To get experienced programmer on the support contact to IBM is close to the Utopia...

BTW, what could be the reason to develop SQL language which does not support tables returned by SPs? (Or which allows it with issues...)  Is IBM so isolated from the rest of the world?
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:DhavalShah0824
ID: 39745329
i got the same doc from ibm support team. It mentioned that it is possible. However, it is not clear from the  doc, if you have to create a table first and then insert the rows into that table first  (your sql query  results would go into this table )

, what would happen if you happen to run the sp at the same time fro 5 different users ( logged in via web ) and trying to execute a report with 5 diff set of parameters being passed in each execution of the sp.

this would mingle the results ?

it is not clear. still awaiting an answer from ibm  support team

how ever doc says that internally it will create a temporary table but it is really not clear.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39745761
The table must exist when you create the SP for sure. The necessity of its existence when you call the procedure is questionable...

Concurrent users should create/use temporary tables associated to the current SQL session. Your SELECT command should use syntax SELECT ... INTO TEMP.  And yes, this could be the issue for SP... but it should not be so difficult to test it.
0
 

Author Comment

by:DhavalShah0824
ID: 39756715
from ibm tech support,

"REFTABLE is only used as a schema definition for the result set.  Each invocation of a stored procedure by a user/connection creates it's own copy of the "REFTABLE" for that specific invocation of the stored procedure.  There can be no concurrency issues based on this implementation. The functionality works described in the sp document guide"


I have not tested the functionlaity yet but will be doing soon.
once i have the answer will post it. however based on this , i think it should work wthout any issues.
0
 

Author Closing Comment

by:DhavalShah0824
ID: 39756720
Actual sample with the example would have been more helpful.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question