Need to put the same query as DB2 in SQLRPGLE

[vijayrmca]

All,

I am having one DB2 query

SELECT year(current date)*10000 +
month(current date)*100 +
day(current date-1 days),count(*) FROM mstfile.

Above query gives me the result as 20200108 ,100 as output


I need to put this in one of the SQL RPGLE program where i am having the code like this

Str = 'Insert into TMPFILE +
SELECT year(current date)*10000 || +
month(current date)*100 || +
day(current date-1 days),count(*) FROM '+
file_name;

But when i am debugging the program it is not getting the output .


Kindly help me correcting the SQL RPGLE Script.

[Herb...]

vijayrmca, assuming that TMPFILE has only 2 columns, try something simple like (I apologize for the formatting):

Exec Sql
INSERT INTO TMPFILE
SELECT year(current date)*10000 +
month(current date)*100 +
day(current date-1 days), count(*)
FROM mstfile;


The trick is to start with an EXEC SQL to indicate that the statement block contains SQL. Unless you are doing more complex SQL operations, you do not need manually build the SQL statement into a variable.

[JonBoy]

You haven't shown us the prepare etc. Just the build of the SQL statement. How are you actually executing it?

And why not just use RPG to build the date string? The whole thing seems very messy.

[Vectorspace]

Why are you subtracting one day from the date you use to get day of month? If you think this will mean you get yesterdays date then it won't always work, as month and year are coming from todays date. So if today was 2020-01-01 you would get 20200131 instead of 20191231.

It used to be that you had to do arithmetic like you are doing above to get a date to numeric yyyymmdd format in SQL. But nowadays there is a better way:

this:
int(varchar_format(current date,'yyyymmdd'))

produces the same result as this:
year(current date)*10000 + month(current date)*100 + day(current date)

or, if you do need it to be yesterdays date:
int(varchar_format(current date - 1 day,'yyyymmdd'))


varchar_format() takes a date, time, or timestamp and converts it to character using the specified format. Int() then turns it into a numeric.

[Vectorspace]

As Birgitta pointed out, you can just use dec() to convert a date to numeric now, so no need to use varchar_format

[jtaylor___]

What is the resulting SQLSTATE?

[B.Hauser]

Just as an aside: You are determining the date of the previous day and convert it into a numeric value in the format YYYYMMDD.
... but you are only subtracting 1 day from the days part and not from the month and also not from the year. So what happens if the current date is Fanuary, 1 or February, 1?

Also converting a real date into a numeric date in the format YYYYMMDD much easier now (since 7.3 / 7.2 with PTF).
Just use the DEC scalar Function or the CAST specification to convert the real date into a numeric date:

... and there is no need to use dynamic SQL (as Herb already pointed out)
The following statement should do the job:

Exec SQL Insert into TMPFILE
          Select Dec(Current_Date - 1 Day, 8, 0), Count(*)
             From MSTFILE;

... after any (embedded) SQL Statement your should check either the SQLCODE or the SQLSTATE to check whether your SQL statement was successfully executed or not.

Birgitta

[Vectorspace]

dec(dateField,8,0) and int(dateField) work on 7.1 as well. Thanks Birgitta, I didn't know that