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?

[Vitaliy-R]

If I got it right, you should use AS in a different place of the code. But I have not checked it on my instance.

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

Regards.

[mgregur]

Hi,

like everyone else said, you have "AS" in the wrong place, but also I see two CASE WHEN but only one ELSE statements, you might want to check that part.

BR,

Matija

[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

[Jrg_Brandeis]

Hi Danilo,

the keyword AS has no place in a CASE expression. When you want to calculate a column with this expression, the AS should be placed after the outer END. If you show us the whole statement, then we can help you better.

B.t.w: the red marking and the formatting makes it difficult to read. Readable formatted questions have a better chance to get answered. You can use the CODE feature when creating a question:

Regards,

Jörg

[nathan23]

Hi i update with code

i implement this

FOR EACH C55CONTID, IF CRCBECFVN >1, THEN RETRIEVE THE VALUES FROM K5SCDCEC HAVING CRCBECFVN =1

can you fix my code ?

[Jrg_Brandeis]

Hi Danilo,

thank you for the code update. You should give us a little more information about the context. Are we talking about HANA SQL, SQL Anywhere or MS SQL Server? You selected all tags. To what database should the expected answer fit? When its an AMDP: Show us the whole code of the AMDP Class.

From your last comment I'll guess:

You should do a LEFT OUTER JOIN of INTAB with /BIC/AZD_TVOG2 and then do a CASE in the fieldlist to select either the value from the INTAB or from the other table.

Regards,

Jörg

[nathan23]

is inside a select and in practice I have to value the field / BA1 / K5SCDCEC, if this condition is verified "FOR EACH C55CONTID, IF CRCBECFVN> 1, THEN RETRIEVE THE VALUES FROM K5SCDCEC HAVING CRCBECFVN = 1".

I have to replace "/ BIC / K5SCDCEC" AS "/ BIC / K5SCDCEC", with the above formula, I hope I was clear

[nathan23]

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.

outTab = SELECT
         "/BIC/C11NODENO" AS "/BIC/C11NODENO" ,
         "/BIC/C55ACCRCT" AS "/BIC/C55ACCRCT" ,
         "/BIC/C55ACCSY" AS "/BIC/C55ACCSY" ,
         "/BIC/C55CMETH" AS "/BIC/C55CMETH" ,
         "/BIC/C55CONTCT" AS "/BIC/C55CONTCT" ,
         "/BIC/C55CONTID" AS "/BIC/C55CONTID" ,
         "/BIC/C55IOIND" AS "/BIC/C55IOIND" ,
         "/BIC/C55PRCSCT" AS "/BIC/C55PRCSCT" ,
         "/BIC/C55SLALC" AS "/BIC/C55SLALC" ,
         "/BIC/CIDPSEID" AS "/BIC/CIDPSEID" ,
         "/BIC/CR0RSKYDT" AS "/BIC/CR0RSKYDT" ,
         LPAD(DENSE_RANK ( ) OVER (PARTITION BY :inTab."/BIC/C55CONTID" ORDER BY :inTab."/BIC/CR0RSKYDT")
       + COALESCE((SELECT MAX(T1."/BIC/CRCBECFVN")FROM "/BIC/AZD_TVOG2" T1 WHERE :inTab."/BIC/C55CONTID" = T1."/BIC/C55CONTID"),0),10,0)
         AS "/BIC/CRCBECFVN" ,
         "/BIC/CRCPAYCAT" AS "/BIC/CRCPAYCAT" ,
         "/BIC/C55CLCLTR" AS "/BIC/C55CLCLTR" ,
         "/BIC/C55CURPOS" AS "/BIC/C55CURPOS" ,
         RECORDMODE ,
         "/BIC/RDLAREA" AS "/BIC/RDLAREA" ,
         "/BIC/RDLVIEW" AS "/BIC/RDLVIEW" ,
         "/BIC/C55SECNNO" AS "/BIC/C55SECNNO" ,
         "/BIC/C55SRCSYS" AS "/BIC/C55SRCSYS" ,
         "/BIC/K5SCDCIC" AS "/BIC/K5SCDCIC",
         "/BIC/K5SCDCIP" AS "/BIC/K5SCDCIP",
         "/BIC/K5SCDCEC" AS "/BIC/K5SCDCEC" , // i change this with formula FOR EACH C55CONTID,
                                 IF CRCBECFVN >1, THEN RETRIEVE THE VALUES FROM K5SCDCEC HAVING CRCBECFVN =1

         "/BIC/K5SCDCEP" AS "/BIC/K5SCDCEP" ,
         "/BIC/K5SCDIRC" AS "/BIC/K5SCDIRC" ,
         "/BIC/K5SCDIRP" AS "/BIC/K5SCDIRP" ,
         "/BIC/K5SCDIOC" AS "/BIC/K5SCDIOC" ,
         "/BIC/K5SCDIOP" AS "/BIC/K5SCDIOP" ,
         "/BIC/K5SCDT2C" AS "/BIC/K5SCDT2C" ,
         "/BIC/K5SCDT2P" AS "/BIC/K5SCDT2P" ,
         "/BIC/K5TMGRCC" AS "/BIC/K5TMGRCC" ,
         "/BIC/K5TMGRCP" AS "/BIC/K5TMGRCP" ,
         "/BIC/POS_CURR" AS "/BIC/POS_CURR" ,
         "/BIC/TRAN_CURR" AS "/BIC/TRAN_CURR" ,
         "/BIC/CRPTRDAT" AS "/BIC/CRPTRDAT" ,
         "/BIC/C55BEFCH" AS "/BIC/C55BEFCH" ,
         RECORD,
         SQL__PROCEDURE__SOURCE__RECORD
         FROM :inTab;

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

<br>

[Jrg_Brandeis]

Hi Danilo,

has any of the answers solved your problem? Than it should be marked as accepted, right?

Regards,

Jörg