SELECT MIN(DTT) AS DT FROM(
SELECT DTT FROM(
select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+7 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+8 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+9 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+10 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+11 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+12 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+13 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+14 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+15 dt from dual)
) WHERE mod(TO_NUMBER(SUBSTR(DTT,0,2)),2)<>0) CREATE OR REPLACE TRIGGER SUPERMAG.LMMATRIXCHANGE
BEFORE DELETE OR INSERT
ON SUPERMAG.SMASSORTMATRIX
FOR EACH ROW
DECLARE
dayForZak int;
DDATE date;
daten char(10);
BEGIN
SELECT TO_NUMBER(VALUE) into dayForZak FROM SUPERMAG.LMCONFIG WHERE PARM='day_for_vvod';
SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY') into daten FROM dual;
SELECT TO_DATE(MIN(DTT)) into DDATE FROM(
SELECT DTT FROM(
select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+7 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+8 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+9 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+10 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+11 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+12 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+13 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+14 dt from dual)
UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+15 dt from dual)
) WHERE mod(TO_NUMBER(SUBSTR(DTT,0,2)),2)<>0);
--Core.FailMsg('Äàòà'||DDATE);
if inserting or updating then
INSERT INTO LMMATRIXACTIONS(ARTICLE,ACTIONDATE,OWNER,ACTION,MATRIXID,LOCATION,ZAKDATE)
VALUES (:New.ARTICLE,SYSDATE,USER,'insert',:New.IDMATRIX,(SELECT STORELOC FROM SMASSORTMATRIXLOC WHERE IDMATRIX=:New.IDMATRIX),DDATE);
else
INSERT INTO LMMATRIXACTIONS(ARTICLE,ACTIONDATE,OWNER,ACTION,MATRIXID,LOCATION) VALUES
(:Old.ARTICLE,SYSDATE,USER,'delete',:Old.IDMATRIX,(SELECT STORELOC FROM SMASSORTMATRIXLOC WHERE IDMATRIX=:Old.IDMATRIX));
end if;
END LMMATRIXCHANGE;
/ WITH datelist AS
(SELECT TO_DATE ('01012010', 'DDMMYYYY') fromdate, TO_DATE ('31122010', 'DDMMYYYY') todate
FROM DUAL)
SELECT DAY
FROM (SELECT fromdate + LEVEL - 1 DAY
FROM datelist
CONNECT BY LEVEL <= todate - fromdate + 1)
WHERE TO_CHAR (DAY, 'D') = 1 AND TO_NUMBER (TO_CHAR (DAY, 'DD')) / 2 != TRUNC (TO_NUMBER (TO_CHAR (DAY, 'DD')) / 2) AND ROWNUM = 1 Error at line 1 SQL statement doesn't return rows
SQL*Plus: Release 10.2.0.4.0 - Production on Вт Июн 15 17:44:01 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Присоединен к:
Oracle9i Release 9.2.0.8.0 - Production
JServer Release 9.2.0.8.0 - Production
SQL> WITH datelist AS
2 (SELECT TO_DATE ('01012010', 'DDMMYYYY') fromdate, TO_DATE ('31122010', 'DDMMYYYY') todate
3 FROM DUAL)
4 SELECT DAY
5 FROM (SELECT fromdate + LEVEL - 1 DAY
6 FROM datelist
7 CONNECT BY LEVEL <= todate - fromdate + 1)
8 WHERE TO_CHAR (DAY, 'D') = 1 AND TO_NUMBER (TO_CHAR (DAY, 'DD')) / 2 != TRUNC (TO_NUMBER (TO_CHAR (DAY, 'DD')) / 2) AND ROWNUM = 1;
DAY
--------
11.01.10
SQL>