Jump to content
  • 0
Shuchi

SQL Filtering with Auth field in where clause not working

Question

I am trying to get the count from a table and applying record level security, but in the where clause, when I am comparing it with my auth field, it is saying "Invalid Query"
For instance, my query:

ISNULL((SELECT COUNT(DISTINCT [@field:PropertyKeyId]) FROM UI_FactTrxn_1 WHERE [@field:InvestorUserId] = [@authfield:Users_UserId]),0)

I am getting Invalid error for this.

image.png.a4e799deb4ab3f10acad3141be3be619.png

But when I am trying to pass the string in place of authfield, it is a valid one!

Can anyone please help me on this?

Share this post


Link to post
Share on other sites

6 answers to this question

Recommended Posts

  • 0

If you are using SQL SELECT to read the value you need to use the name of the field form that table not using parameter

 

so you need to have:
 

SELECT COUNT(DISTINCT PropertyKeyId)

FROM UI_FactTrxn_1 WHERE InvestorUserId = [@authfield:Users_UserId]

And you do not have to replace the value with 0 as COUNT returns 0 if there is none. 

Share this post


Link to post
Share on other sites
  • 0
On 10/29/2018 at 11:41 AM, MayMusic said:

If you are using SQL SELECT to read the value you need to use the name of the field form that table not using parameter

 

so you need to have:
 


SELECT COUNT(DISTINCT PropertyKeyId)

FROM UI_FactTrxn_1 WHERE InvestorUserId = [@authfield:Users_UserId]

And you do not have to replace the value with 0 as COUNT returns 0 if there is none. 

Thanks for the reply Melody, but I am still getting the same error :

image.png.1531320779b54d2f2d068d4d29bf95e4.png

even when I used the query you suggested. :( 

Share this post


Link to post
Share on other sites
  • 0

By picker, I hope you mean to say that I should use image.png.9af94df85bbdc1b7711e27aa605fd7ad.png button to insert the auth field. I am inserting it from there itself. Is there any other way to do it? Is it a syntax error? Like for using a view we use "_v_" before the name, for referring to fields on data page, we use "target." and then the field name, do we need to append something for authfield as well?

Share this post


Link to post
Share on other sites
  • 0

Thanks for the help Melody, I figured it out. The syntax was incorrect as my User_Id Field is a string value, I should pass it in ''.

The corrected query is

 

SELECT COUNT(DISTINCT PropertyKeyId)

FROM UI_FactTrxn_1 WHERE InvestorUserId = '[@authfield:Users_UserId]'

:) 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...