如何根据与第二个表的比较将布尔列添加到 SQL 结果集

发布于 2024-12-06 09:37:48 字数 878 浏览 1 评论 0原文

基本上我已经回答了问题的第一部分，这是我到目前为止的 SQL 语句...

SELECT DbaRolePrivs.GRANTEE,
       DbaRolePrivs.GRANTED_ROLE,
       ApplicationRoleDefinition.ROLE,
       ApplicationRoledefinition.description_tx, 
       CASE
         WHEN GRANTED_ROLE = ROLE AND GRANTEE = USERNAME THEN
           'T'
         ELSE
           'F'
       END AS checkMark
FROM DBA_ROLE_PRIVS DbaRolePrivs, APPLICATION_ROLE_DEFINITION ApplicationRoleDefinition

这是我试图用简单的英语执行的逻辑。

“给定所有角色及其描述的列表，根据当前选定的 USERNAME 是否已根据其自己的 GrantedRoles 列表授予该角色，将每个角色标记为 TRUE 或 FALSE。”

现在它当然会返回一个双重列表，因为“case when”部分将每个授予的角色与角色的总列表进行比较。（我说重复是因为测试用户只有两个角色，在当前的 SQL 查询中，它将为每个可能的角色为每个授予的角色返回一行。

我几乎没有使用过 SQL，所以我更习惯于 C++、Objective-C实际上，我在“case when”部分中需要的是检查当前 ROLE 是否存在于 GRANTED_ROLE 列中，我已经看到了 EXISTS 函数，但这似乎仅适用于诸如“WHERE”之类的结果集。存在。”

谢谢提前，我知道这对于经常使用数据库的人来说可能很简单，我才刚刚开始。

原文

Essentially I have the first part of the question answered and here is the SQL statement I have so far...

SELECT DbaRolePrivs.GRANTEE,
       DbaRolePrivs.GRANTED_ROLE,
       ApplicationRoleDefinition.ROLE,
       ApplicationRoledefinition.description_tx, 
       CASE
         WHEN GRANTED_ROLE = ROLE AND GRANTEE = USERNAME THEN
           'T'
         ELSE
           'F'
       END AS checkMark
FROM DBA_ROLE_PRIVS DbaRolePrivs, APPLICATION_ROLE_DEFINITION ApplicationRoleDefinition

Here is the logic I am trying to perform in plain english.

"Given a list of all roles and their descriptions, mark each role as TRUE or FALSE based on if the current selected USERNAME has been granted that role based on their own GrantedRoles list."

Right now it is of course returning a double list since the "case when" section compares each of the granted roles with the total list of roles. (I say duplicate because the test user only has two roles, in the current SQL query it will return a row per granted role for every possible role.

I've barely worked with SQL so I'm more used to C++, Objective-C, etc. Really all I need in the "case when" section is to check if the current ROLE exists at all in the GRANTED_ROLE column. I've seen the EXISTS function but that seems to be meant solely for result sets such as "WHERE EXISTS."

Thanks in advance, I know it's probably simple for anyone whose worked with databases often, I've only just started.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

画▽骨i 2024-12-13 09:37:48

您需要外连接，然后测试连接是否成功。如果外连接失败，仍会返回一行，但所有 DbaRolePrivs 列将为 null

SELECT  DbaRolePrivs.GRANTEE,
        DbaRolePrivs.GRANTED_ROLE,
        ApplicationRoleDefinition.ROLE,
        ApplicationRoledefinition.description_tx,
        NVL2( DbaRolePrivs.GRANTED_ROLE, 'T', 'F' ) AS checkMark
FROM    DBA_ROLE_PRIVS DbaRolePrivs
    ,   APPLICATION_ROLE_DEFINITION ApplicationRoleDefinition
WHERE   ApplicationRoleDefinition.ROLE = DbaRolePrivs.GRANTED_ROLE (+)
AND     USERNAME                       = DbaRolePrivs.GRANTEE      (+)

You need to outer join and then test whether the join was successful or not. If the outer join fails a row is still returned but all the DbaRolePrivs columns will be null

SELECT  DbaRolePrivs.GRANTEE,
        DbaRolePrivs.GRANTED_ROLE,
        ApplicationRoleDefinition.ROLE,
        ApplicationRoledefinition.description_tx,
        NVL2( DbaRolePrivs.GRANTED_ROLE, 'T', 'F' ) AS checkMark
FROM    DBA_ROLE_PRIVS DbaRolePrivs
    ,   APPLICATION_ROLE_DEFINITION ApplicationRoleDefinition
WHERE   ApplicationRoleDefinition.ROLE = DbaRolePrivs.GRANTED_ROLE (+)
AND     USERNAME                       = DbaRolePrivs.GRANTEE      (+)

回复收藏 0 原文

~没有更多了~