I'm having a little trouble with this sql view.
CREATE OR REPLACE VIEW view_themed_booking AS
SELECT tb.*,
CASE
WHEN (tb.themed_party_size % 2) = 0 THEN
(tb.themed_party_size-2)/2
ELSE ((tb.themed_party_size-2)/2) + 0.5
END themed_tables
FROM themed_booking tb;
Can anyone help me here? I'm trying to add a column to the end of the view that the natural number result of (S-2)/2
where S
is the themed_party_size
.
When i say natural number result i mean like round up the answers that end in .5 so if S=7
the answer would be 3
and not 2.5
.
The error I get when I try and run the above code is
Error starting at line 1 in command:
CREATE OR REPLACE VIEW view_themed_booking AS
SELECT tb.*,
CASE WHEN (tb.themed_party_size % 2) = 0
THEN (tb.themed_party_size-2)/2
ELSE ((tb.themed_party_size-2)/2) + 0.5
END themed_tables
FROM themed_booking tb
Error at Command Line:3 Column:34
Error report:
SQL Error: ORA-00911: invalid character
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than
letters and numbers. $#_ are also allowed after the first
character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes
(q'#...#') cannot use spaces, tabs, or carriage returns as
delimiters. For all other contexts, consult the SQL Language
Reference Manual.
*Action:
If it makes a difference I am using sqldeveloper connected to an oracle server so I can use PL/SQL.
I think you can simplify with the CEIL()
or ROUND()
function:
CREATE OR REPLACE VIEW view_themed_booking AS
SELECT tb.*,
ROUND((tb.themed_party_size-2)/2) AS themed_tables
FROM themed_booking tb;
Not sure why you get that error. Perhaps it's the %
operator that is not available in Oracle. This links suggests so: Fundamentals of PL/SQL. There seems to be a MOD()
function though.