Forum Stats

  • 3,837,476 Users
  • 2,262,262 Discussions
  • 7,900,297 Comments

Discussions

Session variable problem : equality comparisons

ADB
ADB Member Posts: 261
I have defined the following session variable returning the error *"[nQSError: 10058] A general error has occurred. [nQSError: 42040] The session variable, NQ_SESSION.TEST, is defined as Row-Wise Initialization. It is restricted to usage with equality comparisons. (HY000)"*

select 'TEST', organisatie_nummer
from st_organisatie
where (select case when regio = -1 then 1 else
regio_nummer end
from st_gebruikers
where upper(gebruiker) = upper('VALUEOF(NQ_SESSION.USER)')) =
(select case when regio = -1 then '1' else regio end
from st_gebruikers
where upper(gebruiker) = upper('VALUEOF(NQ_SESSION.USER)'))

Why this setup...a user can have access to 1 region or 'ALL' region. To make the 'ALL' available a dummy records has been inserted with a value of -1.

There's no problem to retrieve the region (another session variable), it returns '%' in case a user has all regions or the region name itself.

But i wanted to use this variable to retrieve the organisations linked to a region, but using a like statement or the query above isn't possible as it always returns the same error.

Someone any idea how to resolve this?!

Txs,
Andy
Tagged:
«1

Answers

  • Stijn Gabriels
    Stijn Gabriels Member Posts: 1,032
    Hi Andy,

    first of all, I don't really understand your SQL query. Nevertheless, I am wondering why you have used Row-Wise Initialization for this session variable, if you only want value "ALL" or the name of the region for the TEST session variable.

    Regards,
    Stijn
  • ADB
    ADB Member Posts: 261
    Stijn

    in case a user has 'all' regions dedicated, I want to see all the organisations otherwise I only want to see the organisations linked to the specific region.

    As I can't use a 'LIKE' (see error that you can only use =) into the session variable, I've worked something out returning the column name (user has 1 region) or 1 (user has all regions).

    I hope it's more clear now.

    KR,
    A
  • Stijn Gabriels
    Stijn Gabriels Member Posts: 1,032
    So a user is joined to one or all regions and an organizations is joined to a region. Why don't you create one query, in which you join all the required tables?

    Regards,
    Stijn
  • ADB
    ADB Member Posts: 261
    [nQSError: 10058] A general error has occurred. [nQSError: 42040] The session variable, NQ_SESSION.ORGANISATIE, is defined as Row-Wise Initialization. It is restricted to usage with equality comparisons. (HY000)

    I transformed my query like this, I don't have any clue why this isn't working...if I run this sql statement it works perfect but not in OBi

    select 'ORGANISATIE', organisatie_nummer
    from st_organisatie
    ,(select gebruiker,regio from st_gebruikers) b
    where case when b.regio = -1 then 1 else regio_nummer end
    like
    case when b.regio = -1 then '%' else b.regio end
    and upper(gebruiker) = upper(':USER')
  • ADB
    ADB Member Posts: 261
    Even after trying to create a view of the query and using this view within my session variable i have the same error.

    My query is
    select 'PARAM', organisatienummer
    from vw_org
    where upper(gebruiker) = upper(':USER')

    It makes me desperate, so all help is welcome!

    Kr,
    A
  • Stijn Gabriels
    Stijn Gabriels Member Posts: 1,032
    I think your query should look like this:

    SELECT 'ORGANISATIE', o.organisatie_nummer
    FROM st_organisatie o, st_gebruikers g
    WHERE o.regio_nummer = g.regio_nummer AND g.gebruiker = 'UPPER(VALUEOF(NQ_SESSION.USER))'

    Regards, Stijn
  • ADB
    ADB Member Posts: 261
    edited Dec 10, 2008 11:39AM
    :-) I know it should look like that, but that's the problem.

    I don't have organisation for id = -1, that's why a make a translation of it towards a %

    And even whenI put my values into a table I receive this error.....I'm lost...

    Edited by: ADB on Dec 10, 2008 8:38 AM
  • Hi...

    For your view approach post..
    As per my knowledge... USER is the keyword for ORACLE, so it will n't accept it as bind variable.. as you are using...
    So try give other name..

    Thanks & Regards
    Kishore Guggilla
  • ADB
    ADB Member Posts: 261
    Hi kishore,

    txs but as I have the same prob when using a table, I guess there's something else;

    Also when using valueof(nqsession.user) i have probs :-(
  • hi....

    I am not sure this.. but try using content filter of particular logical table source...

    Thanks & Regards
    Kishore Guggilla
This discussion has been closed.