CODEWITHSUNDEEP
oracle-databaseplsqldeclareora-06550pls-00103
Jana Kajabova
Jana Kajabova

Reputation: 1

How to repair declare statement?

Could you help me, pls repair following SQL statement, I have error in first line. Thanks

DECLARE @T_SYS_FROM SMALLINT,VARCHAR(8);
    SET @T_SYS_FROM = 20200922,DATE;
    SELECT 
      T_SYS_FROM, 
      N_D_AHDJ,
      N_FUND_ID,
      N_AHDJ,
      T_SYS_USER,
      BUDAT
    FROM 
      dss.acn_ahdj_h
    where
      T_SYS_FROM=@ T_SYS_FROM
    ORDER by
      T_SYS_USER;

Upvotes: 0

Views: 46

Answers (1)

MT0
MT0

Reputation: 168051

Your DECLARE looks like you are taking the syntax from another SQL dialect because it isn't valid Oracle PL/SQL syntax.

If you are using PL/SQL then something like (assuming that your query will return exactly one row):

DECLARE 
  v_your_date DATE := DATE '2020-09-22';
  v_T_SYS_FROM dss.acn_ahdj_h.T_SYS_FROM%TYPE;
  v_N_D_AHDJ   dss.acn_ahdj_h.N_D_AHDJ%TYPE;
  v_N_FUND_ID  dss.acn_ahdj_h.N_FUND_ID%TYPE;
  v_N_AHDJ     dss.acn_ahdj_h.N_AHDJ%TYPE;
  v_T_SYS_USER dss.acn_ahdj_h.T_SYS_USER%TYPE;
  v_BUDAT      dss.acn_ahdj_h.BUDAT%TYPE;
BEGIN
  SELECT T_SYS_FROM, 
         N_D_AHDJ,
         N_FUND_ID,
         N_AHDJ,
         T_SYS_USER,
         BUDAT
  INTO   v_T_SYS_FROM, 
         v_N_D_AHDJ,
         v_N_FUND_ID,
         v_N_AHDJ,
         v_T_SYS_USER,
         v_BUDAT
  FROM   dss.acn_ahdj_h
  WHERE  T_SYS_FROM = v_your_date
  ORDER BY T_SYS_USER;
END;
/

If you want an SQL query then:

SELECT T_SYS_FROM, 
       N_D_AHDJ,
       N_FUND_ID,
       N_AHDJ,
       T_SYS_USER,
       BUDAT
FROM   dss.acn_ahdj_h
WHERE  T_SYS_FROM = DATE '2020-09-22'
ORDER BY T_SYS_USER;

Upvotes: 2

Related Questions