Link to home
Get AccessLog in
Avatar of Kalpesh_Jajoo
Kalpesh_Jajoo

asked on

Special Query in Oracle 10 sqlplus

Hi,

I am using oracle 10 and sqlplus. In one of my tables there is a field in which either "0" or "1" is stored. Now is it possible to construct a query that if the value of that field is "0" then query should return "1" and if the value of the field is "1" then the query should return "0".
Avatar of Om Prakash
Om Prakash
Flag of India image

you can use case statement for this

SELECT CASE
    WHEN field_name LIKE '1' THEN '0'
    WHEN field_name LIKE '0' THEN '1'
  END
FROM table_name;
ASKER CERTIFIED SOLUTION
Avatar of Om Prakash
Om Prakash
Flag of India image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Kalpesh_Jajoo
Kalpesh_Jajoo

ASKER

Thanks om prakash,

I am actually quit a newbie in Oracle and following is my existing query:

set colsep ',' pagesize 0 trimspool on feedback off echo off linesize 30000
spool final_time.csv
SELECT ip.id|| ',' ||ip.last_name|| ',' ||ip.first_name|| ',' ||ipd.loginuid|| ',' ||am.default_shell|| ',' ||to_char(ipd.start_date,'MM-DD-YY')|| ',' ||to_char(ipd.start_date,'HH24:MM:SS')|| ',' ||to_char(ipd.expiration_date,'MM-DD-YY')|| ',' ||to_char(ipd.expiration_date,'HH24:MM:SS')|| ',' ||gdd.name|| ',' ||sd.name|| ',' ||atk.serial_number|| ',' ||atk.token_code_length|| ',' ||atk.token_code_interval|| ',' ||to_char(atk.token_start_date,'MM-DD-YYYY')|| ',' ||to_char(atk.token_start_date,'HH24:MM:SS')|| ',' ||to_char(atk.token_shutdown_date,'MM-DD-YYYY')|| ',' ||to_char(atk.token_shutdown_date,'HH24:MM:SS')|| ',' ||to_char(ato.last_login_date,'MM-DD-YYYY')|| ',' ||to_char(ato.last_login_date,'HH24:MM:SS')|| ',' ||atk.token_type|| ',' ||atk.is_hex_display|| ',' ||atk.is_enabled|| ',' ||atk.is_new_pin_mode|| ',' ||ip.id|| ',' ||atk.is_next_token_code_mode|| ',' ||atk.bad_token_code_count|| ',' ||to_char(atk.pin_modified_date,'MM-DD-YYYY')|| ',' ||to_char(atk.pin_modified_date,'HH24:MM:SS')|| ',' ||to_char(atk.enable_date,'MM-DD-YYYY')|| ',' ||to_char(atk.enable_date,'HH24:MM:SS')|| ',' ||atk.softid_protect_method|| ',' ||atk.is_softid_deployed|| ',' ||atk.softid_count
             FROM rsa_rep.ims_principal ip,  
              rsa_rep.ims_principal_data ipd,  
                  rsa_rep.am_token atk,  
                  rsa_batchrep.am_token_oob ato,  
                  rsa_rep.ims_identity_source isrc,  
              rsa_rep.ims_security_domain sd,
                  rsa_rep.ims_group_data gdd,
                  rsa_rep.am_principal am,
                  rsa_rep.ims_principal_group gds
            WHERE ipd.id = atk.principal_id  
              AND ipd.identity_src_id=isrc.id  
              AND atk.id = ato.am_token_id  
              AND ipd.owner_id = sd.id
              AND gdd.id = gds.group_id
              AND ipd.id = gds.principal_id
              AND ipd.id = am.id
            AND ipd.identity_src_key = ip.id(+);
spool off

And the field i want to apply this condition in the query is "atk.is_new_pin_mode". So how do i modify my existing query to solve my problem...
please check the modified code
set colsep ',' pagesize 0 trimspool on feedback off echo off linesize 30000
spool final_time.csv
SELECT ip.id|| ',' ||ip.last_name|| ',' ||ip.first_name|| ',' ||ipd.loginuid|| ',' ||am.default_shell|| ',' ||to_char(ipd.start_date,'MM-DD-YY')|| ',' ||to_char(ipd.start_date,'HH24:MM:SS')|| ',' ||to_char(ipd.expiration_date,'MM-DD-YY')|| ',' ||to_char(ipd.expiration_date,'HH24:MM:SS')|| ',' ||gdd.name|| ',' ||sd.name|| ',' ||atk.serial_number|| ',' ||atk.token_code_length|| ',' ||atk.token_code_interval|| ',' ||to_char(atk.token_start_date,'MM-DD-YYYY')|| ',' ||to_char(atk.token_start_date,'HH24:MM:SS')|| ',' ||to_char(atk.token_shutdown_date,'MM-DD-YYYY')|| ',' ||to_char(atk.token_shutdown_date,'HH24:MM:SS')|| ',' ||to_char(ato.last_login_date,'MM-DD-YYYY')|| ',' ||to_char(ato.last_login_date,'HH24:MM:SS')|| ',' ||atk.token_type|| ',' ||atk.is_hex_display|| ',' ||atk.is_enabled|| ',' || SELECT CASE     WHEN atk.is_new_pin_mode = '1' THEN '0' WHEN atk.is_new_pin_mode= '0' THEN '1' END || ',' ||ip.id|| ',' ||atk.is_next_token_code_mode|| ',' ||atk.bad_token_code_count|| ',' ||to_char(atk.pin_modified_date,'MM-DD-YYYY')|| ',' ||to_char(atk.pin_modified_date,'HH24:MM:SS')|| ',' ||to_char(atk.enable_date,'MM-DD-YYYY')|| ',' ||to_char(atk.enable_date,'HH24:MM:SS')|| ',' ||atk.softid_protect_method|| ',' ||atk.is_softid_deployed|| ',' ||atk.softid_count
             FROM rsa_rep.ims_principal ip,  
              rsa_rep.ims_principal_data ipd,   
                  rsa_rep.am_token atk,   
                  rsa_batchrep.am_token_oob ato,   
                  rsa_rep.ims_identity_source isrc,   
              rsa_rep.ims_security_domain sd,
                  rsa_rep.ims_group_data gdd,
                  rsa_rep.am_principal am,
                  rsa_rep.ims_principal_group gds
            WHERE ipd.id = atk.principal_id   
              AND ipd.identity_src_id=isrc.id   
              AND atk.id = ato.am_token_id   
              AND ipd.owner_id = sd.id
              AND gdd.id = gds.group_id
              AND ipd.id = gds.principal_id
              AND ipd.id = am.id
            AND ipd.identity_src_key = ip.id(+);
spool off

Open in new window

Hi Om prakash,

I ran your modified query and got the following error:

ERROR at line 1:
ORA-00936: missing expression

So i did some googleing and modified the query as follows:

set colsep ',' pagesize 0 trimspool on feedback off echo off linesize 30000
spool final_time.csv
SELECT ip.id|| ',' ||ip.last_name|| ',' ||ip.first_name|| ',' ||ipd.loginuid|| ',' ||am.default_shell|| ',' ||to_char(ipd.start_date,'MM-DD-YY')|| ',' ||to_char(ipd.start_date,'HH24:MM:SS')|| ',' ||to_char(ipd.expiration_date,'MM-DD-YY')|| ',' ||to_char(ipd.expiration_date,'HH24:MM:SS')|| ',' ||gdd.name|| ',' ||sd.name|| ',' ||atk.serial_number|| ',' ||(case atk.is_new_pin_mode when 1 then 0 when 0 then 1 end)|| ',' ||atk.token_code_length|| ',' ||atk.token_code_interval|| ',' ||to_char(atk.token_start_date,'MM-DD-YYYY')|| ',' ||to_char(atk.token_start_date,'HH24:MM:SS')|| ',' ||to_char(atk.token_shutdown_date,'MM-DD-YYYY')|| ',' ||to_char(atk.token_shutdown_date,'HH24:MM:SS')|| ',' ||to_char(ato.last_login_date,'MM-DD-YYYY')|| ',' ||to_char(ato.last_login_date,'HH24:MM:SS')|| ',' ||atk.token_type|| ',' ||atk.is_hex_display|| ',' ||atk.is_enabled|| ',' ||atk.is_new_pin_mode|| ',' ||ip.id|| ',' ||atk.is_next_token_code_mode|| ',' ||atk.bad_token_code_count|| ',' ||to_char(atk.pin_modified_date,'MM-DD-YYYY')|| ',' ||to_char(atk.pin_modified_date,'HH24:MM:SS')|| ',' ||to_char(atk.enable_date,'MM-DD-YYYY')|| ',' ||to_char(atk.enable_date,'HH24:MM:SS')|| ',' ||atk.softid_protect_method|| ',' ||atk.is_softid_deployed|| ',' ||atk.softid_count
             FROM rsa_rep.ims_principal ip,  
              rsa_rep.ims_principal_data ipd,  
                  rsa_rep.am_token atk,  
                  rsa_batchrep.am_token_oob ato,  
                  rsa_rep.ims_identity_source isrc,  
              rsa_rep.ims_security_domain sd,
                  rsa_rep.ims_group_data gdd,
                  rsa_rep.am_principal am,
                  rsa_rep.ims_principal_group gds
            WHERE ipd.id = atk.principal_id  
              AND ipd.identity_src_id=isrc.id  
              AND atk.id = ato.am_token_id  
              AND ipd.owner_id = sd.id
              AND gdd.id = gds.group_id
              AND ipd.id = gds.principal_id
              AND ipd.id = am.id
            AND ipd.identity_src_key = ip.id(+);
spool off

The above query executed just fine.

Thanks for pointing me in right direction..