question

innap9999 avatar image
innap9999 asked

Where error is?

I need execute 3 different tables, depending of the day of week, so created: SELECT * FROM DUAL WHERE substr(to_char(sysdate,'DAY'),1,3) = ( CASE WHEN 'Mon' THEN (select * from _MON) WHEN 'FRI' THEN (select * from _FRI) ELSE (select * from _OUT) END ) and getting invalid relational operator error. Where my in case statement is? Thanks
sqloraclecase
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
First problem is that the **substr(to_char(sysdate,'DAY'),1,3)** returns the first 3 letters of the day name spelled in upper case. This would not be an issue in SQL Server installed with default case insensitive collation, but this is a problem in Oracle because the string comparisons in Oracle are always case sensitive. This means that you should use, for example, **MON**, not **Mon** etc. To be safe it would be better to apply upper prior to calling substring. In order to understand the major problem with the statement in question, just deduce what happens if it is executed on, say, Friday. The statement then resolves to this one: select * from dual where (select * from _Fri) It is easy to see that there are 2 reasons why this statement is incorrect. First, it does not resolve to anything that can possibly execute, and second, it is not possible to have the table names in Oracle which begin with underscore unless such table name is quoted. This practice is ill advised, but sadly, it is possible, much like it is possible to come up with phony table names in SQL Server as well, provided that they are quoted with brackets. In SQL Server there would be 2 ways to address this problem: with static and dynamic script. In Oracle, I am afraid that the static method might not be possible, but the dynamic script is easy enough. Please try this: execute immediate 'select * from '|| case when upper(substr(to_char(sysdate,'DAY'), 1, 3)) = 'MON' then '"_Mon"' when upper(substr(to_char(sysdate,'DAY'), 1, 3)) = 'FRI' then '"_Fri"' else '"_Out"' end; Please note that the table names are quoted, this is the only way to query the tables with improper names. Hope this helps. Oleg
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.