having problems with Oracle SQL , define statement , in conjunction with 'WITH' statement-CodePudding

I'm very new to SQL and have a question about declaring a variable, that I can use in multiple statements.

I had hoped that something like this would work. It did not work. I am getting this error:

ORA-06550: line 8, column 1:
PLS-00103: Encountered the symbol "WITH"

When I use the declare statement all by itself, without any table statements I get this error:

ORA-06550: line 12, column 13:
PLS-00221: 'TO_CHAR' is not a procedure or is undefined

This is the code.

The declare statements are exactly as I wrote them

The tables statements are simplified versions of the original, to show you what I am trying to accomplish.

DECLARE 
    EOM  VARCHAR(6);
    LD VARCHAR(1);
BEGIN
    LD := TO_CHAR(LAST_DAY(add_months(sysdate,-1)),'D');
    CASE 
        WHEN LD = 7 THEN 
            EOM := TO_CHAR(LAST_DAY(add_months(sysdate,-1)),'yyyymmdd')-1;
        WHEN LD = 1 THEN 
            EOM := TO_CHAR(LAST_DAY(add_months(sysdate,-1)),'yyyymmdd')-2;
        ELSE 
            ***EOM :=*** TO_CHAR(LAST_DAY(add_months(sysdate,-1)),'yyyymmdd');
    END CASE;
END;

WITH 
TABLE1 AS
    ( SELECT * FROM DBO.ID
      WHERE ID.CYCLE_DT_NUM = EOM )
,TABLE2 AS
    ( SELECT * FROM DBO.LOAN
      WHERE LOAN.CYCLE_DT_NUM = EOM )
SELECT * FROM DBO.INCOME 
    WHERE INCOME.CYCLE_DT_NUM = EOM
    LEFT JOIN TABLE1 ON
        TABLE1.IDNUM=TABLE2.IDNUM
    LEFT JOIN TABLE2 ON
        TABLE1.IDNUM=TABLE3.IDNUM

Can someone point me in the right direction, please?

Is what I am doing even close to being right?

CodePudding user response：

It is only one WITH per CTE; also, TABLE3 looks suspicious, it can't look like that. Maybe parenthesis are missing? If so, you're then missing another SELECT which returns the final result; as this is PL/SQL, it also requires the INTO clause, but I have no idea what exactly you're selecting and which structure is supposed to hold that result.

Something like this:

WITH 
TABLE1 AS
    ( SELECT * FROM DBO.ID
      WHERE ID.CYCLE_DT_NUM = EOM ),
TABLE2 AS
    ( SELECT * FROM DBO.LOAN
      WHERE LOAN.CYCLE_DT_NUM = EOM ),
TABLE3 AS
    ( SELECT * FROM DBO.INCOME 
      WHERE INCOME.CYCLE_DT_NUM = EOM
    )
SELECT   ...                        --> select what?
    INTO ...                        --> into what?
    FROM TABLE1 LEFT JOIN TABLE2 ON
        TABLE1.IDNUM=TABLE2.IDNUM
    LEFT JOIN TABLE3 ON
        TABLE1.IDNUM=TABLE3.IDNUM;

As of to_char issue, there's nothing wrong with it:

SQL> DECLARE
  2      EOM  VARCHAR(6);
  3      LD VARCHAR(1);
  4  BEGIN
  5      LD := TO_CHAR(LAST_DAY(add_months(sysdate,-1)),'D');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

You cannot DECLARE PL/SQL variables inside an anonymous block and then reference then in an SQL statement that is outside the block.

What you want to do is either: declare the end-of-month value inside the SQL statement; or move the SQL statement into the PL/SQL block (but then you would need to use PL/SQL to process the output of the SQL so I am guessing it would be preferable to use the former rather than the latter).

You also do not need to use so many sub-query factoring clauses as you can join the underlying tables and put the filter conditions that you are using inside the join condition.

You want something like:

WITH last_weekday_of_month (day) AS (
  SELECT LEAST(
           -- Last day of the previous month
           TRUNC(SYSDATE, 'MM') - 1,
           -- Or Friday of the week containing the last day of the previous month
           TRUNC(TRUNC(SYSDATE, 'MM') - 1, 'IW')   4
         )
  FROM   DUAL
)
SELECT *
FROM   last_weekday_of_month lwdm
       INNER JOIN DBO.INCOME I
       ON (i.CYCLE_DT_NUM = lwdm.DAY)
       LEFT OUTER JOIN DBO.ID
       ON (I.IDNUM = ID.IDNUM AND id.CYCLE_DT_NUM = lwdm.DAY)
       LEFT OUTER JOIN DBO.LOAN L
       ON (ID.IDNUM = L.IDNUM AND l.CYCLE_DT_NUM = lwdm.DAY)