Creating a procedure for task to work it workshifts
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
add a comment |
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
add a comment |
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
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
oracle stored-procedures plsql
edited Nov 15 '18 at 14:20
user7294900
22.9k113362
22.9k113362
asked Nov 14 '18 at 10:54
uniqezorptuniqezorpt
32
32
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You are missing ID for i
, add it in select:
FOR i IN (SELECT ID, calendar_start, calendar_end, estado
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 removesysdate
. 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
add a comment |
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
You are missing ID for i
, add it in select:
FOR i IN (SELECT ID, calendar_start, calendar_end, estado
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 removesysdate
. 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
add a comment |
You are missing ID for i
, add it in select:
FOR i IN (SELECT ID, calendar_start, calendar_end, estado
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 removesysdate
. 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
add a comment |
You are missing ID for i
, add it in select:
FOR i IN (SELECT ID, calendar_start, calendar_end, estado
You are missing ID for i
, add it in select:
FOR i IN (SELECT ID, calendar_start, calendar_end, estado
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 removesysdate
. 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
add a comment |
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 removesysdate
. 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
add a comment |
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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