New post
Question
Matt Nadolny · 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?

0
0 494
Discussion (11)0
Log in or sign up to continue
John Murray · Jun 21, 2017

In Cache ObjectScript p_Branch isn't a valid identifier, because the underscore character is used as the concatenation operator.

Does your attempt work if you change p_Branch to pBranch ?

If not, please show us your SQL in its complete context.

0 0
Rubens Silva · Jun 22, 2017

Is your class located within the User package? Ex: User.Location?

If you REALLY want to use _ then at least wrap the alias inside double quotes.

 

0 0
Warlin Garcia · Jun 22, 2017

Can you provide more details on what exactly you're trying to accomplish? As you said, the last query works. What exactly is missing? Are you trying to get a prompt (outside of Cache I'm guessing) with the parameter name?

0 0
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
Matt Nadolny · Jun 22, 2017

The class is SQLUser.location

0 0
Matt Nadolny · Jun 22, 2017

I am trying to write a store procedure to create and populate a date table for usage by a report tool. I am using this query to prove out the that I pass prompt value like I do in TSQL or PSQL

0 0
Matt Nadolny · Jun 22, 2017

CREATE function sqlUser.F_TABLE_DATE
(
    ?FIRST_DATE        datetime,
    ?LAST_DATE        datetime
)

Is the beginning of the function

0 0