gluglu
13-05-2012, 10:33:39
Hola a todos !
Tengo una consulta que siempre me ha funcionado correctamente, hasta que he cambiado la versión de Delphi a XE2 (antes trabajaba con Delphi 2007). Utilizo además Firebird 2.1.
Esta consulta que muestro aquí, a su vez una subconsulta dentro de otra consulta mayor :
Select Sum((INV3.AMOUNT - ROUND(INV3.AMOUNT * Coalesce(INV3.DISCOUNT,0) / 100, 2)) +
Round(((INV3.AMOUNT - ROUND(INV3.AMOUNT * Coalesce(INV3.DISCOUNT,0) / 100, 2)) *
case when INV3.INVOICEDATE is not null then
IIF(INV3.VAT_TYPE = 1, FX.VAT_TYPE_1, IIF(INV3.VAT_TYPE = 2, FX.VAT_TYPE_2, FX.VAT_TYPE_3))
else
IIF(INV3.VAT_TYPE = 1, FX2.VAT_TYPE_1, IIF(INV3.VAT_TYPE = 2, FX2.VAT_TYPE_2, FX2.VAT_TYPE_3))
end
/100),2))
from INVOICES INV3
left join FIXEDVALUES FX
on INV3.BUILDINGNO = FX.BUILDINGNO
and INV3.INVOICEDATE between FX.DATEFROM and FX.DATETO
left join FIXEDVALUES FX2
on INV3.BUILDINGNO = FX2.BUILDINGNO
and 'TODAY' between FX2.DATEFROM and FX2.DATETO
where INV3.INVOICENO = 200001
and INV3.INVOICESERIE = 2
and INV3.DEBIT_CREDIT = 1
and INV3.VAT_INCLUDED = 1
Si pongo esta consulta de manera independiente, funciona correctamente, pero si la dejo como parte de la consulta completa, al intentar 'activar' el IBDataSet dentro del propio Delphi, o en tiempo de ejecución, me dá un error indicando que no puede encontrar un campo '' (sin denominación).
La Consulta completa original sería :
Select INV.INVOICESERIE, INV.INVOICENO, INV.INVOICEDATE,
INV.OPERATOR, INUM.IDENTIFICATION,
(Select Coalesce(Sum(INV2.AMOUNT - ROUND(INV2.AMOUNT * Coalesce(INV2.DISCOUNT,0) / 100, 2)), 0)
from INVOICES INV2
where INV2.INVOICENO = INV.INVOICENO
and (((INV.INVOICESERIE is null) and (INV2.INVOICESERIE is null)) or
((INV.INVOICESERIE is not null) and (INV2.INVOICESERIE = INV.INVOICESERIE)))
and INV2.DEBIT_CREDIT = 1
and INV2.VAT_INCLUDED = 1) as TOT_DEBIT_VAT_INCL,
(Select Sum((INV3.AMOUNT - ROUND(INV3.AMOUNT * Coalesce(INV3.DISCOUNT,0) / 100, 2)) +
Round(((INV3.AMOUNT - ROUND(INV3.AMOUNT * Coalesce(INV3.DISCOUNT,0) / 100, 2)) *
case when INV3.INVOICEDATE is not null then
IIF(INV3.VAT_TYPE = 1, FX.VAT_TYPE_1, IIF(INV3.VAT_TYPE = 2, FX.VAT_TYPE_2, FX.VAT_TYPE_3))
else
IIF(INV3.VAT_TYPE = 1, FX2.VAT_TYPE_1, IIF(INV3.VAT_TYPE = 2, FX2.VAT_TYPE_2, FX2.VAT_TYPE_3))
end /100),2))
from INVOICES INV3
left join FIXEDVALUES FX
on INV3.BUILDINGNO = FX.BUILDINGNO
and INV3.INVOICEDATE between FX.DATEFROM and FX.DATETO
left join FIXEDVALUES FX2
on INV3.BUILDINGNO = FX2.BUILDINGNO
and 'TODAY' between FX2.DATEFROM and FX2.DATETO
where INV3.INVOICENO = INV.INVOICENO
and (((INV.INVOICESERIE is null) and (INV3.INVOICESERIE is null)) or
((INV.INVOICESERIE is not null) and (INV3.INVOICESERIE = INV.INVOICESERIE)))
and INV3.DEBIT_CREDIT = 1
and INV3.VAT_INCLUDED = 0) as TOT_DEBIT_VAT_EXCL,
(Select Coalesce(Sum(INV4.AMOUNT - ROUND(INV4.AMOUNT * Coalesce(INV4.DISCOUNT,0) / 100, 2)), 0)
from INVOICES INV4
where INV4.INVOICENO = INV.INVOICENO
and (((INV.INVOICESERIE is null) and (INV4.INVOICESERIE is null)) or
((INV.INVOICESERIE is not null) and (INV4.INVOICESERIE = INV.INVOICESERIE)))
and INV4.DEBIT_CREDIT = 2
and INV4.VAT_INCLUDED = 1) as TOT_CREDIT_VAT_INCL,
(Select Coalesce(Sum((INV5.AMOUNT - ROUND(INV5.AMOUNT * Coalesce(INV5.DISCOUNT,0) / 100, 2)) +
Round(((INV5.AMOUNT - ROUND(INV5.AMOUNT * Coalesce(INV5.DISCOUNT,0) / 100, 2)) *
case when INV5.INVOICEDATE is not null and INV5.VAT_TYPE = 1 then FX.VAT_TYPE_1 else
case when INV5.INVOICEDATE is not null and INV5.VAT_TYPE = 2 then FX.VAT_TYPE_2 else
case when INV5.INVOICEDATE is not null and INV5.VAT_TYPE = 3 then FX.VAT_TYPE_3 else
case when INV5.INVOICEDATE is null and INV5.VAT_TYPE = 1 then FX2.VAT_TYPE_1 else
case when INV5.INVOICEDATE is null and INV5.VAT_TYPE = 2 then FX2.VAT_TYPE_2 else
case when INV5.INVOICEDATE is null and INV5.VAT_TYPE = 3 then FX2.VAT_TYPE_3 end end end end end end
/100),2)), 0)
from INVOICES INV5
left join FIXEDVALUES FX
on INV5.BUILDINGNO = FX.BUILDINGNO
and INV5.INVOICEDATE between FX.DATEFROM and FX.DATETO
left join FIXEDVALUES FX2
on INV5.BUILDINGNO = FX2.BUILDINGNO
and 'TODAY' between FX2.DATEFROM and FX2.DATETO
where INV5.INVOICENO = INV.INVOICENO
and (((INV.INVOICESERIE is null) and (INV5.INVOICESERIE is null)) or
((INV.INVOICESERIE is not null) and (INV5.INVOICESERIE = INV.INVOICESERIE)))
and INV5.DEBIT_CREDIT = 2
and INV5.VAT_INCLUDED = 0) as TOT_CREDIT_VAT_EXCL,
(Select Coalesce(Sum(PD.AMOUNT), 0) from PAYMENTS_DETAIL PD
where (((INV.INVOICESERIE is null) and (PD.INVOICE_SERIE is Null)) or
((INV.INVOICESERIE is not null) and (PD.INVOICE_SERIE = INV.INVOICESERIE)))
and PD.INVOICENO = INV.INVOICENO) as TOT_PAYMENTS,
BK.CLIENTNAME,
BK.DATEARRIVAL as BK_ARRIVAL,
BK.DATEDEPARTURE as BK_DEPARTURE,
RL.SURNAME1,
RL.SURNAME2,
RL.NAME,
RL.DATEARRIVAL as RL_ARRIVAL,
RL.DATEDEPARTURE as RL_DEPARTURE,
BK.OPERATORTYPE,
AG.NAME as AG_NAME,
CO.NAME as CO_NAME,
(Select IH.NAME from INVOICES_HEADERS IH
where IH.BOOKINGNO is null
and IH.ROOMINGLISTNO is null
and IH.OPERATOR is null
and IH.INVOICEDIV is null
and (((INV.INVOICESERIE is null) and (IH.INVOICESERIE is Null)) or
((INV.INVOICESERIE is not null) and (IH.INVOICESERIE = INV.INVOICESERIE)))
and IH.INVOICENO = INV.INVOICENO
and IH.JOINNO is null) as IH1_NAME
from INVOICES INV
left join BOOKINGS BK
on BK.BOOKINGNO = INV.BOOKINGNO
left join BOOKINGS_ROOMINGLIST RL
on RL.ROOMINGLISTNO = INV.ROOMINGLISTNO
left join AGENCIES AG
on AG.AGENCYNO = BK.OPERATORNO
and AG.BRANCHNO = 0
left join COMPANIES CO
on CO.COMPANYNO = BK.OPERATORNO
left join INVOICES_NUM INUM
on INUM.SERIALNO = INV.INVOICESERIE
where INV.INVOICENO is not null
group by INV.INVOICESERIE, INV.INVOICENO, INV.INVOICEDATE, INV.OPERATOR, INUM.IDENTIFICATION,
BK.CLIENTNAME, RL.SURNAME1, RL.SURNAME2, RL.NAME, BK.OPERATORTYPE, AG.NAME,
CO.NAME, BK.DATEARRIVAL, BK.DATEDEPARTURE, RL.DATEARRIVAL, RL.DATEDEPARTURE
order by INV.INVOICESERIE, INV.INVOICENO
Tanto en la primera versión, que he utilizado un 'Case' y después sentencias 'IIF', como en la versión 'original' de la consulta, me salta el error indicado. Este error no me ocurre, ni en el IBExpert ni en la versión Delphi 2007, sólo en entorno de edición o de ejecución con Delphi XE2.
Lo que necesito calcular sería el importe total de una factura con IVA Incluido, dependiendo si los registros individuales de esa factura de marcaron como 'con o sin Iva incluido'.
Además el Case o IIF lo he utilziado ya que si la factura todavía no ha sido emitida, debo de calcular el tipo de IVA que rige con fecha de 'Hoy', pero si la factura ya fue emitida, el tipo de IVA a aplicar será el tipo de IVA válido en el momento de la emisión de la factura. Tipos de IVA que a su vez guardo en otra tabla denominada FIXEDVALUES.
Gracias por vuestra ayuda.
Saludos ;)
Tengo una consulta que siempre me ha funcionado correctamente, hasta que he cambiado la versión de Delphi a XE2 (antes trabajaba con Delphi 2007). Utilizo además Firebird 2.1.
Esta consulta que muestro aquí, a su vez una subconsulta dentro de otra consulta mayor :
Select Sum((INV3.AMOUNT - ROUND(INV3.AMOUNT * Coalesce(INV3.DISCOUNT,0) / 100, 2)) +
Round(((INV3.AMOUNT - ROUND(INV3.AMOUNT * Coalesce(INV3.DISCOUNT,0) / 100, 2)) *
case when INV3.INVOICEDATE is not null then
IIF(INV3.VAT_TYPE = 1, FX.VAT_TYPE_1, IIF(INV3.VAT_TYPE = 2, FX.VAT_TYPE_2, FX.VAT_TYPE_3))
else
IIF(INV3.VAT_TYPE = 1, FX2.VAT_TYPE_1, IIF(INV3.VAT_TYPE = 2, FX2.VAT_TYPE_2, FX2.VAT_TYPE_3))
end
/100),2))
from INVOICES INV3
left join FIXEDVALUES FX
on INV3.BUILDINGNO = FX.BUILDINGNO
and INV3.INVOICEDATE between FX.DATEFROM and FX.DATETO
left join FIXEDVALUES FX2
on INV3.BUILDINGNO = FX2.BUILDINGNO
and 'TODAY' between FX2.DATEFROM and FX2.DATETO
where INV3.INVOICENO = 200001
and INV3.INVOICESERIE = 2
and INV3.DEBIT_CREDIT = 1
and INV3.VAT_INCLUDED = 1
Si pongo esta consulta de manera independiente, funciona correctamente, pero si la dejo como parte de la consulta completa, al intentar 'activar' el IBDataSet dentro del propio Delphi, o en tiempo de ejecución, me dá un error indicando que no puede encontrar un campo '' (sin denominación).
La Consulta completa original sería :
Select INV.INVOICESERIE, INV.INVOICENO, INV.INVOICEDATE,
INV.OPERATOR, INUM.IDENTIFICATION,
(Select Coalesce(Sum(INV2.AMOUNT - ROUND(INV2.AMOUNT * Coalesce(INV2.DISCOUNT,0) / 100, 2)), 0)
from INVOICES INV2
where INV2.INVOICENO = INV.INVOICENO
and (((INV.INVOICESERIE is null) and (INV2.INVOICESERIE is null)) or
((INV.INVOICESERIE is not null) and (INV2.INVOICESERIE = INV.INVOICESERIE)))
and INV2.DEBIT_CREDIT = 1
and INV2.VAT_INCLUDED = 1) as TOT_DEBIT_VAT_INCL,
(Select Sum((INV3.AMOUNT - ROUND(INV3.AMOUNT * Coalesce(INV3.DISCOUNT,0) / 100, 2)) +
Round(((INV3.AMOUNT - ROUND(INV3.AMOUNT * Coalesce(INV3.DISCOUNT,0) / 100, 2)) *
case when INV3.INVOICEDATE is not null then
IIF(INV3.VAT_TYPE = 1, FX.VAT_TYPE_1, IIF(INV3.VAT_TYPE = 2, FX.VAT_TYPE_2, FX.VAT_TYPE_3))
else
IIF(INV3.VAT_TYPE = 1, FX2.VAT_TYPE_1, IIF(INV3.VAT_TYPE = 2, FX2.VAT_TYPE_2, FX2.VAT_TYPE_3))
end /100),2))
from INVOICES INV3
left join FIXEDVALUES FX
on INV3.BUILDINGNO = FX.BUILDINGNO
and INV3.INVOICEDATE between FX.DATEFROM and FX.DATETO
left join FIXEDVALUES FX2
on INV3.BUILDINGNO = FX2.BUILDINGNO
and 'TODAY' between FX2.DATEFROM and FX2.DATETO
where INV3.INVOICENO = INV.INVOICENO
and (((INV.INVOICESERIE is null) and (INV3.INVOICESERIE is null)) or
((INV.INVOICESERIE is not null) and (INV3.INVOICESERIE = INV.INVOICESERIE)))
and INV3.DEBIT_CREDIT = 1
and INV3.VAT_INCLUDED = 0) as TOT_DEBIT_VAT_EXCL,
(Select Coalesce(Sum(INV4.AMOUNT - ROUND(INV4.AMOUNT * Coalesce(INV4.DISCOUNT,0) / 100, 2)), 0)
from INVOICES INV4
where INV4.INVOICENO = INV.INVOICENO
and (((INV.INVOICESERIE is null) and (INV4.INVOICESERIE is null)) or
((INV.INVOICESERIE is not null) and (INV4.INVOICESERIE = INV.INVOICESERIE)))
and INV4.DEBIT_CREDIT = 2
and INV4.VAT_INCLUDED = 1) as TOT_CREDIT_VAT_INCL,
(Select Coalesce(Sum((INV5.AMOUNT - ROUND(INV5.AMOUNT * Coalesce(INV5.DISCOUNT,0) / 100, 2)) +
Round(((INV5.AMOUNT - ROUND(INV5.AMOUNT * Coalesce(INV5.DISCOUNT,0) / 100, 2)) *
case when INV5.INVOICEDATE is not null and INV5.VAT_TYPE = 1 then FX.VAT_TYPE_1 else
case when INV5.INVOICEDATE is not null and INV5.VAT_TYPE = 2 then FX.VAT_TYPE_2 else
case when INV5.INVOICEDATE is not null and INV5.VAT_TYPE = 3 then FX.VAT_TYPE_3 else
case when INV5.INVOICEDATE is null and INV5.VAT_TYPE = 1 then FX2.VAT_TYPE_1 else
case when INV5.INVOICEDATE is null and INV5.VAT_TYPE = 2 then FX2.VAT_TYPE_2 else
case when INV5.INVOICEDATE is null and INV5.VAT_TYPE = 3 then FX2.VAT_TYPE_3 end end end end end end
/100),2)), 0)
from INVOICES INV5
left join FIXEDVALUES FX
on INV5.BUILDINGNO = FX.BUILDINGNO
and INV5.INVOICEDATE between FX.DATEFROM and FX.DATETO
left join FIXEDVALUES FX2
on INV5.BUILDINGNO = FX2.BUILDINGNO
and 'TODAY' between FX2.DATEFROM and FX2.DATETO
where INV5.INVOICENO = INV.INVOICENO
and (((INV.INVOICESERIE is null) and (INV5.INVOICESERIE is null)) or
((INV.INVOICESERIE is not null) and (INV5.INVOICESERIE = INV.INVOICESERIE)))
and INV5.DEBIT_CREDIT = 2
and INV5.VAT_INCLUDED = 0) as TOT_CREDIT_VAT_EXCL,
(Select Coalesce(Sum(PD.AMOUNT), 0) from PAYMENTS_DETAIL PD
where (((INV.INVOICESERIE is null) and (PD.INVOICE_SERIE is Null)) or
((INV.INVOICESERIE is not null) and (PD.INVOICE_SERIE = INV.INVOICESERIE)))
and PD.INVOICENO = INV.INVOICENO) as TOT_PAYMENTS,
BK.CLIENTNAME,
BK.DATEARRIVAL as BK_ARRIVAL,
BK.DATEDEPARTURE as BK_DEPARTURE,
RL.SURNAME1,
RL.SURNAME2,
RL.NAME,
RL.DATEARRIVAL as RL_ARRIVAL,
RL.DATEDEPARTURE as RL_DEPARTURE,
BK.OPERATORTYPE,
AG.NAME as AG_NAME,
CO.NAME as CO_NAME,
(Select IH.NAME from INVOICES_HEADERS IH
where IH.BOOKINGNO is null
and IH.ROOMINGLISTNO is null
and IH.OPERATOR is null
and IH.INVOICEDIV is null
and (((INV.INVOICESERIE is null) and (IH.INVOICESERIE is Null)) or
((INV.INVOICESERIE is not null) and (IH.INVOICESERIE = INV.INVOICESERIE)))
and IH.INVOICENO = INV.INVOICENO
and IH.JOINNO is null) as IH1_NAME
from INVOICES INV
left join BOOKINGS BK
on BK.BOOKINGNO = INV.BOOKINGNO
left join BOOKINGS_ROOMINGLIST RL
on RL.ROOMINGLISTNO = INV.ROOMINGLISTNO
left join AGENCIES AG
on AG.AGENCYNO = BK.OPERATORNO
and AG.BRANCHNO = 0
left join COMPANIES CO
on CO.COMPANYNO = BK.OPERATORNO
left join INVOICES_NUM INUM
on INUM.SERIALNO = INV.INVOICESERIE
where INV.INVOICENO is not null
group by INV.INVOICESERIE, INV.INVOICENO, INV.INVOICEDATE, INV.OPERATOR, INUM.IDENTIFICATION,
BK.CLIENTNAME, RL.SURNAME1, RL.SURNAME2, RL.NAME, BK.OPERATORTYPE, AG.NAME,
CO.NAME, BK.DATEARRIVAL, BK.DATEDEPARTURE, RL.DATEARRIVAL, RL.DATEDEPARTURE
order by INV.INVOICESERIE, INV.INVOICENO
Tanto en la primera versión, que he utilizado un 'Case' y después sentencias 'IIF', como en la versión 'original' de la consulta, me salta el error indicado. Este error no me ocurre, ni en el IBExpert ni en la versión Delphi 2007, sólo en entorno de edición o de ejecución con Delphi XE2.
Lo que necesito calcular sería el importe total de una factura con IVA Incluido, dependiendo si los registros individuales de esa factura de marcaron como 'con o sin Iva incluido'.
Además el Case o IIF lo he utilziado ya que si la factura todavía no ha sido emitida, debo de calcular el tipo de IVA que rige con fecha de 'Hoy', pero si la factura ya fue emitida, el tipo de IVA a aplicar será el tipo de IVA válido en el momento de la emisión de la factura. Tipos de IVA que a su vez guardo en otra tabla denominada FIXEDVALUES.
Gracias por vuestra ayuda.
Saludos ;)