Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Problem using TOraQuery.SetorderBy

https://forums.devart.com/viewtopic.php?f=5&t=15392

Page 1 of 1

Problem using TOraQuery.SetorderBy

Posted: Wed 22 Jul 2009 07:51
by Luc
Hello,
we are using
Delphi 2006 and
ODAC version 6.80.0.47


We are using a TORAQUERY with function 'setOrderBy'

We use the function SetOrderBy with following parameter:
OQR_PREPA_IMP_BP.SetOrderBy(OQR_PREPA_IMP_BP.GetOrderBy+';decode((SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz WHERE tz.code= t.code_zone_prepa ),'+quotedstr('1')+',ordre_livraison,'''')');



When TORAQUERY is open, in DBMonitor,
we can see following sequence:

'ORDER BY decode((SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz'


the same program was OK with ODAC 4.50.4.25

Can somebody help me ?

thanks

Re: Problem using TOraQuery.SetorderBy

Posted: Thu 23 Jul 2009 07:48
by Luc
in others terms :

We are using a TORAQUERY with function 'setOrderBy'

We use the function SetOrderBy with following parameter:
OQR_PREPA_IMP_BP.SetOrderBy('code_tournee,code_emplacement,code_article,(SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz WHERE tz.code= t.code_zone_prepa) ,ordre_livraison');



When TORAQUERY is open, in DBMonitor,
we can see following sequence:

'ORDER BY code_tournee,code_emplacement,code_article,(SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz WHERE ,ordre_livraison


The "where condition" is missing.

Posted: Thu 23 Jul 2009 13:36
by Challenger
Please specify the original SQL statement.

Posted: Fri 24 Jul 2009 07:07
by Luc
ORIGINAL SQL Statement is
select ( t.*
from (
SELECT Numero_Impression,
Cde_Individuelle,
Code_Cde,
Date_Cde,
Date_Liv,
Ordre_Livraison,
Code_Client,
Id_Depot,
Rs_Client,
Nom_client,
Adresse1,
Adresse2,
Cp_Client,
Ville_Client,
Telephone,
Portable,
Numero_Uti,
Date_Beurre,
Code_Onilait,
Code_Representant,
Nom_Representant,
min(RowId) minrow,
Code_Tournee_Livraison,
Libelle_Tournee,
Code_Zone_Prepa Code_Zone_Prepa,
Libelle_Zone_Prepa,
Type_Zone_Prepa,
nvl(Index_Bordereau_Tournee,'0') Index_Bordereau_Tournee,
ordre_saisie_article,
Code_Article,
Trim(Libelle_Article) Libelle_Article,
Code_Lot,
Emplacement,
Fam_Stat_Article,
Code_Fournisseur,
Libelle_Fournisseur,
Categorie_Article,
Famille_Tarif_Article,
Sous_Famille_Tarif_Article,
Conditionnement,
Poids,
decode(A_PESER,1,decode(sum(QUANTITE_PREPAREE),0,sum(QUANTITE_CDEE),SUM(QUANTITE_PREPAREE)),SUM(QUANTITE_PREPAREE)) Quantite_Origine,
SUM(QUANTITE_PREPAREE) QUANTITE_Facturee,
SUM(Quantite_Manque) Quantite_Manque,
SUM(Piece_Manque) Piece_Manque,
SUM(Piece_commande) Piece_commande,
Code_Special,
MIN(Stock_Resultant) Stock_Resultant,
Nature_Article,
a_Peser,
poids_piece,
trim(To_Char(Chaine_Trace)) Chaine_Trace,
Stock,
Edition_Bp_Tournee,
Edition_Bp_Individuel,
cde_a_peser,
NP_DEJA_EDITE,
LIBELLE_CONDITIONNEMENT,
heure_cde,
piece_stock stockpiece
FROM T$prepa_Imp_Bp p
WHERE Code_Lot 2
GROUP BY a_Peser,poids_piece,code_article,conditionnement,Trim(Libelle_Article),
trim(To_Char(Chaine_Trace)),
cde_a_peser,
Numero_Impression,
Cde_Individuelle,
Code_Cde,
ordre_saisie_article,
Date_Cde,
Date_Liv,
Ordre_Livraison,
Code_Client,
Id_Depot,
Rs_Client,
Nom_client,
Adresse1,
Adresse2,
Cp_Client,
Ville_Client,
Telephone,
Portable,
Numero_Uti,
Date_Beurre,
Code_Onilait,
Code_Representant,
Nom_Representant,
Code_Tournee_Livraison,
Libelle_Tournee,
Code_Zone_Prepa,
Libelle_Zone_Prepa,
Type_Zone_Prepa,
Index_Bordereau_Tournee,
Code_Lot,
Emplacement,
Fam_Stat_Article,
Code_Fournisseur,
Libelle_Fournisseur,
Categorie_Article,
Famille_Tarif_Article,
Sous_Famille_Tarif_Article,
Conditionnement,
Poids,
Code_Special,
Nature_Article,
Stock,
Edition_Bp_Tournee,
Edition_Bp_Individuel,
NP_DEJA_EDITE,
LIBELLE_CONDITIONNEMENT,
HEURE_CDE,
piece_stock
) t
WHERE NUMERO_IMPRESSION=196010
AND nvl((SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz WHERE tz.code= t.code_zone_prepa),0)2 AND nvl(edition_bp_tournee,0)=0 AND (nvl(NP_DEJA_EDITE,0)=0 OR (nvl(poids_piece,0)=0 AND NP_DEJA_EDITE=1 AND (stock> 0) AND A_peser=1) OR (nvl(poids_piece,0)=1 AND NP_DEJA_EDITE=1 AND stockpiece>0 AND A_peser=1)) AND Cde_Individuelle=0
ORDER BY Code_Tournee_Livraison,
Code_Zone_Prepa,
decode((SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz WHERE tz.code= t.code_zone_prepa ),'1',ordre_livraison,''),
decode((SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz WHERE tz.code= t.code_zone_prepa ),'1',code_client,''),
Emplacement,
cde_a_peser,
Code_article,
Libelle_article,
Conditionnement,
Chaine_Trace
It was good with ODAC 4.50.4.25

The same SQL Statement With TORAQUERY from ODAC 6.80.0.47
select (t.*
from (
SELECT Numero_Impression,
Cde_Individuelle,
Code_Cde,
Date_Cde,
Date_Liv,
Ordre_Livraison,
Code_Client,
Id_Depot,
Rs_Client,
Nom_client,
Adresse1,
Adresse2,
Cp_Client,
Ville_Client,
Telephone,
Portable,
Numero_Uti,
Date_Beurre,
Code_Onilait,
Code_Representant,
Nom_Representant,
min(RowId) minrow,
Code_Tournee_Livraison,
Libelle_Tournee,
Code_Zone_Prepa Code_Zone_Prepa,
Libelle_Zone_Prepa,
Type_Zone_Prepa,
nvl(Index_Bordereau_Tournee,'0') Index_Bordereau_Tournee,
ordre_saisie_article,
Code_Article,
Trim(Libelle_Article) Libelle_Article,
Code_Lot,
Emplacement,
Fam_Stat_Article,
Code_Fournisseur,
Libelle_Fournisseur,
Categorie_Article,
Famille_Tarif_Article,
Sous_Famille_Tarif_Article,
Conditionnement,
Poids,
decode(A_PESER,1,decode(sum(QUANTITE_PREPAREE),0,sum(QUANTITE_CDEE),SUM(QUANTITE_PREPAREE)),SUM(QUANTITE_PREPAREE)) Quantite_Origine,
SUM(QUANTITE_PREPAREE) QUANTITE_Facturee,
SUM(Quantite_Manque) Quantite_Manque,
SUM(Piece_Manque) Piece_Manque,
SUM(Piece_commande) Piece_commande,
Code_Special,
MIN(Stock_Resultant) Stock_Resultant,
Nature_Article,
a_Peser,
poids_piece,
trim(To_Char(Chaine_Trace)) Chaine_Trace,
Stock,
Edition_Bp_Tournee,
Edition_Bp_Individuel,
cde_a_peser,
NP_DEJA_EDITE,
LIBELLE_CONDITIONNEMENT,
heure_cde,
piece_stock stockpiece
FROM T$prepa_Imp_Bp p
WHERE Code_Lot 2
GROUP BY a_Peser,poids_piece,code_article,conditionnement,Trim(Libelle_Article),
trim(To_Char(Chaine_Trace)),
cde_a_peser,
Numero_Impression,
Cde_Individuelle,
Code_Cde,
ordre_saisie_article,
Date_Cde,
Date_Liv,
Ordre_Livraison,
Code_Client,
Id_Depot,
Rs_Client,
Nom_client,
Adresse1,
Adresse2,
Cp_Client,
Ville_Client,
Telephone,
Portable,
Numero_Uti,
Date_Beurre,
Code_Onilait,
Code_Representant,
Nom_Representant,
Code_Tournee_Livraison,
Libelle_Tournee,
Code_Zone_Prepa,
Libelle_Zone_Prepa,
Type_Zone_Prepa,
Index_Bordereau_Tournee,
Code_Lot,
Emplacement,
Fam_Stat_Article,
Code_Fournisseur,
Libelle_Fournisseur,
Categorie_Article,
Famille_Tarif_Article,
Sous_Famille_Tarif_Article,
Conditionnement,
Poids,
Code_Special,
Nature_Article,
Stock,
Edition_Bp_Tournee,
Edition_Bp_Individuel,
NP_DEJA_EDITE,
LIBELLE_CONDITIONNEMENT,
HEURE_CDE,
piece_stock
) t
WHERE ((((NUMERO_IMPRESSION=196011) AND nvl((SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz WHERE tz.code= t.code_zone_prepa),0)2) AND nvl(edition_bp_tournee,0)=0) AND (nvl(NP_DEJA_EDITE,0)=0 OR (nvl(poids_piece,0)=0 AND NP_DEJA_EDITE=1 AND (stock> 0) AND A_peser=1) OR (nvl(poids_piece,0)=1 AND NP_DEJA_EDITE=1 AND stockpiece>0 AND A_peser=1))) AND Cde_Individuelle=0
ORDER BY Code_Tournee_Livraison,
Code_Zone_Prepa,
decode((SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz,
decode((SELECT tz.index_bordereau_tournee FROM t$zone_preparation tz,
Emplacement,
cde_a_peser,
Code_article,
Libelle_article,
Conditionnement,
Chaine_Trace

Posted: Mon 03 Aug 2009 08:10
by Plash
It seems that your SQL statement is incorrect. I didn't find the right bracket that matches the bracket in the first line (after SELECT).
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/