Query

[John192]

Hi,


How to write a single statement SQL query ( in AS/400 I mean in SQL/400) to select all the records of a file for a specific date field (whose date format is CYY/MM/DD) which has current system date ?



Thanks much...

[jtaylor___]

select * from TABLE where TABLE.COLUMN = current_date

[B.Hauser]

Just a note: Please do no longer use terms like AS/400 and SQL/400.
Use IBM i and SQL on Db2 for i instead.

Birgitta

[mgarczynski]

I do agree.

[Vectorspace]

When you say "date field", do you mean a field of the "date" data type?
If so, then jtaylor's answer is right

But if by "date field" you mean a field of numeric or string data type that holds a date in CYYMMDD or CYY/MM/DD format:

CYYMMDD is relatively easy. You can convert current date to numeric with
field = int(current date)
Or to char with
field = char(int(current date))

But if the field stores it as a string in CYY/MM/DD format with slashes, then it's much more complicated. This is the only way I could think of to convert it:
field = substring(char((int(current date)-19000000)),1,3) ||'/'|| substring(char((int(current date)-19000000)),4,2) ||'/'|| substring(char((int(current date)-19000000)),6,2)
If I had to do that at all regularly I would build an SQL function for it

Unfortunately the SQL date to string formatter VARCHAR_FORMAT() does not seem to have a format code for C where for 2022, C = 1 and 1900, C = 0

[Vectorspace]

Typo in the above. The bit about CYYMMDD format should have been:
CYYMMDD is relatively easy. You can convert current date to numeric with
field = int(current date) - 19000000
Or to char with
field = trim(char(int(current date) - 19000000))

[John192]

My date field is numeric with 7 length and has date data in it in CYY/MM/DD format so for this how can i write a SQL query which would return all the data from this specific file for this date field which has date in CYY/MM/DD format and whose value is equal to current system date?


Thanks much...

[John192]

My date field is numeric with 7 length and has date data in it in CYY/MM/DD format so for this how can i write a SQL query which would return all the data from this specific file for this date field which has date in CYY/MM/DD format and whose value is equal to current system date?


Thanks much...

I tried this SQL query but it ended up in error "column or global variable / not found."


select * from filename where field = substring(char((int(current date)-19000000)),1,3) ||'/'|| substring(char((int(current date)-19000000)),4,2) ||'/'|| substring(char((int(current date)-19000000)),6,2)


also how can we make a SQL function for this and what advantages/disadvantages it would have over above SQL query?



Thanks much...

[B.Hauser]

You should not convert a date into a numeric value, but the numeric value into a valid date:
If you want to select all rows with the current date:

Select *
   From yourTable
   Where Date(DIGITS ( DEC ( CHAR ( DATENUM + 19000000 ) , 8 ) ) CONCAT '000000') = Current_Date;

You may also create an SQL View with additional columns where the numeric date is converted into a real date.
If you want to have a function. Just write one (ACS has a wizard for generating functions) and include the expression above for converting the numeric value.

[MartinTosney]

You should not convert a date into a numeric value, but the numeric value into a valid date.

I think this is generally good advice, but in this one specific case I would argue the opposite is actually better. The date conversion would have to be performed for every row in the table which means that with no other selection criteria that the query plan will just use a table scan and be more CPU heavy. That's probably not ideal if it's a large table.

By converting the current date into a numeric value, that gives the optimiser the possibility at least of using an index over the date column, should one exist, or generating an MTI if not.

[Vectorspace]

John192

column or global variable not found suggests your SQL is not being run correctly, and the / characters are being interpreted as field names, not string values. That suggests you are bulding your SQL statement in a string variable in a program and then executing it with PREPARE/EXECUTE, or the RUNSQL command.

A date cannot be numeric and in CYY/MM/DD format, because the '/''s make it character. It is either a numeric field in CYYMMDD format (that you choose to display with '/'s), or character CYYMMDD, or character CYY/MM/DD

Since you have confirmed the field is numeric it must be stored as CYYMMDD. So you can match with:
select * from myfile where field = (int(current date) - 19000000);

[John192]

You should not convert a date into a numeric value, but the numeric value into a valid date:
If you want to select all rows with the current date:

Select *
From yourTable
Where Date(DIGITS ( DEC ( CHAR ( DATENUM + 19000000 ) , 8 ) ) CONCAT '000000') = Current_Date;

You may also create an SQL View with additional columns where the numeric date is converted into a real date.
If you want to have a function. Just write one (ACS has a wizard for generating functions) and include the expression above for converting the numeric value.

I tried this SQL query but getting some errors like below:-

1. Argument 2 of function Decimal not valid.


What i did is instead of S i just kept that field name which is numeric with 7 length .

[John192]

I tried this SQL query but getting some errors like below:-

1. Argument 2 of function Decimal not valid.


What i did is instead of S i just kept that field name which is numeric with 7 length .
and instead of DATENUM also i put the name of that same field which is numeric and having a length of 7.




Thanks..

[John192]

John192

column or global variable not found suggests your SQL is not being run correctly, and the / characters are being interpreted as field names, not string values. That suggests you are bulding your SQL statement in a string variable in a program and then executing it with PREPARE/EXECUTE, or the RUNSQL command.

A date cannot be numeric and in CYY/MM/DD format, because the '/''s make it character. It is either a numeric field in CYYMMDD format (that you choose to display with '/'s), or character CYYMMDD, or character CYY/MM/DD

Since you have confirmed the field is numeric it must be stored as CYYMMDD. So you can match with:
select * from myfile where field = (int(current date) - 19000000);

Just to correct it is currently present in C/YY/MM/DD format in a file and the field in which this date data is present that is numeric and having a length of 7.

So still this query is valid to find all the records in that physical file whose specific field's data type is numeric and length is 7 and which has it's that specific field value equal to today's date (current system date which is '01/10/22') ?


Thanks...

[Vectorspace]

"Just to correct it is currently present in C/YY/MM/DD format in a file and the field in which this date data is present that is numeric and having a length of 7."

That is impossible. If the table field is numeric, it must be CYYMMDD, not C/YY/MM/DD
A numeric field cannot contain slashes.
Maybe your user interface is displaying the value with slashes, but that is different. We need to know how the database table stores it, not how your user interface displays it.

If the database column is a numeric 7 digit, then this should work:
select * from myfile where field = (int(current date) - 19000000);

[John192]

"Just to correct it is currently present in C/YY/MM/DD format in a file and the field in which this date data is present that is numeric and having a length of 7."

That is impossible. If the table field is numeric, it must be CYYMMDD, not C/YY/MM/DD
A numeric field cannot contain slashes.
Maybe your user interface is displaying the value with slashes, but that is different. We need to know how the database table stores it, not how your user interface displays it.

If the database column is a numeric 7 digit, then this should work:
select * from myfile where field = (int(current date) - 19000000);

It's quite possible when i run sql query like select fieldname from file name then i see that specific field heading along with data of this field in CYY/MM/DD format only how come user inter face come in to picture here?

[John192]

I meant when I am running SQL query then seeing date in CYY/MM/DD format where are these slashes coming from in SQL query output?

And what do you mean by "how your user interface displays it" SQL is being executed in IBM i environment only do you men different IBM i environment display same SQL query result in different formats? do you mean on some IBM i environment same SQL query show this result without slashes?

Could you please clarify what exactly do you mean to say here?


Thanks much...

[MartinTosney]

Is this a DDS file with an EDTWRD on the column?