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?
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.
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
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
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
Are you using Dynamic SQL (%ResultSet, %SQL.Statement)?
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...
Are you using embedded SQL or named queries?
Then it's the same as Oracle. Ex: :parameterName
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.
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.
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?
Here is the procedure sample:
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...
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.
Here is the sql I have attempted
got and identifier error
Here is the response from management studio window
Input (?) encountered after the of the query^
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
The class is SQLUser.location
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
CREATE function sqlUser.F_TABLE_DATE
(
?FIRST_DATE datetime,
?LAST_DATE datetime
)
Is the beginning of the function
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue