//p_type is a varchar which has some value to be comapred. and USER_ID is a int which holds a integer value. i want cond1 OR con2 OR con3 to be worked only for condA OR condB.. i mean i want this result to be displayed only for admin or matched user id for id of tab_2. i think this is not the correct one. because it work for both OR statements . can anyone plz help.. thanx in advance
select count(1) from
from
(
select id,
CASE
when a >= 0 and a <= 30 then 'one'
when a >= 31 and a <= 60 then 'two'
when a >= 61 and a <= 90 then 'three'
else 'NO'
END
FROM tabel_1 t
Where
(
(
TO_CHAR(t.aDate, 'YYYYMMDD') BETWEEN TO_CHAR(pFrom_Date, 'YYYYMMDD') AND
TO_CHAR(pTo_Date, 'YYYYMMDD')
//cond1
) OR
(
TO_CHAR(t.bDate, 'YYYYMMDD') BETWEEN TO_CHAR(pFrom_Date, 'YYYYMMDD') AND
TO_CHAR(pTo_Date, 'YYYYMMDD')
//cond2
)OR
(
TO_CHAR(t.bDate, 'YYYYMMDD') BETWEEN TO_CHAR(pFrom_Date, 'YYYYMMDD') AND
TO_CHAR(pTo_Date, 'YYYYMMDD')
//cond3
)
AND
(
p_Type = 'Admin' AND
t.ID > 0 //condA
)OR
< 0
(
SELECT COUNT(1)
FROM tab_2 t2
WHERE t2.ID = USER_ID AND
t.ID = t2.ID condB
)
)
)
Datta KharadPosted Jan 28, 2012, 3:14 AM
Try this code...
select count(1) from
from
(
select id,
CASE
when a >= 0 and a <= 30 then 'one'
when a >= 31 and a <= 60 then 'two'
when a >= 61 and a <= 90 then 'three'
else 'NO'
END
FROM tabel_1 t
Where
(
((
TO_CHAR(t.aDate, 'YYYYMMDD') BETWEEN TO_CHAR(pFrom_Date, 'YYYYMMDD') AND
TO_CHAR(pTo_Date, 'YYYYMMDD')
//cond1
) OR
(
TO_CHAR(t.bDate, 'YYYYMMDD') BETWEEN TO_CHAR(pFrom_Date, 'YYYYMMDD') AND
TO_CHAR(pTo_Date, 'YYYYMMDD')
//cond2
)OR
(
TO_CHAR(t.bDate, 'YYYYMMDD') BETWEEN TO_CHAR(pFrom_Date, 'YYYYMMDD') AND
TO_CHAR(pTo_Date, 'YYYYMMDD')
//cond3
))
AND
((
p_Type = 'Admin' AND
t.ID > 0 //condA
)OR
< 0
(
SELECT COUNT(1)
FROM tab_2 t2
WHERE t2.ID = USER_ID AND
t.ID = t2.ID condB
))
)
)