Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3912
  • Last Modified:

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

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
DhavalShah0824
Asked:
DhavalShah0824
  • 4
  • 3
1 Solution
 
DhavalShah0824Author Commented:
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
 
pcelbaCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
DhavalShah0824Author Commented:
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
 
pcelbaCommented:
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
 
DhavalShah0824Author Commented:
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
 
DhavalShah0824Author Commented:
Actual sample with the example would have been more helpful.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now