Hi Sarnath, Problem is with line1, not with inner query (case statement) and Col4 is not single value, it will vary. By the way, single case statement and multiple when-then will give only one column. But according to my requirement I want more than 200 columns with all combination.
Thanks, Parkavi. -----Original Message----- From: Sarnath [mailto:[email protected]] Sent: Friday, May 22, 2015 10:51 AM To: [email protected] Subject: Re: Operand stack overflow error This is not a solution. By why don't you use a single case stmt and multiple when-then clause and 1 end clause. At least hive supports it. Not sure if ANSI SQL does. You may want to check. On May 22, 2015 10:18 AM, "Parkavi Nandagopal" <[email protected]> wrote: > Hi, > > > > When run the below query for pivoting, Operand stack overflow error is > coming. > > *ERROR:* > > Caused by: org.codehaus.janino.JaninoRuntimeException: Baz$9.apply(): > Operand stack overflow at offset 1292 > > at > org.codehaus.janino.CodeContext.flowAnalysis(CodeContext.java:490) > > at > org.codehaus.janino.CodeContext.flowAnalysis(CodeContext.java:299) > > > > *QUERY:* > > Select col1,col2,col3,max(ncol1),max(ncol2),……..max(ncol127) //line1 > > From > > ( select col1,col2,col3, > > Case when col4= ‘abc’ and col5 = ‘123’ then max(val) else NULL end as > ncol1, > > … > > … > > Case when col4=’abc’ and col5 = ‘890’ then max(val) else NULL end as > ncol127 > > Case when col4=’abc’ and col5 = ‘891’ then max(val) else NULL end as > ncol128 > > Case when col4=’abc’ and col5 = ‘892’ then max(val) else NULL end as > ncol129 > > Case when col4=’abc’ and col5 = ‘893’ then max(val) else NULL end as > ncol130 > > From facttable > > Group by col1,col2,col3,col4,col5 ) as tab1 > > Group by col1,col2,col3 > > > > > > No error upto ncol126, but if I give more than 126 in line1 then I’m > getting this stack overflow error. > > > > Then I found, in Janino->codecontext.java, stack size is hardcoded as > MAX_STACK_SIZE = 254. > > > > After I increased the stack size to 500, I’m able to get the output > upto ncol127(in line1). > > But if I increase the col after that i.e. ncol128, it is shouting > another error. > > > > *ERROR:* > > java.lang.VerifyError: (class: Baz$9, method: apply signature: > ()Ljava/lang/Object;) Signature > (Ljava/math/BigDecimal;ZLjava/math/BigDecimal;ZLjava/math/BigDecimal; > > > > Please find the log in the attachment. > > > > Please help me to solve this issue. > > > > > > Thanks, > > Parkavi. > > > > > > > > ::DISCLAIMER:: > > ---------------------------------------------------------------------- > ---------------------------------------------------------------------- > -------- > > The contents of this e-mail and any attachment(s) are confidential and > intended for the named recipient(s) only. > E-mail transmission is not guaranteed to be secure or error-free as > information could be intercepted, corrupted, lost, destroyed, arrive > late or incomplete, or may contain viruses in transmission. The e mail > and its contents (with or without referred errors) shall therefore not > attach any liability on the originator or HCL or its affiliates. > Views or opinions, if any, presented in this email are solely those of > the author and may not necessarily reflect the views or opinions of > HCL or its affiliates. Any form of reproduction, dissemination, > copying, disclosure, modification, distribution and / or publication > of this message without the prior written consent of authorized > representative of HCL is strictly prohibited. If you have received > this email in error please delete it and notify the sender > immediately. > Before opening any email and/or attachments, please check them for > viruses and other defects. > > > ---------------------------------------------------------------------- > ------------------------------------------------------------------------------ >
