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 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