Utilizar un valor de un Select dentro de otro Select

[[gluglu]]

Uff !

A ver cómo me explico ....

No sé si se puede utilizar en FIREBIRD, un valor de un Select determinado que se va a repetir varias veces dentro de otro Select. Algo así como :

Código SQL [-]
Select CampoA1, CampoA2,
  (Select Min(CampoB1) from TABLA_B) as Min_CampoB1,
  case when Min_CampoB1 = 1 then ..... else .... end as Resultado1,
  case when Min_CampoB1 = 2 then ..... else .... end as Resultado2,
  case when Min_CampoB1 = 3 then ..... else .... end as Resultado3
from TABLA_A

He esquematizado bastante la consulta aquí expuesta, pero realmente el valor (Min_CampoB1) del Select de la Subconsulta necesito utilizarlo en numerosas condiciones y subconsultas posteriores, por lo que no considero rápido y eficiente utilizarlo de esta manera :

Código SQL [-]
Select CampoA1, CampoA2,
  case when (Select Min(CampoB1) from TABLA_B) = 1 then ..... else .... end as Resultado1,
  case when (Select Min(CampoB1) from TABLA_B) = 2 then ..... else .... end as Resultado2,
  case when (Select Min(CampoB1) from TABLA_B) = 3 then ..... else .... end as Resultado3
from TABLA_A

Algún consejo ? ... muchas gracias !

[[gluglu]]

No sé si lo siguiente es correcto y si realmente es eficiente en cuanto a tiempo de ejecución frente a otras posibilidades :

Código SQL [-]
Select TA.CampoA1, TA.CampoA2,
  case when TB.Min_CampoB1 = 1 then ..... else .... end as Resultado1,
  case when TB.Min_CampoB1 = 2 then ..... else .... end as Resultado2,
  case when TB.Min_CampoB1 = 3 then ..... else .... end as Resultado3
from TABLA_A TA
left join SP_PRUEBA(TA.CLAVE1) TB
  on TA.CLAVE1 = TA.CLAVE1

... pero me permite utilizar TB.Min_CampoB1 como 'valor' en toda la consulta.

El Stored Procedure SP_PRUEBA se podría definir como :

Código SQL [-]
CREATE OR ALTER PROCEDURE SP_PRUEBA (
    in_Clave1 integer)
returns (
    Min_CampoB1 integer)
as
begin
 
  Select Min(CampoB1) from TABLA_B 
  where Clave1 = :In_Clave1
  into Min_CampoB1;
  
  suspend;

end

[Casimiro Notevi]

¿Y has conseguido solucionarlo de esa forma?

[[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.

[[gluglu]]

Y todo esto en código Delphi era algo así como ...

Código Delphi [-]
Aux_PType := -1;
Aux_BType := -1;
 
with pDataSetBDM do begin
  SelectSQL.Clear;
  SelectSQL.Add('Select BPT.PAXTYPE, BPT.BOARDTYPE,');
  SelectSQL.Add('case when BK.OPERATORTYPE = 1 then 0 else');
  SelectSQL.Add(' case when BK.FULLCREDIT = 1 then 1 else');
  SelectSQL.Add(' case when BK.ANTIFULLCREDIT = 1 then 0 else');
  SelectSQL.Add(' BPT.OPERATORDEBIT end end end as OPERATOR_DEBIT');
  SelectSQL.Add('from BOOKINGS_PAXTYPE BPT');
  SelectSQL.Add('left join BOOKINGS BK');
  SelectSQL.Add(' on BK.BOOKINGNO = BPT.BOOKINGNO');
  SelectSQL.Add('where BPT.BOOKINGNO = :Txt1');
  SelectSQL.Add('group by PAXTYPE, BOARDTYPE, OPERATOR_DEBIT');
  ParamByName('Txt1').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
  Prepare;
  Open;
end;
 
pDataSetBDM.First;
while not pDataSetBDM.Eof do begin
  if pDataSetBDM.FieldByName('PAXTYPE').AsInteger = 1 then
    Aux_PType := 1
  else if Aux_PType = -1 then
    Aux_PType := pDataSetBDM.FieldByName('PAXTYPE').AsInteger;
  if not pDataSetBDM.FieldByName('BOARDTYPE').IsNull then begin
    if Aux_BType = -1 then
      Aux_BType := pDataSetBDM.FieldByName('BOARDTYPE').Value
    else if pDataSetBDM.FieldByName('BOARDTYPE').Value <> Aux_BType then
      Aux_BType := -2;
  end;
  pDataSetBDM.Next;
end;
 
with pDataSetBDM2 do begin
  SelectSQL.Clear;
  SelectSQL.Add('Select OPERATORTYPE, FULLCREDIT, ANTIFULLCREDIT');
  SelectSQL.Add('from BOOKINGS');
  SelectSQL.AdD('where BOOKINGNO = :Txt1');
  ParamByName('Txt1').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
  Prepare;
  Open;
end;
 
if pDataSetBDM2.FieldByName('OPERATORTYPE').AsInteger = 1 then
  Aux_Operator_Debit := 0
else if pDataSetBDM2.FieldByName('FULLCREDIT').AsInteger = 1 then
  Aux_Operator_Debit := 1
else if pDataSetBDM2.FieldByName('ANTIFULLCREDIT').AsInteger = 1 then
  Aux_Operator_Debit := 0
else begin
  with pDataSetBDM2 do begin
    SelectSQL.Clear;
    SelectSQL.Add('Select Count(distinct OPERATORDEBIT), Max(OPERATORDEBIT)');
    SelectSQL.Add('from BOOKINGS_PAXTYPE');
    SelectSQL.Add('where BOARDTYPE is not null');
    SelectSQL.Add('and BOOKINGNO = :Txt1');
    ParamByName('Txt1').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
    Prepare;
    Open
  end;
  if pDataSetBDM2.FieldByName('Count').AsInteger = 2 then
    Aux_Operator_Debit := -1
  else
    Aux_Operator_Debit := pDataSetBDM2.FieldByName('Max').AsInteger;
end;
 
Aux_INo := 0;
 
// Locate In Rooming-List
with pDataSetBDM do begin
  SelectSQL.Clear;
  SelectSQL.Add('Select RL.*, BO.OBJECTNO, BO.BUILDINGNO, BO.DATEFROM, BO.DATETO, BO.INTERNALNO,');
  SelectSQL.Add('BPD.PAXTYPE1_QUANT as BPD_P1Q, BPD.PAXTYPE2_QUANT as BPD_P2Q, BPD.PAXTYPE3_QUANT as BPD_P3Q,');
  SelectSQL.Add('BPD.BOARDTYPE1 as BPD_B1, BPD.BOARDTYPE2 as BPD_B2, BPD.BOARDTYPE3 as BPD_B3,');
  SelectSQL.Add('BPD.ROOMINGLISTNO as BPD_NUM,');
  SelectSQL.Add('BPD.OPERATORDEBIT,');
  SelectSQL.Add('BPD.CREATETIMESTAMP as BPD_CTS, BPD.CREATEUSER as BPD_CUS, BPD.CREATEPOSITION as BPD_CPS,');
  SelectSQL.Add('BPD.MODIFYTIMESTAMP as BPD_MTS, BPD.MODIFYUSER as BPD_MUS, BPD.MODIFYPOSITION as BPD_MPS');
  SelectSQL.Add('from BOOKINGS_ROOMINGLIST RL');
  SelectSQL.Add('left join BOOKINGS_OBJECTS BO');
  SelectSQL.Add(' on BO.ROOMINGLISTNO = RL.ROOMINGLISTNO');
  if Modus < 10 then
    SelectSQL.Add(' and BO.DATEFROM = RL.DATEARRIVAL');
  if (Modus > 10) and (Modus < 20) then
    SelectSQL.Add(' and BO.DATETO = RL.DATEDEPARTURE - 1');
  if (Modus > 20) and (Modus < 30) then
    SelectSQL.Add(' and BO.DATEFROM <= :Txt3 and BO.DATETO >= :Txt2');
  if (Modus > 30) and (Modus < 40) then begin
    SelectSQL.Add(' and ((RL.DATEARRIVAL between :Txt2 and :Txt3 and');
    SelectSQL.Add(' BO.DATEFROM = RL.DATEARRIVAL) or');
    SelectSQL.Add(' (RL.DATEDEPARTURE between :Txt2 and :Txt3 and');
    SelectSQL.Add(' BO.DATETO = RL.DATEDEPARTURE -1))');
  end;
  if (Modus > 40) and (Modus < 50) then begin
    SelectSQL.Add(' and (((RL.DATEARRIVAL < ''TODAY'') and (BO.DATEFROM = RL.DATEARRIVAL)) or');
    SelectSQL.Add(' ((RL.DATEARRIVAL >= ''TODAY'') and (''TODAY'' < RL.DATEDEPARTURE) and (BO.DATEFROM <= ''TODAY'') and (''TODAY'' <= BO.DATETO)) or');
    SelectSQL.Add(' ((RL.DATEDEPARTURE <= ''TODAY'') and (BO.DATETO = RL.DATEDEPARTURE)))');
  end;
  if (Modus > 50) and (Modus < 60) then begin
    SelectSQL.Add(' and ((RL.DATEDEPARTURE = :Txt2 and BO.DATETO = RL.DATEDEPARTURE - 1) or');
    SelectSQL.Add(' (RL.DATEDEPARTURE <> :Txt2 and BO.DATEFROM <= :Txt2 and BO.DATETO >= :Txt2))');
  end;
  SelectSQL.Add('left join BOOKINGS_PAXDETAIL BPD');
  SelectSQL.Add(' on RL.ROOMINGLISTNO = BPD.ROOMINGLISTNO');
  SelectSQL.Add('where RL.BOOKINGNO = :Txt1');
  ParamByName('Txt1').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
  if (Modus > 20) and (Modus < 40) then begin
    ParamByName('Txt2').AsDate := StayOverFrom;
    ParamByName('Txt3').AsDate := StayOverTo;
  end;
  if (Modus > 50) and (Modus < 60) then
    ParamByName('Txt2').AsDate := StayOverFrom;
  Prepare;
  Open;
end;
 
while not pDataSetBDM.Eof do begin
 
  Dec(Aux_INo);
 
  Aux_Name := Trim(pDataSetBDM.FieldByName('SURNAME1').AsString);
  If Trim(pDataSetBDM.FieldByName('SURNAME2').AsString) <> '' then Aux_Name := Aux_Name + ' ' + Trim(pDataSetBDM.FieldByName('SURNAME2').AsString);
  If Trim(pDataSetBDM.FieldByName('NAME').AsString) <> '' then Aux_Name := Aux_Name + ', ' + Trim(pDataSetBDM.FieldByName('NAME').AsString);
 
  pDataSet1.Append;
  pDataSet1.FieldByName('RANDOMNO').Value := Aux_R1;
  pDataSet1.FieldByName('RANDOMNO2').Value := Random_No;
  pDataSet1.FieldByName('BOOKINGNO').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
  if pDataSetBDM.FieldByName('BPD_NUM').IsNull then
    pDataSet1.FieldByName('INTERNALNO').Value := Aux_INo
  else
    pDataSet1.FieldByName('INTERNALNO').Value := pDataSetBDM.FieldByName('ROOMINGLISTNO').Value;
  pDataSet1.FieldByName('ROOMINGLISTNO').Value := pDataSetBDM.FieldByName('ROOMINGLISTNO').Value;
  pDataSet1.FieldByName('BUILDINGNO').Value := pDataSetBook.FieldByName('BUILDINGNO').Value;
  pDataSet1.FieldByName('DATEARRIVAL').Value := pDataSetBDM.FieldByName('DATEARRIVAL').Value;
  pDataSet1.FieldByName('DATEDEPARTURE').Value := pDataSetBDM.FieldByName('DATEDEPARTURE').Value;
  if (Modus > 50) and (Modus < 60) then
    pDataSet1.FieldByName('DATECALC').Value := StayOverFrom;
  pDataSet1.FieldByName('CATEGORYNO').Value := pDataSetBDM.FieldByName('CATEGORYNO').Value;
  pDataSet1.FieldByName('SUBCATEGORYNO').Value := pDataSetBDM.FieldByName('SUBCATEGORYNO').Value;
  pDataSet1.FieldByName('DATEFROM').Value := pDataSetBDM.FieldByName('DATEFROM').Value;
  if not pDataSetBDM.FieldByName('DATETO').IsNull then
    pDataSet1.FieldByName('DATETO').Value := pDataSetBDM.FieldByName('DATETO').Value;
  if not pDataSetBDM.FieldByName('CLIENTNO').IsNull then
    pDataSet1.FieldByName('CLIENTNO').Value := pDataSetBDM.FieldByName('CLIENTNO').Value;
  if not pDataSetBDM.FieldByName('INTERNALNO').IsNull then
    pDataSet1.FieldByName('OBJECTINTNO').Value := pDataSetBDM.FieldByName('INTERNALNO').Value;
  pDataSet1.FieldByName('BOOKINGNAME').Value := Aux_Name;
  pDataSet1.FieldByName('SURNAME1').Value := pDataSetBDM.FieldByName('SURNAME1').AsString;
  pDataSet1.FieldByName('SURNAME2').Value := pDataSetBDM.FieldByName('SURNAME2').AsString;
  pDataSet1.FieldByName('NAME').Value := pDataSetBDM.FieldByName('NAME').AsString;
  if not pDataSetBDM.FieldByName('OBJECTNO').IsNull then
    pDataSet1.FieldByName('OBJECTNO').Value := pDataSetBDM.FieldByName('OBJECTNO').AsString;
  if not pDataSetBDM.FieldByName('PAXQUANTITY').IsNull then begin
    if Aux_PType = 1 then begin
      pDataSet1.FieldByName('PAXTYPE1_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
      if Aux_BType > 0 then
        pDataSet1.FieldByName('BOARDTYPE1').Value := Aux_BType;
    end;
    if Aux_PType = 2 then begin
      pDataSet1.FieldByName('PAXTYPE2_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
      if Aux_BType > 0 then
        pDataSet1.FieldByName('BOARDTYPE2').Value := Aux_BType;
    end;
    if Aux_PType = 3 then begin
      pDataSet1.FieldByName('PAXTYPE3_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
      if Aux_BType > 0 then
        pDataSet1.FieldByName('BOARDTYPE3').Value := Aux_BType;
    end;
  end;

  if pDataSetBDM.FieldByName('BPD_NUM').IsNull then begin
    if not pDataSetBDM.FieldByName('PAXQUANTITY').IsNull then begin
      if Aux_PType = 1 then begin
        pDataSet1.FieldByName('P_TYPE1_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
        if Aux_BType > 0 then
          pDataSet1.FieldByName('B_TYPE1').Value := Aux_BType;
      end;
      if Aux_PType = 2 then begin
        pDataSet1.FieldByName('P_TYPE2_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
        if Aux_BType > 0 then
          pDataSet1.FieldByName('B_TYPE2').Value := Aux_BType;
      end;
      if Aux_PType = 3 then begin
        pDataSet1.FieldByName('P_TYPE3_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
        if Aux_BType > 0 then
          pDataSet1.FieldByName('B_TYPE3').Value := Aux_BType;
      end;
    end;
    pDataSet1.FieldByName('OPERATORDEBIT').Value := Aux_Operator_Debit;
  end
  else begin
    if not pDataSetBDM.FieldByName('BPD_P1Q').IsNull then begin
      pDataSet1.FieldByName('P_TYPE1_QUANT').Value := pDataSetBDM.FieldByName('BPD_P1Q').Value;
      if not pDataSetBDM.FieldByName('BPD_B1').IsNull then
        pDataSet1.FieldByName('B_TYPE1').Value := pDataSetBDM.FieldByName('BPD_B1').Value;
    end;
    if not pDataSetBDM.FieldByName('BPD_P2Q').IsNull then begin
      pDataSet1.FieldByName('P_TYPE2_QUANT').Value := pDataSetBDM.FieldByName('BPD_P2Q').Value;
      if not pDataSetBDM.FieldByName('BPD_B2').IsNull then
        pDataSet1.FieldByName('B_TYPE2').Value := pDataSetBDM.FieldByName('BPD_B2').Value;
    end;
    if not pDataSetBDM.FieldByName('BPD_P3Q').IsNull then begin
      pDataSet1.FieldByName('P_TYPE3_QUANT').Value := pDataSetBDM.FieldByName('BPD_P3Q').Value;
      if not pDataSetBDM.FieldByName('BPD_B3').IsNull then
        pDataSet1.FieldByName('B_TYPE3').Value := pDataSetBDM.FieldByName('BPD_B3').Value;
    end;
    pDataSet1.FieldByName('CREATETIMESTAMP').Value := pDataSetBDM.FieldByName('BPD_CTS').Value;
    pDataSet1.FieldByName('CREATEUSER').Value := pDataSetBDM.FieldByName('BPD_CUS').Value;
    pDataSet1.FieldByName('CREATEPOSITION').Value := pDataSetBDM.FieldByName('BPD_CPS').Value;
    if not pDataSetBDM.FieldByName('BPD_MTS').IsNull then begin
      pDataSet1.FieldByName('MODIFYTIMESTAMP').Value := pDataSetBDM.FieldByName('BPD_MTS').Value;
      pDataSet1.FieldByName('MODIFYUSER').Value := pDataSetBDM.FieldByName('BPD_MUS').Value;
      pDataSet1.FieldByName('MODIFYPOSITION').Value := pDataSetBDM.FieldByName('BPD_MPS').Value;
    end;
    if Aux_Operator_Debit = -1 then
      pDataSet1.FieldByName('OPERATORDEBIT').Value := pDataSetBDM.FieldByName('OPERATORDEBIT').Value
    else
      pDataSet1.FieldByName('OPERATORDEBIT').Value := Aux_Operator_Debit;
  end;
  pDataSet1.Post;
  pDataSetBDM.Next;
end;

... menudo tostón, no ???

Y estas son sólo 200 y poco líneas de las 1800 que tiene el procedimiento completo y que quiero optimizar con sentencias SQL en vez de bucles de programación, y además meterlo todo en un SP que se ejecute en el servidor, y que no tenga que haber tráfico de datos en la red para poder ejecutar el código necesario.

Probablemente así, logre optimizar un proceso que al pedir ahora un mes de datos, tarda unos 30 o 40 segundos, por todos los cálculos internos que debe de realizar, a conseguirlo en un 'abrir y cerrar de ojos'.

Ya lo he hecho con algún que otro proceso diferente también del programa. Lo que antes tardaba 10 - 15 segundos, ahora es inmediato .... 'ya ta !!'.

Con esto se puede observar que como había leido ya alguna vez en alguna parte .... que las sentencias SQL pueden ser todo lo complejas que el programador sea capaz de programar !

Ya pondré los resultados definitivos cuando esté todo hecho !

[[gluglu]]

Parece que funciona de momento ....

Tengo que terminar ahora el código completo, para encajar todo el código Delphi en sentencias SQL.

De momento el tocho de sentencia anterior ha quedado reducido a :

Código SQL [-]
Select -1, -2, RL.BOOKINGNO,
  case when SP_PAXTYPE.OUT_PAXTYPE = 1 then RL.PAXQUANTITY else null end as PAXTYPE1_QUANT,
  case when SP_PAXTYPE.OUT_PAXTYPE = 1 and SP_BOARDTYPE.OUT_BOARDTYPE > 0 then SP_BOARDTYPE.OUT_BOARDTYPE else null end as BOADTYPE1,
  case when SP_PAXTYPE.OUT_PAXTYPE = 2 then RL.PAXQUANTITY else null end as PAXTYPE2_QUANT,
  case when SP_PAXTYPE.OUT_PAXTYPE = 2 and SP_BOARDTYPE.OUT_BOARDTYPE > 0 then SP_BOARDTYPE.OUT_BOARDTYPE else null end as BOADTYPE2,
  case when SP_PAXTYPE.OUT_PAXTYPE = 3 then RL.PAXQUANTITY else null end as PAXTYPE3_QUANT,
  case when SP_PAXTYPE.OUT_PAXTYPE = 3 and SP_BOARDTYPE.OUT_BOARDTYPE > 0 then SP_BOARDTYPE.OUT_BOARDTYPE else null end as BOADTYPE3,
  case when BPD.ROOMINGLISTNO is null
       then case when SP_PAXTYPE.OUT_PAXTYPE = 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 SP_PAXTYPE.OUT_PAXTYPE = 1 and SP_BOARDTYPE.OUT_BOARDTYPE > 0 then SP_BOARDTYPE.OUT_BOARDTYPE 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 SP_PAXTYPE.OUT_PAXTYPE = 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 SP_PAXTYPE.OUT_PAXTYPE = 2 and SP_BOARDTYPE.OUT_BOARDTYPE > 0 then SP_BOARDTYPE.OUT_BOARDTYPE 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 SP_PAXTYPE.OUT_PAXTYPE = 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 SP_PAXTYPE.OUT_PAXTYPE = 3 and SP_BOARDTYPE.OUT_BOARDTYPE > 0 then SP_BOARDTYPE.OUT_BOARDTYPE 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,
  SP_OPERATORDEBIT.OUT_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
left join SP_PAXDETAIL_PAXTYPE(RL.BOOKINGNO) SP_PAXTYPE
  on RL.ROOMINGLISTNO = RL.ROOMINGLISTNO
left join SP_PAXDETAIL_BOARDTYPE(RL.BOOKINGNO) SP_BOARDTYPE
  on RL.ROOMINGLISTNO = RL.ROOMINGLISTNO
left join SP_PAXDETAIL_OPERATORDEBIT(RL.BOOKINGNO) SP_OPERATORDEBIT
  on RL.ROOMINGLISTNO = RL.ROOMINGLISTNO
where RL.BOOKINGNO = 101974

siendo SP_PAXDETAIL_PAXTYPE, SP_PAXDETAIL_BOARDTYPE y SP_PAXDETAIL_OPERATORDEBIT los procedimientos almacenados que he creado. No creo necesario detallar el contenido de los SP ya que lo único que importa aquí es que llamo desde esta sentencia a los SP's sin entrar en el detalle de lo que devuelve cada uno.

[Casimiro Notevi]

¡¡¡Mareante!!!

Lo raro es que no te hayas quedado sin memoria, en versiones anteriores de firebird, creo que hasta la v1.0 el tamaño máximo para los procedimientos, triggers, etc. era de sólo 32 Kb. Así que había que seguir el lema de "devide y vencerás" , haciendo lo que has hecho, procedimientos alternativos que se llaman desde el principal.

Lo malo de ese "mogollón" es que como algo no funcione bien te tienes que concentrar "mu bien concentrao" para descubrir el problema