left join error syntax related

[#1]

hi I have a query like below:

Master:

SQL SELECT

     a,

     n,

     c

from sometable

where (a='s' or a='p')   and year(n) >= year(CURRENT) -2;

Left join (Master)

......

Problem I am facing is that left join does not work it says table 'Master' not found. What I have realized is that error is comming from where statement after 'and' part.

If i cake out year(n) >= year(CURRENT) -2  then the left join works fine.

What could cause this?, how can i rewrite that statement so it wont thru error.

[Nicole-Smith]

What is CURRENT?  Is it a field in the table?

[#3]

Hi,

You ar trying to combine two kinds of script, the first where you tip where function belong to SQL and the second is Qlikview Script (left join (Master)). You can try something like this:

Master_temp:

SQL SELECT

     a,

     n,

     c

from sometable

where (a='s' or a='p')   and year(n) >= year(CURRENT) -2;

no concatenate

Master:

Load * resident Master_temp;

Left join (Master)

Table2:

Load * from mitable2.qvd;

Drop Table Master_temp;

Best regards..

[its_anandrjs]

Try with this way store value after >= in any variable then try

Let vCurrVal = year(CURRENT) - 2;

Master:

SQL SELECT

     a,

     n,

     c

from sometable

where (a='s' or a='p')   and year(n) >= $(vCurrVal);

Left join (Master)

...

...

[Joseph_Musekura]

Hi,

If you want to deal in your conditions with today’s year  –  2, you could play in  your condition with this:

1. Year(YearStart(AddMonths(Today(),-24)))  will return 2012
2. Year(YearStart(AddMonths(Today(),0)))  will return 2014
3. YearStart(AddMonths(Today(),-24))  will return  01-01 -2012
4. YearStart(AddMonths(Today(),0))) will return 01-01-2014

regards,

[#6]

Anand your solution did not work.

Puzzling part is thing recently change exact code worked before. Not sure how.

[#7]

CURRENT condition is not comming from qlikview it is comming from SQL.

[#8]

It seems that you need named a second table for left join...

Master:

SQL SELECT

     a,

     n,

     c

from sometable

where (a='s' or a='p')   and year(n) >= year(CURRENT) -2;

Table 2:

Left join (Master)

......

I hope to help you

[#9]

Nancy creating new table did not work:

I have changed the sql part to

and year(n) >= year(GetDate())-2;

[#10]

OK, thanks for sharing