Oracle SQL Developer suggesting query change that doesn't work



  • I may not be a SQL guru and I know that I do not know Oracle specific SQL very well, but I'm fairly sure that the suggestion it is giving won't work.

     

    Actually, I already know it won't work and confirmed it by trying and getting "ORA-00934: group function is not allowed here"



  • SQL Developer's suggested GROUP BY clauses are pretty rubbish.

    Occasionally it gets it right and you save like 2 seconds by autocompleting it, but then it stops saving you time because it puts it in the wrong place and you have to move it.



  • @loopback0 said:

    SQL Developer's suggested GROUP BY clauses are pretty rubbish.

    Yes, I've turned them off. The way it wants to format them is offensive, too.



  • Oh, you mean Oracle SQL WTF machine? Yeah, I usually disable everything which makes it behave more than a SQL syntax highlighter.



  • @Eldelshell said:

    Oracle SQL WTF machine

    Agreed.

    @Eldelshell said:

    I usually disable everything

    I do find the ability to substitute select * from tablename to select a, b, c, ... from tablename to be helpful on the occasions it works. However, I often find myself doing

    select listagg(column_name, ', ') within group(order by column_id) from all_tab_columns where table_name = 'TABLENAME';


  • @jaming said:

    ```
    select listagg(column_name, ', ') within group(order by column_id) from all_tab_columns where table_name = 'TABLENAME';

    
    LOL...that hadn't occurred to me. Though usually `*` is just a placeholder for me while I put my joins together.


  • @jaming said:

    However, I often find myself doing

    select listagg(column_name, ', ') within group(order by column_id) from all_tab_columns where table_name = 'TABLENAME';</blockquote>
    

    We're still using 10g here, so no LISTAGG :(

    @boomzilla said:

    Though usually * is just a placeholder for me while I put my joins together.

    This too.



  • @loopback0 said:

    We're still using 10g here, so no LISTAGG

    Livin' dangerously with the WM_CONCAT! I remember those days. We got fucked when we upgraded to 11 and they started returning CLOBs. Making LISTAGGs DISTINCT is a PITA.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.