How to pass current date in yyyymmdd format in sqlci table.

neeraj kumar

unread,

Sep 22, 2015, 8:02:56 AM9/22/15

to

I need to pass current date in where clause in sqlci query:

below is the sqlci query i wrote :

out_report $data11.nkumar.testdata;
SET LAYOUT PAGE_LENGTH ALL ;
SET SESSION LIST_COUNT 0 ;
SET SESSION WRAP OFF ;
SET STYLE HEADINGS OFF ;
select
EARLIEST_DATE,
EARLIEST_TIME,
BATCH_FUNCTION,
BATCH_STATUS_CODE,
CREATE_DATE,
CREATE_TIME,
TABLE_MARKING
FROM =P-BATCH-JOB where BATCH_FUNCTION IN(BATCH_FUNCTION) and
BATCH_STATUS_CODE <> "9" AND
CREATE_DATE = [yy][mm1][dd1]
browse access;
detail
EARLIEST_DATE as I8 heading "EARLIEST_DATE","~",
EARLIEST_TIME as I8 heading "EARLIEST_TIME","~",
BATCH_FUNCTION as A10 heading "BATCH_FUNCTION","~",
BATCH_STATUS_CODE as A1 heading "BATCH_STATUS_CODE","~",
CREATE_DATE as I8 heading "CREATE_DATE","~",
CREATE_TIME as I8 heading "CREATE_TIME","~";

and I was trying to pass [yy][mm1][dd1] through tacl :

?tacl macro
[#LOOP
|DO|
#push #inlineprefix
#push mm dd yy dd1 mm1
#push #out :rec
#setmany yy mm dd , [#contime [#timestamp]]
[#if [#charcount :dd] = 1 |then|
[#set dd1 0[dd]]
|else|
[#set dd1 [dd]]
]
[#if [#charcount :mm] = 1 |then|
[#set mm1 0[mm]]

|else|
[#set mm1 [mm]]
]
[#if [#charcount :yy] = 4 |then|
#chardel :yy 1 to 2
]
inlout off
inlecho off
#set #inlineprefix ++
fup /inline/
[#if [#fileinfo /existence/ $DATA11.NKUMAR.testdata]
|then|
#output purgedata on testdata
++ purgedata $DATA11.NKUMAR.testdata
|else|
#output creating on testdata
++ create $DATA11.NKUMAR.testdata,like $DATA11.NKUMAR.test
]
sqlci/ pri 5, name $test, in batqry/
pop #inlineprefix

== Delay
#DELAY 6000
|UNTIL| 0 = 1
]
#unframe

but it didn't worked. Is there any way to pass current date in yyyymmdd format in where clause ?

Flong

unread,

Sep 22, 2015, 9:23:08 AM9/22/15

to

Hi,

The easiest way is to get the date from SQL instead of TACL

Use this in the WHERE clause:
CREATE_DATE =
cast(current year as char(4)) ||
cast(current month as char(2)) ||
cast(current day as char(2))

HTH

John

wbreidbach

unread,

Sep 22, 2015, 9:28:29 AM9/22/15

to

There are 2 possible versions:
At first start the whole query as inline process, then setting the parameters using TACL would do the trick (That is how I use to do such things).
The other chance is looking at the SQL/MP Reference Manual at the functions CURRENT and CURRENT_TIMESTAMP.
Without having the description of the table that is all I can say for now.

neeraj kumar

unread,

Sep 22, 2015, 11:18:24 AM9/22/15

to

Hi Flong,

i have tried your suggestion, but CREATE_DATE is of numeric type so i am getting error :
*** ERROR from SQL [-7100]: Comparisons between NUMERIC and CHARACTER data types
*** are not allowed.

Even i have changed cast(current year as char(4)) to cast(current year as numeric(4)) but i seems like cast supports only character variable

neeraj kumar

unread,

Sep 22, 2015, 11:20:01 AM9/22/15

to

Hi wbreidbach,

I am trying your solution to make the sql query inline, will update you if it works.

Thank you wbreidbach and Flong for your help and suggestions.

Flong

unread,

Sep 22, 2015, 11:32:29 AM9/22/15

to

So, cast the concatenated string to a numeric(8), like this:

CREATE_DATE = cast(

cast(current year as char(4)) ||
cast(current month as char(2)) ||
cast(current day as char(2))

AS NUMERIC(8))

neeraj kumar

unread,

Sep 23, 2015, 3:36:09 AM9/23/15

to

Hi Flong,

Thank you very much, it worked fine.

I have tried another way also, through tacl inline and it also worked fine.

Thank you all, for help!!!

Regards,
Neeraj kumar

neeraj kumar

unread,

Sep 23, 2015, 5:14:29 AM9/23/15

to

Query which i have created using inline is :

?tacl macro
#set #trace -1
#FRAME
==[#LOOP
==|DO|
#push mm dd yy dd1 mm1 l_in
#SET l_in set page_length all;

[#if [#fileinfo /existence/ $DATA11.NKUMAR.testdata]
|then|
#output purgedata on testdata

FUP purgedata $DATA11.NKUMAR.testdata

|else|
#output creating on testdata

FUP create $DATA11.NKUMAR.testdata,like $DATA11.NKUMAR.test
]
== sqlci/ pri 5, name $test, in batqry/
sqlci /inv l_in dynamic, nowait, pri 2, name $nk1/

#APPEND l_in out_report $data11.nkumar.testdata;
#APPEND l_in SET SESSION LIST_COUNT 0 ;
#APPEND l_in SET SESSION WRAP OFF ;
#APPEND l_in SET STYLE HEADINGS OFF ;
#APPEND l_in select
#APPEND l_in EARLIEST_DATE,
#APPEND l_in EARLIEST_TIME,
#APPEND l_in BATCH_FUNCTION,
#APPEND l_in BATCH_STATUS_CODE,
#APPEND l_in CREATE_DATE,
#APPEND l_in CREATE_TIME,
#APPEND l_in TABLE_MARKING
#APPEND l_in FROM =P-BATCH-JOB where BATCH_FUNCTION IN(BATCH_FUNCTION) and
#APPEND l_in BATCH_STATUS_CODE <> "9" AND
#APPEND l_in CREATE_DATE < [yy][mm1][dd1]
#APPEND l_in browse access;
#APPEND l_in detail
#APPEND l_in EARLIEST_DATE as I8 heading "EARLIEST_DATE",",",
#APPEND l_in EARLIEST_TIME as I8 heading "EARLIEST_TIME",",",
#APPEND l_in BATCH_FUNCTION as A10 heading "BATCH_FUNCTION",",",
#APPEND l_in BATCH_STATUS_CODE as A1 heading "BATCH_STATUS_CODE",",",
#APPEND l_in CREATE_DATE as I8 heading "CREATE_DATE",",",
#APPEND l_in CREATE_TIME as I8 heading "CREATE_TIME",",";
#APPEND l_in list all;
#APPEND l_in exit

SINK [#WAIT l_in]

== #pop #inlineprefix
== Delay
== #DELAY 6000
== |UNTIL| 0 = 1
==]
#unframe

Just updated ... May be in future it will help somebody. :)

Keith Dick

unread,

Sep 23, 2015, 8:27:23 AM9/23/15

to

Two comments.

1. I do not know whether this applies to your situation, but queries that normally run using the current date sometime have to be run with a different date than the current date. For example, something delayed work until after midnight and you need to run the query with the previous day's date. Or the output was lost or seems wrong and you want to re-run the query to get the results again. If any case like this might occur for times when this query is being used, it would be good to provide a simple way to override the current date and use a date supplied when you run the query. A fairly easy way to do that would be to have this macro look for an argument, and if one is supplied, use the value of that argument as the date to use, or maybe look for three arguments and if they are supplied, use them to set yy, mm, and dd instead of getting those values from #timestamp.

As I said, this might not apply to your situation, but if it does, allowing for override of the date to use could save some hassle when the need arises.

2. There is a simpler way to send the query from TACL to SQLCI. There is nothing wrong with using #append to an INV variable. That works fine. But there is a simpler way that could be used in this case, and I just wanted to point it out so you know how to do it a little simpler the next time you need to feed commands to a program from TACL. It would look like:

#push #inlineprefix
#set #inlineprefix +
sqlci /inline, pri 2, name $nk1/
+ out_report $data11.nkumar.testdata;
+ SET SESSION LIST_COUNT 0 ;
+ SET SESSION WRAP OFF ;
+ SET STYLE HEADINGS OFF ;
+ select
+ EARLIEST_DATE,
+ EARLIEST_TIME,
+ BATCH_FUNCTION,
.
.
.
+ list all;
+ exit

The #unframe at the end of the macro will pop the value of #inlineprefix, so you do not have to do that yourself.

I left out a lot of the lines of the query (where the dots are) to make this example short, but of course you would include them all.

As I said, there is nothing wrong with the approach using #append. This approach is just a little simpler and easier to read.