Solved

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

Posted on 2013-12-27
7
3,147 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now