英文:
SQL - replace constant with variable
问题
如何用变量替换常量'constantHere'？我使用普通的SQL，我了解在普通SQL中不存在变量的概念，但也许有一种解决方法？
重要的是我保留了'%'符号，因为我不知道常量的结尾。
我有多个联接，以及作为条件的相同'constantHere'。每次运行脚本时，我需要更改它。我想只在代码中有一个地方更改它，而不是上下滚动并更改所有这些地方。
英文:
How to replace the constant 'constantHere' with a variable? I use plain SQL, I understand that concept of variables doesn't exist in plain SQL but maybe there is a workaround?
It is important that I left '%' sign there, as I never know how the constant ends.
I have multiple unions and the same 'constantHere' as a condition. Each time I run the script I need to change it. I'd like to have only 1 place in the code to change it instead of scrolling up and down and change all of them.
select tuse.user_id user_id,
tuse.user_name user_name,
tuse.login_name login,
tuse.is_active user_active,
userint.role_code role_code,
userint.is_active role_active,
null special_priv,
null special_priv_active
from object.t_user tuse
left join object.user_special_privilege usespec
on tuse.user_id = usespec.user_id
left join object.special_privilege specpriv
on usespec.special_privilege_id = specpriv.special_privilege_id
left join object.user_role_int userint
on tuse.user_id = userint.user_id
where upper(tuse.user_name) like upper('constantHere%')
union
select tuse.user_id user_id,
tuse.user_name user_name,
tuse.login_name login,
tuse.is_active user_active,
null role_code,
null role_active,
specpriv.description special_priv,
specpriv.is_active special_priv_active
from object.t_user tuse
left join object.user_special_privilege usespec
on tuse.user_id = usespec.user_id
left join object.special_privilege specpriv
on usespec.special_privilege_id = specpriv.special_privilege_id
left join object.user_role_int userint
on tuse.user_id = userint.user_id
where upper(tuse.user_name) like upper('constantHere%')
order by 2, 5, 7
答案1
得分: 0
以下是您要翻译的部分：
引用：“我想只在代码中更改一个地方，而不是上下滚动并更改所有地方。”... 结束引用。
如果这是您所需的内容，那么您可以将其放在CTE中：
WITH var_def AS (Select 'something' "constantHere" From Dual) -- 在这里定义它
select tuse.user_id user_id,
tuse.user_name user_name,
tuse.login_name login,
tuse.is_active user_active,
userint.role_code role_code,
userint.is_active role_active,
null special_priv,
null special_priv_active
from object.t_user
Inner Join var_def ON(1 = 1) -- 在这里将var_def.constantHere附加到每一行 tuse
left join object.user_special_privilege usespec
on tuse.user_id = usespec.user_id
left join object.special_privilege specpriv
on usespec.special_privilege_id = specpriv.special_privilege_id
left join object.user_role_int userint
on tuse.user_id = userint.user_id
where upper(tuse.user_name) like upper(var_def.constantHere||'%') -- 在这里使用它
union
select tuse.user_id user_id,
tuse.user_name user_name,
tuse.login_name login,
tuse.is_active user_active,
null role_code,
null role_active,
specpriv.description special_priv,
specpriv.is_active special_priv_active
from object.t_user
Inner Join var_def ON(1 = 1) -- 在这里将var_def.constantHere附加到每一行 tuse
left join object.user_special_privilege usespec
on tuse.user_id = usespec.user_id
left join object.special_privilege specpriv
on usespec.special_privilege_id = specpriv.special_privilege_id
left join object.user_role_int userint
on tuse.user_id = userint.user_id
where upper(tuse.user_name) like upper(var_def.constantHere||'%') -- 在这里使用它
order by 2, 5, 7
现在您只需更改一个地方.... 根据需要命名您的常量，并使用大写字母。我之所以将名称命名为constantHere，是因为您在问题中使用了它。
英文:
Quote: " I'd like to have only 1 place in the code to change it instead of scrolling up and down and change all of them." ...end quote.
If this is what you need then you could place it in CTE:
WITH var_def AS (Select 'something' "constantHere" From Dual) -- define it here
select tuse.user_id user_id,
tuse.user_name user_name,
tuse.login_name login,
tuse.is_active user_active,
userint.role_code role_code,
userint.is_active role_active,
null special_priv,
null special_priv_active
from object.t_user
Inner Join var_def ON(1 = 1) -- here you attach var_def.constantHere to every row tuse
left join object.user_special_privilege usespec
on tuse.user_id = usespec.user_id
left join object.special_privilege specpriv
on usespec.special_privilege_id = specpriv.special_privilege_id
left join object.user_role_int userint
on tuse.user_id = userint.user_id
where upper(tuse.user_name) like upper(var_def.constantHere||'%') -- here you use it
union
select tuse.user_id user_id,
tuse.user_name user_name,
tuse.login_name login,
tuse.is_active user_active,
null role_code,
null role_active,
specpriv.description special_priv,
specpriv.is_active special_priv_active
from object.t_user
Inner Join var_def ON(1 = 1) -- here you attach var_def.constantHere to every row tuse
left join object.user_special_privilege usespec
on tuse.user_id = usespec.user_id
left join object.special_privilege specpriv
on usespec.special_privilege_id = specpriv.special_privilege_id
left join object.user_role_int userint
on tuse.user_id = userint.user_id
where upper(tuse.user_name) like upper(var_def.constantHere||'%') -- here you use it
order by 2, 5, 7
Now you have only 1 place to change it .... Name your constant accordingly and with capital letters. I put the name constantHere because you used it in question.
答案2
得分: 0
使用替代变量。
使用定义的常量constantHere='SomeValue'。
替换为&&constantHere。
英文:
Use Substitution variable.
use define constantHere='SomeValue'
Replace with &&constantHere
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论