Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Re: Need urgent help in hive query


Copy link to this message
-
Re: Need urgent help in hive query
Robin Morris 2013-06-28, 18:33
Split up the query – put results of the subquery into a table, run the final query on that, then drop the temporary table.

Robin

From: dyuti a <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Reply-To: "[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Date: Friday, June 28, 2013 10:54 AM
To: "[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>, Michael Malak <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Subject: Re: Fwd: Need urgent help in hive query

Hi Michael,
Thanks for your help, is there any other possible options apart from this.
On Fri, Jun 28, 2013 at 10:33 PM, Michael Malak <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
Just copy and paste the whole long expressions to their second occurrences.

________________________________
From: dyuti a <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
Sent: Friday, June 28, 2013 10:58 AM
Subject: Fwd: Need urgent help in hive query

Hi Experts,
I'm trying with the below SQL query in Hive, which does not support column alias access in subsequent columns as shown below in the query. Is there any other way to rewrite the same without using alias? any of your help are really appreciated.

INSERT INTO CAUD
   (
     pst_cc
  pst_no
  pst_ber
  pst_tkn
  pst_dtm
  pst_nbr
  pst_cde
  pst_dte
)
SELECT der.cc<http://der.cc/>
  der.no<http://der.no/>
  der.ber
  der.tkn
  der.dtm
  der.nbr
  der.cde
  der.dte

FROM (SELECT udp.cc<http://udp.cc/>
   udp.no<http://udp.no/>
   udp.ber
   udp.tkn
   ,CASE WHEN udp.SYSTEM_DTE>16000000 AND udp.SYSTEM_DTE<100000000 THEN udp.SYSTEM_DTE
            WHEN udp.DTE_OUT>16000000 AND udp.DTE_OUT<100000000 THEN udp.DTE_OUT
            WHEN udp.DTE_IN>16000000 AND udp.DTE_IN<100000000 THEN udp.DTE_IN
            ELSE '1231'
            END  AS DTE_OUT
   ,CASE WHEN udp.TME_OUT <> 0 THEN udp.TME_OUT
            WHEN udp.TME_IN <> 0 THEN udp.TME_IN
            ELSE 0
            END AS TME_OUT
            ,TRIM(CAST(TME_OUT  AS CHAR(6))) AS TME_OUT1
    ,CAST(CAST(SUBSTR(TRIM(DTE_OUT),1,8)  AS CHAR(8)) AS DATE FORMAT 'yyyymmdd')  AS DTE_OUT_O
    ,CASE WHEN TME_OUT>99999 THEN CAST(TME_OUT1 AS CHAR(6))
            WHEN TME_OUT>9999 AND TME_OUT<=99999 THEN CAST('0'||TME_OUT1  AS CHAR(6))
            WHEN TME_OUT>999 AND TME_OUT<=9999 THEN CAST('00'||TME_OUT1 AS CHAR(6))
            WHEN TME_OUT>99 AND TME_OUT<=999 THEN CAST('000'||TME_OUT1 AS CHAR(6))
            WHEN TME_OUT>9 AND TME_OUT<=99 THEN CAST('0000'||TME_OUT1 AS CHAR(6))
            WHEN TME_OUT>0 AND TME_OUT<=9 THEN CAST('00000'||TME_OUT1 AS CHAR(6))
            WHEN TME_OUT=0 THEN '000000'
            END AS TME_OUT2
            ,SUBSTR(TRIM(TME_OUT2),1,2)||':'||SUBSTR(TRIM(TME_OUT2),3,2)||':'||SUBSTR(TRIM(TME_OUT2),5,2) AS
            TME_OUT_O
            , CAST( DTE_OUT_O||' '||TME_OUT_O AS TIMESTAMP FORMAT 'YYYYMMDD:HH: MI:SS')     AS DTM
            ,udp.nbr  AS  nbr

   FROM   STS_GNCAUDP   udp

            INNER JOIN LOAD_LOG LZ_LL  ON udp.LOG_KEY=LZ_LL.LOG_KEY
            INNER JOIN ESA_LOAD_LOG ESA_LL  ON LZ_LL.ESA_LOAD_LOG_KEY=ESA_LL.LOG_KEY
            AND ESA_LL.PBLSH_IND='$PBLSH_IND'
            AND ESA_LL.LOAD_END_DTM ='$HIGH_DATE_TIME'
            AND ESA_LL.SOR_CD= '$CLM_SOR_CD'
            AND ESA_LL.SUBJ_AREA_NM= '$SUBJ_AREA_NM'
            AND ESA_LL.WORK_FLOW_NM= '$WORK_FLOW_NM'
            QUALIFY ROW_NUMBER()  OVER  (PARTITION BY udp.cc,udp.pst_no,
            udp.cde,udp.nbr,udp.dte,udp.LOG_KEY
            ORDER BY DTM DESC)=1) AS der
;

Thanks in advance!
Dti