-1

I tried to put :

Select .... , (CASE WHEN  (D.C_ZONE is null or D.C_ZONE = '') THEN 
coalesce(DL.C_ZONE, ' ') ELSE coalesce(D.C_ZONE, ' ' )  END ) AS     C_ZONE, 
coalesce(Z.N_ZONE,'') as N_ZONE, coalesce(Z.N_REGN,'') as N_REGN,..... FROM 
(CASE WHEN (D.C_ZONE is null or D.C_ZONE = '') THEN ( S.SNFDLR DL LEFT OUTER 
join S.SRCZNRG Z ON ( DL.C_ZONE=Z.C_ZONE )) ELSE ( S.SFDDIST D LEFT OUTER 
join S.SRCZNRG Z ON ( D.C_ZONE=Z.C_ZONE )) END ), S.SNFDLR DL, S.SFDDIST D 
..... WHERE ....... 

Basically, C_ZONE is supposed to be fetched from either of the two tables, S.SNFDLR or S.SFDDIST, based on whether it is null or empty.

Simultaneously, there are 2 more columns, N_ZONE and N_REGN, which are supposed to be fetched using the C_ZONE value from another table, S.SRCZNRG.

For this purpose, I need to perform a left outer join between (S.SNFDLR and S.SRCZNRG) or (S.SFDDIST and S.SRCZNRG), based on whichever gives me the C_ZONE value.

I am using a CASE expression for this scenario, but practically I am unable to use it properly. The CASE expression syntax usage is wrong. I basically want my joins to work out based on my WHEN condition.

0

1 Answer 1

4

The code that produces the syntax error:

SELECT V.I_VIN_FIRST_9, -- many more columns 

    case when  (D.C_ZONE is null or D.C_ZONE = '') 
        then 
             (coalesce(DL.C_ZONE, ' ')  as C_ZONE    -- issue 1 here
              from S.SNFDLR DL
             ) 
        else 
             (coalesce(D.C_ZONE, ' ' )  as C_ZONE    -- same issue again
              from S.SFDDIST D
             ) 
    end

--- here is the main problem. Where is the FROM clause of the query?    

    LEFT OUTER join S.SRCZNRG Z ON ( DL.C_ZONE=Z.C_ZONE )  -- issue 3
    LEFT OUTER join S.SRCZNRG Z ON ( D.C_ZONE=Z.C_ZONE ),  -- here

    -- a few more joins

    P.PBUS P
    LEFT OUTER JOIN S.SFDDIST D 
        ON ( D.I_FRNCHZ = P.I_BUS  AND D.C_TYP IN ('X') ) 
    left outer join S.SNFDLR DL  
        on (DL.I_CUST = P.I_BUS and D.C_ZONE is null)

WHERE ... ;

There are three issues here:

  • the main query has no FROM clause. The FROM has been hidden inside the CASE expression.

  • the expressions inside the CASE (inside the THEN and ELSE parts) have no SELECT clause!

  • you join the same table twice with the same alias.

All the above are invalid syntax.

  • (and a 4th issue discussed below, mixing implicit with explicit join syntax)

It is not entirely clear what the correct solution would be. The two tables (DL and D) that are referred inside the CASE are also referred in the following (many joins). So, you might just simplify the CASE expression:

select 
    V.I_VIN_FIRST_9, 
   -- many more columns 

    case when  D.C_ZONE is null or D.C_ZONE = '' 
        then 
             coalesce(DL.C_ZONE, ' ')
        else 
             coalesce(D.C_ZONE, ' ' )
    end
         as C_ZONE                     -- added alias

and add the missing FROM, rearranging the tables (a FROM cannot start with a LEFT JOIN) and combining the duplicate join into one:

from
    -- rearrange 
    P.PBUS as P
    left outer join S.SFDDIST as D 
        on  D.I_FRNCHZ = P.I_BUS 
        and D.C_TYP IN ('X')  
    left outer join S.SNFDLR as DL  
        on  DL.I_CUST = P.I_BUS 
        and (D.C_ZONE is null or D.C_ZONE = '') 

    -- combine the two joins in one
    left outer join S.SRCZNRG as Z 
        on  case when  D.C_ZONE is null or D.C_ZONE 
                then DL.C_ZONE else D.C_ZONE
            end = Z.C_ZONE  

Since we are rewriting, lets also (solve the 4th issue). This is not needed but mixing implicit with explicit join syntax can be confusing and error-prone.
So lets convert the implicit join syntax to explicit JOIN by moving the conditions from WHERE to where they belong (ON clauses):

    inner join S.SRCXDTL as V
        on  V.I_BUS = P.I_BUS  
    inner join S.SRCXDEF as F
        on  V.I_VHCL_RECAL     = F.I_VHCL_RECAL 
        and V.I_VHCL_RECAL_SEQ = F.I_VHCL_RECAL_SEQ
    inner join S.SVEHW as W
        on  V.I_PRTITN     = W.I_PRTITN    
        and V.I_VHCL_SAN   = W.I_VHCL_SAN 
        and V.I_VIN_LAST_8 = W.I_VIN_LAST_8

and keeping only the non-joining condition in the WHERE clause:

where   V.I_VHCL_RECAL = 'X05' 
  and   V.I_VIN_LAST_8 > ' ' 
  and   V.I_VIN_FIRST_9 > ' ' 
  and   V.I_BUS between 0 and 99999999999 
  and   V.I_PRTITN in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 
  and   V.I_VHCL_SAN between 0 and 99999999999 ;
0

Your Answer

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

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