asked on
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
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
Databases
Last Comment
DhavalShah0824
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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?
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?
ASKER
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.
, 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.
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.
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.
ASKER
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.
"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.
ASKER
Actual sample with the example would have been more helpful.
However, direct communication with ibm suport tells me that there might be the issues.
Once get more info, will submit here.