Home » SQL & PL/SQL » SQL & PL/SQL » SQL code syntax Understanding
SQL code syntax Understanding [message #671896] Sat, 22 September 2018 10:03 Go to next message
anirdesh
Messages: 1
Registered: September 2018
Junior Member
I have query code below.
Can some body explain me, what is being calculated or defined? I need to convert query code into SAS syntax/query.

It seems to me that take 2018-09-21 date and subtract ITDT, and I don't know floor and TURNC and CAST functions with date.


sum(case when TO_NUMBER(TO_CHAR(TO_DATE('2018-09-21' , 'YYYY-MM-DD'), 'D'), '99') -
TO_NUMBER(TO_CHAR(T5759482.ITDT, 'D'), '99') - 4
+ floor(( TRUNC( ( TO_DATE('2018-09-21' , 'YYYY-MM-DD')
+ 8 - TO_NUMBER(TO_CHAR(TO_DATE('2018-09-21' , 'YYYY-MM-DD'), 'D'), '99') ) )
- TRUNC( ( CAST(T5759482.ITDT as DATE)
+ 1 - TO_NUMBER(TO_CHAR(T5759482.ITDT, 'D'), '99') ) ) ) / 7) * 5 - 1 C < 11 then 1 else 0 end ) as a1,
Re: SQL code syntax Understanding [message #671897 is a reply to message #671896] Sat, 22 September 2018 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

consider to just Read The Fine Manual yourself

https://docs.oracle.com/database/121/SQLRF/toc.htm

https://docs.oracle.com/database/121/nav/portal_booklist.htm


Re: SQL code syntax Understanding [message #671898 is a reply to message #671896] Sat, 22 September 2018 12:20 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Of course you need to read up on all those functions. Then I would break down the query to see what each clause is doing. For example:
pdby1>
pdby1> select TO_NUMBER(TO_CHAR(TO_DATE('2018-09-21' , 'YYYY-MM-DD'), 'D'), '99') from dual;

TO_NUMBER(TO_CHAR(TO_DATE('2018-09-21','YYYY-MM-DD'),'D'),'99')
---------------------------------------------------------------
                                                              5

pdby1> select TRUNC( ( TO_DATE('2018-09-21' , 'YYYY-MM-DD')  + 8 - TO_NUMBER(TO_CHAR(TO_DATE('2018-09-21' , 'YYYY-MM-DD'), 'D'), '99') ) ) from dual;

TRUNC((TO_DATE('201
-------------------
2018-09-24:00:00:00

pdby1>
One thing I can say is that the person who wrote that query does not know what he is doing. There are numerous unnecessary function calls, it could be a lot simpler. I would throw it back to the developer.
Re: SQL code syntax Understanding [message #671902 is a reply to message #671898] Sat, 22 September 2018 14:29 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Even worse, that query is NLS dependent so client in US and say in Sweden will see different results:

SQL> alter session set nls_territory=america;

Session altered.

SQL> alter session set nls_date_format='yyyy-mm-dd';

Session altered.

SQL> select TRUNC( ( TO_DATE('2018-09-21' , 'YYYY-MM-DD')  + 8 - TO_NUMBER(TO_CHAR(TO_DATE('2018-09-
21' , 'YYYY-MM-DD'), 'D'), '99') ) ) from dual;

TRUNC((TO_
----------
2018-09-23

SQL> alter session set nls_territory=sweden;

Session altered.

SQL> select TRUNC( ( TO_DATE('2018-09-21' , 'YYYY-MM-DD')  + 8 - TO_NUMBER(TO_CHAR(TO_DATE('2018-09-
21' , 'YYYY-MM-DD'), 'D'), '99') ) ) from dual;

TRUNC((TO_
----------
2018-09-24

SQL> 

SY.
Previous Topic: OE SAMPEL SCHEMA
Next Topic: How to Find the Parent of Index Organized Table
Goto Forum:
  


Current Time: Thu Mar 28 13:34:12 CDT 2024