用于在SQL中检索转换后的列值的动态查询 [英] Dynamic Query for Retriving the translated column value in SQL
问题描述
我有一个table_lookup.这是主表，其中包含所有查找代码，例如
I have a table_lookup. This is the master table which has all the lookup codes like
LOOKUP_TYPE LOOKUP_CODE MEANING ENABLED_FLAG
EMP_CAT 3 Hourly with fixed hours per week Y
EMP_CAT 4 Hourly Y
EMP_CAT CAS Casual Y
EMP_CAT FR Full-time regular Y
EMP_CAT FR_01 Full-time Y
ABSENCE_CATEGORY DLHM Leave Y
ABSENCE_CATEGORY DLHNM Leave on the death of a husband Y
ABSENCE_CATEGORY DLR Leave on the death of a relative Y
ABSENCE_CATEGORY GB_ADO Adoption Y
ABSENCE_CATEGORY GB_PAT_ADO Paternity adoption Y
NATIONALITY PY Paraguayan Y
NATIONALITY QA Qatari Y
NATIONALITY RO Romanian Y
NATIONALITY RS Serbian
该表在不同的表中被引用，例如
table_assignment
This table is referenced in different tables like
table_assignment
emp no. name Employee category active/inactive
1 divya 3 A
2 abc FR A
3 XYZ 4 I
4 aMY 100 A
表table_nationality
Emp no. nationality
1 QA
2 RS
4 RO
即lookup_code f在table_Assignment employee_category列和table_nationality的国籍列中进行翻译.
That is the lookup_code f is translated in the table_Assignment employee_category column and nationality column in table_nationality.
我有一个查询，例如:
select emp_no.,
name,
employee_category
from table_assignment lookup_assignmen,
table_lookup lookup_stg
where lookup_stg.lookup_type = 'EMP_CAT'
AND LOOKUP_STG.LOOKUP_CODE = lookup_assignmen.employee_category;
与table_nationality类似，例如:
select emp_no.,
nationality
from TABLE_NATIONALITY lookup_NATIONALITY,
table_lookup lookup_stg
where lookup_stg.lookup_type = 'NATIONALITY'
AND LOOKUP_STG.LOOKUP_CODE = lookup_NATIONALITY.employee_category;
现在我想要一个动态查询，该查询可以检测例如table_assignment是否存在table_lookup中没有的任何员工类别.
Now I want a dynamic query which detects if in say for example table_assignment there is any employee category which is not there in the table_lookup.
例如:在table_assignment中，雇员类别列中提供的值为100，而在table_lookup中则不存在.
此类值应在查询中检索，但查询应是动态的，以便它应检索table_Assignment，table_nationality或其他任何值中的所有无效查找值
桌子.我可以在查询中输入lookup_type和表名，并且应该检索无效值.
For example : In the table_assignment there is a value 100 given in the employee category column this is not there in the table_lookup.
Such values should be retrieved in a query but the query should be dynamic such that it should retrieve all the invalid lookup values in table_Assignment, table_nationality or any other
tables. I can input the lookup_type and table name in the query and the invalid values should be retrieved.
如何将静态查询更改为动态查询?
How do I change my static query to dynamic?
推荐答案
您可以合并所有无效代码并避免使用动态sql.
you can union all the invalid codes and avoid dynamic sql.
select *
from (
select emp_no as entity_id, 'emp_cat' as lookup_type, eployee_category as invalid_lookup_code
from table_assignment e
left join table_lookup r
on e.employee_category = r.lookup_code
and r.lookup_type = 'emp_cat'
where r.lookup_type is null
union all
select emp_no as entity_id, 'nationality' as lookup_type, nationality as invalid_lookup_code
from table_nationality e
left join table_lookup r
on e.employee_category = r.lookup_code
and r.lookup_type = 'nationality'
where r.lookup_type is null
) as t
where t.lookup_type = 'nationality'
这篇关于用于在SQL中检索转换后的列值的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
