SQL Developer - à l'Aide de début et date de fin de variables dans la clause where de la requête

J'ai une question que j'ai de courir tous les jours qui nécessite un StartDate et EndDate valeur. Le StartDate et EndDate utilisé pour être une saisie manuelle, mais je suis en train de m'éloigner de ce et de calculer la StartDate et EndDate à être utilisé dans la requête. J'ai développé le code de capture de l' StartDate et EndDate dans les variables:

DECLARE
c_DateMask VARCHAR2(20) := 'DD-Mon-YYYY';
c_TimeMask VARCHAR2(20) := 'HH24:MI';
v_Month char(4) := 'Prev'; 
v_StartDate date; 
v_EndDate date; 
v_Environment char(7) := 'Prod';  
BEGIN
if v_MONTH = 'Prev'
THEN 
select TO_DATE ('01-' || TO_CHAR (ADD_MONTHS (SYSDATE, -1),'mon-yyyy')) into v_StartDate from dual; 
select Last_day(TO_DATE('01-' || TO_CHAR (ADD_MONTHS (SYSDATE, -1),'mon-yyyy'))) into v_EndDate from dual;
ELSE
select TO_DATE ('01-' || TO_CHAR (ADD_MONTHS (SYSDATE, 0),'mon-yyyy')) into v_StartDate from dual;
CASE
WHEN v_Environment = 'Prod'
THEN
-- Production Environment --
select 
to_char(sysdate, 'dd-Mon-yyyy ') ||
case 
when to_char(sysdate, 'mi') between 00 and 20
then to_char(sysdate, 'hh24')-1||':58'||':00' 
when to_char(sysdate, 'mi') between 21 and 40
then to_char(sysdate, ' hh24')||':18'||':00' 
when to_char(sysdate, 'mi') between 41 and 60
then to_char(sysdate, ' hh24')||':38'||':00'
END 
into v_EndDate from dual;
WHEN v_Environment = 'OldTest'
THEN
-- Test Environment --
select 
to_char(sysdate, 'dd-Mon-yyyy ') ||
case 
when to_char(sysdate, 'mi') between 10 and 30
then to_char(sysdate, 'hh24')||':08'||':00'
when to_char(sysdate, 'mi') between 31 and 50
then to_char(sysdate, ' hh24')||':28'||':00' 
when to_char(sysdate, 'mi') between 51 and 60
then to_char(sysdate, ' hh24')||':48'||':00'
END 
into v_EndDate from dual;
end case;    
end if;

Je veux utiliser les variables dans mon instruction select ci-dessous:

    -----------------
/* KPI Figures */
-----------------
SELECT 
SysDate as RunTime
, v_StartDate
, v_EndDate
, TTM_OFF_CONTRIBUTOR
, SUM(NVL(TTM_PER_OFF_FEE,0)) Fees
FROM  [Table]
Where 
TTM_PROCESSED_DATE    >= v_StartDate
AND TTM_PROCESSED_DATE    <= v_EndDate
group by SysDate, v_StartDate, v_EndDate, TTM_OFF_CONTRIBUTOR
END;

Tout fonctionne jusqu'à ce que j'essaie d'utiliser les valeurs de variable dans l'indicateur de performance clé Figures de la requête. Ce qui me manque?

Mise à jour:

Concernant La réponse de Phil: j'ai essayé mais ça ne marchait pas et j'obtiens l'erreur suivante:

PLS-00428: an INTO clause is expected in this SELECT statement.

Je suis sûr que j'ai vu une autre réponse, hier, qui a maintenant disparu relatives à pouvoir attribuer plusieurs valeurs à des variables ou quelque chose.

Est que ce dont j'ai besoin et comment dois-je procéder?

OriginalL'auteur JanW | 2010-11-03

1

Les variables v_StartDate et v_EndDate sont uniquement dans la portée dans le PL/SQL bloc où elles sont déclarées. Il ressemble à vous ensuite d'essayer de les utiliser en dehors du bloc dans une requête distincte. Pour cela, vous aurez besoin de créer SQL Developer lier les variables en dehors de la PL/SQL bloc comme ceci:
```
var v_start_date varchar2(11)
var v_end_date varchar2(11)
```
Référence à ces variables de liaison dans les deux PL/SQL bloc et la requête SQL:
```
declare
...
begin
....
:v_start_date := '01-' || TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'mon-yyyy');
:v_end_date := TO_CHAR(Last_day(TO_DATE('01-' 
|| TO_CHAR (ADD_MONTHS (SYSDATE, -1),'mon-yyyy'))));
-- (NB No need to select from dual)
...
end;
```
SQL:
```
...
Where 
TTM_PROCESSED_DATE    >= TO_DATE(:v_StartDate)
AND TTM_PROCESSED_DATE    <= TO_DATE(:v_EndDate)
```
Noter que ces variables ne peuvent pas être déclarées avec un type de DATE, de sorte qu'ils doivent être convertis à des dates dans la requête (en utilisant le format correct du masque).

OriginalL'auteur Tony Andrews

Je vous suggère d'envelopper la date de début/date de fin dans deux fonctions (procédures stockées)

create or replace function get_startdate(p_month in varchar2, p_env in varchar2)
return date
is
l_return date;
begin
... logic goes here ...
return l_return;
end;

Similaires pour enddate.

Ensuite utiliser ces fonctions dans votre requête:

SELECT 
SysDate as RunTime
, get_startdate('Prev', 'Prod')
, get_enddate('Prev', 'Prod')
, TTM_OFF_CONTRIBUTOR
, SUM(NVL(TTM_PER_OFF_FEE,0)) Fees
FROM  [Table]
Where 
TTM_PROCESSED_DATE    >= get_startdate('Prev', 'Prod')
AND TTM_PROCESSED_DATE    <= get_enddate('Prev', 'Prod')
group by SysDate, get_startdate('Prev', 'Prod'), get_enddate('Prev', 'Prod'), TTM_OFF_CONTRIBUTOR

Note: veuillez considérer une version plus concise pour déterminer le premier et le dernier jour d'un mois. Vous pouvez également vous suffit d'attribuer une valeur à une variable sans l'aide de SELECT INTO, par exemple

startdate := trunc(sysdate, 'MM'); -- first day of current month
enddate := last_day(trunc(sysdate, 'MM')); -- last day of current month

De calculer avec les heures, vous pouvez ajouter/soustraire un certain nombre de minutes, c'est plus lisible que tous les to_date/to_char conversions que vous faites, par exemple,

enddate := trunc(sysdate, 'HH24');
case 
when to_char(sysdate, 'mi') between 00 and 20 then enddate := enddate - 2/(24*60);
when to_char(sysdate, 'mi') between 21 and 40 then enddate := enddate + 18/(24*60);
when to_char(sysdate, 'mi') between 41 and 60 then enddate := enddate + 38/(24*60);
end;

Bonne chance, Martin

OriginalL'auteur Martin Schapendonk

Je pense que le problème est avec votre requête et d'alias. Essayez

SELECT 
SysDate as RunTime
, v_StartDate AS StartDate
, v_EndDate AS EndDate
, TTM_OFF_CONTRIBUTOR
, SUM(NVL(TTM_PER_OFF_FEE,0)) Fees
FROM  [Table]
Where 
TTM_PROCESSED_DATE    >= v_StartDate
AND TTM_PROCESSED_DATE    <= v_EndDate
group by RunTime, StartDate, EndDate, TTM_OFF_CONTRIBUTOR

OriginalL'auteur Phil

Vous devez vous connecter pour publier un commentaire.