Hi, I'm trying to reference the Session Id in an Attribute Lookup Query to bring back the current 'user' and 'organisation'...I'm using $Session.Id for the variable but it's not recognizing it. Any ideas? Am I wasting my time?
Thanks
Paul
Paul,
Have you tried $Session.Person.Id for Person? Session.Id is for session not for the logged on person's id.
Manav,
Okay...still getting a problem....this is the Attribute Lookup Query...
SELECT dbo.documents.prefix + ' - ' + dbo.documents.title AS value, dbo.org_persons.person_id, dbo.organisations.organisation_code FROM dbo.documents INNER JOIN dbo.organisations ON Substring(dbo.documents.prefix, 8, 3) = dbo.organisations.organisation_code INNER JOIN dbo.char_data ON dbo.documents.document_id = dbo.char_data.object_id INNER JOIN dbo.org_persons ON dbo.organisations.organisation_id = dbo.org_persons.organisation_id WHERE ( dbo.char_data.char_id IN ( 9, 11, 17 ) ) AND ( dbo.char_data.char_value = 'Inspection and Test Plan' ) AND ( dbo.documents.current_revision = 'y' ) AND ( dbo.org_persons.person_id = $Session.Person.Id ) AND ( dbo.documents.template = 'n' )
When we Test Query we get Invalid pseudocolumn "$Session"
Any ideas?
Paul,You are running query on Database or using eQL query builder as above query is SQL and I was thinking you are running eQL query for Attribute Lookup?
Hi Manav,
I'm building the query in SQL Server. I can see what I'm doing there and grab the correct tables. Then I'm transferring the code to the eQL panel. I thought I was using eQL accepted code though? Do you think not?
In SQL Server, if I substitute the session.person.id for a known person Id and then run the code, it works fine....and if I substitute the variable for the same know person id in the eQL panel it works fine too.
I've dug through some other queries that we have in eB and on Attribute Queries and they seem to recognise the $Session.Persion.id.......it's just my attempt that is failing.
Thanks for your time on this...it's much appreciated.
The code you are using is with SQL syntax and eQL syntax is totally different.So you cant transfer the same query to eQL. You need to change the syntax before query to eQL.
Please let me know if you need help with eQL query.
Thank Manav.....I know eQL is slightly different from normal SQL but most of the time it it follows the same syntax/structure etc....or, at least, that's what we have found on our more basic queries. I'm sure the more complex stuff would show up greater differences.
However the query I sent works perfectly well except for the id variable that we are trying to sort out now. If I substitute $Session.Person.Id with 3379, a known person.id the query works properly. So the code I'm using, up to the point of referencing the variable, must be okay mustn't it?
Hi Paul,To use variable in it, you can do something like this: Declaring and setting the variable and using in SELECT.
DECLARE @pi_object_id INT
SET @pi_object_id = 1
SELECT * FROM tasks WITH(NOLOCK) WHERE task_id = @pi_object_id
This is effectively doing
SELECT * FROM tasks WITH(NOLOCK) WHERE task_id = 1
Answer Verified By: Carma De Villiers
Yes, I've tried that but the issue remains that you cannot SET the new @pi_object_id to the Session.Persion.Id........so the problem is exactly the same. If I set it to a known person_id e.g. 3379 then it works but not if I set it to $Session.Persion.Id.
Maybe eB queries don't accept variables like this.
Hi Paul,
Are you using variables in eQL directly or to SQL?
Hi Manav...thanks for sticking with me on this one!
The code I've previously posted is what I'm dropping into the Attribute Query window. As I understand it eB takes EQL and converts it back to SQL anyway. As I've said, everything works fine apart from trying to reference the Session.Persion.Id variable.
I know we can not drop SQL query to eQL designer (or even attribute window) and expect it to work. I am not how it is working for you. May be I still don't understand where the problem is.Could you please send me the screen print where you put your query on attribute window, also the screen shot of the window where you mentioned it works when you mentioned the person_id as 3379.
Sure Manav......files attached.