Home » SQL & PL/SQL » SQL & PL/SQL » Case function error (10g)
Case function error [message #571784] Fri, 30 November 2012 07:46 Go to next message
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 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
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 #571786 is a reply to message #571784] Fri, 30 November 2012 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Sun, 04 November 2012 10:26
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel


Re: Case function error [message #571787 is a reply to message #571784] Fri, 30 November 2012 08:03 Go to previous message
Michel Cadot
Messages: 59143
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
Previous Topic: Displaying N number of rows from a large result set
Next Topic: Unable to send Email through Google-mail to private domain
Goto Forum:
  


Current Time: Fri Sep 19 15:33:31 CDT 2014

Total time taken to generate the page: 0.13648 seconds