[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2013-12-27
7
Medium Priority
?
3,842 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 43

Accepted Solution

by:
pcelba earned 1500 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 43

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 43

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

650 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