2 Replies Latest reply: Feb 7, 2014 1:19 PM by Gary Graham-Oracle RSS

    '/' character. Wrong interpretation by SQL Developer.

    Rob Naastepad

      Hello,

       

      The following query executes well in SQL Plus:

       

         select distinct  

                t09.vpe_vpe_id  

         ,      t09.vdg_id   

         ,      t09.vpe_usg_id_start  

         ,      t09.vpe_usg_id_eind  

         ,      t09.persoonsnummer  

         ,      t09.polisnummer  

         ,      t09.dekkingscode  

         ,      t09.verzekeringspakketnummer  

         ,      t09.ingangsdatum_verzekerde_period         

         ,      t09.einddatum_verzekerde_periode  

         ,      comp_pre.src_pre_functions_pck.bep_jaarpremie_func(t09.verzekeringspakketnummer  

                                                                  ,t09.dekkingscode  

                                                                  ,t09.ingangsdatum_verzekerde_period  

                                                                  ,case when to_char(t09.einddatum_verzekerde_periode,'YYYY')<>to_char(t09.ingangsdatum_verzekerde_period,'YYYY')   

                                                                        then to_date('31-12-'||to_char(t09.ingangsdatum_verzekerde_period,'YYYY'),'dd-mm-yyyy')   

                                                                        else t09.einddatum_verzekerde_periode  

                                                                   end  

                                                                  ,t09.vao_id  

                                                                  ,t09.jaarpremie_vastgestelde_dekkin  

                                                                  )

                /

                case when t09.soort_oplegperiode = 'WKD'

                     then case when nvl(t09.jaarpremie_vastgestelde_dekkin,0) = 0

                               then comp_dm.dm_aantal_werkdagen_fnc(t09.ingangsdatum_verzekerde_period, case when to_char(t09.reken_einddatum,'YYYY')<>to_char(t09.ingangsdatum_verzekerde_period,'YYYY')   

                                                                                                             then to_date('31-12-'||to_char(t09.ingangsdatum_verzekerde_period,'YYYY'),'dd-mm-yyyy')   

                                                                                                             else t09.reken_einddatum  

                                                                                                        end

                                                                   )

                               else comp_dm.dm_aantal_werkdagen_fnc(trunc(t09.ingangsdatum_verzekerde_period,'YYYY'), add_months(trunc(t09.ingangsdatum_verzekerde_period,'YYYY'),12)-1)

                          end

                     else case when nvl(t09.jaarpremie_vastgestelde_dekkin,0) = 0

                               then comp_dm.dm_aos_aantal_premiedagen_fnc(t09.ingangsdatum_verzekerde_period, case when to_char(t09.reken_einddatum,'YYYY')<>to_char(t09.ingangsdatum_verzekerde_period,'YYYY')   

                                                                                                                   then to_date('31-12-'||to_char(t09.ingangsdatum_verzekerde_period,'YYYY'),'dd-mm-yyyy')   

                                                                                                                   else t09.reken_einddatum  

                                                                                                              end

                                                                         )

                               else comp_dm.dm_aos_aantal_premiedagen_fnc(trunc(t09.ingangsdatum_verzekerde_period,'YYYY'), add_months(trunc(t09.ingangsdatum_verzekerde_period,'YYYY'),12)-1)

                          end

                end dagpremie         

         ,      t09.min_prem_gao  

         ,      t09.max_prem_gao  

         ,      nvl(t09.salarisdeel,0) + nvl(t09.provisiedeel,0) jaarsalaris  

         ,      t09.sk_verzekerde_perioden_incl_u  

         ,      t09.sa_opc  

         ,      t09.sa_credate  

         ,      t09.sa_moddate  

         ,      t09.sa_runid  

         ,      t09.sa_source  

         ,      t09.wk_curr  

         ,      t09.wk_root  

         ,      t09.wk_psn  

         ,      t09.wk_dkg  

         ,      t09.wk_pao  

         ,      t09.wk_ivd_vpe  

         ,      t09.wk_evd_vpe  

         from   comp_stg.stg_h_vpe_in_usg_prc09                  t09  

         ,      comp_stg.stg_wia_gevallen                        wgl  

         where  nvl(t09.salarisdeel,0) + nvl(t09.provisiedeel,0) >= nvl(t09.min_prem_gao,0)  

         and    t09.persoonsnummer                               = wgl.persoonsnummer  

         and    t09.ingangsdatum_verzekerde_period               between nvl(wgl.ingangsdatum_wia_geval,t09.ingangsdatum_verzekerde_period)  

                                                                 and     nvl(wgl.einddatum_wia_geval   ,t09.ingangsdatum_verzekerde_period)

         ;

       

      On line 22 of this query there is a '/' sign to be able to divide the result of the function by that from the case statement. As said in SQL-Plus this query executes fine but in SQL Developer 4.0.0.13 I get ORA-000923: FROM keyword not found where expected.

       

      Database is 10.2.0.4.

       

      When I change the '/' sign into '*' the query executes fine in SQL Developer also, but that is not what I want of course.

       

      Just thought to share this with you.

       

      Regards,
      Rob