SC erases columns in sql query

[manfred]

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.

SELECT distinct B.CEDULA as cedula, [B]B.NBR_EMPLEADO[/B] AS nombre, [B]B.APE_1 [/B]AS apellido1,
[B]B.APE_2[/B] as apellido2, A.PERIODO as periodo, b.PUESTO,[B] b.UBICACION[/B], A.COMPETENCIA,
A.ESPERADO_CUALITATIVO, A.OBTENIDO_CUALITATIVO, A.BRECHA, A.COD_PERFIL as perfil,
e.des_ubicacion, [B]c.nombre[/B] 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:

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.

[alvagar]

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

[manfred]

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:

select distinct a.cedula
from srh260 a
order by a.cedula desc

To keep track of what SC does I enabled the debug SQL:

(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:

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:

(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: [TABLE=“border: 1, cellpadding: 1”]
[TR]
[TD]591[/TD]
[TD]$_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)” ;[/TD]
[/TR]
[TR]
[TD]592[/TD]
[TD]$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) ;[/TD]
[/TR]
[/TABLE]

• At the line 591, I see that a session variable named [B]sc_sql_ult_comando[/B] 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:

[INDENT] > 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.[/INDENT]

• Because of [B]$nmgp_select[/B] 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.

[Kdriscoll]

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

[manfred]

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.

[mitja]

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

[manfred]

@mitja

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.

[mitja]

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.

[manfred]

@mitja

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.

[frank]

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

[Giu]

@frank

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

[manfred]

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.