Announcement

Collapse
No announcement yet.

SELECT WHERE CASE statement

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SELECT WHERE CASE statement

    I am working on a project in which the user's access to records is restricted based on the user's User Group. I have created a global variable $usr_sec_group, and I want to add to the WHERE clause in the SELECT statement for several applications a CASE statement that applies a different filter based on the value of $usr_sec_group. I am a relative "newbie" with regards to mySQL, and my attempts at writing such a statement haven't worked. Here is the basic logic:

    Code:
    SELECT 
       field1,
       field2,
       etc
    FROM
      Organizations
    CASE $user_sec_group
      WHEN 1 THEN 'filter_statement_1'
      WHEN 2 THEN 'filter_statement_2'
      WHEN 3 THEN 'filter_statement_3'
      ELSE 'filter_statement_else'
    END CASE
    ORDER By
       field1
    The 'filter_statements' could be any valid filter, such as

    Code:
    'oName => 'a' AND oName < 'g'
    I am assuming that the problem is a relatively simple matter of syntax, but so far I haven't been able to write a CASE statement that works.

    I will be grateful for some guidance!

    Best regards,

    Eric
    Last edited by EricDahl; 04-01-2013, 08:53 PM.

  • #2
    Originally posted by EricDahl View Post
    I am assuming that the problem is a relatively simple matter of syntax, but so far I haven't been able to write a CASE statement that works.
    One possibility is:

    SELECT field1, field2, fieldx FROM tablename WHERE [filter_statement]

    Set [filter_statement] to "1=1" then it shows all records, set [filter_statement] to field1 = 'x' then shows only these data sets.

    I do this more often, for example, if I need occasionally several filters: SELECT ... WHERE 1=1 AND 2=2 AND 3=3 etc. and replace in the sql string then 1=1 by field1=x ... speak, a placeholder for the conditional.
    Best regards: - Reinhard -

    I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

    Comment


    • #3
      Thanks, Reinhard, for the suggestion. I am afraid I don't understand the last part of your comment:
      Originally posted by RHS View Post
      and replace in the sql string then 1=1 by field1=x ... speak, a placeholder for the conditional.
      Could you please explain that in more detail? -- Eric

      Comment


      • #4
        another solution could be to set the whole sql-statement on the starting event manually. You are able to set the statement as you want and can change the condition as you want. Made that in an app for member-management and works perfect ...

        BR

        EricB

        Comment


        • #5
          Originally posted by EricB View Post
          another solution could be to set the whole sql-statement on the starting event manually. You are able to set the statement as you want and can change the condition as you want. Made that in an app for member-management and works perfect ...

          BR

          EricB
          Eric, could you explain a bit more on what you did and how?? If you don't want to explain here, could you send me PM?
          I think this would solve a few problems that I'm having and sure would help me out.
          Thanks
          Alan

          Comment


          • #6
            Bump .. how do I do this?

            Comment

            Working...
            X