Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Query only last 4 days In-DB Oracle

Highlighted
8 - Asteroid

Trying to pull in a massive table, from beginning of this year to current is over 100mil rows. I have several criteria that will lessen the load. Most importantly, I only need the previous 4 days.

 

I have a timestamp column but cannot get any of the SQL to work. Admittedly I usually use Postgress or MySQL and this is an Oracle DB.

 

Syntax to pull only previous 4 days?

 

I've tried 

          where TIMESTAMP   >= DATEADD(day,-4, GETDATE())

          where  TIMESTAMP > SYSDATE - 4 

 

To no real solution

Highlighted
Alteryx
Alteryx
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @wmcclure ,

 

you could try:

 

DATEDIFF(DAY, CAST(TIMESTAMP AS date), GetDate()) < 5

 

I assume, the TIMESTAMP column is a TIMESTAMP data type (i.e. date + additional time information). To avoid confusion, the column should be converted to a DATE data type. Datediff function return the number of units (days) between first and last day. Hope this is helpful.

 

Best,

 

Roland

Highlighted
8 - Asteroid

This makes sense but the trouble that seems to be occurring is the SQL query editor is so **bleep** specific on its structure that anytime I put in a straight forward query, I get errors.

 

I strip out all the parenthesis, to get the "test query" to pass and once run it just ignores any date argument I wrote and returns the whole table

 

Is this an incompatibility with the SQL editor and Oracle DBs?

 

I have two other data sources that are in other DBs (MSSQL, Postgres) and I don't have this issue.

@JunyaK 

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

In general, there is no incompatiblity in my opinion (at least this is my experience using sometimes Alteryx with Oracle DBs).  Do you create your SQL in Oracle Developer or Toad and copy it to Alteryx or do you use the Alteryx SQL editor to create queries? Could you provide the complete SQL - it's strange that only the WHERE clause does not work. And - is it only related to date restrictions in the where clause, or does this happen also on other conditions?

Highlighted
8 - Asteroid

@RolandSchubert 

 

I believe I found it. Previous pulls I was doing a syntax like this:

 

WHERE
 TimeStamp > SysDate -5
AND
 Media_code = 'R'
AND
 Seconds > 180
 OR
  Seconds <2701

 

 

I used alteryx SQL editor. I am pulling in the entirety of a single table (31 million rows), filtering out by date, if a certain code is R, and a numerical value.

 

 

 

 

 

Where
   (  TIMESTAMP > SysDate -5
      And MEDIA_TYPE_CODE = 'R' 
      And TIMESHIFT_SECS > 180
   )
   Or 
   (  TIMESTAMP > SysDate -5
      And MEDIA_TYPE_CODE = 'R' 
      And TIMESHIFT_SECS < 2701
   )

 

 

 

Oh, my goodness. The level of explicit writing needed. It never occurred to me to do it as such.

Thank you @RolandSchubert for pushing me to keep adding detail. Trying to explain it more helped me work it out!

 

Labels