First Query * Second 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 2000 Forums

SQL Server Development (2000)

First Query * Second Query

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2004-04-01 : 14:27:17

Hi , i have this query below (works fine)
but how can i do to get the result from query1 * query2 whithout
repeating ( query 1 * query 2) ?

is there any way to put the result from query1 and query2 in a host variable and do something like this
@tot * @value?

tks
carlos Lages
(Powercobol x MSSQL)

SELECT
(select SUM(ped1_quantp2) from pedido1
WHERE (ped1_codigo = ped_codigo) ) as Qttot,

(select fre_valor from tabfrete
WHERE (ped_tabfrete = fre_codigo) and
((select SUM(ped1_quantp2 )from pedido1
WHERE (ped1_codigo = ped_codigo) ) between fre_fxinicial and fre_fxfinal)) as Vlfrete,

/*here i would like to have Fisrt Query * Second or Qttot * Vlfrete */
ped_codigo,
ped_dtentrega,
ped_codigoCliente,
cli_fantasia,
cli_pendencia,
ped_despacho,
tra_nome

FROM pedido WITH (NOLOCK)
INNER JOIN transpor WITH (NOLOCK) ON pedido.ped_despacho = transpor.tra_codigo
INNER JOIN Clientes WITH (NOLOCK) ON pedido.ped_codigocliente = Clientes.cli_codigo
WHERE (ped_codigocliente = 456457 )
ORDER BY ped_dtentrega, Ped_despacho, cli_fantasia

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-01 : 14:30:37

I would put the two queries into a variable:

DECLARE @var1 INT
DECLARE @var2 INT

select @var1 = SUM(ped1_quantp2) from pedido1
WHERE ped1_codigo = ped_codigo

select @var2 = fre_valor from tabfrete
WHERE (ped_tabfrete = fre_codigo) and
((select SUM(ped1_quantp2 )from pedido1
WHERE (ped1_codigo = ped_codigo) ) between fre_fxinicial and fre_fxfinal)

Then:

SELECT
@var1, @var2, @var * @var,
ped_codigo,
ped_dtentrega,
ped_codigoCliente,
cli_fantasia,
cli_pendencia,
ped_despacho,
tra_nome

FROM pedido WITH (NOLOCK)
INNER JOIN transpor WITH (NOLOCK) ON pedido.ped_despacho = transpor.tra_codigo
INNER JOIN Clientes WITH (NOLOCK) ON pedido.ped_codigocliente = Clientes.cli_codigo
WHERE (ped_codigocliente = 456457 )
ORDER BY ped_dtentrega, Ped_despacho, cli_fantasia

Tara

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-01 : 15:18:59

Tara, the variable idea is good, but it won't work unless there is only one row in the resultset. Otherwise, the expression needs to be evaluated for every row. CLages you can use my favorite trick, a derived table for this:


SELECT *, Qttot * Vlfrete AS MyProduct FROM
(
  SELECT
  (select SUM(ped1_quantp2) from pedido1 WHERE (ped1_codigo = ped_codigo) ) as Qttot,
  (select fre_valor from tabfrete WHERE (ped_tabfrete = fre_codigo) and
  ((select SUM(ped1_quantp2 )from pedido1
  WHERE (ped1_codigo = ped_codigo) ) between fre_fxinicial and fre_fxfinal)) as Vlfrete,

  ped_codigo, ped_dtentrega, ped_codigoCliente,
  cli_fantasia, cli_pendencia, ped_despacho, tra_nome

  FROM pedido WITH (NOLOCK)
  INNER JOIN transpor WITH (NOLOCK) ON pedido.ped_despacho = transpor.tra_codigo
  INNER JOIN Clientes WITH (NOLOCK) ON pedido.ped_codigocliente = Clientes.cli_codigo
  WHERE (ped_codigocliente = 456457 )
  ORDER BY ped_dtentrega, Ped_despacho, cli_fantasia 
) D

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-01 : 16:00:29

Yeah, the first query would only produce one row since it is doing a SUM without a GROUP BY. I wasn't sure about the second one.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources