Good day!

A minor problem with the procedures. I have a procedure that (among other things) pulls out a list of dates between two given dates (parameters). It is necessary to make sure that at the output I receive all the data and dates only in the number of 12 pieces (ie, info on 12 dates from the date "c"), and if between them, for example, the year - the dates of the first numbers of the months . How can this be realized?

PROCEDURE GET_EXCHAGEINFO_CURRENCIES(P_DATEFROM varchar2, P_DATETILL varchar2, P_CURRENCIES varchar2, RC OUT SYS_REFCURSOR) IS SQL_V VARCHAR2(1000 CHAR); BEGIN SQL_V := 'select entr.*, case when entr.ratedif>0 then 1 else 0 end DIRECTION from ( select VLT.Vltpref AS VLTNAME, VLT.VLTNAME AS VLTFULLNAME, VLT.VLTCODE AS VLTCODE, VLT.VLTCOLOR AS VLTCOLOR, R.RATE, TO_CHAR(R.RATEDATE,''DD.MM.YYYY'') AS RATEDATE, R.RATE - (SELECT R3.RATE FROM VLT_RATES R3 WHERE R3.vltid = r.vltid and r3.ratedate = ( SELECT MAX(R1.Ratedate) FROM VLT_RATES R1 WHERE R1.Vltid = r.vltid and R1.RateDATE<> TO_DATE('''||P_DATETILL||''',''dd.mm.yyyy'') ) ) RATEDIF FROM VLT_RATES R left join VLT_VALUTAS VLT ON VLT.ID = R.VLTID WHERE R.VLTID IN ('|| P_CURRENCIES ||') AND TRUNC(R.RATEDATE) BETWEEN TO_DATE('''||P_DATEFROM||''',''dd.mm.yyyy'') AND TO_DATE('''||P_DATETILL||''',''dd.mm.yyyy'') ORDER BY VLT.VLTNAME,R.RATEDATE )entr'; OPEN RC FOR SQL_V; END;
  • It is impossible to answer the question without seeing your data and the desired final result. Reduce the code to the minimum required to solve the problem (that is, remove all fields and tables that are not needed to solve the problem), then provide the code to create the tables and load the minimum test case into them. And attach what should be to this after executing the query on the example data provided - Mike
  • @Mike Yes, probably too fancy. I just understood how, the only thing is a simple question: how can I choose every second date between date_c and date_po? - JDoeBloke
  • Choose from existing in the table or in the calendar? those. if there is only January 1,3,10,13 in the table, then which ones to choose - Mike
  • @Mike No, there is a period. Suppose from 1 to 30 number. Returns 30 days, then type 30/12 (desired dates) = 2.5 - an interval of 2 days. Then take 30 number as a start and choose 30, 28, 26, etc., a total of 12 (well, with an error). Something like that. - JDoeBloke
  • Then the usual number generator and add from to the starting date, i.e. something like select DATE '2017-05-02'+(level-1)*2 from dual connect by level <= 16 - Mike

0