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 <michaelma...@yahoo.com>wrote:
> Just copy and paste the whole long expressions to their second occurrences.
>
> ------------------------------
> *From:* dyuti a <hadoop.hiv...@gmail.com>
> *To:* user@hive.apache.org
> *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
> der.no
> der.ber
> der.tkn
> der.dtm
> der.nbr
> der.cde
> der.dte
>
> FROM (SELECT udp.cc
> 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
>
>
>
>
>
