SQL query with restrictions on date not fetching data

Thu, 09 Jul 2015 19:45:01 GMT

I'm working on compiling Nielsen ratings for our project and I'm running into a weird issue. I have a load statement that looks something like this:

NIELSEN_DATA:

load

FRANCHISE_AIRING_ID,

DEMO_TYPE_CD,

NIELSEN_RATING;

sql

select fa.franchise_airing_id FRANCHISE_AIRING_ID,

d.demo_type_cd DEMO_TYPE_CD,

GetNielsenRating(fa.schedule_id, fa.franchise_airing_id, ta.title_id, d.demo_type_id) NIELSEN_RATING,

ta.title_id,

fa.schedule_id

from t_fran_air fa

join t_title_air ta on ta.franchise_airing_id=fa.franchise_airing_id

cross join t_demo_type d

where ta.title_id=331223;

That works without an issue. It gets all ratings for all demographics for one particular movie and stuffs it into a qvd file. It fetched 18000 rows worth of data. But when I try and restrict airings to a certain date like below:

NIELSEN_DATA:

load

FRANCHISE_AIRING_ID,

DEMO_TYPE_CD,

NIELSEN_RATING;

sql

select fa.franchise_airing_id FRANCHISE_AIRING_ID,

d.demo_type_cd DEMO_TYPE_CD,

GetNielsenRating(fa.schedule_id, fa.franchise_airing_id, ta.title_id, d.demo_type_id) NIELSEN_RATING,

ta.title_id,

fa.schedule_id

from t_fran_air fa

join t_title_air ta on ta.franchise_airing_id=fa.franchise_airing_id

cross join t_demo_type d

where ta.title_id=331223

and fa.schedule_air_date>='01-JAN-10' and fa.schedule_air_date<='01-JAN-15';

Qlikview won't fetch any rows once I add a date restriction.

I've run the SQL statement against our Oracle 11g database using SQL Developer and it fetches me all 3280 rows of data that falls within that date range. Why does Qlikview's script choke on the date restriction?

Re: SQL query with restrictions on date not fetching data

petter — Thu, 09 Jul 2015 19:54:33 GMT

It might be an ODBC issue that SQL Developer is not exposed to when it comes to date conversion from strings...

Try wrapping your date into something like this:

TO_DATE('01/JAN/2010','dd/mon/yyyy')

Re: SQL query with restrictions on date not fetching data

maxgro — Thu, 09 Jul 2015 19:55:30 GMT

try with

fa.schedule_air_date >= to_date('20100101', 'YYYYMMDD') and ............

topic SQL query with restrictions on date not fetching data in QlikView

SQL query with restrictions on date not fetching data

Re: SQL query with restrictions on date not fetching data

Re: SQL query with restrictions on date not fetching data