New post
Question
· Sep 17, 2024

Stored procedure for insert into and select from tables

#SQL #Caché

How to add the following to a stored procedure (Cache Studio)
1. Select from a few tables and insert result into a TABLEA.
2. Then select data from the TABLEA, apply some SQL logic, insert results into TABLEB.
3. SELECT * FROM TableA
    UNION ALL
    SELECT * FROM TableB

Product version: Caché 2012.1
$ZV: Cache for UNIX (Red Hat Enterprise Linux 5 for x86-64) 2010.2.3 (Build 702) Tue Feb 15 2011 14:21:10 EST
Discussion (7)1
Log in or sign up to continue
KEVIN TAYLOR Sep 18, 2024 to Robert Cemper

Hi Robert, the webpage mentions version - InterSystems IRIS Data Platform 2024.2 = (2024)
The version I use is - InterSystems 2010.2.3 (Build 702) = (2010)
None of the examples shows how to use a temp table/normal table to insert into, then do a select from that temp/normal table.

............................................................................

If I try doing below:

  CREATE PROCEDURE PersonStateSP() 
      BEGIN
      SELECT Name,Home_State FROM Sample.Person ;
      END

I get error [Code: 25, SQL State: 37000]  [SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (END) encountered after end of query^Sample.Person       END>]

.........................................................................................

Tried using a class (Cache Studio)
Class ....ReportStoredProcs Extends %RegisteredObject

for stored procedure, but instead of returning 7 rows I only get 3 rows. Seems issue lies with the Fetch section

.......................................................................

0 0
Robert Cemper Sep 18, 2024 to KEVIN TAYLOR

I use it since Caché 2014.* at least.
this just the latest documentation.
What's the purpose of the semicolon ?  SELECT Name,Home_State FROM Sample.Person   ;
for TEMP table see: https://docs.intersystems.com/iris20242/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_createtable#RSQL_createtable_desc_temp

Before collecting all pieces in the Stores Procedure
I'd suggest to test you queries isolated im SMP or from terminal in SQL shell

0 0
KEVIN TAYLOR Sep 18, 2024 to Robert Cemper

It gives same error with/without semicolon.

Queries work 100% from within DBVisualizer.

Seems issue lies with the Fetch section in Class ....ReportStoredProcs Extends %RegisteredObject 
 

The insert works 100%, but below fetch only returns 3 rows instead of 7, if I remove Quit $$$OK , then I get 7 rows, but of course it returns an error

    // Prepare and execute the final query to fetch data from the temporary table
    s rsFetchTempData = ##class(%ResultSet).%New()
    s sc = rsFetchTempData.Prepare("SELECT * FROM TableA")
    if 'sc q sc
    s sc = rsFetchTempData.Execute()
    if 'sc q sc
    s qHandle = rsFetchTempData
    Quit $$$OK


ClassMethod ListPersonsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = ListPersonsExecute ]
{
s sc = qHandle.Next()
    
    if (qHandle.Next())
    {
    // Fetch the current row
     s Row = $LB(qHandle.Get("Col1"), qHandle.Get("Col2"), qHandle.Get("Col3"), qHandle.Get("Col4"), qHandle.Get("Col5"), qHandle.Get("Col6"), qHandle.Get("Col7"))
    }
    else
    {
        s AtEnd = 1
        s Row = ""
    }
Quit $$$OK
}

0 0