Parameter in Subquery

  • Thread starter Thread starter José A. Fernández
  • Start date Start date
J

José A. Fernández

Hello,
I am trying to do a query to obtain the quantity bought of articles by
Families between a period of date and I am using the following query:
Select f.nombre, (select sum (ldp.cant) as ' Cant. Comprada' from LINDPRO as
ldp where ldp.codfam=fam.codigo and ldp.fecha <=desfec and ldp.fecha>
=hasfec)) from Familas as f;
When I executed the query get a error because it takes parameters (desfec
and hasfec).
Is it possible to do it.

Thanks.
Jose A. Fernández
 
Hello Patrice,
Desfec and Hasfec are program variables and I want to pass these values to
DB engine in a subquery.
I know how passs parameter in a query bat not in a query that contain a
subquery.

The result of query is:

Familia Cant. Comprada Total
 
Hello Patrice,
I´m using Pervasive.Data.SqlClient:

DataSet ds = new DataSet();
string comando;
string dfec;
string hfec;
dfec = l.DaFecha(desfec);
hfec = l.DaFecha(hasfec);
con.Open();

comando = "SELECT f.nombre as 'Familia',s.nombre as 'Subfamilia', " +
"(SELECT sum(art.exi) as 'Stock' FROM ARTICULO
as art WHERE art.codfam=f.codigo and art.codsub=s.codigo group by
art.codfam,art.codsub), " +
"(SELECT sum(ldp.cant) as 'Unidades Compradas'
FROM LINDPRO as ldp WHERE ldp.codfam=f.codigo and ldp.codsub=s.codigo and " +
"ldp.fecha >= ? and ldp.fecha <= ? and " +
"left(ldp.concepto,2)<>'--' AND
POSITION(LEFT(ldp.codigo,1),'" + series + "')<>0 " +
" group by ldp.codfam,ldp.codsub), " +
"(SELECT sum(ldp.total) as 'Valor Compras' FROM
LINDPRO as ldp WHERE ldp.codfam=f.codigo and ldp.codsub=s.codigo group by
ldp.codfam,ldp.codsub), " +
"(SELECT sum(ldc.cant) as 'Unidades Vendidas'
FROM LINDCLI as ldc WHERE ldc.codfam=f.codigo and ldc.codsub=s.codigo group
by ldc.codfam,ldc.codsub), " +
"(SELECT sum(ldc.total) as 'Valor Ventas' FROM
LINDCLI as ldc WHERE ldc.codfam=f.codigo and ldc.codsub=s.codigo group by
ldc.codfam,ldc.codsub) " +
"FROM OFAM as f,OSUB as s " +
"WHERE (f.codigo=s.codfam)";

PsqlDataAdapter da = new
PsqlDataAdapter(comando, con);

da.SelectCommand.Parameters.AddWithValue("Desfec", dfec);
da.SelectCommand.Parameters.AddWithValue("Hasfec", hfec);

da.SelectCommand.CommandTimeout = 1000;
da.Fill(ds, nombre);

con.Close();

I have the next error:

Pervasive.Data.SqlClient.Lna.LnaException: [LNA][Pervasive][ODBC Engine
Interface]Parameters are not permitted in the SELECT list.

Thanks.
 
José,

This might be of help:

http://cs.pervasive.com/forums/p/6322/6322.aspx

Kerry Moorman


José A. Fernández said:
Hello Patrice,
I´m using Pervasive.Data.SqlClient:

DataSet ds = new DataSet();
string comando;
string dfec;
string hfec;
dfec = l.DaFecha(desfec);
hfec = l.DaFecha(hasfec);
con.Open();

comando = "SELECT f.nombre as 'Familia',s.nombre as 'Subfamilia', " +
"(SELECT sum(art.exi) as 'Stock' FROM ARTICULO
as art WHERE art.codfam=f.codigo and art.codsub=s.codigo group by
art.codfam,art.codsub), " +
"(SELECT sum(ldp.cant) as 'Unidades Compradas'
FROM LINDPRO as ldp WHERE ldp.codfam=f.codigo and ldp.codsub=s.codigo and " +
"ldp.fecha >= ? and ldp.fecha <= ? and " +
"left(ldp.concepto,2)<>'--' AND
POSITION(LEFT(ldp.codigo,1),'" + series + "')<>0 " +
" group by ldp.codfam,ldp.codsub), " +
"(SELECT sum(ldp.total) as 'Valor Compras' FROM
LINDPRO as ldp WHERE ldp.codfam=f.codigo and ldp.codsub=s.codigo group by
ldp.codfam,ldp.codsub), " +
"(SELECT sum(ldc.cant) as 'Unidades Vendidas'
FROM LINDCLI as ldc WHERE ldc.codfam=f.codigo and ldc.codsub=s.codigo group
by ldc.codfam,ldc.codsub), " +
"(SELECT sum(ldc.total) as 'Valor Ventas' FROM
LINDCLI as ldc WHERE ldc.codfam=f.codigo and ldc.codsub=s.codigo group by
ldc.codfam,ldc.codsub) " +
"FROM OFAM as f,OSUB as s " +
"WHERE (f.codigo=s.codfam)";

PsqlDataAdapter da = new
PsqlDataAdapter(comando, con);

da.SelectCommand.Parameters.AddWithValue("Desfec", dfec);
da.SelectCommand.Parameters.AddWithValue("Hasfec", hfec);

da.SelectCommand.CommandTimeout = 1000;
da.Fill(ds, nombre);

con.Close();

I have the next error:

Pervasive.Data.SqlClient.Lna.LnaException: [LNA][Pervasive][ODBC Engine
Interface]Parameters are not permitted in the SELECT list.

Thanks.


Patrice said:
I see no reason for doing something else. Parameters are just placeholders
so I'm not anticipating any difference. Have you tried to create a
parametized query as described in the documentation ?

Or what have you tried to do as usual and what is the error you got ?

--
Patrice

"Jos A. Fernndez" <[email protected]> a crit dans le
message de groupe de discussion :
(e-mail address removed)...
 
Back
Top