Announcement

Collapse
No announcement yet.

SC erases columns in sql query

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SC erases columns in sql query

    This is my query running in oracle without any problem, please, consider the columns in bold, because they represent the ordering at the end of the query.

    Code:
    SELECT distinct B.CEDULA as cedula, B.NBR_EMPLEADO AS nombre, B.APE_1 AS apellido1,
    B.APE_2 as apellido2, A.PERIODO as periodo, b.PUESTO, b.UBICACION, A.COMPETENCIA,
    A.ESPERADO_CUALITATIVO, A.OBTENIDO_CUALITATIVO, A.BRECHA, A.COD_PERFIL as perfil,
    e.des_ubicacion, c.nombre as nombre_competencia, f.nbr_puesto as nombre_puesto
    FROM srh260 a, srh011 b, srh250 c, srh018 d, srh072 e, srh040 f
    WHERE b.cedula = a.cedula
    and b.puesto = f.puesto
    and b.cedula = d.cedula
    and b.ubicacion = e.ubicacion
    and a.competencia = c.competencia
    and a.cod_perfil = f.cod_perfil
    and a.origen = 'COM'
    and a.periodo like '%'
    and b.ubicacion like '%'
    and a.cod_perfil like '%'
    and b.cedula like '%'
    and b.TPO_NOMBRAMIENTO like '%'
    and F_CALCULA_EDAD(b.FCH_NACIMIENTO, SYSDATE) BETWEEN 0 AND 100
    and F_CALCULA_EDAD(b.FCH_INGRESO, SYSDATE) BETWEEN 0 AND 100
    and b.SEXO LIKE '%'
    and b.EDO_CIVIL LIKE '%'
    and d.GRADO_ACADEMICO_OBTENIDO LIKE '%'
    order by b.ubicacion asc, b.ape_1 asc, b.ape_2 asc, b.nbr_empleado asc, c.nombre asc
    When I open the application, this error is prompted:

    ORA-01791: not a SELECTed expression
    What is the problem? well, ScriptCase parsed the query as:

    Code:
    SELECT distinct A.PERIODO as periodo, b.CEDULA as cedula,
    f.nbr_puesto as nombre_puesto, e.des_ubicacion as e_des_ubicacion,
    A.ESPERADO_CUALITATIVO as a_esperado_cualitativo, A.OBTENIDO_CUALITATIVO as a_obtenido_cualitativo,
    A.BRECHA as a_brecha, B.NBR_EMPLEADO as nombre, B.APE_1 as apellido1,
    B.APE_2 as apellido2, A.COD_PERFIL as perfil
    from srh260 a, srh011 b, srh250 c, srh018 d, srh072 e, srh040 f
    where b.cedula = a.cedula
    and b.puesto = f.puesto
    and b.cedula = d.cedula
    and b.ubicacion = e.ubicacion
    and a.competencia = c.competencia
    and a.cod_perfil = f.cod_perfil
    and a.origen = 'COM'
    and a.periodo like '%'
    and b.ubicacion like '%'
    and a.cod_perfil like '%'
    and b.cedula like '%'
    and b.TPO_NOMBRAMIENTO like '%'
    and F_CALCULA_EDAD(b.FCH_NACIMIENTO, SYSDATE) BETWEEN 0 AND 100
    and F_CALCULA_EDAD(b.FCH_INGRESO, SYSDATE) BETWEEN 0 AND 100
    and b.SEXO LIKE '%'
    and b.EDO_CIVIL LIKE '%'
    and d.GRADO_ACADEMICO_OBTENIDO LIKE '%'
    order by b.UBICACION asc, b.ape_1 asc, b.ape_2 asc, b.nbr_empleado asc, c.nombre asc
    See how in this query parsed by ScriptCase the column b.Ubicacion and c.nombre are not listed, as result, Oracle engine throws an error because you can not order by a field that is not listed in the select statement using "distinct" keyword. So, now what? I do not know what to do:

    1. I have created others applications and the error persist.
    2. Clearing cache, same result
    3. Logged time after time with no success
    4. Changing the query to order only by one field, and then reordering as a I need keeps the same error.

    I am stuck.

    Last edited by manfred; 04-29-2016, 04:45 PM.

  • #2
    Maybe work that. Try order by number of Colums: Order by 1, 3, 5.

    Comment


    • #3
      Hello alvagar and everyone,

      the problem is not the ordering, the problem is that ScriptCase remove the columns from select section, I can not order by number of columns because there are not columns to order. This is an very annoying bug, take a look to this easy example and try yourselft with one of your tables.

      1. Create a grid application
      2 Select a column an order it by that column:

      Code:
      select distinct a.cedula
      from srh260 a
      order by a.cedula desc
      To keep track of what SC does I enabled the debug SQL:
      Code:
      (oci805): select count(*) from (select distinct a.cedula from srh260 a ) nm_sel_esp  
      (oci805): SELECT /*+FIRST_ROWS*/ distinct cedula from srh260 a order by a.cedula desc
      3. The grid is displayed as expected. Everything cool at this point.
      4. Change your query to select an additional column:

      Code:
      select distinct a.cedula, a.periodo
      from srh260 a
      order by a.cedula desc, a.periodo desc
      5. Save, generate and run the application. The error comes here.

      Error
      ORA-01791: not a SELECTed expression
      SelectLimit(SELECT distinct cedula from srh260 a order by a.cedula desc, a.periodo desc, 12, 0)
      See above how the column of periodo has disappeared. The debug mode shows the following queries:

      Code:
      (oci805): select count(*) from (select distinct a.cedula, a.periodo from srh260 a ) nm_sel_esp
      (oci805): SELECT /*+FIRST_ROWS*/ distinct cedula from srh260 a order by a.cedula desc, a.periodo desc
      Here, when SC try to count the total of results see how SC is recognized the columns successfully, but magically in the next query where SC brings the data the column is not there. So, this confirms that is some point of the compiling process the query lose its structure.

      Addiontal notes.
      Because of the error above, SC states a file and a line number where the errors is evoked. So, I went to that line and I found this at line 592, the line of death:
      591 $_SESSION['scriptcase']['sc_sql_ult_comando'] = "SelectLimit($nmgp_select, " . ($_SESSION['sc_session'][$this->Ini->sc_page]['REPORTE_AREA_EVALUACION_COMPETENCIA2']['qt_reg_grid'] + 2) . ", $this->nmgp_reg_start)" ;
      592 $this->rs_grid = $this->Db->SelectLimit($nmgp_select, $_SESSION['sc_session'][$this->Ini->sc_page]['REPORTE_AREA_EVALUACION_COMPETENCIA2']['qt_reg_grid'] + 2, $this->nmgp_reg_start) ;
      • At the line 591, I see that a session variable named sc_sql_ult_comando is assigned to save the latest sql query executed by SC (this practice of storing sql in sesion var just invoke to get the system buggy, because there is not control of access or clearing of this variable across the application), so I decided to see what this var have in the session data of SC:
      SelectLimit(SELECT distinct cedula from srh260 a order by a.cedula desc, a.periodo desc, 12, 0)
      Yes, something wrong comes from line 591, because the variable $nmgp_select contains incorrect data of the columns.
      • Because of $nmgp_select incorrect data, this issue also affect the query at line 592 which take us to the main problem of this topic.... Columns are lost.
      This looks so ugly.
      Last edited by manfred; 04-29-2016, 08:04 PM.

      Comment


      • #4
        I think the easier solution is to stop Scriptcase from trying to parse the query on how it thinks it should be

        Scriptcase is more reliable if on complicated queries if you create a view and then select the view for the application.

        Thats my 2 cents worth
        I would try it, it keeps the hairs on your head instead of your hands

        Kevin

        Comment


        • #5
          Thank you for suggestions, in other scenario this could be solution, but I will not change the database schema just because ScriptCase has no idea how to parse my query. I work with databases that are very sensitive for clients, need tons of approvals.

          Comment


          • #6
            If I understand right, you just need to sort it out correctly?

            If yes, why don't use Sorting Rules? It is on the left side panel. Grid => Sorting Rules => New Rule

            Comment


            • #7
              Originally posted by mitja View Post
              If I understand right, you just need to sort it out correctly?

              If yes, why don't use Sorting Rules? It is on the left side panel. Grid => Sorting Rules => New Rule
              If I understand right, this sorting is to allow the user to sorting the records in the web layer when the records are already loaded from database.

              Comment


              • #8
                It is created as option to sort when user click "Sorting" button in grid. Yes, you are right - sorting the records in the web layer when the records are already loaded from database.
                For "pre-sorting" for complex sortings, searches, etc. I am using build-in tool SQL Builder. You can find it in the top menu of SC under Database section. It works very well.

                Sometimes I experience that some rows are missing. Then I just refresh (Update) missing rows and they come back. I do not figured why it happens, but it is managable by hands or extra code to update all rows in certain tabe.
                Last edited by mitja; 05-02-2016, 09:35 AM. Reason: more info added - last paragraf

                Comment


                • #9
                  Originally posted by mitja View Post
                  It is created as option to sort when user click "Sorting" button in grid. Yes, you are right - sorting the records in the web layer when the records are already loaded from database.
                  For "pre-sorting" for complex sortings, searches, etc. I am using build-in tool SQL Builder. You can find it in the top menu of SC under Database section. It works very well.

                  Sometimes I experience that some rows are missing. Then I just refresh (Update) missing rows and they come back. I do not figured why it happens, but it is managable by hands or extra code to update all rows in certain tabe.
                  Nothing, same result.

                  Giu, could you please take a review at this topic and move it to bugs if necessary? I have documented it very well and how to reproduce it. I don't want this error get lose is time.

                  Thank you.

                  Comment


                  • #10
                    Manfred if you can, use a view, by this way you avoid these troubles.

                    Comment


                    • #11
                      Originally posted by frank View Post
                      Manfred if you can, use a view, by this way you avoid these troubles.

                      Agree, but sometimes you can't modify a legacy DB and this is not an option.

                      Moved to bugs
                      /Giuseppe

                      Professional Scriptcase Services
                      Some Customers opinions

                      Comment


                      • #12
                        I know guys, there are solutions via database, I appreciate your advice, but I am working under a scenario where the client is not able to change the database because it is a production environment where I need to fill tons of papers for an approval just to create a view. This is the law in major number of companies.

                        Comment

                        Working...
                        X