Re: An easy (I think) SQL question

Very cool!

Bill, you will still need to convert something - either the packed column that holds the date (DO NOT do that - see other posts about functions applied to columns that force full table scans) or the expression using CURRENT DATE - you want to convert that to the CYYMMDD format.

Since you don't have the TIMESTAMP_FORMAT function, and you DO have the REPLACE function, you can use the following to get the CYYMMDD form in a number of your date expression -

dec( '1' || substr(replace(char(current date, iso), '-', ''), 3, 6))

and test whether it is equal to your "date" column.

I assume here that your dates are all in the 2100s - I think your assumption was things would be greater than a date that makes that happen.

HTH
Vern

On 6/29/2016 8:33 AM, Charles Wilt wrote:

I would think you should be able to run the SQL as a "pass through" query.

Which means the SQL is sent complete to the server where it is run.

Pass through queries are usually the prefered option. Since otherwise MS
SQL server pulls back the tables involved in there entirety and then does
the joining/filtering.

The following statements work when issued directly in MS SQL Server
(assuming LINKEDSVR is setup).

-- Pull all rows from the table(s) back and does the where locally
select * from LINKEDSVR.MYIBMI.MYLIB.MYTBL where locnbr = '00335';

-- Sends the statement to linked server for processing
select * from openquery(LINKEDSVR, 'select * from MYTBL where locnbr =
''00335''');

I suspect there's a way to use OPENQUERY() from SSRS also...

As an aside...OPENQUERY() isn't just for SELECTS
delete from openquery(LINKEDSVR, 'select * from MYTBL where locnbr =
''00335''');

Charles




On Wed, Jun 29, 2016 at 9:20 AM, Bill Howie <blhowie66@xxxxxxxxx> wrote:

All,

A couple of things I probably should have mentioned initially.......first,
I'm only using SQL to accomplish this, not an RPG program. What I'm
actually doing is writing an SQL script in SSRS but accessing an AS/400
database. So basically the script has to work on the AS/400, but I'll
actually be running it within SSRS. Is the TIMESTAMP_FORMAT function
something that came along with more current releases of the OS? We are,
unfortunately, still on V5R4.

Bill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.