Creating a procedure for task to work it workshifts










0















I'm creating a procedure for task for a workshift and I stumble upon some errors



CREATE OR REPLACE PROCEDURE check_task_turnos
AS
BEGIN
FOR i
IN (SELECT calendar_start,
calendar_end,
estado,
CASE
WHEN TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) >=
7
AND TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) <
15
AND estado = 'Aceite'
THEN
'apex-cal-yellow'
ELSE
NULL
END
FROM PASSAGEM)
LOOP
-- registo no LOG , aqui nao registas na PASSAGEM, registas numa tabela ao lado de LOG para teres referencia de quando foi alterada a data da tarefa
INSERT INTO PASSAGEM_LOG (passagem_id, start_date, end_date)
VALUES (
i.id,
i.calendar_start + (SYSDATE - 8 / 24),
i.calendar_end + (SYSDATE - 8 / 24));

-- update das horas de inico e fim para posicionamento no calendario
UPDATE PASSAGEM
SET calendar_start = i.calendar_start + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
,
calendar_end = i.calendar_end + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
WHERE ID = i.ID;
END LOOP;

COMMIT;
END;


Errors:



Error(11,17): PL/SQL: SQL Statement ignored 
Error(15,42): PL/SQL: ORA-00984: column not allowed here
Error(15,42): PLS-00302: component 'ID' must be declared
Error(19,17): PL/SQL: SQL Statement ignored
Error(22,28): PL/SQL: ORA-00904: "I"."ID": invalid identifier
Error(22,30): PLS-00302: component 'ID' must be declared









share|improve this question




























    0















    I'm creating a procedure for task for a workshift and I stumble upon some errors



    CREATE OR REPLACE PROCEDURE check_task_turnos
    AS
    BEGIN
    FOR i
    IN (SELECT calendar_start,
    calendar_end,
    estado,
    CASE
    WHEN TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) >=
    7
    AND TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) <
    15
    AND estado = 'Aceite'
    THEN
    'apex-cal-yellow'
    ELSE
    NULL
    END
    FROM PASSAGEM)
    LOOP
    -- registo no LOG , aqui nao registas na PASSAGEM, registas numa tabela ao lado de LOG para teres referencia de quando foi alterada a data da tarefa
    INSERT INTO PASSAGEM_LOG (passagem_id, start_date, end_date)
    VALUES (
    i.id,
    i.calendar_start + (SYSDATE - 8 / 24),
    i.calendar_end + (SYSDATE - 8 / 24));

    -- update das horas de inico e fim para posicionamento no calendario
    UPDATE PASSAGEM
    SET calendar_start = i.calendar_start + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
    ,
    calendar_end = i.calendar_end + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
    WHERE ID = i.ID;
    END LOOP;

    COMMIT;
    END;


    Errors:



    Error(11,17): PL/SQL: SQL Statement ignored 
    Error(15,42): PL/SQL: ORA-00984: column not allowed here
    Error(15,42): PLS-00302: component 'ID' must be declared
    Error(19,17): PL/SQL: SQL Statement ignored
    Error(22,28): PL/SQL: ORA-00904: "I"."ID": invalid identifier
    Error(22,30): PLS-00302: component 'ID' must be declared









    share|improve this question


























      0












      0








      0








      I'm creating a procedure for task for a workshift and I stumble upon some errors



      CREATE OR REPLACE PROCEDURE check_task_turnos
      AS
      BEGIN
      FOR i
      IN (SELECT calendar_start,
      calendar_end,
      estado,
      CASE
      WHEN TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) >=
      7
      AND TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) <
      15
      AND estado = 'Aceite'
      THEN
      'apex-cal-yellow'
      ELSE
      NULL
      END
      FROM PASSAGEM)
      LOOP
      -- registo no LOG , aqui nao registas na PASSAGEM, registas numa tabela ao lado de LOG para teres referencia de quando foi alterada a data da tarefa
      INSERT INTO PASSAGEM_LOG (passagem_id, start_date, end_date)
      VALUES (
      i.id,
      i.calendar_start + (SYSDATE - 8 / 24),
      i.calendar_end + (SYSDATE - 8 / 24));

      -- update das horas de inico e fim para posicionamento no calendario
      UPDATE PASSAGEM
      SET calendar_start = i.calendar_start + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
      ,
      calendar_end = i.calendar_end + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
      WHERE ID = i.ID;
      END LOOP;

      COMMIT;
      END;


      Errors:



      Error(11,17): PL/SQL: SQL Statement ignored 
      Error(15,42): PL/SQL: ORA-00984: column not allowed here
      Error(15,42): PLS-00302: component 'ID' must be declared
      Error(19,17): PL/SQL: SQL Statement ignored
      Error(22,28): PL/SQL: ORA-00904: "I"."ID": invalid identifier
      Error(22,30): PLS-00302: component 'ID' must be declared









      share|improve this question
















      I'm creating a procedure for task for a workshift and I stumble upon some errors



      CREATE OR REPLACE PROCEDURE check_task_turnos
      AS
      BEGIN
      FOR i
      IN (SELECT calendar_start,
      calendar_end,
      estado,
      CASE
      WHEN TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) >=
      7
      AND TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) <
      15
      AND estado = 'Aceite'
      THEN
      'apex-cal-yellow'
      ELSE
      NULL
      END
      FROM PASSAGEM)
      LOOP
      -- registo no LOG , aqui nao registas na PASSAGEM, registas numa tabela ao lado de LOG para teres referencia de quando foi alterada a data da tarefa
      INSERT INTO PASSAGEM_LOG (passagem_id, start_date, end_date)
      VALUES (
      i.id,
      i.calendar_start + (SYSDATE - 8 / 24),
      i.calendar_end + (SYSDATE - 8 / 24));

      -- update das horas de inico e fim para posicionamento no calendario
      UPDATE PASSAGEM
      SET calendar_start = i.calendar_start + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
      ,
      calendar_end = i.calendar_end + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
      WHERE ID = i.ID;
      END LOOP;

      COMMIT;
      END;


      Errors:



      Error(11,17): PL/SQL: SQL Statement ignored 
      Error(15,42): PL/SQL: ORA-00984: column not allowed here
      Error(15,42): PLS-00302: component 'ID' must be declared
      Error(19,17): PL/SQL: SQL Statement ignored
      Error(22,28): PL/SQL: ORA-00904: "I"."ID": invalid identifier
      Error(22,30): PLS-00302: component 'ID' must be declared






      oracle stored-procedures plsql






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 15 '18 at 14:20









      user7294900

      22.9k113362




      22.9k113362










      asked Nov 14 '18 at 10:54









      uniqezorptuniqezorpt

      32




      32






















          1 Answer
          1






          active

          oldest

          votes


















          1














          You are missing ID for i, add it in select:



           FOR i IN (SELECT ID, calendar_start, calendar_end, estado 





          share|improve this answer























          • Thank you for the assistance but my other problem is giving me is :Error(21,53): PL/SQL: ORA-00975: date + date not allowed

            – uniqezorpt
            Nov 14 '18 at 11:08












          • @uniqezorpt remove sysdate. In your code remove + (SYSDATE

            – user7294900
            Nov 14 '18 at 11:16











          • remove sysdate put like this "i.calendar_start ( -8/24)"

            – uniqezorpt
            Nov 14 '18 at 11:25











          • The code in apex calendar is not applying the condition of case when established in code can you help me

            – uniqezorpt
            Nov 14 '18 at 13:41










          Your Answer






          StackExchange.ifUsing("editor", function ()
          StackExchange.using("externalEditor", function ()
          StackExchange.using("snippets", function ()
          StackExchange.snippets.init();
          );
          );
          , "code-snippets");

          StackExchange.ready(function()
          var channelOptions =
          tags: "".split(" "),
          id: "1"
          ;
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function()
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled)
          StackExchange.using("snippets", function()
          createEditor();
          );

          else
          createEditor();

          );

          function createEditor()
          StackExchange.prepareEditor(
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader:
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          ,
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          );



          );













          draft saved

          draft discarded


















          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53298517%2fcreating-a-procedure-for-task-to-work-it-workshifts%23new-answer', 'question_page');

          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          1














          You are missing ID for i, add it in select:



           FOR i IN (SELECT ID, calendar_start, calendar_end, estado 





          share|improve this answer























          • Thank you for the assistance but my other problem is giving me is :Error(21,53): PL/SQL: ORA-00975: date + date not allowed

            – uniqezorpt
            Nov 14 '18 at 11:08












          • @uniqezorpt remove sysdate. In your code remove + (SYSDATE

            – user7294900
            Nov 14 '18 at 11:16











          • remove sysdate put like this "i.calendar_start ( -8/24)"

            – uniqezorpt
            Nov 14 '18 at 11:25











          • The code in apex calendar is not applying the condition of case when established in code can you help me

            – uniqezorpt
            Nov 14 '18 at 13:41















          1














          You are missing ID for i, add it in select:



           FOR i IN (SELECT ID, calendar_start, calendar_end, estado 





          share|improve this answer























          • Thank you for the assistance but my other problem is giving me is :Error(21,53): PL/SQL: ORA-00975: date + date not allowed

            – uniqezorpt
            Nov 14 '18 at 11:08












          • @uniqezorpt remove sysdate. In your code remove + (SYSDATE

            – user7294900
            Nov 14 '18 at 11:16











          • remove sysdate put like this "i.calendar_start ( -8/24)"

            – uniqezorpt
            Nov 14 '18 at 11:25











          • The code in apex calendar is not applying the condition of case when established in code can you help me

            – uniqezorpt
            Nov 14 '18 at 13:41













          1












          1








          1







          You are missing ID for i, add it in select:



           FOR i IN (SELECT ID, calendar_start, calendar_end, estado 





          share|improve this answer













          You are missing ID for i, add it in select:



           FOR i IN (SELECT ID, calendar_start, calendar_end, estado 






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 14 '18 at 10:58









          user7294900user7294900

          22.9k113362




          22.9k113362












          • Thank you for the assistance but my other problem is giving me is :Error(21,53): PL/SQL: ORA-00975: date + date not allowed

            – uniqezorpt
            Nov 14 '18 at 11:08












          • @uniqezorpt remove sysdate. In your code remove + (SYSDATE

            – user7294900
            Nov 14 '18 at 11:16











          • remove sysdate put like this "i.calendar_start ( -8/24)"

            – uniqezorpt
            Nov 14 '18 at 11:25











          • The code in apex calendar is not applying the condition of case when established in code can you help me

            – uniqezorpt
            Nov 14 '18 at 13:41

















          • Thank you for the assistance but my other problem is giving me is :Error(21,53): PL/SQL: ORA-00975: date + date not allowed

            – uniqezorpt
            Nov 14 '18 at 11:08












          • @uniqezorpt remove sysdate. In your code remove + (SYSDATE

            – user7294900
            Nov 14 '18 at 11:16











          • remove sysdate put like this "i.calendar_start ( -8/24)"

            – uniqezorpt
            Nov 14 '18 at 11:25











          • The code in apex calendar is not applying the condition of case when established in code can you help me

            – uniqezorpt
            Nov 14 '18 at 13:41
















          Thank you for the assistance but my other problem is giving me is :Error(21,53): PL/SQL: ORA-00975: date + date not allowed

          – uniqezorpt
          Nov 14 '18 at 11:08






          Thank you for the assistance but my other problem is giving me is :Error(21,53): PL/SQL: ORA-00975: date + date not allowed

          – uniqezorpt
          Nov 14 '18 at 11:08














          @uniqezorpt remove sysdate. In your code remove + (SYSDATE

          – user7294900
          Nov 14 '18 at 11:16





          @uniqezorpt remove sysdate. In your code remove + (SYSDATE

          – user7294900
          Nov 14 '18 at 11:16













          remove sysdate put like this "i.calendar_start ( -8/24)"

          – uniqezorpt
          Nov 14 '18 at 11:25





          remove sysdate put like this "i.calendar_start ( -8/24)"

          – uniqezorpt
          Nov 14 '18 at 11:25













          The code in apex calendar is not applying the condition of case when established in code can you help me

          – uniqezorpt
          Nov 14 '18 at 13:41





          The code in apex calendar is not applying the condition of case when established in code can you help me

          – uniqezorpt
          Nov 14 '18 at 13:41



















          draft saved

          draft discarded
















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid


          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.

          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53298517%2fcreating-a-procedure-for-task-to-work-it-workshifts%23new-answer', 'question_page');

          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          How to how show current date and time by default on contact form 7 in WordPress without taking input from user in datetimepicker

          Syphilis

          Darth Vader #20