help using a 'BETWEEN' join on tables from different datasources

[#1]

Hi all,

I have a query on my PC that uses the BETWEEN command in ORACLE

e.g.

transdate between period.start_date and period.end_date

I need to take the period table in our Oracle database, and join it in the same way with a table in SQL Server

so that I get the period name that each financial transaction belongs to.

Is there a way of doing this in QlikView script?

thanks in advance,

Matt

[#2]

maybe, you can try with "link server" oracle to sql serve

[#3]

Either you keep using the Between function in the SQL statement in your script.

Or you can have a look at interval function in the help file

Rgds,

Sébastien

[Miguel_Angel_Baeyens · Accepted solution]

Hello Matt,

If I understood your question right, this example may help

DatesTable:
LOAD * INLINE [
Start, End, Name
01/01/2010, 31/01/2010, First
01/02/2010, 28/02/2010, Second
]; 
FactsTable:
LOAD * INLINE [
ID, Date, Amount
A, 03/02/2010, 100
B, 16/01/2010, 200
]; 
FactsDates:
INTERVALMATCH (Date) LEFT JOIN LOAD Start, 
     End
RESIDENT DatesTable;
LEFT JOIN (FactsTable) LOAD * 
RESIDENT DatesTable; 
DROP TABLE DatesTable;

Is that correct?

[#5]

that is absolutely correct!

thank you very much.

Strangely enough I have not used the interval match function before..

by the look of it, its something that I may want to use a lot going forward

Matt

[Anonymous]

Hi Miguel,

Thanks for you nice explanation...

[Anonymous]

This was a very simple and clear example on Interval Match. Just awesome. Thanks Miguel. I still have doubts on the resident loads and how it solved this particular problem using Interval match. I was able to use it to get the right answer as in the image above but have some questions. When you drop the dates table, I am still able to use the field "name" because I brought that field from that table to facts table using a left join, correct..?

1)

1. INTERVALMATCH (Date) LEFT JOIN LOAD Start,  
2.      End 
3. RESIDENT DatesTable;  

Here, i am doing a left join on which table?

2)

1. LEFT JOIN (FactsTable) LOAD *  
2. RESIDENT DatesTable;

Here, I am bringing fields from dates table to Factstable, correct?

3) and Finally why did I use Resident Loads twice..?

I am finding such meaningful answers in this community. Thanks to everyone involved.

Happy Friday :

[Anonymous]

No Reply??????