SQLSCRIPT: sql syntax error: incorrect syntax near "AS"

[nathan23]

Hi 
CASE WHEN (inTab."/BIC/C55CONTID" is NOT null) 
               THEN      
               CASE WHEN (inTab."/BIC/CRCBECFVN" >1 ) THEN
                    (SELECT "/BIC/K5SCDCEC" FROM "/BIC/AZD_TVOG2"  WHERE "/BIC/AZD_TVOG2"."/BIC/CRCBECFVN" = 1) AS "/BIC/K5SCDCEC",
                 ELSE 
                   "/BIC/K5SCDCEC" AS "/BIC/K5SCDCEC"
                   END
				   END

after writing this logic gives me an error on the AS, can you tell me the reason?

[Jrg_Brandeis]

Hi Danilo,

the code should look somehow like this:

METHOD GLOBAL_END BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY using /BIC/AZD_TVOG2.
-- *** Begin of routine - insert your code only below this line ***

-- Note the _M class are not considered for DTP execution.
-- AMDP Breakpoints must be set in the _A class instead.

-- sorry for the stupid names of the table variables, but I don't know the 
-- business background to name them properly. Of course variables should 
-- never be numbered but meaningfull named. @see: Clean Code (Robert C. Martin)
lt_tmp1 = SELECT "/BIC/C55CONTID",
                MAX("/BIC/CRCBECFVN") as max_crcbecfvn
           FROM "/BIC/AZD_TVOG2" 
           group by "/BIC/C55CONTID" ;
       
lt_tmp2 = SELECT "/BIC/C55CONTID",
                     "/BIC/K5SCDCEC"
           FROM "/BIC/AZD_TVOG2" 
           where "/BIC/CRCBECFVN" = 1  ; -- is it guaranteed that there will 
                                         -- be always exaclty 1 record per C55CONTID?   
                                         -- 0 or N records could cause trouble and should be handled                                         

outTab = SELECT
         it."/BIC/C11NODENO" AS "/BIC/C11NODENO" ,
         it."/BIC/C55ACCRCT" AS "/BIC/C55ACCRCT" ,
         it."/BIC/C55ACCSY" AS "/BIC/C55ACCSY" ,
         it."/BIC/C55CMETH" AS "/BIC/C55CMETH" ,
         it."/BIC/C55CONTCT" AS "/BIC/C55CONTCT" ,
         it."/BIC/C55CONTID" AS "/BIC/C55CONTID" ,
         it."/BIC/C55IOIND" AS "/BIC/C55IOIND" ,
         it."/BIC/C55PRCSCT" AS "/BIC/C55PRCSCT" ,
         it."/BIC/C55SLALC" AS "/BIC/C55SLALC" ,
         it."/BIC/CIDPSEID" AS "/BIC/CIDPSEID" ,
         it."/BIC/CR0RSKYDT" AS "/BIC/CR0RSKYDT" ,
         LPAD(DENSE_RANK ( ) OVER (PARTITION BY it."/BIC/C55CONTID" ORDER BY it."/BIC/CR0RSKYDT") --May be ROW_NUMBER is the better choice to avoid duplicate numbers
       + COALESCE((tmp1.max_crcbecfvn),0),10,0)
         AS "/BIC/CRCBECFVN" ,
         it."/BIC/CRCPAYCAT" AS "/BIC/CRCPAYCAT" ,
         it."/BIC/C55CLCLTR" AS "/BIC/C55CLCLTR" ,
         it."/BIC/C55CURPOS" AS "/BIC/C55CURPOS" ,
         it.RECORDMODE ,
         it."/BIC/RDLAREA" AS "/BIC/RDLAREA" ,
         it."/BIC/RDLVIEW" AS "/BIC/RDLVIEW" ,
         it."/BIC/C55SECNNO" AS "/BIC/C55SECNNO" ,
         it."/BIC/C55SRCSYS" AS "/BIC/C55SRCSYS" ,
         it."/BIC/K5SCDCIC" AS "/BIC/K5SCDCIC",
         it."/BIC/K5SCDCIP" AS "/BIC/K5SCDCIP",
         case when it."/BIC/CRCBECFVN" > 1     --assuming that you want the value that is given in the intab and not the one we calculated above.... Otherwise we should split this SELECT in multiple steps. 
                  then tmp2."/BIC/K5SCDCEC"
          else it."/BIC/K5SCDCEC" end as "/BIC/K5SCDCEC"

         it."/BIC/K5SCDCEP" AS "/BIC/K5SCDCEP" ,
         it."/BIC/K5SCDIRC" AS "/BIC/K5SCDIRC" ,
         it."/BIC/K5SCDIRP" AS "/BIC/K5SCDIRP" ,
         it."/BIC/K5SCDIOC" AS "/BIC/K5SCDIOC" ,
         it."/BIC/K5SCDIOP" AS "/BIC/K5SCDIOP" ,
         it."/BIC/K5SCDT2C" AS "/BIC/K5SCDT2C" ,
         it."/BIC/K5SCDT2P" AS "/BIC/K5SCDT2P" ,
         it."/BIC/K5TMGRCC" AS "/BIC/K5TMGRCC" ,
         it."/BIC/K5TMGRCP" AS "/BIC/K5TMGRCP" ,
         it."/BIC/POS_CURR" AS "/BIC/POS_CURR" ,
         it."/BIC/TRAN_CURR" AS "/BIC/TRAN_CURR" ,
         it."/BIC/CRPTRDAT" AS "/BIC/CRPTRDAT" ,
         it."/BIC/C55BEFCH" AS "/BIC/C55BEFCH" ,
         RECORD,
         SQL__PROCEDURE__SOURCE__RECORD

         FROM :inTab as it

         left outer join :lt_tmp1 as tmp1
         on it."/BIC/C55CONTID" = tmp1."/BIC/C55CONTID"
         
         left outer join :lt_tmp2 as tmp2
         on it."/BIC/C55CONTID" = tmp2."/BIC/C55CONTID";

-- *** End of routine - insert your code only before this line ***
ENDMETHOD.

Please note the comments. And you may have to make some adjustments, since I don't know the exact table structures and possible data constellations.

Regards,

Jörg