Hi there, i have a string that has '239, 236, 232, 934' (not always in that order) and want to insert into another table if 239 is in the string.

INSERT OVERWRITE TABLE video_plays_for_sept

SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region from omniture where regexp_extract(event_list, '\d+') = "239";

is that I have at the minute but always returns 0 Rows loaded to video_plays_for_sept
Many thanks

Dave Houston
[EMAIL PROTECTED]

Hi Dave
 
       If I get
your requirement correct, you need to load data into video_plays_for_sept  table FROM omniture table only if omniture.event_list contain the string 239.
 
Try the following query, it should work fine.
 
INSERT OVERWRITE TABLE video_plays_for_sept
SELECT concat(visid_high, visid_low), geo_city,
geo_country, geo_region FROM omniture WHERE event_list LIKE  ‘%239%’;
 
Hope it helps!..
 
Regards,
Bejoy.K.S
________________________________
 From: Dave Houston <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: Wednesday, January 4, 2012 6:41 PM
Subject: Insert based on whether string contains
 

Hi there, i have a string that has '239, 236, 232, 934' (not always in that order) and want to insert into another table if 239 is in the string.

INSERT OVERWRITE TABLE video_plays_for_sept

SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region from omniture where regexp_extract(event_list, '\d+') = "239";

is that I have at the minute but always returns 0 Rows loaded to video_plays_for_sept
Many thanks

Dave Houston
[EMAIL PROTECTED]

The find_in_set() UDF is a safer choice for doing a search for that value, as %239% could also match 2390, which has a different meaning in Omniture logs.

On Jan 4, 2012, at 8:46 AM, "Bejoy Ks" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:

Hi Dave

       If I get your requirement correct, you need to load data into video_plays_for_sept  table FROM omniture table only if omniture.event_list contain the string 239.

Try the following query, it should work fine.

INSERT OVERWRITE TABLE video_plays_for_sept
SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region FROM omniture WHERE event_list LIKE  ‘%239%’;

Hope it helps!..

Regards,
Bejoy.K.S

________________________________
From: Dave Houston <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
Sent: Wednesday, January 4, 2012 6:41 PM
Subject: Insert based on whether string contains

Hi there, i have a string that has '239, 236, 232, 934' (not always in that order) and want to insert into another table if 239 is in the string.

INSERT OVERWRITE TABLE video_plays_for_sept

SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region from omniture where regexp_extract(event_list, '\d+') = "239";

is that I have at the minute but always returns 0 Rows loaded to video_plays_for_sept
Many thanks

Dave Houston
[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>

I agree with Matt on that aspect. The solution proposed by me was purely based on the sample data provided where there were  3 digit comma separated values. If there are chances of 4 digit values as well in event_list you may need to revisit the solution.

Regards
Bejoy K S

-----Original Message-----
From: "Tucker, Matt" <[EMAIL PROTECTED]>
Date: Wed, 4 Jan 2012 08:56:44
To: [EMAIL PROTECTED]<[EMAIL PROTECTED]>; Bejoy Ks<[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Subject: Re: Insert based on whether string contains

The find_in_set() UDF is a safer choice for doing a search for that value, as %239% could also match 2390, which has a different meaning in Omniture logs.

On Jan 4, 2012, at 8:46 AM, "Bejoy Ks" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:

Hi Dave

       If I get your requirement correct, you need to load data into video_plays_for_sept  table FROM omniture table only if omniture.event_list contain the string 239.

Try the following query, it should work fine.

INSERT OVERWRITE TABLE video_plays_for_sept
SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region FROM omniture WHERE event_list LIKE  ‘%239%’;

Hope it helps!..

Regards,
Bejoy.K.S

________________________________
From: Dave Houston <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
Sent: Wednesday, January 4, 2012 6:41 PM
Subject: Insert based on whether string contains

Hi there, i have a string that has '239, 236, 232, 934' (not always in that order) and want to insert into another table if 239 is in the string.

INSERT OVERWRITE TABLE video_plays_for_sept

SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region from omniture where regexp_extract(event_list, '\d+') = "239";

is that I have at the minute but always returns 0 Rows loaded to video_plays_for_sept
Many thanks

Dave Houston
[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>