FedSQL Convert and Compare current_date

[Uemerson]

Hello experts!

 I am trying to convert the code below (Oracle SQL) to FedSQL used by SAS DataFlux. The field called in this example "DATE" is a numeric type with the format 'yyyymmdd'.

Name   Type

DATE    NUMBER(8)

select count(0) AS Ten_Days_Before
from [TABLE]
where DATE = TO_CHAR(SYSDATE-10, 'yyyymmdd');

Any suggestion?

 I tried the function "PUT" with the code below, but It´s not working because of different data types.

select count(0) AS Ten_Days_Before 
from [TABLE]
where PUT(DATE,yymmddn8.) = PUT(current_date-10,yymmddn8.) ;

Thank you!

[ChrisNZ]

1.  It´s not working because of different data types.

What are the data types?

2. If this works

 put(DATE,yymmddn8.) = put(CURRENT_DATE-10,yymmddn8.) 

then this works

 DATE = CURRENT_DATE-10

(not that it's better, just different and a tad faster)

[Uemerson]

Hello ChrisNZ, Thanks for your reply.

 The different types I mean are about the field called "DATE" and the system variable "current_date". The first one is Numeric, and after use the function PUT it will be transformed into Double, right? And the second one is a date, and I don´t know what type it will be after PUT function, anyway, the result of this example I gave is 0.

Do you have another function or logic to do this count comparing these fields?

Thank you!

[ChrisNZ]

1. I am confused as to what the values are.

>The first one is Numeric  

Is it a SAS date like 23056?  Or a number expressing a date like 20180612?

> And the second one is a date, and I don´t know what type it will be after PUT function,

A date should probably not need using PUT

Please provide the raw untransformed values for each, for example the first lines of :

select DATE, SYSDATE from TABLE

2. I am confused as to where you run your query.

This is invalid SAS code:

  select count(0) AS Ten_Days_Before

  from [TABLE]

  where DATE = TO_CHAR(SYSDATE-10, 'yyyymmdd');

while this is only valid in SAS:

  select count(0) AS Ten_Days_Before 
  from [TABLE]
  where PUT(DATE,yymmddn8.) = PUT(current_date-10,yymmddn8.) ;

So which one is it? Where do you run the code? Please provide full code and log.

[Uemerson · Accepted solution]

Hi ChrisNZ,

1. The field "DATE" is a number expressing a date (Ex.: 20180829).

2. My query was running in Oracle SQL, now I need to migrate the code to SAS DataFlux.

Solution:

 I tried the code below, and It works well. I think that the problem was the format used with PUT function.

select count(0) AS Ten_Days_Before 
  from 

       [TABLE]
  where

       PUT(DATE,8.) = PUT(current_date-10,yymmddn8.) ;  

Thanks for your reply!

[ChrisNZ]

It's clearer now.
where input(DATE,yymmddn8.) = current_date-10 ; 
orwhere input(DATE,yymmdd8.) = current_date-10 ; would probably work too. Not that it's better. Just to show how to compare dates rather than strings.