Expert: Jean-Pierre Zuate - 6/6/2008

Question
Jean-Pierre,

You recently told me how to pad the output from a copy table statement. I cannot get the syntax to work, can you take a look and what I am doing wrong. The code is:

procedure create_export_textfile(strChosen =  Array of StringObject default null, EiacCode = VarChar(10) not null) =
declare

  k = smallint not null;
  OutputFile = StringObject;
  OutputString = StringObject;
  XA = StringObject;
  XAHeader = StringObject;
  XASpace = StringObject;
  ErrorStatus = integer not null;
  NameOfTable = char(3) not null;  
  vstring = varchar(3000) NOT NULL;
  fromClause = varchar(18) not null;

enddeclare

BEGIN

  XAHeader = XAHeader.ConcatVarchar(Text = '*');

for k=1 to strchosen.lastrow do

  NameOfTable = strChosen[k].Value;
  fromClause = NameOfTable + ' t';

  Case strChosen[k].value of

  'XA':{  vstring =  'DECLARE GLOBAL TEMPORARY TABLE SESSION.temp ' +
                'AS SELECT ''' +  :nameOfTable + ''' AS fromTable, t.* ' +
                'FROM ' + :fromClause + '' +  
                'WHERE eiacodxa = ''' + :EiacCode + ''' ' +
                'ON COMMIT PRESERVE ROWS WITH NORECOVERY';
                 execute immediate :vstring;
        }

Jean-Pierre,

From what you have said before I think I need to state all the columns in that table i.e. ‘As select from table etc’ then list columns as below. The field altlcnxb I set as shown to make it 2 characters padded at the front so that it will display 00, 01, 02 etc. Then in the copy statement below I am hoping it will copy out the ‘padded’ value for this field. However, I do not understand the syntax, I have tried what is shown below and other configurations, but to no avail, could you give an example of the correct syntax below.

Thanks
Laurence

  'XB':{  vstring =  'DECLARE GLOBAL TEMPORARY TABLE SESSION.temp ' +
                'AS SELECT ''' +  :nameOfTable + ''' AS fromTable, t.* ' + AS eiacodxa ' + + lsaconxb +
right('0' + varchar(altlcnxb),2) as altlcnxb + lcntypxb + lcnindxb + lcnamexb + tmfgcdxb + sysidnxb + secitmxb + ramindxb +
                'FROM ' + :fromClause '' +
                'WHERE eiacodxa = ''' + :EiacCode + ''' ' +
                'ON COMMIT PRESERVE ROWS WITH NORECOVERY';
                 execute immediate :vstring;
        }            

  endCase;

  case strChosen[k].value of

     'XA':{ vstring = 'COPY TABLE SESSION.temp ('    + HC_NEWLINE +  
                      'fromTable    = c0,'    + HC_NEWLINE +
        'eiacodxa   = c0,'    + HC_NEWLINE +   
        'csreorxa   = c0,'         + HC_NEWLINE +          
        'trncstxa   = c0,'         + HC_NEWLINE +
        'wstyaqxa   = c0,'         + HC_NEWLINE +  
        'tsscodxa   = c0, sp=d1)'         + HC_NEWLINE +  

        'INTO  ''c:\ExportTemp\xa.txt'' ';

         execute immediate :vstring;

        OutputFile.FileHandle = 'c:\ExportTemp\xa.txt';        
                     xa = xa.ConcatString(OutputFile);
                     xa = xaheader.ConcatString(OutputFile);
        
  }           

   'XB':{ vstring = 'COPY TABLE SESSION.temp ('       + HC_NEWLINE +  
                      'fromTable    = c0,'             + HC_NEWLINE +
         'eiacodxa   = c0,'      + HC_NEWLINE +  
        'lsaconxb   = c18,'         + HC_NEWLINE +
        'altlcnxb   = c2,'              + HC_NEWLINE +
        'lcntypxb   = c1,'             + HC_NEWLINE +
        'secitmxb   = c0,'            + HC_NEWLINE +
        'ramindxb   = c0nl, sp=d1)'         + HC_NEWLINE +

        'into    ''c:\ExportTemp\xb.txt'' ';

        execute immediate :vstring;
                      OutputFile.FileHandle = 'c:\ExportTemp\xb.txt';
                      xa = xa.ConcatString(OutputFile);  
  }

   endcase;

   DROP TABLE SESSION.temp;

endfor;

  ErrorStatus = xa.WriteToFile(Filename = 'C:XAoutputFile.txt');
        
END  

Answer
Hello,

Considering your code :
'XB':{  vstring =  'DECLARE GLOBAL TEMPORARY TABLE SESSION.temp ' +

               'AS SELECT ''' +  :nameOfTable + ''' AS fromTable, t.* ' + AS eiacodxa ' + + lsaconxb +

right('0' + varchar(altlcnxb),2) as altlcnxb + lcntypxb + lcnindxb + lcnamexb + tmfgcdxb + sysidnxb + secitmxb + ramindxb +

               'FROM ' + :fromClause '' +

               'WHERE eiacodxa = ''' + :EiacCode + ''' ' +

               'ON COMMIT PRESERVE ROWS WITH NORECOVERY';

I see here many syntax issues :
1/ you could not use the star (*) you must write the name of each columns (see in my previous example)
2/ AS eiacodxa ' + + lsaconxb + : contain 2 + sign with nothing inside

During your test, please do the following :
- print vstring before each execution
- if your program go down with an SQL error you will be more comfortable to correct syntax issue

Padding explanation
The SQL expression : right('0' + varchar(altlcnxb),2)
Let's have a look of separate functions :
1/ varchar(altlcnxb) : convert your numeric data into varchar
2/ '0' + : to add the first zero if altlcnxb is less equal to 9
3/ right(expression,2) : extract the 2 rightest caracters (or the two last) of the chain given by the expression "'0' + varchar(altlcnxb)"

Examples
* altlcnxb 1 digit (altlcnxb = 9)
1/ varchar(altlcnxb) = '9'
2/ '0' + varchar(altlcnxb) = '0' + '9' = '09'
3/ right('0' + varchar(altlcnxb),2) = right('0' + '09',2) = '09'
=> '9' has been padded !

* altlcnxb 2 digits (altlcnxb = 11)
1/ varchar(altlcnxb) = '11'
2/ '0' + varchar(altlcnxb) = '0' + '11' = '011'
3/ right('0' + varchar(altlcnxb),2) = right('0' + '011',2) = '11'
=> '11' do not need to be padded, so the 2 last position represent your number ...

Hope this help.

Best regards