Home » SQL & PL/SQL » SQL & PL/SQL » Case function error (10g)
| Case function error [message #571784] |
Fri, 30 November 2012 07:46  |
 |
engsng
Messages: 5
Registered: November 2012
Location: Istanbul
|
Junior Member |
|
|
Hi all, Today I wanted to run my sql code but I got an error 'from keyword not found where expected'. The logic is when there is 'b' character in EMS_TYPE I want to group select elements by under the string called 'BACKBONE'. Under the backbone I want to group region name and city name and so on. The substr, instr functions work fine. The select, from statement below UNION also works but I think there is a problem in CASE function that I used. Here I copy the code:
Regards
SELECT
SUBSTR(INPUT_FILE_NAME,INSTR(INPUT_FILE_NAME,'_', '1', '1')+1,1 )=EMS_TYPE,
CASE WHEN EMS_TYPE = 'b' THEN 'BACKBONE' ELSE REGION_NAME END REGION_NAME,
CITY_NAME,
ALU_DWDM.BLOCK_1626_1686_CFG.DEVICE_NAME,
ALU_DWDM.BLOCK_1626_1686_CFG.RACK_SUBRACK_SLOT_BOARD,
ALU_DWDM.BLOCK_1626_1686_CFG.PORT_MOD_FREQ_BLOCK
FROM GLOBAL.IL_BOLGE,ALU_DWDM.BLOCK_1626_1686_CFG, ALU_DWDM.ANALOG
WHERE substr(device_name,1,3)=IL_KODU AND SUBSTR(DEVICE_NAME,instr(DEVICE_NAME,'_','1','1')+1,instr(DEVICE_NAME,'_','1','2')-5)=CITY_NAME
UNION
SELECT
REGION_NAME,
CITY_NAME,
ALU_DWDM.BLOCK_1626_1686_CFG.DEVICE_NAME,
ALU_DWDM.BLOCK_1626_1686_CFG.RACK_SUBRACK_SLOT_BOARD,
ALU_DWDM.BLOCK_1626_1686_CFG.PORT_MOD_FREQ_BLOCK
FROM GLOBAL.IL_BOLGE, ALU_DWDM.BLOCK_1626_1686_CFG
WHERE substr(device_name,1,3)=IL_KODU AND SUBSTR(DEVICE_NAME,instr(DEVICE_NAME,'_','1','1')+1,instr(DEVICE_NAME,'_','1','2')-5)=CITY_NAME
|
|
|
|
| Re: Case function error [message #571785 is a reply to message #571784] |
Fri, 30 November 2012 07:52  |
Manoj.Gupta.91
Messages: 156
Registered: March 2008
Location: Delhi
|
Senior Member |
|
|
Hi,
Try This :
CASE
WHEN SUBSTR(INPUT_FILE_NAME,INSTR(INPUT_FILE_NAME,'_', '1', '1')+1,1 ) = 'b' THEN
'BACKBONE'
ELSE REGION_NAME
END
You are using assignmet which is not correct in SQL
SUBSTR(INPUT_FILE_NAME,INSTR(INPUT_FILE_NAME,'_', '1', '1')+1,1 )=EMS_TYPE
Thanks & Regards
Manoj
|
|
|
|
|
|
| Re: Case function error [message #571787 is a reply to message #571784] |
Fri, 30 November 2012 08:03 |
 |
Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If you do what I say, the error is obvious:
SQL> SELECT
2
3 SUBSTR(INPUT_FILE_NAME,INSTR(INPUT_FILE_NAME,'_', '1', '1')+1,1 )=EMS_TYPE,
4 CASE WHEN EMS_TYPE = 'b' THEN 'BACKBONE' ELSE REGION_NAME END REGION_NAME,
5 CITY_NAME,
6 ALU_DWDM.BLOCK_1626_1686_CFG.DEVICE_NAME,
7 ALU_DWDM.BLOCK_1626_1686_CFG.RACK_SUBRACK_SLOT_BOARD,
8 ALU_DWDM.BLOCK_1626_1686_CFG.PORT_MOD_FREQ_BLOCK
9
10 FROM GLOBAL.IL_BOLGE,ALU_DWDM.BLOCK_1626_1686_CFG, ALU_DWDM.ANALOG
11 WHERE substr(device_name,1,3)=IL_KODU AND SUBSTR(DEVICE_NAME,
instr(DEVICE_NAME,'_','1','1')+1,instr(DEVICE_NAME,'_','1','2')-5)=CITY_NAME
12
13 UNION
14
15 SELECT
16 REGION_NAME,
17 CITY_NAME,
18 ALU_DWDM.BLOCK_1626_1686_CFG.DEVICE_NAME,
19 ALU_DWDM.BLOCK_1626_1686_CFG.RACK_SUBRACK_SLOT_BOARD,
20 ALU_DWDM.BLOCK_1626_1686_CFG.PORT_MOD_FREQ_BLOCK
21
22
23 FROM GLOBAL.IL_BOLGE, ALU_DWDM.BLOCK_1626_1686_CFG
24 WHERE substr(device_name,1,3)=IL_KODU AND SUBSTR(DEVICE_NAME,
instr(DEVICE_NAME,'_','1','1')+1,instr(DEVICE_NAME,'_','1','2')-5)=CITY_NAME
25
26 /
SUBSTR(INPUT_FILE_NAME,INSTR(INPUT_FILE_NAME,'_', '1', '1')+1,1 )=EMS_TYPE,
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Thu May 23 22:14:02 CDT 2013
Total time taken to generate the page: 0.15055 seconds
|
] 
Members
Search
Help
Register
Login
Home
