选择查询 [英] Select Query

问题描述

请让我们检查以下情况:
我有一个名为Salesman_customer的表，并希望使用该表输入客户及其各自的业务员，条件是.某个时间段内某个客户只能属于一个销售员(例如:客户A在2010年10月1日到2010年10月30日之间属于Salesman1，在这个月(十月)没有其他销售员可以将A添加为他/她的客户，但可以在10月之前或10月之后添加)

现在请检查条件:
1.A是salesman1的客户从2010年10月1日到----

如果表包含case1的记录.用户应该不能输入以下类型的记录

一个.在2010年10月1日到-----
期间不能以客户身份输入A b.不能在大于10月的时间内以客户身份输入A(因为TO_DATE)为空

我通过以下查询实现了此目的

Let you guys please examine the following scenario:
I have a table called Salesman_customer and want to use this table for enter customers and their respective salesman, with a condition that. at a time a customer can belongs to only one salesman during a time period(eg:Customer A belongs to Salesman1 during 1-Oct-2010 to 30_Oct-2010, during this month(october) no other salesman can''t Add A as his/her customer, but it is possible to add before October or after October )

Now please examine the condition:
1.A is a Customer of salesman1 From 1-Oct-2010 to ----

if the table contain record of case1. the users should not be able enter the record of following type

a. Can''t enter A as customer during 1-Oct-2010 to -----
b.can''t enter A as Customer during greater than October(becoz the TO_DATE) is null

I acheived this with the following query

AND ((MC_FM_DT >= :NEW.MC_FM_DT and MC_TO_DT <= :NEW.MC_TO_DT)
                        OR MC_TO_DT Is Null);

但是我的问题是无法使用我的query在2010年9月1日至2010年9月30日之间将A添加为客户.当我尝试这样输入时，它已显示显示记录的返回消息，但实际上不存在记录.

有人可以帮助我修改查询，而又不违反上述条件(对查询没有任何更改，但要增加一些附加条件).

but my problem is am not able add A as a Customer for the period 1-Sep-2010 to 30-Sep-2010 with my query.when i try to enter like this it return message displaying record already exist but actually there is no such record.

Some one please help me to modify the query without violating the above condition(no change on the query but some addition).

推荐答案

当前，Salesman_Customer表中的MC_TO_DT为null . OR子句将始终返回true.如果仍然要检查是否为空，则还需要确保:NEW.MC_TO_DT也小于MC_FM_DT.

Currently MC_TO_DT is null in the Salesman_Customer table. The OR clause will always return true. If you still want to check for null then you will also want to make sure that the :NEW.MC_TO_DT is less than the MC_FM_DT as well.

这篇关于选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！