Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

PLSQL Procedure

Hi I have a procedure which gives me the successdate like below:

 2011-02-25 02:24

Now I want to implement a simple logic like

 
IF ( to_date(successdate,'yyyy-mm-dd hh24:mi') BETWEEN to_date(input_date || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') AND to_date(input_date,'yyyy-mm-dd')+1 + (11/24) ) 
THEN
ERROR_MSG :='THE LAST SUCESSDATE WHICH IS ON'||SUCCESSDATE;
end if ;
end;

Open in new window



This is not working but if I remove the if condition then i am able to get the ERROR_MSG .

Need you help experts..
0
pinkuray
Asked:
pinkuray
  • 8
  • 8
  • 2
  • +1
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
what is the data type of this "successdate" ? Can you confirm please.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
what is the value you are passing for the input_date ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i tested this with the below and is working perfectly fine unless you can tell me what values are being given for the input_Date and successdate variables ?

i used the below to test this...

select 'THE LAST SUCESSDATE WHICH IS ON' from dual
where to_date('2011-02-28 19:10','yyyy-mm-dd hh24:mi') BETWEEN to_date('2011-02-28' || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') AND to_date('2011-02-28','yyyy-mm-dd')+1 + (11/24)
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
pinkurayAuthor Commented:
Sorry a typo mistake while posting the question:

It should be like below:

 IF SUCCESSDATE IS NOT NULL THEN ERROR_MSG    :='THE LAST SUCESSDATE WHICH IS ON '||SUCCESSDATE;
         ELSE IF ( TO_DATE(SUCCESSDATE,'YYYY-MM-DD HH24:MI') BETWEEN TO_DATE(SUCCESSDATE || ' 18:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(SUCCESSDATE,'YYYY-MM-DD')+1 + (11/24) ) THEN
ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON '||to_date(SUCCESSDATE,'yyyy-mm-dd');
          END IF;


The SUCESSDATE  is of type SUCCESSDATE OUT VARCHAR2.

I am calling a procedure before this statement.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
ok.
 
1) what is the output you are getting for the below code currently and what is the output you are expecting ?
2) can you provide the values of  successdate ?

 IF SUCCESSDATE IS NOT NULL THEN ERROR_MSG    :='THE LAST SUCESSDATE WHICH IS ON '||SUCCESSDATE;
         ELSE IF ( TO_DATE(SUCCESSDATE,'YYYY-MM-DD HH24:MI') BETWEEN TO_DATE(SUCCESSDATE || ' 18:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(SUCCESSDATE,'YYYY-MM-DD')+1 + (11/24) ) THEN
ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON '||to_date(SUCCESSDATE,'yyyy-mm-dd');
          END IF;
0
 
pinkurayAuthor Commented:
Values of SUCESSDATE IS : 2011-02-25 02:24  

As an output.

I want to implement the if the time is between 18:00 to 11:59  then it should be the previous day
0
 
pinkurayAuthor Commented:
It is going to the exception as when other then part..
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i am sorry i did not understand what your requirement is.

first give your full code otherwise i cannot understand this "It is going to the exception as when other then part "
0
 
sdstuberCommented:
is success_date already a date?


if so  don't use to_date on it.


use to_char to convert a date to a string
0
 
LFLFMCommented:
Your code seems to be all wrong... you need to post the whole thing...
First you check that SUCCESSDATE is not null, ELSE you check it's value!?? the value in that condition will always be NULL...

Also, doesnt SUCCESSDATE need to be an IN parameter?
Here's what I think your code may have to be:
First, declare the variable dtRealDate as DATE.
/*
IF SUCCESSDATE IS NOT NULL THEN
    ERROR_MSG    :='THE LAST SUCESSDATE WHICH IS ON '||SUCCESSDATE;
ELSE --... ELSE NOTHING! By this time SUCCESSDATE IS NULL, so there is no point in checking its value again*/
IF SUCCESSDATE IS NULL THEN
    ERROR_MSG    :='THERE IS NO SUCCESSDATE!';
ELSE
    dtRealDate = to_date(SUCCESSDATE,'YYYY-MM-DD HH24:MI');
    IF ( dtRealDate BETWEEN TO_DATE(substr(SUCCESSDATE,1,11) || ' 18:00:00' ,'YYYY-MM-DD HH24:MI:SS')
                        AND trunc(dtRealDate)+1-(1/24/60/60)) THEN
        ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON ' || trunc(dtRealDate);
    END IF;
/*END IF;*/

Open in new window



Pseudocode:
If there is no value in SUCCESSDATE then
   error
else
   assign a real date variable dtRealDate with the actual date value 
   if datevariable between (date at 18:00) and (date at 23:59:59) then
        Error message informing (date)
    END IF;
END IF;

Open in new window

0
 
pinkurayAuthor Commented:
successdate is a data with time stored in my DB and also used as a out parameter for a procedure but the datatype is in varchar2(200) ..

Please find my attached code:

 
DECLARE
  INSTANCE SR.TARGET_LIST_T.INSTANCENAME%TYPE;
  SCHEMA SR.TARGET_LIST_T.SCHEMANAME%TYPE;
  HOSTNAME SR.TARGET_LIST_T.HOSTNAME%TYPE;
  SUCCESSDATE VARCHAR2(100);
  dtRealDate date;
  VSTATUS     NUMBER := 0;
  VPRODUCT    VARCHAR2(15);
  ERROR_MSG   VARCHAR2(2000):='NO ERRORS';
BEGIN
  BEGIN
    IF :P1_TARGET IS NOT NULL THEN
      EA.SCHEMA_CHECK ( :P1_TARGET ,TO_NUMBER(:P1_PRODUCT),VSTATUS);
      BEGIN
        SELECT PRODUCTNAME INTO VPRODUCT FROM PRODUCT_M WHERE PRODUCTID = :P1_PRODUCT;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        ERROR_MSG:= 'DATA DOESN NOT FOUND WITH THE GIVEN INPUT OF PRODUCT/REFRESH THE PAGE';
      WHEN OTHERS THEN
        ERROR_MSG:= 'ERROR FOUND IN THE INPUT CONTACT ADMINISTRATOR';
      END;
      IF VSTATUS    = 0 THEN
        ERROR_MSG  := 'IN ' || VPRODUCT || ' PRODUCT TARGET SCHEMA DOES NOT FOUND. PLEASE CHECK THE EXISTANCE OF ' || :P1_TARGET || '  TARGET ON SS';
      ELSIF VSTATUS = 8 THEN
        --NEED TO CHECK THE BELOW LINE
        ERROR_MSG   := 'PLEASE SPECITY THE PRODUCT OR FOR THIS PRODUCT IT IS STILL IN PROGRESS';
      ELSIF VSTATUS  = 9 THEN
        ERROR_MSG   := 'ERROR FOUND IN THE INPUT CONTACT ADMINISTRATOR AND PROVIDE NECESSARY DETAILS';
      ELSIF VSTATUS != 1 THEN
        ERROR_MSG   := 'MORE THAN ONE VALUE EXISTS IN SS FOR TARGET ' || :P1_TARGET || 'NOT ABLE TO SCHEDULE AND DISPLAY THE BACKUP CONTACT ADMINISTRATOR';
      ELSE
        EA.GET_INSTANCE ( :P1_TARGET ,TO_NUMBER(:P1_PRODUCT),INSTANCE,HOSTNAME);
        IF TO_NUMBER(:P1_PRODUCT) != 4 AND TO_NUMBER(:P1_PRODUCT) != 6 AND TO_NUMBER(:P1_PRODUCT) != 5 THEN
          SCHEMA                  := 'ALL';
        ELSE
          SCHEMA :=:P1_TARGET;
        END IF;
        EA.SUCCESSDATE(SCHEMA,HOSTNAME,INSTANCE,SUCCESSDATE);
        IF SUCCESSDATE IS NULL THEN
    ERROR_MSG    :='THERE IS NO SUCCESSDATE!';
ELSE  
DTREALDATE = TO_CHAR(SUCCESSDATE,'YYYY-MM-DD HH24:MI'); 
IF ( to_date(DTREALDATE,'yyyy-mm-dd hh24:mi') BETWEEN to_date(DTREALDATE || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss') 
    AND to_date(DTREALDATE,'yyyy-mm-dd')+1 + (11/24) ) THEN
        ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON ' || TRUNC(DTREALDATE);
    END IF;
END IF;
END IF;
END IF;

  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ERROR_MSG:= SUCCESSDATE||'NOT FOUND PLEASE CHECK THE SUCCESSDATE';
  WHEN OTHERS THEN
    ERROR_MSG:='NO DATA FOUND PLEASE CHECK ON OTHERS ';
  END;
  HTP.PRN('<td>&nbsp;'||INSTANCE||'</td>');
  HTP.PRN('<td>&nbsp;'||HOSTNAME||'</td>');
  HTP.PRN('<td>&nbsp;'||SUCCESSDATE||'</td>');
  HTP.PRN('<td>&nbsp;'||ERROR_MSG||'</td>');
END;

Open in new window


Now this gives me an error.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
what is the error you are getting with the code given in the update : 35003132  ?

Also can you provide what output you are expecting from that code ? Also good to know the value of succcessdate variable returned by the procedure. You can add a debug message to see the value immediately after the procedure call line.

Thanks
0
 
pinkurayAuthor Commented:
The return value from the procedure  in line 38 i.e. :

 EA.SUCCESSDATE(SCHEMA,HOSTNAME,INSTANCE,SUCCESSDATE);

is 2011-02-25 02:24  and after which I have added the lines for checking if it is in between time.

The error is converting a varchar2 to date ....

where as after this it is getting SUCESSDATE  as varchar2(200).
and then I am trying to check the dates and time in between which is conflicting it to get the result.

When it is error it is going to :

WHEN OTHERS THEN
    ERROR_MSG:='NO DATA FOUND PLEASE CHECK ON OTHERS ';

0
 
pinkurayAuthor Commented:
this can be easily solved by using regular expression but I know to do it without using this one.

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
ok. understood now. You should be at least this much explanative when you create a question otherwise it is just something which no one will have a clue on.

your existing code :
DTREALDATE = TO_CHAR(SUCCESSDATE,'YYYY-MM-DD HH24:MI');
IF ( to_date(DTREALDATE,'yyyy-mm-dd hh24:mi') BETWEEN to_date(DTREALDATE || ' 18:00:00' ,'yyyy-mm-dd hh24:mi:ss')
    AND to_date(DTREALDATE,'yyyy-mm-dd')+1 + (11/24) ) THEN
        ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON ' || TRUNC(DTREALDATE);
    END IF;
END IF;
END IF;
END IF;

modify it as below :
DTREALDATE = TO_DATE(SUCCESSDATE,'YYYY-MM-DD HH24:MI');  
-- you don't need to use to_char because successdate is already a varchar variable.
IF ( DTREALDATE BETWEEN to_date(to_char(DTREALDATE,'YYYY-MM-DD') || ' 18:00' ,'yyyy-mm-dd hh24:mi') AND trunc(DTREALDATE+1)+(11/24) ) THEN
        ERROR_MSG    :='THE LAST SUCESSDATE NEW WHICH IS ON ' || TRUNC(DTREALDATE);
    END IF;
END IF;
END IF;
END IF;

Test it out.
0
 
pinkurayAuthor Commented:
I will test this and update you accordingly.
0
 
LFLFMCommented:
you should not have put to_date() on the DTREALDATE... when I told you to use this variable, it was so you didn't have to do exactly that :-)
Try the code nav_kum_v posted, its just like my original code (except for the final time).

onde thing, your SUCCESSDATE doesn't store seconds?
If it does, you really want:
dtRealDate = to_date(SUCCESSDATE,'YYYY-MM-DD HH24:MI:SS');
0
 
pinkurayAuthor Commented:
Thanks this solve my problem
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Good & Thanks,
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now