[[gluglu]]

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 :

Código SQL [-]
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 :

Código SQL [-]
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