IF condition in the SQL query in the WHERE clause

I have Oracle sql query like this

SELECT *
FROM test
WHERE 1 =1
AND req_no IN
  (SELECT req_no
  FROM test
  WHERE date1 IS NOT NULL
  AND date1 BETWEEN '1-JUN-12' AND '30-JUN-12'
  )

The problem is that the WHERE
condition doesn’t work properly. In above condition if ‘date1’ is NOT NULL then I want to check ‘date1’ in between ‘FROM’ and ‘TO’ date.
* EDIT
*

I have added sample table data

Test table
From date: 1-JUN-2012
To date: 30-JUN-2012

record      date1          date2            date3
1       12-JUN-2012     13-JUN-2012         12-JUN-2012
2       null            null                12-JUN-2012
3       null            3-JUN-2012          null
4       12-JUN-2012     null                null
5       12-JUN-2012     13-JUL-2012         12-JUN-2012
6       null                null             null

In the above record it will return record number=1,2,3,4 not 6th record.

You can use OR
like this:

WHERE date1 IS NULL
  OR date1 BETWEEN '1-JUN-12' AND '30-JUN-12'

Your whole query should be:

SELECT *
FROM test
WHERE 1 =1
AND req_no IN
  (SELECT req_no
  FROM test
  WHERE date1 IS NULL
  OR date1 BETWEEN '1-JUN-12' AND '30-JUN-12'
  )
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » IF condition in the SQL query in the WHERE clause

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录