SQLTeam.com | Weblogs | Forums

Help me with the sql error missing keyword

#1

Select Datum, Wdomain, lgstkproc, Username,

Case when LGSTKPROC ='NAVUL'

Count (unique username '-'palnumber end nr_pal_per_user_navul,

Case when LGSTKPROC !='NAVUL'

      Count (unique username '-'palnumber end nr_pal_per_user_veplaatst

From (

SELECT username, res, restype, wdomain, lgstkproc, locname, endlocname, palnumber, TRUNC(SYSDATE-1) datum

FROM TransportStock$VW TS

WHERE TRUNC(timestamp)<TRUNC(sysdate)-1

AND res NOT LIKE '%CONVS%'

)

Group by

username, Datum, lgstkproc, Wdomain

0 Likes

#2

CASE expression is structured as:

CASE {expression}
WHEN {value} THEN {expression}
WHEN {othervalue} THEN {expression}
ELSE {expression}
 END

Or

CASE WHEN {expression} THEN {expression}
     WHEN {expression} THEN {expression}
     ELSE {expression}
 END

You do not have THEN or ELSE or END and you are missing parentheses in the appropriate places with 'end' included where it isn't valid.

Count (unique username '-'palnumber end nr_pal_per_user_navul,

This may be what you are looking for, but since this code appears to be MySQL and not Microsoft SQL Server I cannot be sure:

CASE WHEN LGSTKPROC ='NAVUL' THEN count(unique username + '-' + palnumber) END AS nr_pal_per_user_veplaatst
1 Like

#3

Hello @jeffw8713 Thank you for your quick reply, to be clear I'm not advanced in sql, I'm a simple user of an interface where I often run query's. so can you please highlight on red what should I replace on my query?
THANK YOU SIR

0 Likes

#4

There were quite a few issues - so I reformatted the query:

 Select Datum
      , Wdomain
      , lgstkproc
      , Username

      , Case When LGSTKPROC = 'NAVUL'
             Then count(Unique username + '-' + palnumber)
         End As nr_pal_per_user_navul

      , Case When LGSTKPROC != 'NAVUL'
             Then count(Unique username + '-' + palnumber)
         End As nr_pal_per_user_veplaatst

   From (Select username
              , res
              , restype
              , wdomain
              , lgstkproc
              , locname
              , endlocname
              , palnumber
              , TRUNC(SYSDATE - 1) As datum
           From TransportStock$VW               TS
          Where TRUNC(timestamp) < TRUNC(sysdate) -1
            And res Not Like '%CONVS%'
        )
  Group By
        username
      , Datum
      , lgstkproc
      , Wdomain

These are the changes I made:

  , Case When LGSTKPROC = 'NAVUL'
         **Then** count(Unique username **+** '-' **+** palnumber**)**
     End **As** nr_pal_per_user_navul

Similar changes were made to the next CASE expression.

2 Likes