-4

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!

5
  • "A.SVC_Num which is wrongly written" - no it's not. "'2022-01-09' is wrongly written" - not it's not Mar 27, 2022 at 10:28
  • LEFT JOIN PLAP_TWM_USERSPACE_PTQ.BUs_fleet_2022 AS B A.BUS_REGISTER_NO= Missing "ON" immediately after the (crap) alias of "B" for the JOIN logic.
    – SMor
    Mar 27, 2022 at 10:51
  • and A.BIZ_DT='2022-01-04' and '2022-01-09' A column cannot be equal to two different values. Perhaps you meant to use BETWEEN? group 1,2,3,4,5,6,7,8,9,10 A bad and lazy habit. Also, time to start terminating all your statements.
    – SMor
    Mar 27, 2022 at 10:52
  • A-B NetBoard No idea what this is nor means. But I end here since you did not post the actual errors you encounter - something you should do for a question like this.
    – SMor
    Mar 27, 2022 at 10:53
  • group 1,2,3,4,5,6,7,8,9,10 which should be group by 1,2,3,4,5,6,7,8,9,10 only works in PostgreSQL, for some reason you've also tagged SQL Server Mar 28, 2022 at 9:07

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.