Home » SQL & PL/SQL » SQL & PL/SQL » CASE STATEMENT
| CASE STATEMENT [message #7773] |
Mon, 07 July 2003 21:26  |
HENNAQUICK
Messages: 1
Registered: July 2003
|
Junior Member |
|
|
I am constructuing to following statement to insert into a table from a view of that table. The table to be inserted into is CRIMINAL_PL and its view is ALTERNATIVE_PL.
Basically, this table of penal laws needs to be amended to include "attempts" of crimes. An attempt of a crime is one weight level lower e.g. An attempt of a Class B felony is Class C except an attempt of a Class E felony becomes a Class A misdemeanor. Attempts have '110/' in front of the numeric statute.
Here's the code
INSERT INTO CRIMINAL_PL( STATUTE_SUBSEC, NAME, DEGREE, WEIGHT, CLASS)
SELECT '110/'||STATUTE_SUBSEC, 'Attempted '||NAME, DEGREE,WEIGHT,
Case CLASS
WHEN 'A-I' THEN CLASS := 'B';
WHEN 'B' THEN CLASS := 'C';
WHEN 'C' THEN CLASS := 'D';
WHEN 'D' THEN CLASS := 'E';
WHEN 'E' THEN CLASS := 'A' AND WEIGHT :='Misdemeanor';
ELSE CLASS
END CASE;
FROM ALTERNATIVE_PL
WHERE SUBSTR(STATUTE_SUBSEC,1,3) NOT LIKE '110'
AND CLASS LIKE 'Felony';
I'm testing this in SQL+ and pl/sql developer and I get the following error message:
'Missing keyword' (in pl/sql developer)
AND IN SQL+ I get these error messages..
SQL> WHEN 'B' THEN CLASS := 'C';
SP2-0734: unknown command beginning "WHEN 'B' T..." - rest of line ignored.
SQL> WHEN 'C' THEN CLASS := 'D';
SP2-0734: unknown command beginning "WHEN 'C' T..." - rest of line ignored.
SQL> WHEN 'D' THEN CLASS := 'E';
SP2-0734: unknown command beginning "WHEN 'D' T..." - rest of line ignored.
SQL> WHEN 'E' THEN CLASS := 'A' AND WEIGHT :='Misdemeanor';
SP2-0734: unknown command beginning "WHEN 'E' T..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> ELSE CLASS
SP2-0042: unknown command "ELSE CLASS" - rest of line ignored.
SQL> END CASE;
SP2-0042: unknown command "END CASE" - rest of line ignored.
SQL> FROM NYSPCR.ALTERNATIVE_PL
SP2-0734: unknown command beginning "FROM NYSPC..." - rest of line ignored.
SQL> WHERE SUBSTR(STATUTE_SUBSEC,1,3) NOT LIKE '110'
SP2-0734: unknown command beginning "WHERE SUBS..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> AND CLASS LIKE 'Felony';
Is CLASS a reserved word? If so, how was I able to name a column 'CLASS'? Why am I getting command errors?
|
|
|
|
| Re: CASE STATEMENT [message #7774 is a reply to message #7773] |
Mon, 07 July 2003 23:23  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I will assume you are on 9i since you are using the simple CASE expression (as opposed to the searched expression which was the only variation available in 8i).
insert ...
select ...,
case class
when 'a-i' then 'b'
when 'b' then 'c'
when 'c' then 'd'
when 'd' then 'e'
when 'e' then 'a'
else class
end case
from alternative_pl
where substr(statute_subsec, 1, 3) <> '110'
and class = 'felony';
I'm not sure what you're trying to do with the 'Misdemeanor' logic. You bring the WEIGHT column straight across and then it appears you are trying to apply some conditional test to it in the middle of the case statement for the CLASS column. This will need some clarification from you...
Also, if you are not using wildcards with the LIKE operators, lose the LIKE and just use straight (in)equality tests.
|
|
|
|
| Re: CASE STATEMENT [message #7803 is a reply to message #7773] |
Wed, 09 July 2003 13:42 |
 |
Barbara Boehmer
Messages: 9086
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You could to this with either decode or case. Please see the examples below.
SQL> -- test data:
SQL> SELECT * FROM criminal_pl
2 /
STATUTE_SU NAME DEGREE WEIGHT CLA
---------- ------------------------------ ---------- ----------- ---
1 Class A-I Felony Felony A-I
2 Class B Felony Felony B
3 Class C Felony Felony C
4 Class D Felony Felony D
5 Class E Felony Felony E
SQL> SELECT * FROM alternative_pl
2 /
STATUTE_SU NAME DEGREE WEIGHT CLA
---------- ------------------------------ ---------- ----------- ---
1 Class A-I Felony Felony A-I
2 Class B Felony Felony B
3 Class C Felony Felony C
4 Class D Felony Felony D
5 Class E Felony Felony E
SQL> -- insert using decode:
SQL> INSERT INTO criminal_pl (statute_subsec, name, degree, weight, class)
2 SELECT '110/' || statute_subsec,
3 'Attempted ' || name,
4 degree,
5 DECODE (class, 'E', 'Misdemeanor', weight),
6 DECODE (class,
7 'A-I', 'B',
8 'B' , 'C',
9 'C' , 'D',
10 'D' , 'E',
11 'E' , 'A')
12 FROM alternative_pl
13 WHERE SUBSTR (statute_subsec, 1, 3) <> '110'
14 AND weight = 'Felony'
15 /
5 rows created.
SQL> -- results:
SQL> SELECT * FROM criminal_pl
2 /
STATUTE_SU NAME DEGREE WEIGHT CLA
---------- ------------------------------ ---------- ----------- ---
1 Class A-I Felony Felony A-I
2 Class B Felony Felony B
3 Class C Felony Felony C
4 Class D Felony Felony D
5 Class E Felony Felony E
110/1 Attempted Class A-I Felony Felony B
110/2 Attempted Class B Felony Felony C
110/3 Attempted Class C Felony Felony D
110/4 Attempted Class D Felony Felony E
110/5 Attempted Class E Felony Misdemeanor A
10 rows selected.
SQL> -- reset for test of insert using case:
SQL> ROLLBACK
2 /
Rollback complete.
SQL> -- insert using case:
SQL> INSERT INTO criminal_pl (statute_subsec, name, degree, weight, class)
2 SELECT '110/' || statute_subsec,
3 'Attempted ' || name,
4 degree,
5 CASE WHEN class = 'E' THEN 'Misdemeanor' ELSE weight END,
6 CASE
7 WHEN class = 'A-I' THEN 'B'
8 WHEN class = 'B' THEN 'C'
9 WHEN class = 'C' THEN 'D'
10 WHEN class = 'D' THEN 'E'
11 WHEN class = 'E' THEN 'A'
12 END
13 FROM alternative_pl
14 WHERE SUBSTR (statute_subsec, 1, 3) <> '110'
15 AND weight = 'Felony'
16 /
5 rows created.
SQL> -- results:
SQL> SELECT * FROM criminal_pl
2 /
STATUTE_SU NAME DEGREE WEIGHT CLA
---------- ------------------------------ ---------- ----------- ---
1 Class A-I Felony Felony A-I
2 Class B Felony Felony B
3 Class C Felony Felony C
4 Class D Felony Felony D
5 Class E Felony Felony E
110/1 Attempted Class A-I Felony Felony B
110/2 Attempted Class B Felony Felony C
110/3 Attempted Class C Felony Felony D
110/4 Attempted Class D Felony Felony E
110/5 Attempted Class E Felony Misdemeanor A
10 rows selected.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 12:26:54 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
