Finding the errors in SQL and interpreting what the SQL codes mean



  • Find all 5 errors in the SQL script below and explain what the script is trying to do

    select
    A.BIZ_DT,
    A.ACTL_PARENT_SVC_NUM,
    A.DIRCTN_IND,
    A.BUS_STOP_CD,
    A.BUS_STOP_ARRVL_TM,
    A.OPR_ID_NUM,
    A.REG_NUM,
    A.BUS_REGISTER_NO,
    A.TRIP_NUM,
    B.PSGR_CC_CNT,
    sum(A.BOARD_CNT) Board,
    sum(A.ALIGHT_CNT) Alight,
    A-B NetBoard
    NetBoard/B.PSGR_CC_CNT as Utilization 
    from PLAP_VIEW_PTQ.V_OD_NET_ON_BOARD_SEP as A
    LEFT JOIN PLAP_TWM_USERSPACE_PTQ.BUs_fleet_2022 AS B
    A.BUS_REGISTER_NO=B.BUS_REGISTER_NO AND A.OPR_ID_NUM=B.OPR_ID_NUM
    where A.SVC_NUM in (147)
    where A.BUS_STOP_CD in(63199)
    and A.BIZ_DT='2022-01-04' and '2022-01-09'
    group 1,2,3,4,5,6,7,8,9,10
    

    The errors that i managed to find are, A.SVC_Num which is wrongly written,
    '2022-01-09' is wrongly written, and the A-B netboard which is missing.

    Im still unable to find the remaining 2 errors and interpret what the SQL code above actually means. Could someone help explains. Thanks!



  • My first suggestion would be to indent the code so that it becomes readable:

    SELECT A.BIZ_DT,
           A.ACTL_PARENT_SVC_NUM,
           A.DIRCTN_IND,
           A.BUS_STOP_CD,
           A.BUS_STOP_ARRVL_TM,
           A.OPR_ID_NUM,
           A.REG_NUM,
           A.BUS_REGISTER_NO,
           A.TRIP_NUM,
           B.PSGR_CC_CNT,
           sum(A.BOARD_CNT) Board,
           sum(A.ALIGHT_CNT) Alight,
    
       A-B NetBoard
       NetBoard/B.PSGR_CC_CNT as Utilization 
    

    FROM PLAP_VIEW_PTQ.V_OD_NET_ON_BOARD_SEP as A
    LEFT JOIN PLAP_TWM_USERSPACE_PTQ.BUs_fleet_2022 AS B

    A.BUS_REGISTER_NO=B.BUS_REGISTER_NO AND A.OPR_ID_NUM=B.OPR_ID_NUM

    where A.SVC_NUM in (147)

    where A.BUS_STOP_CD in(63199)

    and A.BIZ_DT='2022-01-04' and '2022-01-09'

    group 1,2,3,4,5,6,7,8,9,10

    1. What does A and B represent, are there columns in the tables named like this? I guess not and that's the first error
    2. You are also missing a ",".
    3. You can't use the alias Netboard in the calculation
    4. LEFT JOIN needs an ON predicate
    5. You are repeating the WHERE clause
    6. It's GROUP BY, no GROUP

    Whether the GROUP BY is correct is impossible to tell without information on the tables involved.

    That's 6 errors




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2