Hi all,
had an unusual requirement regarding some sql statements and I
wanted to share what I've learned with the list, it might be useful to
someone.
We have 3 tables, say T1, T2, T3.
T1 has a one to many relationship to T2 and to T3.
The customer wants to list the tables as follows.
Say a record in T1 can be linked to 2 records in T2 and 5 records in T3.
Then he would want :
(record #s in parenthesis)(Fn = Field # n)
T1.F1(1) T1.F2(1) T1.F3(1) T1.F4(1) T2.F1(1) T2.F2(1)
T2F3(1) T3.F1(1) T3.F2(1) T3.F3(1)
T1.F1(1) T1.F2(1) T2.F1(2)
T2.F2(2) T2F3(2) T3.F1(2) T3.F2(2) T3.F3(2)
T1.F1(1) T1.F2(1)
T3.F1(3) T3.F2(3)
T3.F3(3)
T1.F1(1) T1.F2(1)
T3.F1(4) T3.F2(4)
T3.F3(4)
T1.F1(1) T1.F2(1)
T3.F1(5) T3.F2(5)
T3.F3(5)
T1.F1(2) T1.F2(2) T1.F3(2) T1.F4(2) T2.F1(3) T2.F2(3)
T2F3(3) T3.F1(6) T3.F2(6) T3.F3(6)
T1.F1(2) T1.F2(2) T2.F1(4)
T2.F2(4) T2F3(4) T3.F1(7) T3.F2(7) T3.F3(7)
T1.F1(2) T1.F2(2)
T3.F1(8) T3.F2(8)
T3.F3(8)
T1.F1(3) T1.F2(3) T1.F3(3) T1.F4(3) T2.F1(5) T2.F2(5)
T2F3(5) T3.F1(9) T3.F2(9) T3.F3(9)
T1.F1(3) T1.F2(3) T2.F1(6)
T2.F2(6) T2F3(6) T3.F1(10) T3.F2(10) T3.F3(10)
T1.F1(3) T1.F2(3) T2.F1(7)
T2.F2(7) T2F3(7)
T1.F1(3) T1.F2(3) T2.F1(8)
T2.F2(8) T2F3(8)
With normal joins I would get 1x2x5=10 lines in the first document while
the customer wants only 5.
And 1x2x3=6 lines in the second document while the customer wants only 3.
And 1x4x2=8 lines in the third document while the customer wants only 4.
Had to perform this in SQL Server queries.
So I first found out how to put correlative numbers to every record in
an SQL Server query :
----------------------
select IDENTITY(smallint, 1, 1) as 'nro', *
into #tempo
from adm35.ordpgd
where ordpgosuc = 1 and ordpgotpo = 1 and ordpgo in (3, 4, 5, 6)
select *
from #tempo
drop table #tempo
----------------------
Then I found out how to make the count start again when a group of
fields changed :
----------------------
select IDENTITY(smallint, 1, 1) as 'nro', *
into #tempo
from adm35.ordpgd
where ordpgosuc = 1 and ordpgotpo = 1 and ordpgo in (3, 4, 5, 6)
select nro-prim.Numero+1, *
from #tempo tempo
inner join (
select OrdPgoSuc, OrdPgoTpo, OrdPgo, min(nro) as 'Numero'
from #tempo
group by OrdPgoSuc, OrdPgoTpo, OrdPgo
) prim
on prim.OrdPgoSuc = tempo.OrdPgoSuc
and prim.OrdPgoTpo = tempo.OrdPgoTpo
and prim.OrdPgo = tempo.OrdPgo
drop table #tempo
----------------------
So now I can have line numbers to a document. The idea being to join the
tables by the corresponding fields AND ALSO by the line numbers in order
to comply with the requirement.
And now in the real query I join T2 and T3 not only by their
corresponding keys but also by their line number (the one that is
restarted for every document), sorry, I don't have the time to make an
easier example right now, this is the actual code (before putting the
filter variables in) where T1 = adm35.OrdCab (purchase orders), T2 =
adm35.AnxPgo (payments), and T3 = adm35.OrdPgd (invoices)
----------------------
select IDENTITY(smallint, 1, 1) as 'nro', Pgd.OrdPgoSuc, Pgd.OrdPgoTpo,
Pgd.OrdPgo
, Pgd.OrdPgoFOpe, Pgd.OrdPgoTOpe, Pgd.OrdPgoROpe,
Pgd.OrdPgoSOpe, Pgd.OrdPgoNOpe
, Pgd.OrdPgoIOpe
into #AuxPgd
from adm35.OrdPgd Pgd
inner join adm35.OrdCab OC
on OC.OrdPgoSuc = Pgd.OrdPgoSuc
and OC.OrdPgoTpo = Pgd.OrdPgoTpo
and OC.OrdPgo = Pgd.OrdPgo
where OC.OrdSta <> 'A'
and OC.OrdFch between '01/01/2008' and '22/01/2008'
Order by Pgd.OrdPgoSuc, Pgd.OrdPgoTpo, Pgd.OrdPgo
select nro-prim.Numero+1 as 'Por_Pgo', AuxPgd.*
into #Pgd
from #AuxPgd AuxPgd
inner join (
select OrdPgoSuc, OrdPgoTpo, OrdPgo, min(nro) as 'Numero'
from #AuxPgd
group by OrdPgoSuc, OrdPgoTpo, OrdPgo
) prim
on prim.OrdPgoSuc = AuxPgd.OrdPgoSuc
and prim.OrdPgoTpo = AuxPgd.OrdPgoTpo
and prim.OrdPgo = AuxPgd.OrdPgo
order by AuxPgd.OrdPgoSuc, AuxPgd.OrdPgoTpo, AuxPgd.OrdPgo, AuxPgd.nro
drop table #AuxPgd
select IDENTITY(smallint, 1, 1) as 'nro', AnxSuc, AnxPgoTpo, AnxPgoNro,
AnxOpeEnt
, AnxTpoOpe, AnxSucOpe, AnxNroOpe, AnxOpeImp, AnxOpeVto
into #AuxAnx
from adm35.AnxPgo Anx
inner join adm35.OrdCab OC
on OC.OrdPgoSuc = Anx.AnxSuc
and OC.OrdPgoTpo = Anx.AnxPgoTpo
and OC.OrdPgo = Anx.AnxPgoNro
where OC.OrdSta <> 'A'
and OC.OrdFch between '01/01/2008' and '22/01/2008'
order by AnxSuc, AnxPgoTpo, AnxPgoNro
select nro-prim.Numero+1 as 'Por_Pgo', AuxAnx.*
into #Anx
from #AuxAnx AuxAnx
inner join (
select AnxSuc, AnxPgoTpo, AnxPgoNro, min(nro) as 'Numero'
from #AuxAnx
group by AnxSuc, AnxPgoTpo, AnxPgoNro
) prim
on prim.AnxSuc = AuxAnx.AnxSuc
and prim.AnxPgoTpo = AuxAnx.AnxPgoTpo
and prim.AnxPgoNro = AuxAnx.AnxPgoNro
order by AuxAnx.AnxSuc, AuxAnx.AnxPgoTpo, AuxAnx.AnxPgoNro, AuxAnx.nro
drop table #AuxAnx
select OC.OrdFch as 'Emision', OC.OrdPrvId as 'Entidad', OC.OrdPrvNom as
'Nombre'
, OC.OrdPgo as 'OP', case when lins.Renglon = 1 then
TPgd.TotIOpe else NULL end as 'Total_Cancelado'
, case when lins.Renglon = 1 then OC.OrdIvaImp else NULL end as
'Ret_IVA'
, case when lins.Renglon = 1 then OC.OrdGanImp1 else NULL end as
'Ret_Gan'
, case when lins.Renglon = 1 then OC.OrdIBrImp else NULL end as
'Ret_IB'
, case when lins.Renglon = 1 then OC.OrdSUSSImp else NULL end as
'Ret_SUSS'
, case when lins.Renglon = 1
then
TPgd.TotIOpe-OC.OrdIvaImp-OC.OrdGanImp1-OC.OrdIBrImp-OC.OrdSUSSImp
else NULL end as 'Neto_Pagado'
, Lins.AnxOpeEnt as 'Bco', Lins.AnxTpoOpe as 'Tipo',
Lins.AnxSucOpe as 'Sucursal'
, Lins.AnxNroOpe as 'Chq_Nro', Lins.AnxOpeImp as 'Importe',
Lins.AnxOpeVto as 'Fec_Cobro'
, Lins.OrdPgoFOpe as 'Fec_Pgo', Lins.OrdPgoTOpe as 'Tipo_Pgo',
Lins.OrdPgoROpe as 'Letra'
, Lins.OrdPgoSOpe as 'Suc_Pgo', Lins.OrdPgoNOpe as 'Nro_Pgo',
Lins.OrdPgoIOpe as 'Imp_pgo'
from adm35.OrdCab OC
left join (select OrdPgoSuc, OrdPgoTpo, OrdPgo, sum(OrdPgoIOpe) as 'TotIOpe'
from adm35.OrdPgd
group by OrdPgoSuc, OrdPgoTpo, OrdPgo
) TPgd
on OC.OrdPgoSuc = TPgd.OrdPgoSuc
and OC.OrdPgoTpo = TPgd.OrdPgoTpo
and OC.OrdPgo = TPgd.OrdPgo
left join (
select IsNull(Pgd.OrdPgoSuc, Anx.AnxSuc) as 'Suc',
IsNull(Pgd.OrdPgoTpo, Anx.AnxPgoTpo) as 'Tpo'
, IsNull(Pgd.OrdPgo, Anx.AnxPgoNro) as 'Nro',
IsNull(Pgd.Por_Pgo, Anx.Por_Pgo) as 'Renglon'
, AnxOpeEnt, AnxTpoOpe, AnxSucOpe, AnxNroOpe,
AnxOpeImp, AnxOpeVto, Pgd.OrdPgoFOpe
, Pgd.OrdPgoTOpe, Pgd.OrdPgoROpe, Pgd.OrdPgoSOpe,
Pgd.OrdPgoNOpe, Pgd.OrdPgoIOpe
from #Pgd Pgd
full join #Anx Anx
on Pgd.OrdPgoSuc = Anx.AnxSuc
and Pgd.OrdPgoTpo = Anx.AnxPgoTpo
and Pgd.OrdPgo = Anx.AnxPgoNro
and Pgd.Por_Pgo = Anx.Por_Pgo
) Lins
on OC.OrdPgoSuc = Lins.Suc
and OC.OrdPgoTpo = Lins.Tpo
and OC.OrdPgo = Lins.Nro
where OC.OrdSta <> 'A'
and OC.OrdFch between '01/01/2008' and '22/01/2008'
order by OC.OrdFch, OC.OrdPrvId, OC.OrdPgo, Lins.Renglon
drop table #Pgd
drop table #Anx
----------------------
These queries will return exactly what I was asked for, they would be
run from a vfp application (through sqlExec()) which will then copy and
format them into an Excel spreadsheet.
Run into a problem, sometimes SQL Server's ODBC will not give any error
but will return no cursors (usually when I use temporary tables like
#AuxPgd), so I had to find a workaround.
This is how I solved it (this is VFP6), these first queries are against
SQL Server (filter variables are in, this is the final product) :
----------------------
select Pgd.OrdPgoSuc, Pgd.OrdPgoTpo, Pgd.OrdPgo
, Pgd.OrdPgoFOpe, Pgd.OrdPgoTOpe, Pgd.OrdPgoROpe,
Pgd.OrdPgoSOpe, Pgd.OrdPgoNOpe
, Pgd.OrdPgoIOpe
-- Cursor Salida #AuxPgd#
from adm35.OrdPgd Pgd
inner join adm35.OrdCab OC
on OC.OrdPgoSuc = Pgd.OrdPgoSuc
and OC.OrdPgoTpo = Pgd.OrdPgoTpo
and OC.OrdPgo = Pgd.OrdPgo
where OC.OrdSta <> 'A'
and OC.OrdPgoSuc = @Empresa
and OC.OrdFch between @DdeFecha and @HtaFecha
and (@TipoOP = -1 or OC.OrdPgoTpo = @TipoOP)
and OC.OrdPgo between @OPDesde and @OPHasta
and (@Empid = -1 or OC.OrdPrvId = @EmpId)
Order by Pgd.OrdPgoSuc, Pgd.OrdPgoTpo, Pgd.OrdPgo
select AnxSuc, AnxPgoTpo, AnxPgoNro, AnxOpeEnt
, AnxTpoOpe, AnxSucOpe, AnxNroOpe, AnxOpeImp, AnxOpeVto
-- Cursor Salida #AuxAnx#
from adm35.AnxPgo Anx
inner join adm35.OrdCab OC
on OC.OrdPgoSuc = Anx.AnxSuc
and OC.OrdPgoTpo = Anx.AnxPgoTpo
and OC.OrdPgo = Anx.AnxPgoNro
where OC.OrdSta <> 'A'
and OC.OrdPgoSuc = @Empresa
and OC.OrdFch between @DdeFecha and @HtaFecha
and (@TipoOP = -1 or OC.OrdPgoTpo = @TipoOP)
and OC.OrdPgo between @OPDesde and @OPHasta
and (@Empid = -1 or OC.OrdPrvId = @EmpId)
order by AnxSuc, AnxPgoTpo, AnxPgoNro
select OC.OrdFch as 'Emision', OC.OrdPrvId as 'Entidad', Emp.EmpNom as
'Nombre'
, OC.OrdPgoSuc as 'Suc', Oc.OrdPgoTpo as 'Tipo', OC.OrdPgo as 'OP'
, TPgd.TotIOpe, OC.OrdIvaImp, OC.OrdGanImp1, OC.OrdIBrImp,
OC.OrdSUSSImp
-- Cursor Salida #OC#
from adm35.OrdCab OC
left join (select OrdPgoSuc, OrdPgoTpo, OrdPgo, sum(OrdPgoIOpe) as 'TotIOpe'
from adm35.OrdPgd
group by OrdPgoSuc, OrdPgoTpo, OrdPgo
) TPgd
on OC.OrdPgoSuc = TPgd.OrdPgoSuc
and OC.OrdPgoTpo = TPgd.OrdPgoTpo
and OC.OrdPgo = TPgd.OrdPgo
left join adm35.emp Emp
on Emp.EmpId = OC.OrdPrvId
where OC.OrdSta <> 'A'
and OC.OrdPgoSuc = @Empresa
and OC.OrdFch between @DdeFecha and @HtaFecha
and (@TipoOP = -1 or OC.OrdPgoTpo = @TipoOP)
and OC.OrdPgo between @OPDesde and @OPHasta
and (@Empid = -1 or OC.OrdPrvId = @EmpId)
----------------------
Next, in my vfp application I take the cursors created by these previous
queries and process them (the following are VFP6 queries) :
----------------------
select Recno() as 'nro', *
into cursor PrePgd
from Auxpgd
Order by OrdPgoSuc, OrdPgoTpo, OrdPgo
select OrdPgoSuc, OrdPgoTpo, OrdPgo, min(nro) as 'Numero'
into cursor primPgd
from PrePgd
group by OrdPgoSuc, OrdPgoTpo, OrdPgo
select nro-prim.Numero+1 as 'Por_Pgo', PrePgd.*
into cursor Pgd
from PrePgd
inner join primPgd prim
on prim.OrdPgoSuc = PrePgd.OrdPgoSuc
and prim.OrdPgoTpo = PrePgd.OrdPgoTpo
and prim.OrdPgo = PrePgd.OrdPgo
order by PrePgd.OrdPgoSuc, PrePgd.OrdPgoTpo, PrePgd.OrdPgo, PrePgd.nro
select Recno() as 'nro', *
into cursor PreAnx
from AuxAnx
order by AnxSuc, AnxPgoTpo, AnxPgoNro
select AnxSuc, AnxPgoTpo, AnxPgoNro, min(nro) as 'Numero'
into cursor primAnx
from PreAnx
group by AnxSuc, AnxPgoTpo, AnxPgoNro
select nro-prim.Numero+1 as 'Por_Pgo', PreAnx.*
into cursor Anx
from PreAnx
inner join primAnx prim
on prim.AnxSuc = PreAnx.AnxSuc
and prim.AnxPgoTpo = PreAnx.AnxPgoTpo
and prim.AnxPgoNro = PreAnx.AnxPgoNro
order by PreAnx.AnxSuc, PreAnx.AnxPgoTpo, PreAnx.AnxPgoNro, PreAnx.nro
select NVL(Pgd.OrdPgoSuc, Anx.AnxSuc) as 'Suc', NVL(Pgd.OrdPgoTpo,
Anx.AnxPgoTpo) as 'Tpo'
, NVL(Pgd.OrdPgo, Anx.AnxPgoNro) as 'Nro', NVL(Pgd.Por_Pgo,
Anx.Por_Pgo) as 'Renglon'
, AnxOpeEnt, AnxTpoOpe, AnxSucOpe, AnxNroOpe, AnxOpeImp,
AnxOpeVto, Pgd.OrdPgoFOpe
, Pgd.OrdPgoTOpe, Pgd.OrdPgoROpe, Pgd.OrdPgoSOpe,
Pgd.OrdPgoNOpe, Pgd.OrdPgoIOpe
into cursor Lins
from Pgd
full join Anx
on Pgd.OrdPgoSuc = Anx.AnxSuc
and Pgd.OrdPgoTpo = Anx.AnxPgoTpo
and Pgd.OrdPgo = Anx.AnxPgoNro
and Pgd.Por_Pgo = Anx.Por_Pgo
select OC.Emision, OC.Entidad, OC.Nombre, OC.Suc, OC.Tipo as 'Tipo_OP',
OC.OP
, iif(lins.Renglon = 1, transf(OC.TotIOpe), space(20)) as
'Total_Cancelado'
, iif(lins.Renglon = 1, transf(OC.OrdIvaImp), space(20)) as
'Ret_IVA'
, iif(lins.Renglon = 1, transf(OC.OrdGanImp1), space(20)) as
'Ret_Gan'
, iif(lins.Renglon = 1, transf(OC.OrdIBrImp), space(20)) as 'Ret_IB'
, iif(lins.Renglon = 1, transf(OC.OrdSUSSImp), space(20)) as
'Ret_SUSS'
, iif(lins.Renglon = 1
,
transf(OC.TotIOpe-OC.OrdIvaImp-OC.OrdGanImp1-OC.OrdIBrImp-OC.OrdSUSSImp)
, space(20)) as 'Neto_Pagado'
, Lins.AnxOpeEnt as 'Bco', Lins.AnxTpoOpe as 'Tipo',
Lins.AnxSucOpe as 'Sucursal'
, Lins.AnxNroOpe as 'Chq_Nro', Lins.AnxOpeImp as 'Importe',
Lins.AnxOpeVto as 'Fec_Cobro'
, Lins.OrdPgoFOpe as 'Fec_Pgo', Lins.OrdPgoTOpe as 'Tipo_Pgo',
Lins.OrdPgoROpe as 'Letra'
, Lins.OrdPgoSOpe as 'Suc_Pgo', Lins.OrdPgoNOpe as 'Nro_Pgo',
Lins.OrdPgoIOpe as 'Imp_pgo'
from OC
left join Lins
on OC.Suc = Lins.Suc
and OC.Tipo = Lins.Tpo
and OC.OP = Lins.Nro
order by OC.Emision, OC.Entidad, OC.OP, Lins.Renglon
use in select('PrePgd')
use in select('primPgd')
use in select('Pgd')
use in select('PreAnx')
use in select('primAnx')
use in select('Anx')
use in select('Lins')
----------------------
To finally get the result (the last select, the one that has no "into
cursor" clause) and copy and format it into an Excel spreadsheet.
HTH
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
