CODEWITHSUNDEEP
plsqloracle10g
Denys S.
Denys S.

Reputation: 6515

Conditional pl/sql output

I need to get a value 1 or 0 from DB query which in its turn should do next:

So far I have next thing:

select instr(field, 'literal') from table_name where trunc(time) = trunc(sysdate)

which returns 1 if field from table table_name contains 'literal' (where clause checks if truncated time in table_name is equal to truncated system time).

What I can't get is how I can:

Thanks in advance.

P.S.: Please comment on the question if something is left vague.

Upvotes: 1

Views: 360

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

It sounds like you want a CASE statement. It would be helpful if you posted the DDL to create the table, some DML to populate the data, and the expected output. You seem to have conflicting requirements about what you want returned if the query is run between 9 and 10:00 am. You say "if ... query is executed in certain time period ... it should return 0, else 1" initially but then later you say "if its from 9:00 AM to 10:00 AM always return 1"). My guess is that you want something like

SELECT MAX(zero_or_one)
  FROM (
      SELECT (CASE WHEN to_char( sysdate, 'HH24' ) = '09'
                     THEN 1
                   WHEN instr( column_name, 'literal' ) > 0
                     THEN 1
                   ELSE 0
                END) zero_or_one
        FROM table_name
       WHERE trunc(date_column) = trunc(sysdate)
        )

Upvotes: 3

Related Questions