New post
Question
· Jun 21, 2017

Parameters

#Analytics #Beginner #SQL #Caché

Hi There

 

New to the cache sql commands looking for how to create a parameter within a sql statement like with MSSQL is @ and Oracle is : 

 

What is cache's?

Discussion (11)0
Log in or sign up to continue
Rubens Silva · Jun 22, 2017

Here is the procedure sample:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

 

ClassMethod CalcAvgScore(firstname As %String,lastname As %String) [sqlproc]
{
  New SQLCODE,%ROWID
  &sql(UPDATE students SET avgscore = 
    (SELECT AVG(sc.score) 
     FROM scores sc, students st
     WHERE sc.student_id=st.student_id 
       AND st.lastname=:lastname
       AND st.firstname=:firstname)
     WHERE students.lastname=:lastname
       AND students.firstname=:firstname)

  IF ($GET(%sqlcontext)'= "") {
    SET %sqlcontext.%SQLCODE = SQLCODE
    SET %sqlcontext.%ROWCOUNT = %ROWCOUNT
  }
  QUIT
}

 

Instead of using SQL to define PROCEDURES, even though you can, it's easier to create one using your own class. Just declare it as [ SqlProc] and it'll be available to use inside SQL. You can use that way to define a SQL function as well.

0 0
Matt Nadolny · Jun 21, 2017

Here is the sql I have attempted

SELECT l.code AS Location_Code,
       l.description AS Location_Name,
       b.code AS Branch_Code,
       b.description AS Branch_Name
FROM LOCATION l,
              branch b ​
WHERE b.code = l.branchcode
  AND b.code = :p_Branch

got and identifier error

0 0
Matt Nadolny · Jun 22, 2017

Here is the response from management studio window

Input (?) encountered after the of the query^

SELECT l.code AS Location_Code,
       l.description AS Location_Name,
       b.code AS Branch_Code,
       b.description AS Branch_Name
FROM LOCATION l,
              branch b ​
?
0 0
Matt Nadolny · Jun 22, 2017

While this works and pops up a screen 

SELECT l.code AS Location_Code,
       l.description AS Location_Name,
       b.code AS Branch_Code,
       b.description AS Branch_Name
FROM LOCATION l,
              branch b 
WHERE b.code = l.branchcode
and b.code=?
order by branch_code, Location_Code

Just want to name prompt so when I create the function it will pass the correct value along

0 0