Forum Stats

  • 3,759,232 Users
  • 2,251,515 Discussions
  • 7,870,547 Comments

Discussions

How to resolve this errror?

jdev1
jdev1 Member Posts: 222 Green Ribbon
select rows.a_kod as a_kod,    
 rows.did1_nr as did1_nr, rows.adm_var1 as adm_var1,    
 rows.did_dat1 as did_dat1,   
 count(1) as eil.skaicius1 from     
 ( select Outer.a_kod, Outer.did1_nr,     
 Outer.adm_var1,     Outer.did_dat1    
 from (select ide_vidp as ide_vidp2,        
 zu0_kod as a_kod, did1_nr as did1_nr,       
 ADM_VAR1 as ADM_VAR1, did_dat1 as did_dat1      
 from db.vidp1     where did_dat1 >= '" & sFilterDate & "'    ) 
 Outer    left outer join db.vidp1 Inner on Outer.ide_vidp2 = 
 Inner.ide_vidp)  ) rows  group by a_kod, did1_nr,   
 adm_var1, did_dat1


Tagged:

Answers

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi @jdev1 ,

    check this out:

     SELECT rws.a_kod    AS a_kod,
         rws.did1_nr   AS did1_nr,
         rws.adm_var1  AS adm_var1,
         rws.did_dat1  AS did_dat1,
         COUNT (1)    AS skaicius1
      FROM (SELECT tab1.a_kod,
             tab1.did1_nr,
             tab1.adm_var1,
             tab1.did_dat1
          FROM (SELECT db.vidp1.ide_vidp  AS ide_vidp2,
                 db.vidp1.zu0_kod   AS a_kod,
                 db.vidp1.did1_nr   AS did1_nr,
                 db.vidp1.adm_var1  AS adm_var1,
                 db.vidp1.did_dat1  AS did_dat1
              FROM db.vidp1
              WHERE db.vidp1.did_dat1 >= :filterdate) tab1
             LEFT OUTER JOIN db.vidp1 tab2
              ON tab1.ide_vidp2 = tab2.ide_vidp) rws
    GROUP BY rws.a_kod,
         rws.did1_nr,
         rws.adm_var1,
         rws.did_dat1
    

    Outer, inner and rows are reserved key words in oracle. Thats why I replaced these aliases with tab1, tab2 and rws. Then you should use a bind variable for the filterdate. Last but not least the alias for the count contained a point which causes the orignal error.

    Best regards

    Jan

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond

    In addition take a look at in-line view rws:

             SELECT  tab1.a_kod,
                     tab1.did1_nr,
                     tab1.adm_var1,
                     tab1.did_dat1
               FROM  (
                      SELECT  db.vidp1.ide_vidp AS ide_vidp2,
                              db.vidp1.zu0_kod  AS a_kod,
                              db.vidp1.did1_nr  AS did1_nr,
                              db.vidp1.adm_var1 AS adm_var1,
                              db.vidp1.did_dat1 AS did_dat1
                        FROM  db.vidp1
                        WHERE db.vidp1.did_dat1 >= :filterdate
                     ) tab1
                     LEFT OUTER JOIN db.vidp1 tab2
                       ON tab1.ide_vidp2 = tab2.ide_vidp
    

    Select list contains TAB1 columns only. Left outer join returns all TAB1 rows regardless if there is a match in TAB2 or not. Therefore that left join is just a waste of time.

    Sėkmės.

    SY.

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi Solomon,

    I don't agree with you: The left outer join may have an effect on the count.

    Best regards

    Jan

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond
    edited Sep 5, 2021 12:49PM

    Ah, you're right I missed count in main query. So question is what type of relationship is

    tab1.ide_vidp2 = tab2.ide_vidp
    

    Left join is needed only if is one to many.

    SY.

  • User_H3J7U
    User_H3J7U Member Posts: 485 Bronze Trophy

    @Jan Gorkow Outer, inner and rows are reserved key words in oracle.

    select keyword, reserved, res_semi
    from v$reserved_words where keyword in ('OUTER', 'INNER', 'AND', 'ROWS');
    
    KEYWORD              R R
    -------------------- - -
    ROWS                 N Y
    AND                  Y N
    INNER                N N
    OUTER                N N
    


    Jan Gorkow
  • jdev1
    jdev1 Member Posts: 222 Green Ribbon

    Hello,

    But my table column name is eil.skaicius, I am getting error "." I have tried to make like this: "eil.skaicius",

    'eil.skaicius', eil.skaicius, `eil.skaicius`. With these all I am getting same error. How to fix it?


    SELECT rws.zu0_kod AS zu0_kod,     
       rws.did_nr  AS did_nr,
       rws.adm_var AS adm_var,
       rws.did_dat AS did_dat
       ,COUNT (1)  AS eil.skaicius
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond

    Remove eil. Alias definition can't have qualifier. We qualify references not definitions:

    SELECT rws.zu0_kod AS zu0_kod,     
       rws.did_nr  AS did_nr,
       rws.adm_var AS adm_var,
       rws.did_dat AS did_dat
       ,COUNT (1)  AS skaicius
    

    SY.

  • jdev1
    jdev1 Member Posts: 222 Green Ribbon

    I am having empty table with this:

    SELECT rws.zu0_kod AS zu0_kod,     
       rws.did_nr  AS did_nr,
       rws.adm_var AS adm_var,
       rws.did_dat AS did_dat
       ,COUNT (1)  AS ""Eil.skaicius""
     FROM (SELECT tab1.zu0_kod as zu0_kod,
         tab1.did_nr as did_nr,
         tab1.adm_var as adm_var,
         tab1.did_dat as did_dat
       FROM (SELECT id_vidp AS id_vidp2,
          zu0_kod  AS zu0_kod,
           did_nr AS did_nr,
          adm_var AS adm_var,
          did_dat AS did_dat
         FROM public.vidp
        WHERE did_dat >= '" & sFilterDate & "') tab1
         LEFT OUTER JOIN public.vidp1 tab2
         ON tab1.id_vidp2 = tab2.id_vidp) rws
    GROUP BY
     rws.zu0_kod,
       rws.did_nr,
       rws.adm_var,
       rws.did_dat
    

    After removing filter:

    Why it happens?