Opnqryf

[abhishek_ec002]

Hello Team,
Firstly let me thank you all in advance !!!

I have one criteria for selection of records from a file using OPNQRYF.....I need to reteive the records which are of TMZA(file A) *GE &tmz2.
TMZA is timestamp in file A and &date is variable declared in CL.

Please find my code below :

 DCL        VAR(&TMZ1) TYPE(*char) LEN(26)
 DCL        VAR(&TMZ2) TYPE(*char) LEN(15) VALUE('00.00.00.000000')
 DCL        VAR(&DATE) TYPE(*CHAR) LEN(8)

DCLF       FILE(FILEA)

RCVF       RCDFMT(filer)
CHGVAR     VAR(&TMZ1) VALUE(&TMZA)
[COLOR="Red"]..........Value of &TMZ1 would be time stamp value from file i.e '2010-01-16-00.38.31.178000'
[/COLOR]CHGVAR     VAR(%SST(&TMZA 12 15)) VALUE(&TMZ2) 
[COLOR="Red"].......value of &TMZA will get changed to ''2010-06-26-00.00.00.000000'

[/COLOR]OVRDBF     FILE(FILEA) OVRSCOPE(*JOB) SHARE(*YES)

OPNQRYF    FILE((FILEA)) QRYSLT('TMZA *GE' *BCAT &TMZ1)

.......once this is executed am getting below error

'Character '.' following string '00.38 ' not valid.'

Also tried in different way even...By extracting date from TMZA...

DCL        VAR(&TMZ2) TYPE(*CHAR) LEN(8)  
DCL        VAR(&tmz1) TYPE(*CHAR) LEN(10) 
DCL        VAR(&DATE) TYPE(*CHAR) LEN(8) 

CHGVAR     VAR(&TMZ1) VALUE(%SST(&TMZA 1 10))
CVTDAT     DATE(&TMZ1) TOVAR(&TMZ1) FROMFMT(*ISO) 
             TOFMT(*YYMD) TOSEP(*NONE) 
CHGVAR     VAR(&TMZ2) VALUE(&TMZ1)

OPNQRYF    FILE((FILEA)) QRYSLT('&TMZ2 *GE' *BCAT &date)

.......once executed am getting below error...
'Missing operand on expression in QRYSLT parameter.'

Could you please help me in getting the correct query for my requirment.

Thankyou......

[Mercury]

Re: Opnqryf

For heaven's sake why are you still wanting to use OPNQRYF again ? Create an RPG program and use SQL instead !

[tomholden]

Re: Opnqryf

For heaven's sake why are you still wanting to use OPNQRYF again ? Create an RPG program and use SQL instead !

agreed! why use this archaic method this day and age?

BTW please use [code][/code] tags on your code so it's more readable!

[abhishek_ec002]

Re: Opnqryf

For heaven's sake why are you still wanting to use OPNQRYF again ? Create an RPG program and use SQL instead !

Actually I need to add my new query to the existing query which is already written in OPNQRYF. I have no other choice, i cant create another new program. Please advise.

[Mercury]

Re: Opnqryf

Try to use the %DATE function instead.

 OPNQRYF
     FILE(library/file)
     QRYSLT(('%DATE(tstampfld) = "1989-10-23"'))

[Frank]

Re: Opnqryf

are you not simply missing some quotes around your timestamp value?

When running your program, the QRYSLT keyword will look like

QRYSLT(TMZA *GE 2010-01-16-00.38.31.178000)

but I think it should be like this:

QRYSLT(TMZA *GE '2010-01-16-00.38.31.178000')

So, try adding in those quotes when you construct the QRYSLT string.

[jmp]

Re: Opnqryf

are you not simply missing some quotes around your timestamp value?

When running your program, the QRYSLT keyword will look like

QRYSLT(TMZA *GE 2010-01-16-00.38.31.178000)

but I think it should be like this:

QRYSLT(TMZA *GE '2010-01-16-00.38.31.178000')

So, try adding in those quotes when you construct the QRYSLT string.

It's a little bit more complicated. It should be :

QRYSLT('TMZA *GE "2010-01-16-00.38.31.178000" ')

and using a CL variable :

OPNQRYF    FILE((FILEA)) QRYSLT('TMZA *GE "' *BCAT &TMZ1 *TCAT '"')