[[gluglu]]

Hola Casimiro !

Estoy en ello ... ahora me pongo de nuevo y a ver si lo termino tal y como lo expuse anteriormente.

Aunque no cumpla ningún propósito, quiero mostrar de qué tipo de sentencia sin optimizar estamos hablando ....

Código SQL [-]
Insert Into BOOKINGS_PAXDETAIL_TEMP
 (RANDOMNO, RANDOMNO2, BOOKINGNO, INTERNALNO, ROOMINGLISTNO,
  BUILDINGNO, DATEARRIVAL, DATEDEPARTURE, DATECALC,
  CATEGORYNO, SUBCATEGORYNO, DATEFROM, DATETO, CLIENTNO,
  OBJECTINTNO, BOOKINGNAME, SURNAME1, SURNAME2, NAME,
  OBJECTNO, PAXTYPE1_QUANT, BOARDTYPE1, PAXTYPE2_QUANT,
  BOARDTYPE2, PAXTYPE3_QUANT, BOARDTYPE3, P_TYPE1_QUANT,
  B_TYPE1, P_TYPE2_QUANT, B_TYPE2, P_TYPE3_QUANT, B_TYPE3,
  OPERATORDEBIT, CREATETIMESTAMP, CREATEUSER, CREATEPOSITION,
  MODIFYTIMESTAMP, MODIFYUSER, MODIFYPOSITION)
 
Select -1, -2, RL.BOOKINGNO,
  IIF(BPD.ROOMINGLISTNO is null, -GEN_ID(TEMP_PAX_DETAIL,1), RL.ROOMINGLISTNO),
  RL.ROOMINGLISTNO, BK.BUILDINGNO, RL.DATEARRIVAL, RL.DATEDEPARTURE,
  null as DATECALC, RL.CATEGORYNO, RL.SUBCATEGORYNO, BO.DATEFROM,
  BO.DATETO, RL.CLIENTNO, BO.INTERNALNO,
  Coalesce(SURNAME1, '') ||
    IIF(SURNAME2 is null, '', ' ' || SURNAME2) ||
    IIF(NAME is null, '', ', ' || NAME),
  RL.SURNAME1, RL.SURNAME2, RL.NAME, BO.OBJECTNO,
 
  case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
             from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO) = 1
       then RL.PAXQUANTITY
       else null
  end as PAXTYPE1_QUANT,
  case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
             from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO) = 1
            and
            (Select case when Count(Distinct BOARDTYPE) > 1 then -2 else
                    case when Count(Distinct BOARDTYPE) = 1 then Min(BOARDTYPE) else
                    -1 end end
             from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO) > 0
       then (Select Min(BOARDTYPE) from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO)
       else null
  end as BOARDTYPE1,
 
  case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
             from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO) = 2
       then RL.PAXQUANTITY
       else null
  end as PAXTYPE2_QUANT,
  case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
             from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO) = 2
            and
            (Select case when Count(Distinct BOARDTYPE) > 1 then -2 else
                    case when Count(Distinct BOARDTYPE) = 1 then Min(BOARDTYPE) else
                    -1 end end
             from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO) > 0
       then (Select Min(BOARDTYPE) from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO)
       else null
  end as BOARDTYPE2,
 
  case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
             from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO) = 3
       then RL.PAXQUANTITY
       else null
  end as PAXTYPE3_QUANT,
  case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
             from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO) = 3
            and
            (Select case when Count(Distinct BOARDTYPE) > 1 then -2 else
                    case when Count(Distinct BOARDTYPE) = 1 then Min(BOARDTYPE) else
                    -1 end end
             from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO) > 0
       then (Select Min(BOARDTYPE) from BOOKINGS_PAXTYPE BPT
             where BPT.BOOKINGNO = RL.BOOKINGNO)
       else null
  end as BOARDTYPE3,
 
  case when BPD.ROOMINGLISTNO is null
       then case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
                       from BOOKINGS_PAXTYPE BPT
                       where BPT.BOOKINGNO = RL.BOOKINGNO) = 1
                 then RL.PAXQUANTITY
                 else null
            end
       else case when BPD.PAXTYPE1_QUANT is not null
                 then BPD.PAXTYPE1_QUANT
                 else null
            end
  end as P_TYPE1_QUANT,
  case when BPD.ROOMINGLISTNO is null
       then case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
                       from BOOKINGS_PAXTYPE BPT
                       where BPT.BOOKINGNO = RL.BOOKINGNO) = 1
                       and (Select case when Count(Distinct BOARDTYPE) > 1 then -2 else
                                   case when Count(Distinct BOARDTYPE) = 1 then Min(BOARDTYPE) else
                                   -1 end end
                            from BOOKINGS_PAXTYPE BPT
                            where BPT.BOOKINGNO = RL.BOOKINGNO) > 0
                 then (Select Min(BOARDTYPE) from BOOKINGS_PAXTYPE BPT
                       where BPT.BOOKINGNO = RL.BOOKINGNO)
                 else null
            end
       else case when BPD.PAXTYPE1_QUANT is not null
                      and BPD.BOARDTYPE1 is not null
                 then BPD.BOARDTYPE1
                 else null
            end
  end as B_TYPE1,
 
  case when BPD.ROOMINGLISTNO is null
       then case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
                       from BOOKINGS_PAXTYPE BPT
                       where BPT.BOOKINGNO = RL.BOOKINGNO) = 2
                 then RL.PAXQUANTITY
                 else null
            end
       else case when BPD.PAXTYPE2_QUANT is not null
                 then BPD.PAXTYPE2_QUANT
                 else null
            end
  end as P_TYPE2_QUANT,
  case when BPD.ROOMINGLISTNO is null
       then case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
                       from BOOKINGS_PAXTYPE BPT
                       where BPT.BOOKINGNO = RL.BOOKINGNO) = 2
                       and (Select case when Count(Distinct BOARDTYPE) > 1 then -2 else
                                   case when Count(Distinct BOARDTYPE) = 1 then Min(BOARDTYPE) else
                                   -1 end end
                            from BOOKINGS_PAXTYPE BPT
                            where BPT.BOOKINGNO = RL.BOOKINGNO) > 0
                 then (Select Min(BOARDTYPE) from BOOKINGS_PAXTYPE BPT
                       where BPT.BOOKINGNO = RL.BOOKINGNO)
                 else null
            end
       else case when BPD.PAXTYPE2_QUANT is not null
                      and BPD.BOARDTYPE2 is not null
                 then BPD.BOARDTYPE2
                 else null
            end
  end as B_TYPE2,
  case when BPD.ROOMINGLISTNO is null
       then case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
                       from BOOKINGS_PAXTYPE BPT
                       where BPT.BOOKINGNO = RL.BOOKINGNO) = 3
                 then RL.PAXQUANTITY
                 else null
            end
       else case when BPD.PAXTYPE3_QUANT is not null
                 then BPD.PAXTYPE3_QUANT
                 else null
            end
  end as P_TYPE3_QUANT,
  case when BPD.ROOMINGLISTNO is null
       then case when (Select Coalesce(Min(BPT.PAXTYPE), -1)
                       from BOOKINGS_PAXTYPE BPT
                       where BPT.BOOKINGNO = RL.BOOKINGNO) = 3
                       and (Select case when Count(Distinct BOARDTYPE) > 1 then -2 else
                                   case when Count(Distinct BOARDTYPE) = 1 then Min(BOARDTYPE) else
                                   -1 end end
                            from BOOKINGS_PAXTYPE BPT
                            where BPT.BOOKINGNO = RL.BOOKINGNO) > 0
                 then (Select Min(BOARDTYPE) from BOOKINGS_PAXTYPE BPT
                       where BPT.BOOKINGNO = RL.BOOKINGNO)
                 else null
            end
       else case when BPD.PAXTYPE3_QUANT is not null
                      and BPD.BOARDTYPE3 is not null
                 then BPD.BOARDTYPE3
                 else null
            end
  end as B_TYPE3,
 
  99 as OPERATORDEBIT,
  BPD.CREATETIMESTAMP, BPD.CREATEUSER, BPD.CREATEPOSITION,
  BPD.MODIFYTIMESTAMP, BPD.MODIFYUSER, BPD.MODIFYPOSITION
from BOOKINGS_ROOMINGLIST RL
left join BOOKINGS_OBJECTS BO
  on BO.ROOMINGLISTNO = RL.ROOMINGLISTNO
  and BO.DATEFROM <= '04-30-2011' and BO.DATETO >= '04-01-2011'
left join BOOKINGS_PAXDETAIL BPD
  on RL.ROOMINGLISTNO = BPD.ROOMINGLISTNO
left join BOOKINGS BK
  on RL.BOOKINGNO = BK.BOOKINGNO
where RL.BOOKINGNO = 101974

Como podrás observar en todo este 'peassso' de Sentencia, el caso que estoy exponiendo en este hilo aparece múltiples veces en la sentencia, y creo que no va a ser muy eficiente de esta manera. Por eso estoy intentando mejorarlo.