如何根据另一张表中的数值来显示数据？

huangapple
go评论89阅读模式
英文:

How to display data based on value in another table?

问题

  1. select s.* from D365_PURCHASE_INVOICE_STAGING s
  2. INNER JOIN USERDETAILS u
  3.     on s.COMPANY = u.D365LE
  4. where (u.ROLE = 'Admin')
  5. or 
  6. (upper(s.COMPANY) = (SELECT u.D365LE FROM USERDETAILS WHERE upper(u.USERNAME) = upper(:app_user)))
  7. and (u.ROLE <> 'Admin')
英文:

I have a Interactive Report which displays data for multiple divisions, column name D365LE.

I also have a users table which contains the same column name, depending on which division the employee is a part of.

I'm able to display the data which only correlates to the users division using the following:

  1. select * from D365_PURCHASE_INVOICE_STAGING 
  2. where upper(company) = (SELECT u.D365LE FROM USERDETAILS u WHERE upper(u.username) = upper(:app_user))

In the USERDETAILS table there is a column called ROLE which holds whether the user is an admin or not. I want the report to display all data if the user is Admin, and if not it should only show the data which correlates to their division, as above.

I've tried the following, which does compile but displays no data:

  1. select s.* from D365_PURCHASE_INVOICE_STAGING s
  2. INNER JOIN USERDETAILS u
  3.     on s.COMPANY = u.D365LE
  4. where (u.ROLE = &#39;Admin&#39;)
  5. or 
  6. (upper(s.COMPANY) = (SELECT u.D365LE FROM USERDETAILS WHERE upper(u.USERNAME) = upper(:app_user)))
  7. and (u.ROLE &lt;&gt; &#39;Admin&#39;)

答案1

得分: 1

以下是翻译好的部分：

  1. SQL&gt; with
  2.   2  d365_purchase_invoice_staging  (company) as
  3.   3    (select 'Apple' from dual union all
  4.   4     select 'IBM'   from dual
  5.   5    ),
  6.   6  userdetails (username, d365le, role) as
  7.   7    (select 'LITTLE', 'IBM'  , 'Admin' from dual union all
  8.   8     select 'FOOT'  , 'Apple', 'Clerk' from dual
  9.   9    )
  11. Query:
  13.  10  select *
  14.  11  from d365_purchase_invoice_staging d
  15.  12  join userdetails u on d.company = u.d365le
  16.  13  where u.username = case when u.role &lt;&gt; 'Admin' then u.username
  17.  14                          else '&amp;APP_USER'
  18.  15                     end;
  19. Enter value for app_user: LITTLE          --&gt; admin
  21. COMPA USERNA D365L ROLE
  22. ----- ------ ----- -----
  23. IBM   LITTLE IBM   Admin
  24. Apple FOOT   Apple Clerk
  26. SQL&gt; /
  27. Enter value for app_user: FOOT           --&gt; not admin
  29. COMPA USERNA D365L ROLE
  30. ----- ------ ----- -----
  31. Apple FOOT   Apple Clerk
  33. SQL>;

请注意，代码部分没有翻译。如果需要进一步的翻译或有其他问题，请告诉我。

英文:

Something like this, perhaps?

Sample data:

  1. SQL&gt; with
  2.   2  d365_purchase_invoice_staging  (company) as
  3.   3    (select &#39;Apple&#39; from dual union all
  4.   4     select &#39;IBM&#39;   from dual
  5.   5    ),
  6.   6  userdetails (username, d365le, role) as
  7.   7    (select &#39;LITTLE&#39;, &#39;IBM&#39;  , &#39;Admin&#39; from dual union all
  8.   8     select &#39;FOOT&#39;  , &#39;Apple&#39;, &#39;Clerk&#39; from dual
  9.   9    )

Query:

  1.  10  select *
  2.  11  from d365_purchase_invoice_staging d
  3.  12  join userdetails u on d.company = u.d365le
  4.  13  where u.username = case when u.role &lt;&gt; &#39;Admin&#39; then u.username
  5.  14                          else &#39;&amp;APP_USER&#39;
  6.  15                     end;
  7. Enter value for app_user: LITTLE          --&gt; admin
  9. COMPA USERNA D365L ROLE
  10. ----- ------ ----- -----
  11. IBM   LITTLE IBM   Admin
  12. Apple FOOT   Apple Clerk
  14. SQL&gt; /
  15. Enter value for app_user: FOOT           --&gt; not admin
  17. COMPA USERNA D365L ROLE
  18. ----- ------ ----- -----
  19. Apple FOOT   Apple Clerk
  21. SQL&gt;

(this was ran in SQL*Plus so I used substitution variable, &#39;&amp;APP_USER&#39;. You'd use :APP_USER as you normally do in Apex).

答案2

得分: 1

  1. 可以使用：
  3. ```sql
  4. select *
  5. from   D365_PURCHASE_INVOICE_STAGING d
  6. where  EXISTS(
  7.          SELECT 
  8.          FROM   USERDETAILS u
  9.          WHERE  upper(u.username) = upper(:app_user)
  10.          AND    (  upper(company) = u.D365LE
  11.                 OR u.role = 'Admin' )
  12.        )
英文:

You can use:

  1. select *
  2. from   D365_PURCHASE_INVOICE_STAGING d
  3. where  EXISTS(
  4.          SELECT 
  5.          FROM   USERDETAILS u
  6.          WHERE  upper(u.username) = upper(:app_user)
  7.          AND    (  upper(company) = u.D365LE
  8.                 OR u.role = &#39;Admin&#39; )
  9.        )

huangapple
  • 本文由 发表于 2023年6月5日 19:14:08
  • 转载请务必保留本文链接：https://go.coder-hub.com/76405871.html
  • oracle-apex
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定