SmartClient Version: v10.1p_2016-03-14/Enterprise Deployment (built 2016-03-14)
Hello, I'm struggling to make a test case for an issue I have, but without success.
Now I've found an (easy) workaround, but I want to let you know anyway.
The query was working with 10.0. The problem appeared with 10.1, from v101p_2015-12-10 (the oldest I've got at hand) to the latest.
The operationBinding is part of a pretty complicated dataSource, stripping almost all of it solves the problem, but I couldn't identify from what originates.
The fetch is issued by a grid.summaryRow, and generates a row count query with an extra 'AND' at the end:
If I add skipRowCount="true" to the operationBinding, the generated query is correct.
The operationBinding is:
the customFields are from the LEG_FASCE_PREZZO table, (they have a customSelectExpression) joined via this foreignKey:
Note: the relatedTableAlias is equal to the table name/dataSource ID. It could be the problem?
there are also a couple of fields 'included from' LEG_FASCE_PREZZO, but with customSQL="true" and not mentioned in customFields:
Anyway, if I'll succeed in putting together a test case, I'll update this thread.
Hello, I'm struggling to make a test case for an issue I have, but without success.
Now I've found an (easy) workaround, but I want to let you know anyway.
The query was working with 10.0. The problem appeared with 10.1, from v101p_2015-12-10 (the oldest I've got at hand) to the latest.
The operationBinding is part of a pretty complicated dataSource, stripping almost all of it solves the problem, but I couldn't identify from what originates.
The fetch is issued by a grid.summaryRow, and generates a row count query with an extra 'AND' at the end:
Code:
2016-03-15 12:10:18,336 DEBUG RPCManager Request #1 (DSRequest) payload: {
criteria:{
ID_STAGIONI_FK:913383
},
operationConfig:{
dataSource:"LEG_ORDINE",
repo:null,
operationType:"fetch",
textMatchStyle:"substring"
},
startRow:0,
endRow:1,
componentId:"gestReportPagamentiListOrdini_summaryRow",
appID:"builtinApplication",
operation:"fetchTotaliPagamCCList",
oldValues:{
ID_STAGIONI_FK:913383
}
}
2016-03-15 12:10:18,336 INFO IDACall Performing 1 operation(s)
2016-03-15 12:10:18,336 DEBUG DeclarativeSecurity Processing security checks for DataSource null, field null
2016-03-15 12:10:18,336 DEBUG DeclarativeSecurity DataSource LEG_ORDINE is not in the pre-checked list, processing...
2016-03-15 12:10:18,336 DEBUG DeclarativeSecurity Processing security checks for DataSource LEG_FASCE_PREZZO, field ID_REC
2016-03-15 12:10:18,336 DEBUG DeclarativeSecurity DataSource LEG_FASCE_PREZZO is not in the pre-checked list, processing...
2016-03-15 12:10:18,336 DEBUG DeclarativeSecurity Processing security checks for DataSource LEG_FASCE_PREZZO, field PREZZO_LORDO
2016-03-15 12:10:18,336 DEBUG DeclarativeSecurity DataSource LEG_FASCE_PREZZO is not in the pre-checked list, processing...
2016-03-15 12:10:18,337 DEBUG DeclarativeSecurity Processing security checks for DataSource LEG_FASCE_PREZZO, field ID_REC
2016-03-15 12:10:18,337 DEBUG DeclarativeSecurity Processing security checks for DataSource LEG_FASCE_PREZZO, field TICKETING_LORDO
2016-03-15 12:10:18,337 DEBUG DeclarativeSecurity Processing security checks for DataSource JPC_LISTINI, field ID_REC
2016-03-15 12:10:18,337 DEBUG DeclarativeSecurity DataSource JPC_LISTINI is not in the pre-checked list, processing...
2016-03-15 12:10:18,337 DEBUG DeclarativeSecurity Processing security checks for DataSource JPC_LISTINI, field DATA_INIZIO
2016-03-15 12:10:18,337 DEBUG DeclarativeSecurity DataSource JPC_LISTINI is not in the pre-checked list, processing...
2016-03-15 12:10:18,340 DEBUG AppBase [builtinApplication.fetchTotaliPagamCCList] No userTypes defined, allowing anyone access to all operations for this application
2016-03-15 12:10:18,340 DEBUG AppBase [builtinApplication.fetchTotaliPagamCCList] No public zero-argument method named '_fetchTotaliPagamCCList' found, performing generic datasource operation
2016-03-15 12:10:18,347 DEBUG PoolableSQLConnectionFactory [builtinApplication.fetchTotaliPagamCCList] makeObject() created an unpooled Connection '789182145'
2016-03-15 12:10:18,347 DEBUG SQLConnectionManager [builtinApplication.fetchTotaliPagamCCList] Borrowed connection '789182145'
2016-03-15 12:10:18,347 DEBUG SQLTransaction [builtinApplication.fetchTotaliPagamCCList] Started new dbJpcEP transaction "789182145"
2016-03-15 12:10:18,347 INFO SQLDriver [builtinApplication.fetchTotaliPagamCCList] Executing SQL query on 'dbJpcEP' using connection '789182145': SELECT COUNT(*) FROM DBSALES.JPC_CONTRATTI, LEG_FASCE_PREZZO, JPC_STATI_CONTRATTO, JPC_ANAGRAFICA_CLIENTI, JPC_TIPI_SOGGETTO, JPC_FATTURE WHERE
(JPC_CONTRATTI.ID_STAGIONI_FK=913383)
AND JPC_ANAGRAFICA_CLIENTI.ID_REC = JPC_CONTRATTI.ID_ANAGRAFICA_CLIENTI_FK
AND JPC_ANAGRAFICA_CLIENTI.ID_TIPI_SOGGETTO_FK = JPC_TIPI_SOGGETTO.ID_REC
AND JPC_CONTRATTI.ID_STATO_CONTRATTO_FK = JPC_STATI_CONTRATTO.ID_REC
AND JPC_CONTRATTI.IS_ANNULLATO = 'F'
AND JPC_CONTRATTI.TIPO = 'LEG'
AND JPC_CONTRATTI.ID_REC = JPC_FATTURE.ID_CONTRATTI_FK(+)
AND JPC_STATI_CONTRATTO.CODICE IN ('INC', 'ACC', 'TKT')
AND JPC_CONTRATTI.ID_FASCIA_PREZZO_LEG_FK = LEG_FASCE_PREZZO.ID_REC
AND
2016-03-15 12:10:18,354 DEBUG DSRequest freeOnExecute is false for request of type fetch on DataSource LEG_ORDINE - not freeing resources!
2016-03-15 12:10:18,354 WARN RequestContext dsRequest.execute() failed:
java.sql.SQLSyntaxErrorException: ORA-00936: espressione mancante
The operationBinding is:
Code:
<operationBinding operationType="fetch" operationId="fetchTotaliPagamCCList"
customFields="DATA_INCASSO,VALORE_SERVIZI_LORDO,VALORE_TICKETING_PER_POSTI,VALORE_ORDINE,SERVIZI_NETTI,VALORE_ORDINE_SCONTATO"
>
<selectClause>
'Totale' AS SOGGETTO_DESC,
NVL(SUM((LEG_FASCE_PREZZO.TICKETING_LORDO * JPC_CONTRATTI.QTA_POSTI)), 0) AS VALORE_TICKETING_PER_POSTI,
NVL(SUM(LEG_FASCE_PREZZO.SERVIZI_LORDO * JPC_CONTRATTI.QTA_POSTI),0) AS VALORE_SERVIZI_LORDO,
NVL(SUM(LEG_FASCE_PREZZO.PREZZO_LORDO * JPC_CONTRATTI.QTA_POSTI), 0) AS VALORE_ORDINE,
NVL(SUM(JPC_CONTRATTI.SCONTO_INCONDIZIONATO_VALORE), 0) AS SCONTO_INCONDIZIONATO_VALORE,
NVL(SUM(NVL(((LEG_FASCE_PREZZO.PREZZO_LORDO * JPC_CONTRATTI.QTA_POSTI) - JPC_CONTRATTI.SCONTO_INCONDIZIONATO_VALORE),
(LEG_FASCE_PREZZO.PREZZO_LORDO * JPC_CONTRATTI.QTA_POSTI))), 0) as VALORE_ORDINE_SCONTATO,
NVL(SUM(
(
DECODE(
((LEG_FASCE_PREZZO.SERVIZI_LORDO * JPC_CONTRATTI.QTA_POSTI) - JPC_CONTRATTI.SCONTO_INCONDIZIONATO_VALORE),
NULL, (LEG_FASCE_PREZZO.SERVIZI_LORDO * JPC_CONTRATTI.QTA_POSTI),
((LEG_FASCE_PREZZO.SERVIZI_LORDO * JPC_CONTRATTI.QTA_POSTI) - JPC_CONTRATTI.SCONTO_INCONDIZIONATO_VALORE)
)
)
), 0) AS SERVIZI_NETTI
</selectClause>
<tableClause>$defaultTableClause, LEG_FASCE_PREZZO, JPC_STATI_CONTRATTO, JPC_ANAGRAFICA_CLIENTI, JPC_TIPI_SOGGETTO, JPC_FATTURE</tableClause>
<whereClause>
$defaultWhereClause
AND JPC_ANAGRAFICA_CLIENTI.ID_REC = JPC_CONTRATTI.ID_ANAGRAFICA_CLIENTI_FK
AND JPC_ANAGRAFICA_CLIENTI.ID_TIPI_SOGGETTO_FK = JPC_TIPI_SOGGETTO.ID_REC
AND JPC_CONTRATTI.ID_STATO_CONTRATTO_FK = JPC_STATI_CONTRATTO.ID_REC
AND JPC_CONTRATTI.IS_ANNULLATO = 'F'
AND JPC_CONTRATTI.TIPO = 'LEG'
AND JPC_CONTRATTI.ID_REC = JPC_FATTURE.ID_CONTRATTI_FK(+)
AND JPC_STATI_CONTRATTO.CODICE IN ('INC', 'ACC', 'TKT')
AND JPC_CONTRATTI.ID_FASCIA_PREZZO_LEG_FK = LEG_FASCE_PREZZO.ID_REC
</whereClause>
</operationBinding>
Code:
<field name="ID_FASCIA_PREZZO_LEG_FK" type="integer" foreignKey="LEG_FASCE_PREZZO.ID_REC" relatedTableAlias="LEG_FASCE_PREZZO"></field>
there are also a couple of fields 'included from' LEG_FASCE_PREZZO, but with customSQL="true" and not mentioned in customFields:
Code:
<field name="VALORE_BIGLIETTO" type="localeFloat" format=",##0.00 €" customSQL="true" includeFrom="LEG_FASCE_PREZZO.PREZZO_LORDO"
includeVia="ID_FASCIA_PREZZO_LEG_FK"/>
<field name="VALORE_TICKETING" type="float" customSQL="true" includeFrom="LEG_FASCE_PREZZO.TICKETING_LORDO"
includeVia="ID_FASCIA_PREZZO_LEG_FK"/>
Comment