Problems with Query. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problems with Query.

Author

Topic

vadmcse
Starting Member

10 Posts

Posted - 2010-12-11 : 05:23:02

Hello everybody.

I have this query:

SELECT T0.CodigoCliente, T0.SerieFactura, T0.Nombre, T0.RazonSocial,T0.Domicilio,
T0.CodigoPostal, T0.Municipio, T0.EjercicioFactura, T0.NumeroFactura, T0.CodigoArticulo,
T0.Unidades, T0.BaseImponible,
T0.ImporteBruto, T0.FechaFactura, T0.Periodo, T0.CodigoComisionista,T0.Colectivo,

(SELECT COUNT(T1.NumeroFactura)
FROM Vis_VDA_ResumenCliente T1
WHERE T1.CodigoCliente = T0.CodigoCliente AND
T1.CodigoArticulo = ((SELECT DISTINCT (T2.CodigoArticulo)
FROM Vis_VDA_ResumenCliente T2
WHERE T2.CodigoArticulo = T0.CodigoArticulo)) AND
T1.EjercicioFactura = '2010' AND
T1.Periodo='10') AS CantFact

FROM Vis_VDA_ResumenCliente T0

WHERE T0.EjercicioFactura='2010' AND
T0.Periodo = '10'
ORDER BY T0.CodigoCliente, T0.CodigoArticulo

It works fine and i get the results that i want.
The problem is that as i'm running it as a SQL Query, and i put the WHERE clause in the query the results are OK.

I want to use it in a software called Logic Class (Similar to SAP Business One).

In this program, the final user (not me), can print a report.
The report is generated automatically, using this query as a base, so i CAN'T use the WHERE clause (see the RED and the GREEN text) since the final user will put this "limits" before the query is executed.

I'll try to explain it better.

In the program, you execute a REPORT, then, you have a screen where you can select the limits for the query that will be executed, and then the query is executed by the program to generate the report aplying the limits that you choosed in the first screen.

Ok, now the problem is that the limits that you can choose, are the ones marked in GREEN, but i can't limit the subquery, so the result for the column "CantFact" is not limited.

Any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-11 : 09:56:31

i think what you need to do is to wrap the above query in a procedure with parameters added for your limits. then from report pass values for this parameters and execute the procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources