Desktop productivity for business analysts and programmers

Join on Dates, One table Oracle, The other Teradata

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

Join on Dates, One table Oracle, The other Teradata

Hi,

I'm not getting any matches when I try to join 2 tables together on a date field when using SAS EG 4.3.

One table was created in an Oracle DB & the other in Teradata SQL Assistant 13.11.

Also, the Oracle date field is in a Date/Time format ('DDMONYYYY:HH:MMSmiley FrustratedS') & the Teradata date field is in date format ('DDMONYYYY')

I've tried changing the format of the Oracle date/time format to just date 'DDMONYYYY' in order to match the Teradata table but I still get no matches.

I've checked both tables & there are matching values in both in the 'DDMONYYYY' format.

Can anyone advise what i should try to get these values to match?

Thanks 


Accepted Solutions
Solution
‎06-20-2013 08:53 PM
Grand Advisor
Posts: 17,320

Re: Join on Dates, One table Oracle, The other Teradata

Convert into true SAS dates, by creating new calculated variables in EG and join on the calculated variables.

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Join on Dates, One table Oracle, The other Teradata

Use datepart(your oracle datetime variable) to match Teradata.

Haikuo

Frequent Contributor
Posts: 80

Re: Join on Dates, One table Oracle, The other Teradata

Thanks Hai.kuo, but I don't know what you mean.

Can you elaborate with maybe an example?

Thanks

Respected Advisor
Posts: 3,124

Re: Join on Dates, One table Oracle, The other Teradata

Ok, the date variable straight from Oracle will be taken as date time format by SAS. So you need to truncate it into date before you can join with other date. if you are writing BASE code or using a Store Process in EG, then you can apply datepart() function to your Oracle date variable in your code before the join. However, You could be using Query builder, in that case, you need to "add" a computed variable using datepart(), then use this new variable to do the join.

HTH,

Haikuo

Frequent Contributor
Posts: 80

Re: Join on Dates, One table Oracle, The other Teradata

Thanks again,

You're right, I am using the query builder & I've used the datepart function as datepart(Fieldname) which I tested on one value that I know matches.

Only thing is that it has changed the date to 19521 which I understand to be the number of days since 1st Jan1960. This isn't going to match a date format in my other table though ??

What do I do from here?

Grand Advisor
Posts: 17,320

Re: Join on Dates, One table Oracle, The other Teradata

If its a date it may, if it's a char you can do a similar conversion, using datepart, or input/put to create a sas date and join.

Basically you need to make the two dates look the same, so you create two new variables that have the same type. I'm not familiar with Teradata so I can't explain how to do it for that type of data.

Frequent Contributor
Posts: 80

Re: Join on Dates, One table Oracle, The other Teradata

Thanks Reeza,

I've already tried changing the values to match as I stated at the start.

"I've tried changing the format of the Oracle date/time format to just date 'DDMONYYYY' in order to match the Teradata table but I still get no matches."

I tried applying a Format to both fields such as 'DDMMYYN8.' but still no matches even though the values look identical on the tables??

Grand Advisor
Posts: 17,320

Re: Join on Dates, One table Oracle, The other Teradata

I don't think SQL merges on formatted values, but on the underlying values.

Frequent Contributor
Posts: 80

Re: Join on Dates, One table Oracle, The other Teradata

So, if that's the case, what do I need to do to fix this?

Solution
‎06-20-2013 08:53 PM
Grand Advisor
Posts: 17,320

Re: Join on Dates, One table Oracle, The other Teradata

Convert into true SAS dates, by creating new calculated variables in EG and join on the calculated variables.

Frequent Contributor
Posts: 80

Re: Join on Dates, One table Oracle, The other Teradata

Thanks

After a lot of testing the functions, I used DATDIF to manually subtract the date field value from 1st Jan 1960 which gave me a number, I could then match these number fields.

I think that's what you were trying to tell me Reeza.

This is a lot of messing about to sort such a simple issue out???

I thought I'd just have to truncate the HH:MMSmiley FrustratedS off the date Time field & I'd get matches, but no ... 

I used datepart to change the date from a normal date format to a number on the date/time field & then create a number from a date using datedif for the other table.

 

DATDIF('01jan60'd, my_date_field ,'act/act').

Thanks to both of you - surely there's an easier way ???

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1089 views
  • 3 likes
  • 3 in conversation