OpenSeq and ReadSeq not working in routine

girishoak · Post by **girishoak** » Tue Jan 27, 2004 10:11 pm

Hi,

I have source file which contains 13 columns. In that last 11 columns contains rates for year 1 to 11. I want to create 11 records from one line. For that I have written following routine. But while testing itself it hangs the routine. I think it hangs while opening or reading the file. Can any body help me out.

Or is there any other way to the same thing in datastage other than use of basics.

FncSplitYearRate (SourceFile,TargetFile)

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H


mycovcd=""
myyears=0
fr_term=0
to_term=0
mynrate=0
myprate=0
myrate=0
errcode=0
paramerr=0

print "hello "
openseq SourceFile to rfilevar  then  errcode = 0 else errcode = -1
print "source errcode " : errcode

openseq TargetFile to wfilevar  then  errcode = 0 else errcode = -1

print "target errcode " : errcode

if errcode = 0
then
    Loop
        readseq rfileline from rfilevar then *errcode = 0 else errcode = -1
        print "Line read is " : rfileline

        AgreementSeqNbr = field(rfileline, ",",1)
        Fr_Term = field(rfileline, ",",2)
        myrate1 = field(rfileline,",",3)
        myrate2 = field(rfileline,",",4)
        myrate3 = field(rfileline,",",5)
        myrate4 = field(rfileline,",",6)
        myrate5 = field(rfileline,",",7)
        myrate6 = field(rfileline,",",8)
        myrate7 = field(rfileline,",",9)
        myrate8 = field(rfileline,",",10)
        myrate9 = field(rfileline,",",11)
        myrate10 = field(rfileline,",",12)
        myrate11 = field(rfileline,",",13)
   
  
  fr_term=0
  to_term=0

      if myrate1 > 0 
      then 
          nRate= myrate1
          nYear=1
          gosub Myroutine
      end
 
      if myrate2 > 0 
      then 
          nRate= myrate2
          nYear=2
          gosub Myroutine
      end
 
      if myrate3 > 0 
      then 
          nRate= myrate3
          nYear=3
          gosub Myroutine
      end
 
      if myrate4 > 0 
      then 
          nRate= myrate4
          nYear=4
          gosub Myroutine
      end
 
      if myrate5 > 0 
      then 
          nRate= myrate5
          nYear=5
          gosub Myroutine
      end
 
      if myrate6 > 0 
      then 
          nRate= myrate6
          nYear=6
          gosub Myroutine
      end
 
      if myrate7 > 0 
      then 
          nRate= myrate7
          nYear=7
          gosub Myroutine
      end
 
      if myrate8 > 0 
      then 
          nRate= myrate8
          nYear=8
          gosub Myroutine
      end
 
      if myrate9 > 0 
      then 
          nRate= myrate9
          nYear=9
          gosub Myroutine
      end
 
      if myrate10 > 0 
      then 
          nRate= myrate10
          nYear=10
          gosub Myroutine
      end
 
      if myrate11 > 0 
      then 
          nRate= myrate11
          nYear=11
          gosub Myroutine
      end
      
    Repeat
     end
Print "Closing Files"
closeseq rfilevar
closeseq wfilevar
Print "Files Closed"
       
Myroutine:
  print "In routine ";
*       if mynrate <> myprate then

*        fr_term = fr_term + 1
*        to_term = to_term + 1

*       myprate = mynrate
*      wfileline = mycovcd : "," : myyears : "," : fr_term : "," : to_term : "," : mynrate
*    print "fr term " : fr_term
*    print "to term " : to_term
*    print "mynrate " : mynrate

       wfileline = AgreementSeqNbr : "," : Fr_Term : ",": nYear : "," : nRate 
 print wfileline
       writeseq wfileline to wfilevar then errcode = 0 else errcode = -1
*  end
 
Return (0);

errorCode = 0


Ans=0

Thanks in advance

Girish Oak[/code]

kcbland · Post by **kcbland** » Tue Jan 27, 2004 10:32 pm

Just write a job with 11 output links from the transformer. Each link has a constraint for 1 of the 11 columns. This is a good design when you have a low, fixed number of pivoted rows.

Code: Select all

seq --->  xfm ---> seq1
              ---> seq2
              ---> seq3
              ---> seq4
              ---> seq5
              ---> seq6
              ---> ... you get the idea

girishoak · Post by **girishoak** » Tue Jan 27, 2004 10:35 pm

Thanks Ken for your idea.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 27, 2004 10:36 pm

You've written an infinite loop containing ReadSeq statements.
That is, there is no exit from this loop.

Try

Code: Select all

Loop
While ReadSeq rfileline from rfilevar 
 * statements
Repeat

However, why not do this with a DataStage job? Read the 13 columns and output one, containing line feeds and repeats of columns 1 and 2. This technique has been discussed in the past, do a search on the Forum. It's exceedingly fast.

kcbland · Post by **kcbland** » Tue Jan 27, 2004 10:41 pm

ray.wurlod wrote: However, why not do this with a DataStage job? Read the 13 columns and output one, containing line feeds and repeats of columns 1 and 2. This technique has been discussed in the past, do a search on the Forum. It's exceedingly fast.

He's doing the reverse, 1 row becomes 11. So, a splitter style transformer stage is the appropriate design. (Plus you caught his code error

)

chulett · Post by **chulett** » Tue Jan 27, 2004 10:42 pm

Also, isn't there an issue with using GOSUB syntax within a Routine? I was under the impression that was a no-no as you tended to exit the routine when the first 'return' was encountered.

Or that the presence of the multiple 'return' statements irritated the compiler...

kcbland · Post by **kcbland** » Tue Jan 27, 2004 10:47 pm

chulett wrote:Also, isn't there an issue with using GOSUB syntax within a Routine? I was under the impression that was a no-no as you tended to exit the routine when the first 'return' was encountered.

Or that the presence of the multiple 'return' statements irritated the compiler...

Nope, just fine. This is how you do this. On a previous post I discussed the way RETURN statements work. Basically, a GOSUB pushes the current code address onto the stack and then does direct branch to the label. At the next RETURN statement encountered, the stack has the current address popped, so that branching goes back to the value pushed. If you do 3 nested GOSUBs, then you have 3 addresses on the stack. It takes 3 RETURNs to get you back to the original point. This is why soooo many people harp on GOTO statements, because people use them to circumvent the RETURN mechanisms and can potentially overflow a stack pointer.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 27, 2004 10:47 pm

chulett wrote:Also, isn't there an issue with using GOSUB syntax within a Routine? I was under the impression that was a no-no as you tended to exit the routine when the first 'return' was encountered.

Or that the presence of the multiple 'return' statements irritated the compiler...

It is a known glitch in the compiler, but the glitch is actually when you use a RETURN statement that lacks an argument - that is, a RETURN statement rather than a RETURN function.
If you provide a RETURN function, as Girish has done with RETURN(0), the compiler is happy and - better - it runs properly. :D

kcbland · Post by **kcbland** » Tue Jan 27, 2004 10:50 pm

ray.wurlod wrote:
chulett wrote:Also, isn't there an issue with using GOSUB syntax within a Routine? I was under the impression that was a no-no as you tended to exit the routine when the first 'return' was encountered.

Or that the presence of the multiple 'return' statements irritated the compiler...
It is a known glitch in the compiler, but the glitch is actually when you use a RETURN statement that lacks an argument - that is, a RETURN statement rather than a RETURN function.
If you provide a RETURN function, as Girish has done with RETURN(0), the compiler is happy and - better - it runs properly. :D

ehhhh? You can put internal subroutines anywhere, even within functions. Am I missing something? You can have subroutines gosub other subroutines. Am I needing to shutup and to go bed?

chulett · Post by **chulett** » Tue Jan 27, 2004 10:51 pm

Thank you for the clarifications, gentlemen.

And from one stooge to another - yes, Ken, it's bedtime. Night Night.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 27, 2004 10:52 pm

kcbland wrote:
ray.wurlod wrote: However, why not do this with a DataStage job? Read the 13 columns and output one, containing line feeds and repeats of columns 1 and 2. This technique has been discussed in the past, do a search on the Forum. It's exceedingly fast.
He's doing the reverse, 1 row becomes 11. So, a splitter style transformer stage is the appropriate design. (Plus you caught his code error )

Yes, that's what I designed. Forgot to mention that you write it into a sequential file which you then read from.

The output column from Transformer stage is derived as

Code: Select all

c1:",":c2:",":c3:LF:c1:",":c2:",":c4:LF:c1:",":c2:",":c5:LF: ... :LF:c1:",":c2:",":c13

The inputs tab to the sequential file stage specifies 000 as the delimiter character and UNIX style end of line. (OP specified UNIX.)

The outputs tab from the sequential file stage specifies "," as the delimiter character and UNIX style end of line.

Go on, try it! :D

kcbland · Post by **kcbland** » Tue Jan 27, 2004 10:55 pm

ray.wurlod wrote:
kcbland wrote:
ray.wurlod wrote: However, why not do this with a DataStage job? Read the 13 columns and output one, containing line feeds and repeats of columns 1 and 2. This technique has been discussed in the past, do a search on the Forum. It's exceedingly fast.
He's doing the reverse, 1 row becomes 11. So, a splitter style transformer stage is the appropriate design. (Plus you caught his code error )
Yes, that's what I designed. Forgot to mention that you write it into a sequential file which you then read from.

The output column from Transformer stage is derived as
Code: Select all
c1:",":c2:",":c3:LF:c1:",":c2:",":c4:LF:c1:",":c2:",":c5:LF: ... :LF:c1:",":c2:",":c13
The inputs tab to the sequential file stage specifies 000 as the delimiter character and UNIX style end of line. (OP specified UNIX.)

The outputs tab from the sequential file stage specifies "," as the delimiter character and UNIX style end of line.

Go on, try it! :D

fffftt! Brain fart. Got it.