Btukfyl

column dcol new_value mydate noprint

select to_char(sysdate - case

                    when to_char(sysdate, 

'hh24:mi:ss') between '17:00:00' and '23:59:59' then 0

                    when to_char(sysdate, 

'hh24:mi:ss') between '00:00:00' and '17:00:00' then 1

                end,'YYYYMMDD') dcol

from dual;



set feedback off



spool "C:testtest_&mydate..csv";



select /*csv*/ * from REPORTS.TEST;



spool off;

Hi,

I have the above SQL query that was written by someone else. I'm trying to understand and follow it.
I have the following questions - wonder if someone could help me with them?

1) There are 2 instances of 'dcol' in the query:
a) column dcol, then b) select dcol from dual. Is 'dcol' a column name from dual table? It seems that if I replace 'dcol' with 'random_col' -
the query still work the same

2) "C:testtest_&mydate..csv"; why is there a double '.' as opposed to single '.' right before 'csv'?

3) How do I debug the SQL query in Oracle SQL Developer? For example, in the following:

select to_char(sysdate - case

                    when to_char(sysdate, 

'hh24:mi:ss') between '17:00:00' and '23:59:59' then 0

                    when to_char(sysdate, 

'hh24:mi:ss') between '00:00:00' and '17:00:00' then 1

                end,'YYYYMMDD') dcol

from dual;

I want to find out: a) what is the value of sysdate?, b) which case, select falls into?

4) If at the end of the SQL query, I add: Define mydate; I would get the following output:

DEFINE MYDATE = "20181126" (VARCHAR2)

Is there other ways to display what the variable mydate is and its type?

1) dcol is the column_name for the output of the dual query

select to_char(sysdate - case

                    when to_char(sysdate, 

'hh24:mi:ss') between '17:00:00' and '23:59:59' then 0

                    when to_char(sysdate, 

'hh24:mi:ss') between '00:00:00' and '17:00:00' then 1

                end,'YYYYMMDD') dcol

from dual;





output.....

+----------+

|   dcol   |

+----------+

| 20181126 |

+----------+

The first line of your code says
column dcol new_value mydate noprint.

This means to store the value of dcol and put that into the "sql plus/sqldeveloper" variable mydate.

NB This is a sqlplus command which is understood by the sqlplus program/sqldeveloper (Its not a standard PL/SQL) command.

set feedback off 

means suppress the query status such as

42 rows returned..

2) "C:testtest_&mydate..csv"; why is there a double '.' as opposed to single '.' right before 'csv'?.

You would need a .. to escape the "." in sqlplus/sqldeveloper program.

Spool command is a sqlplus thing. here you notice that you are creating a "csv" file with the file name as that returned from the 1) output which is present in the variable &mydate

select /*csv*/ * from REPORTS.TEST;

This is a syntax in sqldeveloper, which will generate csv file as the output.
Check this link to see how easy it is to generate csv

https://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/

spool off means create the file in C:testtest_20181126.csv

3) Just run the query as is.

4) Just give this a try select &mydate from dual;