CODEWITHSUNDEEP
sqloracle-databaseplsql
Błażej
Błażej

Reputation: 137

What is wrong with my procedure, data from my procedure is wrong

I wrote a procedure in pl/sql but when I execute procedure The data I get is incorrect and exactly the values which in cases are incorrect and show zero. Please give me a hint what I'm doing wrong. I'm just learning how to create procedures.

It's my procedure.

create or replace PROCEDURE Marza_II_PROCEDURE(FirstDateOpen DATE,FirstDateClose DATE
,SecondDateOpen DATE ,SecondDateClose DATE
)
as

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE MARZA_REPORT' ;
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
COMMIT;
EXECUTE IMMEDIATE 'CREATE TABLE MARZA_REPORT AS
    select BH,ODBIORCA,KANAL_SPRZEDAZY,SEGMENT2018,SEGMENT2019,
SUM(CASE WHEN MIESIAC BETWEEN ' || FirstDateOpen || ' and ' ||FirstDateClose || 'then OBROT else 0 end) OBROT1,
SUM(CASE WHEN MIESIAC BETWEEN ' || SecondDateOpen || ' and ' ||SecondDateClose || 'then OBROT else 0 end) OBROT2
from dws1.marza_netto

WHERE MIESIAC >= ' || SecondDateOpen || '
AND ODBIORCA IN (''0001006834'',''0001024402'',''0001000043'')
GROUP BY BH,ODBIORCA,KANAL_SPRZEDAZY,SEGMENT2018,SEGMENT2019';

END;

I execute procedure :

Begin
Marza_II_PROCEDURE('20190101','20190201','20180101','20180201');
end

Upvotes: 0

Views: 62

Answers (2)

Popeye
Popeye

Reputation: 35900

I dont agree with the answer given here as conversion from date to string will happen when you put the date in the string query.

Converted format depends on your nls_date_format setting if you have not specified any format explicitly (your case is this)

Yes, you need to call the procedure with date parameter as described by @hotfix but you will also need to convert the dates in execute immediate as following

EXECUTE IMMEDIATE 'CREATE TABLE MARZA_REPORT AS
                       select BH, ODBIORCA,KANAL_SPRZEDAZY, SEGMENT2018, SEGMENT2019
                              ,SUM(CASE WHEN MIESIAC BETWEEN DATE ''' || to_char(FirstDateOpen, 'yyyy-mm-dd') || ''' and DATE''' || to_char(FirstDateClose,'yyyy-mm-dd') || ''' then OBROT else 0 end) OBROT1,
                              ,SUM(CASE WHEN MIESIAC BETWEEN DATE ''' || to_char(SecondDateOpen,'yyyy-mm-dd') || ''' and DATE''' || to_char(SecondDateClose,'yyyy-mm-dd') || ''' then OBROT else 0 end) OBROT2
                         from dws1.marza_netto
                         WHERE MIESIAC >= DATE ''' || to_char(SecondDateOpen, 'yyyy-mm-dd') || '''
                           AND ODBIORCA IN (''0001006834'',''0001024402'',''0001000043'')
                         GROUP BY BH, ODBIORCA, KANAL_SPRZEDAZY, SEGMENT2018, SEGMENT2019';

Cheers!!

Upvotes: 0

hotfix
hotfix

Reputation: 3396

you procedure could look like this one:

create or replace PROCEDURE Marza_II_PROCEDURE(FirstDateOpen DATE
                                              ,FirstDateClose DATE
                                              ,SecondDateOpen DATE 
                                              ,SecondDateClose DATE)
as
BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE MARZA_REPORT' ;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
        RAISE;
      END IF;
  end;
--  COMMIT; not sure why you need a commit

  EXECUTE IMMEDIATE 'CREATE TABLE MARZA_REPORT AS
                       select BH, ODBIORCA,KANAL_SPRZEDAZY, SEGMENT2018, SEGMENT2019
                              ,SUM(CASE WHEN MIESIAC BETWEEN ' || FirstDateOpen || ' and ' ||FirstDateClose || 'then OBROT else 0 end) OBROT1,
                              ,SUM(CASE WHEN MIESIAC BETWEEN ' || SecondDateOpen || ' and ' ||SecondDateClose || 'then OBROT else 0 end) OBROT2
                         from dws1.marza_netto
                         WHERE MIESIAC >= ' || SecondDateOpen || '
                           AND ODBIORCA IN (''0001006834'',''0001024402'',''0001000043'')
                         GROUP BY BH, ODBIORCA, KANAL_SPRZEDAZY, SEGMENT2018, SEGMENT2019';

exception
  when others then
    -- handle your exception 
    raise;
END;

The argument that you pass into your proc are strings and not dates, so you should convert tham to date:

Begin
  Marza_II_PROCEDURE(to_date('20190101','yyyymmdd'), to_date('20190201','yyyymmdd'), to_date('20180101','yyyymmdd'), to_date('20180201','yyyymmdd'));
end

Upvotes: 2

Related Questions