如果在oracle SQL查询中还有其他问题

I have a scenario where I can get 2 types of inputs but the outcome is same if you include input 1 or input 2 or both. The following is the query which I have currently. This query retrieves the customer_id from the database from the circuit and telephone inputs.

我有一个场景,我可以获得两种类型的输入,但如果您包含输入1或输入2或两者,结果是相同的。以下是我目前的查询。此查询从电路和电话输入中检索数据库中的customer_id。

select customer.customer_id from circuits join customer on customer.cust_order = circuits.cust_order where circuitId= 'somecircuit'
union
select customer.customer_id from customer join telephone on telephone.number = customer.number where telephone = '34234242'

Now I need the second query to not execute if the input telephone is not available and vice versa for the circuit. Is there a way to do this in the query itself?

现在,如果输入电话不可用,我需要第二个查询不执行,反之亦然。有没有办法在查询本身中执行此操作?

I'm not planning on creating a PL/SQL block for this, If there is no way to do it in the sql i have to resort to Java and two different queries for each scenario.

我不打算为此创建一个PL / SQL块,如果在sql中无法做到这一点,我必须求助于Java和每个场景的两个不同的查询。

2 个解决方案

#1

Would this do?

这会吗?

select customer.customer_id
from circuits join customer on customer.cust_order = circuits.cust_order
where circuitId is not null
union
select customer.customer_id
from customer join telephone on telephone.number = customer.number
where telephone is not null

#2

You can join all your tables in the SQL query and let the WHERE condition handle the rest:

SELECT customer.customer_id
FROM circuits JOIN customer ON customer.cust_order = circuits.cust_order
JOIN telephone on telephone.number = customer.number
WHERE circuitId= 'somecircuit' OR telephone = '34234242'

Either the circuit id that is set will retrieve your customer's data or the telephone number.

您可以在SQL查询中加入所有表,并让WHERE条件处理其余的表:SELECT customer.customer_id FROM circuits JOIN customer ON customer.cust_order = circuits.cust_order在电话上联接电话号码= customer.number WHERE circuitId ='somecircuit '或电话='34234242'设置的电路ID将检索客户的数据或电话号码。