Problem using TOraQuery.SetorderBy

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Luc
Posts: 3
Joined: Wed 22 Jul 2009 07:41
Location: Evreux (France)

Problem using TOraQuery.SetorderBy

Post by Luc » Wed 22 Jul 2009 07:51

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
Top
Luc
Posts: 3
Joined: Wed 22 Jul 2009 07:41
Location: Evreux (France)

Re: Problem using TOraQuery.SetorderBy

Post by Luc » Thu 23 Jul 2009 07:48

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.
Top
Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Thu 23 Jul 2009 13:36

Please specify the original SQL statement.
Top
Luc
Posts: 3
Joined: Wed 22 Jul 2009 07:41
Location: Evreux (France)

Post by Luc » Fri 24 Jul 2009 07:07

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
Top
Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Mon 03 Aug 2009 08:10

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).
Top
Post Reply

Return to “Oracle Data Access Components”