Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am this view which I am trying to compile. Given below is the view.

  CREATE OR REPLACE FORCE VIEW "My_View" ("my_ORDER", 

"REQUESTOR", "DEPARTMENT", "HOD", "SHIP_TO", "SOLD_TO", "PRODUCT", "GOODS_TYPE", "GOODS_CATEGORY", "ADDITIONAL_TEXT", "EXP_RETURN_DATE", "RETURN_STATUS", "VMONTH", "VYEAR", "TOTAL", "CURRENCY", "REMARKS", "FLIGHT_INFO")
    AS
      SELECT DISTINCT a.location
        || DECODE(LENGTH(a.financial_year), 1, '0'
        || a.financial_year , a.financial_year)
        || '-'
        || a.order_no My_ORDER,

    CASE
      WHEN a.ORDS_INITIATOR_USRS_ID != a.ORDS_CREATED_USRS_ID
      THEN v_users.ADUS_GIVEN_NAME                    
        || ' '
        || v_users.ADUS_SURE_NAME                     
        || '/'
        || v_users1.ADUS_GIVEN_NAME                    
        || ' '
        || v_users1.ADUS_SURE_NAME                     
      ELSE v_users.ADUS_GIVEN_NAME                    
        || ' '
        || v_users.ADUS_SURE_NAME                     
    END as Requestor,
    dept.DEPT_NAME Department ,
    c.display_name HOD,

            oadd1.OADD_CUSTOMERNO SHIP_TO,

            oadd2.OADD_CUSTOMERNO SOLD_TO,
    b.type Product,
    d.goods_type_sap Goods_type,
    e.goods_category,
    b.addtext Additional_Text,
    b.exp_return_date,
    'OVERDUE' return_status,
    TO_CHAR(b.exp_return_date, 'MON') vMonth,
    TO_CHAR(b.exp_return_date, 'yyyy') vYear,
    ROUND(DECODE(price_unit,1,price * quantity,9,price,price * quantity / power (10,price_unit -1)),2) Total,
    b.currency,
    a.remarks,
    shipping_details Flight_info
  FROM orders a,


    (SELECT a.STSL_ORDS_ID,

            v_users.ADUS_DISPLAY_NAME    as user,              

              v_users1.ADUS_DISPLAY_NAME AS initialUser,
              LAG (a.STSL_FROM_STAS_ID) OVER (PARTITION BY a.STSL_ORDS_ID ORDER BY STSL_CHANGED_DATE) AS lag_status_id

              FROM ORDERS d

              INNER JOIN STATUS_LOG a ON d.ORDS_ID = a.STSL_ORDS_ID
              INNER JOIN USERS c ON a.STSL_USRS_ID=c.USRS_ID
              -------------------
              INNER JOIN v_uif_ad_users v_users
               ON c.usrs_id = v_users.ADUS_SAM_ACCOUNT_NAME and a.STSL_USRS_ID=c.USRS_ID
  ---------------------
              INNER JOIN v_uif_ad_users v_users1
              ON c.usrs_id = v_users1.ADUS_SAM_ACCOUNT_NAME and c.USRS_ID=d.ORDS_INITIATOR_USRS_ID
  ------------------------
              INNER JOIN ORDERS d on d.ORDS_ID=a.STSL_ORDS_ID
              inner join status b on a.STSL_FROM_STAS_ID=b.STAS_ID

            where
                        d.location    = 'MAL'
    AND d.ORDS_STAS_ID   = 100
    ORDER BY ORDS_ID DESC,
      lag_status_id
    ) c
  INNER JOIN departments dept on a.ORDS_DEPT_ID=dep.DEPT_ID                           
  inner join ORDERS_DETAIL b on a.ords_id=b.ORDL_ORDS_ID
  inner join orders_address oadd1 on a.ORDS_ID=oadd1.oadd_ords_id and oadd1.oadd_atyp_id=2
  inner join orders_address oadd2 on a.ORDS_ID=oadd2.oadd_ords_id and oadd2.oadd_atyp_id=3
  inner join goods_type d on b.ORDL_GTYP_ID = d.GTYP_ID
  inner join goods_category e on b.ORDL_GCAT_ID = e.GCAT_ID
  inner join users f on f.users_id =a.ORDS_INITIATOR_USRS_ID
  inner join users g on g.users_id = a.ORDS_CREATED_USRS_ID
  WHERE --a.ORDS_ID         = b.ORDL_ORDS_ID

   a.ORDS_ID=c.STSL_ORDS_ID

  AND a.ORDS_STAS_ID           = 100
  AND a.location            ='MAL'
  AND c.lag_status_id       = 10

  AND b.ORDL_TO_BE_RETURNED      = 1
  AND b.ORDL_EXP_RETURN_DATE    <= sysdate
  AND ( b.ORDL_RETURN_STATUS    IS NULL
  OR upper(b.ORDL_RETURN_STATUS) = 'OUTSTANDING'
  OR upper(b.ORDL_RETURN_STATUS) = 'OVERDUE' )
 ORDER BY my_Order;

The error which I get is Error at command Line:45 Column:44 Error Report: ORA-00923:From keyword not found where expected

Please see what I can do about this. Thanks in advance.

share|improve this question
1  
Where is "Line:45 Column:44"? There are empty lines in your code. The following may be a cop and paste error "v_users.ADUS_DISPLAY_NAME as user, AS initialUser," –  miracle173 Mar 19 at 9:40
    
reduce your query: remove fields from the select list, remove expression from the where clause etc. see dba.stackexchange.com/a/33482/2047 –  miracle173 Mar 19 at 9:53
1  
You don't explicitly join a to c, there is a comma: FROM orders a, This is probably the cause of this error. Move the join condition from the WHERE to a proper JOIN / ON clause. –  ypercube Mar 19 at 10:03
    
@ypercube A and c do get joined a.ORDS_ID=c.STSL_ORDS_ID –  Phil Mar 19 at 10:21
    
@Phil I should say they are not explicitly JOINed. The join condition is at WHERE and the mix of ansi-92 and pre-ansi-92 joins makes the query not valid. –  ypercube Mar 19 at 10:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.