komputer
asked on
a strange problem
hi all,
i have a very strange problem. i have query like this
select
a.id as queue_id, b.*, c.text
from xxx a left join yyy b on (a.SMS_ID = b.id)
left join zzz c on (c.content_id = b.content_id)
where
is_send = 'N' and
day in (-1, to_number(to_char(SYSDATE, 'D'))) and
(minute = -1 or
minute <= to_number(to_char(sysdate, 'HH24')) * 60 + to_number(to_char(sysdate, 'MI')))
and
c.text is not null and
(b.last_sent_sms is null or b.last_sent_sms != c.text) and
(c.content_date is null or (to_char(sysdate, 'yyyy/mm/dd') = to_char(c.content_date, 'yyyy/mm/dd')))
and (
b.comparison_operator is null or
(b.comparison_operator = '=' and b.comparison_value = c.price) or
(b.comparison_operator = '>' and b.comparison_value > c.price) or
(b.comparison_operator = '<' and b.comparison_value < c.price)
)
when i run this from toad it works fine and return 2 row but when i want to run in my java project it work very strangely and return only 1 work.
i couldnt understand anything why it behaves like that. it problem cause of java.
if you help me, i will be pleased.
thanks in advance.
i have a very strange problem. i have query like this
select
a.id as queue_id, b.*, c.text
from xxx a left join yyy b on (a.SMS_ID = b.id)
left join zzz c on (c.content_id = b.content_id)
where
is_send = 'N' and
day in (-1, to_number(to_char(SYSDATE,
(minute = -1 or
minute <= to_number(to_char(sysdate,
and
c.text is not null and
(b.last_sent_sms is null or b.last_sent_sms != c.text) and
(c.content_date is null or (to_char(sysdate, 'yyyy/mm/dd') = to_char(c.content_date, 'yyyy/mm/dd')))
and (
b.comparison_operator is null or
(b.comparison_operator = '=' and b.comparison_value = c.price) or
(b.comparison_operator = '>' and b.comparison_value > c.price) or
(b.comparison_operator = '<' and b.comparison_value < c.price)
)
when i run this from toad it works fine and return 2 row but when i want to run in my java project it work very strangely and return only 1 work.
i couldnt understand anything why it behaves like that. it problem cause of java.
if you help me, i will be pleased.
thanks in advance.
Try to use brackets..after "Where" clause and after two "and".
ASKER
i' ve just realised the problem cause of to_number(to_char(SYSDATE, 'D')) to return the day of week. it changes depend on the regional settings. for american it returns 2 for monday while returns 1 for UK.
is there any function that runs independent of regional settings to return the week of day in oracle.
or any solution to be independent of regional settings to calculate the week of day.
thanks...
is there any function that runs independent of regional settings to return the week of day in oracle.
or any solution to be independent of regional settings to calculate the week of day.
thanks...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is your problem solved? Please let me know.
ASKER
earthman2's solution seems work.