Sql Command not properly ended//

Question

It displays sql command not properly ended

PROCEDURE getCalendar (
V_Year VARCHAR2, V_Month VARCHAR2, V_LoginId varchar2, curAttendanceDate OUT T_CURSOR )

AS
   
      V_Date  DATE := TO_DATE (V_Year || '-' || V_Month || '-01', 'yyyy-mm-dd');
   BEGIN 
    
    
  Insert into T_ATTENDANCE_ATTENDANCE (LoginId, AttendanceDate,ShiftName, ShiftStartTime, ShiftEndTime, Active, AttendanceStatus)
     
   WITH DaysInMonth (dates) AS 
(
  SELECT V_Date FROM   DUAL 
  UNION ALL
  SELECT dates + INTERVAL '1' DAY
  FROM   DaysInMonth
  WHERE  dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(V_Date, 'MONTH'), 1))
  select u.LoginID, d.dates, '' , null, null, 1, 'Approved' from daysinmonth d, T_ATTENDANCE_USER_MASTER U
  where loginid = V_LoginId
  and extract(month from dates) = extract(month from V_date) 
  and not Exists (select LoginId from T_ATTENDANCE_ATTENDANCE where LoginId = V_LoginId
  AND EXTRACT(YEAR FROM ATTENDANCEDATE) = extract(year from V_date) AND 
  EXTRACT(MONTH FROM AttendanceDate) = extract(month from V_date) AND ACTIVE = 1 );
  
   
     
   V_Date := INTERVAL '1' MONTH + V_Date;
   
WITH DaysInMonth (dates) AS 
  (
  SELECT V_Date FROM   DUAL 
  UNION ALL
  SELECT dates + INTERVAL '1' DAY
  FROM   DaysInMonth
  WHERE  dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(V_Date, 'MONTH'), 1))
  select dates from DaysInMonth;
  
  
  Insert into T_ATTENDANCE_ATTENDANCE (LoginId, AttendanceDate,ShiftName, ShiftStartTime, ShiftEndTime, Active, AttendanceStatus)
         Select u.LoginID, d.dates, '',null, null, 1, 'Approved'  from DaysInMonth d cross join T_ATTENDANCE_USER_MASTER u 
     where extract(month from dates) = extract(month from V_date)  and LoginId = V_LoginId  and 
     not Exists (select LoginId from T_ATTENDANCE_ATTENDANCE where LoginId = V_LoginId AND 
     EXTRACT(YEAR FROM ATTENDANCEDATE) = extract(year from V_date) 
     AND EXTRACT(MONTH FROM AttendanceDate) = extract(month from V_date) AND ACTIVE = 1 );
     
     
    SELECT AttendanceDate, ShiftName, 
    TO_CHAR(AttendanceDate,'ddd') Day, 
    V_YEAR, V_MONTH , 
    To_char(SignIn, 'yyyy-MM-dd hh:mm:ss tt') SignIn, 
    To_char(SignOut, 'yyyy-MM-dd hh:mm:ss tt') SignOut FROM T_ATTENDANCE_ATTENDANCE
        WHERE LoginId = V_LoginId AND EXTRACT(YEAR FROM ATTENDANCEDATE) = V_Year 
    AND EXTRACT(MONTH FROM AttendanceDate) = V_Month
        AND ACTIVE = 1 ;

UPDATE a
           SET a.shiftname =
                  (SELECT CASE
                             WHEN     TO_CHAR (TO_DATE (a.attendancedate), 'DAY') IN
                                         ('SATURDAY')
                                  AND a.week IN (2, 4)
                             THEN
                                'WEEKLYOFF'
                             WHEN TO_CHAR (TO_DATE (a.attendancedate), 'WW') IN
                                     ('SUNDAY')
                             THEN
                                'WEEKLYOFF'
                             ELSE
                                'GENERAL1'                          END
                     FROM (SELECT attendancedate,
                                  shiftname,
                                   ROW_NUMBER ()
                                      OVER (
                                         PARTITION BY TO_CHAR (
                                                         TO_DATE (attendancedate, 'DAY'))
                                        ORDER BY attendancedate) AS week
                            FROM T_ATTENDANCE_ATTENDANCE  a1                                  LEFT JOIN T_ATTENDANCE_USER_MASTER u
                                     ON u.loginid = a1.loginid
                                   LEFT JOIN T_ATTENDANCE_EMPLOYEE_MASTER e
                                     ON e.empid = u.empid
                            WHERE     a1.loginid = V_loginid
                                   AND e.company IS NULL
                                   AND EXTRACT (YEAR FROM attendancedate) =
                                       EXTRACT (YEAR FROM SYSDATE)
                                  AND EXTRACT (MONTH FROM attendancedate) =
                                      EXTRACT (MONTH FROM SYSDATE)
                                  AND NVL (shiftname, 'x') = 'x') a )
                                   ORDER BY AttendanceDate;


end getCalendar;


---It displays sql command not properly ended--

CREATE TABLE "PROMETHEAN"."T_ATTENDANCE_ATTENDANCE" ( "ATTENDANCEID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, "LOGINID" VARCHAR2(200 BYTE) NOT NULL ENABLE, "ATTENDANCEDATE" TIMESTAMP (3), "SHIFTNAME" VARCHAR2(50 BYTE), "SHIFTSTARTTIME" TIMESTAMP (3), "SHIFTENDTIME" TIMESTAMP (3), "SIGNIN" TIMESTAMP (3), "SIGNOUT" TIMESTAMP (3), "OLDATTENDANCEID" NUMBER(10,0), "ACTIVE" NUMBER(10,0), "REMARK" VARCHAR2(255 BYTE), "ATTENDANCESTATUS" VARCHAR2(20 BYTE), "L1APPROVEDDBY" VARCHAR2(20 BYTE), "L1APPROVEDON" TIMESTAMP (3), "L2APPROVEDBY" VARCHAR2(20 BYTE), "L2APPROVALON" TIMESTAMP (3), "CREATEDBY" VARCHAR2(20 BYTE), "CREATEDON" TIMESTAMP (3), "MODIFIEDBY" VARCHAR2(20 BYTE), "MODIFIEDON" TIMESTAMP (3), "IPADDRESS" VARCHAR2(100 BYTE), "LOGONUSERNAME" VARCHAR2(100 BYTE), "COMPOFFDATE" DATE, "WORKLOCATIONTYPE" VARCHAR2(10 BYTE), "WORKLOCATION" VARCHAR2(50 BYTE), PRIMARY KEY ("ATTENDANCEID")

PROCEDURE getCalendar ( V_Year VARCHAR2, V_Month VARCHAR2, V_LoginId varchar2, curAttendanceDate OUT T_CURSOR ) — sudhir mishra
– sudhir mishra, Commented Nov 16, 2023 at 7:38
Not able to compile above code in oracle it always displays sql command not properly ended kindly help — sudhir mishra
– sudhir mishra, Commented Nov 16, 2023 at 7:46
As you haven't included the CREATE part, or a final /, is this a standalone procedure, or part of a package? A minimal reproducible example would be helpful. And which client/IDE are you running it in, and with which mechanism? Some clients don't recognise or handle PL/SQL well, or at all - so maybe try again in SQL*Plus or SQL Developer to rule that out. — Alex Poole
– Alex Poole, Commented Nov 16, 2023 at 8:32

MT0 · Accepted Answer · 2023-11-16 12:21:44Z

You asked about this in your previous question.

Which told you that:

Among other errors, you cannot use a SELECT statement on its own in PL/SQL. You need to SELECT ... [BULK COLLECT] INTO ....

You also cannot use (excluding the fact that the ORDER BY clause is an error):

UPDATE a
SET a.shiftname = (SELECT value
                   FROM   table_name a)

As the alias a is unknown in the outer query and I am assuming that you do not have a table named a.

You can probably simplify your procedure to use a single MERGE statement that inserts 2 months data at once (rather than two 1-month inserts) and also calculates the week number to set the shift name in the INSERT (rather than requiring a subsequent UPDATE):

CREATE PACKAGE BODY package_name AS

PROCEDURE getCalendar (
  V_Year VARCHAR2,
  V_Month VARCHAR2,
  V_LoginId varchar2,
  curAttendanceDate OUT T_CURSOR
)

AS
  V_Date  DATE := TO_DATE (V_Year || '-' || V_Month || '-01', 'yyyy-mm-dd');
BEGIN
  MERGE INTO T_ATTENDANCE_ATTENDANCE dst
  USING (
    WITH DaysInMonth (dates) AS (
      SELECT V_Date FROM   DUAL 
    UNION ALL
      SELECT dates + INTERVAL '1' DAY
      FROM   DaysInMonth
      WHERE  dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(V_Date, 'MONTH'), 2)
    )
    SELECT u.LoginID,
           d.dates,
           (TRUNC(d.dates, 'IW') - a.start_date) / 7 AS week
    FROM   daysinmonth d
           CROSS JOIN T_ATTENDANCE_USER_MASTER U
           CROSS JOIN LATERAL (
             SELECT TRUNC(COALESCE(MIN(attendancedate), v_date), 'IW') AS start_date
             FROM   T_ATTENDANCE_ATTENDANCE a
             WHERE  u.loginid = a.loginid
           ) a
    WHERE  loginid = V_LoginId
  ) src
  ON (dst.loginid = src.loginid AND dst.attendancedate = src.dates)
  WHEN NOT MATCHED THEN
    INSERT (
      LoginId,
      AttendanceDate,
      ShiftName,
      ShiftStartTime,
      ShiftEndTime,
      Active,
      AttendanceStatus
    ) VALUES (
      src.loginid,
      src.dates,
      CASE
      WHEN (   TRUNC(src.dates) - TRUNC(src.dates, 'IW') = 5
           AND MOD(src.week, 4) IN (1, 3) -- Change from 1-indexed weeks to 0-indexed.
           )
      OR   TRUNC(src.dates) - TRUNC(src.dates, 'IW') = 6
      THEN 'WEEKLYOFF'
      ELSE 'GENERAL1'
      END,
      NULL,
      NULL,
      1,
      'Approved'
    );
end getCalendar;

END;
/

However, your logic for calculating weeks is not well defined so this is mostly just a template for how you could implement it and you will need to make sure the logic is correct.

fiddle

Littlefoot · Accepted Answer · 2023-11-16 07:46:28Z

0

Remove ORDER BY clause from UPDATE statement, here:

UPDATE a
  SET a.shiftname = (...)
                   WHERE     a1.loginid = V_loginid
                         AND e.company IS NULL
                         AND EXTRACT (YEAR FROM attendancedate) =
                             EXTRACT (YEAR FROM SYSDATE)
                         AND EXTRACT (MONTH FROM attendancedate) =
                             EXTRACT (MONTH FROM SYSDATE)
                         AND NVL (shiftname, 'x') = 'x') a);
                   --ORDER BY AttendanceDate;                    --> here

answered Nov 16, 2023 at 7:46

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

2 Comments

sudhir mishra Over a year ago

I have done that too still it gives error can you rewrite the whole code for me

Littlefoot Over a year ago

I don't see any other obvious errors, and I can't test your code as I don't have your tables. If you posted CREATE TABLE statements (i.e. prepare test case for us), we would be able to actually execute that code. Without it, nope ... and I don't feel like creating them myself.

Collectives™ on Stack Overflow

Sql Command not properly ended//

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related