No question at this time
DBA Top 10
1 A. Kavsek 8700
2 B. Vroman 5900
3 M. Cadot 5400
4 P. Wisse 4800
5 T. Boles 3700
6 J. Schnackenberg 2100
7 . Lauri 1500
8 R. Pattyn 700
9 T. P 600
9 J. Péran 600
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48409
Total active users1518
Act. users last 24h5
Act. users last hour0
Registered user hits last week284
Registered user hits last month997
Go up

Case in Where Clause
Next thread: list available agents in the library
Prev thread: Issue in GG Replication - OGG-01296

Message Score Author Date
Hi All, I am creating a report for shipment tab...... Dev Rishi Trehan Apr 03, 2019, 08:31
In the report parameters, the From Date and To Dat...... Dev Rishi Trehan Apr 03, 2019, 08:33
Parameters as it should be--- select * from shi...... Dev Rishi Trehan Apr 03, 2019, 08:50
It worked with this sql select sh.SHIPMENT_GI...... Dev Rishi Trehan Apr 03, 2019, 10:08
How about:<pre> select sh.SHIPMENT_GID,SH.INSERT_...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse Apr 03, 2019, 12:58
Obviously, you did not understand the case syntax ...... Joël Péran Apr 04, 2019, 08:06
Hi Dev, although it is not allowed to write thi...... Bruno Vroman Apr 04, 2019, 16:28

Follow up by mail Click here


Subject: Case in Where Clause
Author: Dev Rishi Trehan, India
Date: Apr 03, 2019, 08:31, 141 days ago
Os info: Linux
Oracle info: 12C
Error info: ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 7 Column: 1
Message: Hi All,

I am creating a report for shipment table where user will enter from date and to date. If the difference between from date and to date is more than 10 days, the query should run for from date+10 else the entered values. The date column in shipment table is insert_date.

I am trying below query

select * from shipment sh
where
case when
TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) -TO_DATE(:P_TO_DATE,:P_DATE_FORMAT)>10
THEN
TO_DATE(TO_CHAR(sh.insert_date, :P_DATE_FORMAT), :P_DATE_FORMAT)
between TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) AND TO_DATE(:P_TO_DATE,:P_DATE_FORMAT)+10
ELSE
TO_DATE(TO_CHAR(sh.insert_date, :P_DATE_FORMAT), :P_DATE_FORMAT)
between TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) AND TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT)
END;

I am getting missing keyword.

Please suggest.

Thanks,
Dev
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Case in Where Clause
Author: Dev Rishi Trehan, India
Date: Apr 03, 2019, 08:33, 141 days ago
Message: In the report parameters, the From Date and To Date are Strings.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Case in Where Clause
Author: Dev Rishi Trehan, India
Date: Apr 03, 2019, 08:50, 141 days ago
Message: Parameters as it should be---

select * from shipment sh
where
case when
TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) -TO_DATE(:P_TO_DATE,:P_DATE_FORMAT)>10
THEN
TO_DATE(TO_CHAR(sh.insert_date, :P_DATE_FORMAT), :P_DATE_FORMAT)
between TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) AND TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT)+10
ELSE
TO_DATE(TO_CHAR(sh.insert_date, :P_DATE_FORMAT), :P_DATE_FORMAT)
between TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) AND TO_DATE(:P_TO_DATE,:P_DATE_FORMAT)
END;
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Case in Where Clause
Author: Dev Rishi Trehan, India
Date: Apr 03, 2019, 10:08, 141 days ago
Message: It worked with this sql


select sh.SHIPMENT_GID,SH.INSERT_DATE from shipment sh
where (
((TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) -TO_DATE(:P_TO_DATE,:P_DATE_FORMAT) >10 ) and
(sh.insert_date between TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) and TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT)+10))
or (sh.insert_date between TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) and TO_DATE(:P_TO_DATE,:P_DATE_FORMAT))
);
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Case in Where Clause
Author: Philip Wisse, Netherlands
Date: Apr 03, 2019, 12:58, 141 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: How about:

select sh.SHIPMENT_GID,SH.INSERT_DATE from shipment sh
where
(
(
(
TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) - TO_DATE(:P_TO_DATE,:P_DATE_FORMAT) > 10
)
and
(
sh.insert_date between TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) and TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) + 10
)
)
or
(
(
TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) - TO_DATE(:P_TO_DATE,:P_DATE_FORMAT) <= 10
)
and
(
sh.insert_date between TO_DATE(:P_FROM_DATE,:P_DATE_FORMAT) and TO_DATE(:P_TO_DATE,:P_DATE_FORMAT)
)
)
);
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Case in Where Clause
Author: Joël Péran, France
Date: Apr 04, 2019, 08:06, 140 days ago
Message: Obviously, you did not understand the case syntax :

CASE
WHEN case1 THEN solution1
WHEN case2 THEN solution2
...
ELSE defaut_solution
END ...

You don't have this kind of syntax in your SQL as I can notice ...

Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Case in Where Clause
Author: Bruno Vroman, Belgium
Date: Apr 04, 2019, 16:28, 140 days ago
Message: Hi Dev,

although it is not allowed to write things like you did I think that this is quite interesting...
It can make sense to write something like
SELECT ...

FROM ...
WHERE
CASE WHEN condition1 THEN <a Boolean expression>
WHEN condition2 THEN <another one>
...
this would allow to write distinct complex conditions... And the "rewritten" way
SELECT ...

FROM ...
WHERE
condition1 AND <a Boolean expression>
OR condition2 AND <another one>
OR ...
is not exactly the same (for example: if condition1 is true but not <a Boolean expression> and if condition2 is true as well as <another one>; in this case first statement would not select the row but second statement would. So we should write more complex things like
SELECT ...

FROM ...
WHERE
condition1 AND <a Boolean expression>
OR NOT condition 1 AND condition2 AND <another one>
OR NOT condition1 AND NOT condition2 AND condition3 AND ...
Pfff, and I don't even take into account the "unknown" results...

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here