Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (athena.apache.org: domain of wouter@spotify.com designates
 209.85.215.48 as permitted sender)
Received-SPF: pass (google.com: domain of wouter@spotify.com designates
 10.112.27.165 as permitted sender) client-ip=10.112.27.165;
Date: Mon, 5 Mar 2012 12:58:37 +0100
From: Wouter de Bie <wouter@spotify.com>
To: user@hive.apache.org
Cc: dev@hive.apache.org
Message-ID: <B2E0B2D4888A4CC79670E91FECD725FF@spotify.com>
In-Reply-To: 
 <CADtHtMx6NEkCr6mB0U_8Jpt_HH-UFVd3qsReWFHMMJX3qCgiwQ@mail.gmail.com>
References: 
 <CADtHtMx6NEkCr6mB0U_8Jpt_HH-UFVd3qsReWFHMMJX3qCgiwQ@mail.gmail.com>
Subject: Re: Simple Hive Query
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="4f54aa6d_4d32ab86_db"

--4f54aa6d_4d32ab86_db
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

Your table specifies 3 ints, however, some of your rows contain strings. If you specify you table as strings and then later parse the fields to an int (by doing some replacements and casting it to an int), it should work. 

// Wouter


On Monday, March 5, 2012 at 12:55 PM, hadoop hive wrote:

> Hi Folks ,
> 
> i m facing a problem like i have a table a(k1 int ,k2 int ,k3 int   );
> 
> and data present is like 
> 
> *9,*1,*3
> 3,4,5
> 12,32,54
> *9,12,32
> 12,*9,21
> 
> i need row which doesn't have *9 in any fields.
> 
> i wrote a query
> 
> 1: select k1,k2,k3 from a where (k1<>'\*9' or k2<>'\*9' or k3<>'\*9'); 
> 
> 2: select k1,k2,k3 from a where (k1<>'*9' or k2<>'*9' or k3<>'*9');
> 
> 3: select k1,k2,k3 from a where (k1<>"*9" or k2<>"*9" or k3<>"*9"); 
> 
> any of these case is not working.
> 
> i need the result like
> 
> *9,*1,*3
> 3,4,5
> 12,32,54
> *9,12,32
> 12,*9,21
> 
> 
> Thanks 
> Vikas Srivastava
> 
> 


--4f54aa6d_4d32ab86_db
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline


                <div style=3D=22font-family: Helvetica; font-size: 13px; =
=22>Hi,<div><br></div><div>Your table specifies 3 ints, however, some of =
your rows contain strings. If you specify you table as strings and then l=
ater parse the fields to an int (by doing some replacements and casting i=
t to an int), it should work.</div></div>
                <div><div><br></div><span style=3D=22font-size: 13px; bor=
der-collapse: collapse; =22><div>// Wouter</div></span><div><br></div></d=
iv>
                =20
                <p style=3D=22color: =23A0A0A8;=22>On Monday, March 5, 20=
12 at 12:55 PM, hadoop hive wrote:</p>
                <blockquote type=3D=22cite=22 style=3D=22border-left-styl=
e:solid;border-width:1px;margin-left:0px;padding-left:10px;=22>
                    <span><div><div>Hi =46olks ,<div><br></div><div>i m f=
acing a problem like i have a table a(k1 int ,k2 int ,k3 int &nbsp; );</d=
iv><div><br></div><div>and data present is like&nbsp;</div><div><br></div=
><div>*9,*1,*3</div><div>3,4,5</div><div>12,32,54</div>
<div>*9,12,32</div><div>12,*9,21</div><div><br></div><div>i need row whic=
h doesn't have *9 in any fields.</div><div><br></div><div>i wrote a query=
</div><div><br></div><div>1: select k1,k2,k3 from a where (k1&lt;&gt;'=5C=
*9' or&nbsp;k2&lt;&gt;'=5C*9' or&nbsp;k3&lt;&gt;'=5C*9');</div>
<div><br></div><div>2: select k1,k2,k3 from a where&nbsp;(k1&lt;&gt;'*9' =
or&nbsp;k2&lt;&gt;'*9' or&nbsp;k3&lt;&gt;'*9');</div><div><br></div><div>=
3:&nbsp;select k1,k2,k3 from a where&nbsp;(k1&lt;&gt;=22*9=22 or&nbsp;k2&=
lt;&gt;=22*9=22 or&nbsp;k3&lt;&gt;=22*9=22);</div>
<div><br></div><div>any of these case is not working.</div><div><br></div=
><div>i need the result like</div><div><br></div><div><div>*9,*1,*3</div>=
<div><span style=3D=22background-color:rgb(255,204,51)=22>3,4,5</span></d=
iv><div>
<span style=3D=22background-color:rgb(255,204,51)=22>12,32,54</span></div=
><div>*9,12,32</div><div>12,*9,21</div><br></div><div><br></div><div>Than=
ks&nbsp;</div><div>Vikas Srivastava</div>
</div></div></span>
                =20
                =20
                =20
                =20
                </blockquote>
                =20
                <div>
                    <br>
                </div>
            
--4f54aa6d_4d32ab86_db--