	AllExperts > Ingres
	Search

Volunteer
Answers to thousands of questions

Home · More Ingres Questions · Answer Library

· Encyclopedia ·

Ingres - SQL in Ingres program

Expert: Melvin Davidson - 6/17/2008

Question
Hello Melvin,
I have a form in openroad that is basically a list of check boxes that correspond to database tables. In my problem the user selects XA and HA from the form. (There are about 40 tables in all so I only show a few in the code below !!)
The Create_Export_TextFile uses 2 case statements controlled by the tablenames chosen by the user (say XA and HA in this case). The first case sets up the vstring command and the second case uses the vstring in the copy table setup. My problem is when processing takes place for the HA table in the first case statement I ONLY WANT THOSE RECORDS IN 'HA' that correspond to records in 'HG' that have the SAME code and ref number, i.e. the WHERE caluse - I cannot get the WHERE clause to work!!!. I keep getting and error shown in the error log below. I hope you can help.
Best Wishes
Laurence

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

  k = smallint not null;

  OutputFile = StringObject;
  OutputString = StringObject;
  Report = StringObject;

  XA = StringObject;
  XAHeader = StringObject;
  ErrorStatus = integer not null;
  NameOfTable = char(3) not null;
  vstring = varchar(3000) NOT NULL;
  fromClause = varchar(18) not null;
  fromClauset = varchar(18) not null;

enddeclare

BEGIN

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

for k=1 to strchosen.lastrow do

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

  Case strChosen[k].value of

  'HA':{    vstring = 'DECLARE GLOBAL TEMPORARY TABLE SESSION.temp ' +
                'AS SELECT ''' + :nameOfTable + ''' AS fromTable cagecdxh, refnumha, itnameha, inamecha, refnccha, refnvcha, dlscrcha, docidcha, itmmgcha, cognsnha, smmnsnha, matnsnha, fscnsnha, ninnsnha, actnsnha, uiconvha, shlifeha, slactnha, ppslstha, docavcha, prdldtha, spmaccha, smaincha, critcdha, pricodha, saipcdha, aaplccha, bbplccha, ccplccha, ddplccha, eeplccha, ffplccha, ggplccha, hhplccha, jjplccha, kkplccha, llplccha, mmplccha, physecha, adpeqpha, demiliha, acqmetha, amsufcha, hmscosha, hwdcosha, hwscosha, cticodha, uweighha, ulengtha, uwidthha, uheighha, hazcodha, unitmsha, unitisha, linnumha, crititha, indmathha, mtleadha, mtlwgtha, materlha ' +
                'FROM ' + :fromClause + ' ' +
                'WHERE hg.cagecdxh = ha.cagecdxh and hg.refnumha = ha.refnumha and hg.eiacodxa = Eiacodxa ' +
                'ON COMMIT PRESERVE ROWS WITH NORECOVERY';
                 execute immediate :vstring;
        }

  'HG':{ vstring = 'DECLARE GLOBAL TEMPORARY TABLE SESSION.temp ' +
                'AS SELECT ''' + :nameOfTable + ''' AS fromTable, cagecdxh, refnumha, eiacodxa, lsaconxb, right(''0''+varchar(altlcnxb),2) AS altlcnxb, lcntypxb, plisnohg, qtyasyhg, supindhg, dataschg, prosichg, lliptdhg, pplptdhg, sfpptdhg, cblptdhg, rilptdhg, islptdhg, pclptdhg, ttlptdhg, scpptdhg, araptdhg, arbptdhg, toccodhg, indcodhg, qtypeihg, piplishg, saplishg, hardcihg, remipihg, lrunithg, itmcathg, esscodhg, smrcodhg, mrronehg, mrrtwohg, mrrmodhg, ortdoohg, frtdffhg, hrtdhhhg, lrtdllhg, drtdddhg, minreuhg, maotimhg, maiacthg, rissbuhg, rmsslihg, rtllqthg, totqtyhg, omtdoohg, fmtdffhg, hmtdhhhg, lmtdllhg, dmtdddhg, cbdmtdhg, cadmtdhg, orctoohg, frctffhg, hrcthhhg, lrctllhg, drctddhg, conrcthg, noretshg, repsurhg, drponehg, drptwohg, wrkucdhg, allowchg, aliqtyhg ' +
                'FROM ' + :fromClause + ' ' +
                'WHERE eiacoder = ''' + :EiacCode + ''' ' +
                'ON COMMIT PRESERVE ROWS WITH NORECOVERY';
                 execute immediate :vstring;
        }

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

  endCase;

  case strChosen[k].value of

   'HA':{ vstring = 'COPY TABLE SESSION.temp ('   + HC_NEWLINE +
                       'fromTable = c0,' + HC_NEWLINE +
        'cagenum   = c0,' + HC_NEWLINE +
        'refnum      = c0,' + HC_NEWLINE +
        'code1      = c0,' + HC_NEWLINE +
        'code2      = c0,' + HC_NEWLINE +
        'code3      = c6,' + HC_NEWLINE +
        'code4   = c0nl, sp=d1)'   + HC_NEWLINE +

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

   'HG':{ vstring = 'COPY TABLE SESSION.temp (' + HC_NEWLINE +
                       'fromTable = c0,' + HC_NEWLINE +
        'cagenum   = c0,' + HC_NEWLINE +
        'refnum      = c0,' + HC_NEWLINE +
        'eiacoder = c0,' + HC_NEWLINE +
        'allowpat = c0,' + HC_NEWLINE +
        'allowqty = d0nl, sp=d1)' + HC_NEWLINE +

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

     'XA':{ vstring = 'COPY TABLE SESSION.temp ('   + HC_NEWLINE +
                       'fromTable = c0,' + HC_NEWLINE +
        'eiacoder = c0,' + HC_NEWLINE +
        'lassert   = c0,' + HC_NEWLINE +
        'doninit   = c2,' + HC_NEWLINE +
        'pasterr   = c0,' + HC_NEWLINE +
        'tedopya   = 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);
  }

   endcase;

   DROP TABLE SESSION.temp;

endfor;

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

END

       RETURN = i4 not null, value '1'
       struserid = varchar(30) not null, value 'appdev9a2', BYREF

Query text:
select distinct eiacodxa as enumtext, rowid as introwid from xa order by 1
Query Send Time:       Fri Jun 13 09:56:51 2008
Query Response Time:   Fri Jun 13 09:56:51 2008
Response CPU Time:     6479
Qry End CPU Time:      6479
------------------------------------------------

Call 4GL procedure 'check_db'

Query text:
commit
Query Send Time:       Fri Jun 13 09:56:51 2008
Query Response Time:   Fri Jun 13 09:56:51 2008
Qry End CPU Time:      6479
------------------------------------------------

Call 4GL procedure 'check_commit'
Return from 'check_commit' to 'check_db'
       RETURN = i4 not null, value '0'
Return from 'check_db' to 'dammyall'
       RETURN = i4 not null, value '30'

Query text:
select eiacodxa as streiacodxa from xa where EXISTS(select * from xc where xa
.eiacodxa=xc.eiacodxa and uppercase(xc.lcntypxb)='P') order by streiacodxa
Query Send Time:       Fri Jun 13 09:56:51 2008
Query Response Time:   Fri Jun 13 09:56:52 2008
Response CPU Time:     6479
Qry End CPU Time:      6479
------------------------------------------------

Event: SetValue on 'lbxeiacodxa' in frame 'dammyall'
Event Handled
Event: Click on 'pbtselectsubtables' in frame 'dammyall'
Event Handled
Event: POPUP in frame 'dammyall'
Event Handled
Event: POPUP in frame 'dammyall'

Call 4GL procedure 'create_export_textfile'
       eiaccode = varchar(10) not null, value 'PUMAMK1'
       strchosen = Array of stringobject

Query text:
execute immediate DECLARE GLOBAL TEMPORARY TABLE SESSION.temp AS SELECT 'XA '
  AS fromTable, t.* FROM XA t              WHERE eiacodxa = 'PUMAMK1   ' ON C
OMMIT PRESERVE ROWS WITH NORECOVERY
Query Send Time:       Fri Jun 13 09:57:15 2008
Query Response Time:   Fri Jun 13 09:57:15 2008
Qry End CPU Time:      8802
------------------------------------------------
Query text:
execute immediate COPY TABLE SESSION.temp (
fromTable .= c0,
eiacodxa..= c0,
lcnstrxa..= c0,
addltmxa..= c2,
ctdltmxa..= c3,
contnoxa..= c19,
csreorxa..= c0,
csprrqxa..= c0,
demilcxa..= c2,
discntxa..= c0,
essalvxa..= c2,
hlcspcxa..= c2,
intbinxa..= c4,
incatcxa..= c4,
intratxa..= c0,
invstgxa..= c0,
lodfacxa..= c0,
wsoplvxa..= c2,
oprlifxa..= c2,
prstovxa..= c2,
prstomxa..= c2,
profacxa..= c0,
rcbincxa..= c4,
rccatcxa..= c4,
restcrxa..= c2,
saflvlxa..= c2,
secsfcxa..= c0,
trncstxa..= c0,
wstyaqxa..= c0,
tsscodxa..= c0, sp=d1)
INTO 'c:\ExportTemp\xa.txt'
Query Send Time:       Fri Jun 13 09:57:15 2008
Query text:
execute immediate COPY TABLE SESSION.temp (
fromTable .= c0,
eiacodxa..= c0,
lcnstrxa..= c0,
addltmxa..= c2,
ctdltmxa..= c3,
contnoxa..= c19,
csreorxa..= c0,
csprrqxa..= c0,
demilcxa..= c2,
discntxa..= c0,
essalvxa..= c2,
hlcspcxa..= c2,
intbinxa..= c4,
incatcxa..= c4,
intratxa..= c0,
invstgxa..= c0,
lodfacxa..= c0,
wsoplvxa..= c2,
oprlifxa..= c2,
prstovxa..= c2,
prstomxa..= c2,
profacxa..= c0,
rcbincxa..= c4,
rccatcxa..= c4,
restcrxa..= c2,
saflvlxa..= c2,
secsfcxa..= c0,
trncstxa..= c0,
wstyaqxa..= c0,
tsscodxa..= c0, sp=d1)
INTO 'c:\ExportTemp\xa.txt'
Query Send Time:       Fri Jun 13 09:57:15 2008
Query Response Time:   Fri Jun 13 09:57:16 2008
Qry End CPU Time:      8802
------------------------------------------------
Query text:
drop table session.temp
Query Send Time:       Fri Jun 13 09:57:16 2008
Query Response Time:   Fri Jun 13 09:57:17 2008
Qry End CPU Time:      8802
------------------------------------------------
Query text:
execute immediate DECLARE GLOBAL TEMPORARY TABLE SESSION.temp AS SELECT 'HA '
  AS fromTable, t* FROM HA t              WHERE ha.cagecdxh = hg.cagecdxh and
  ha.refnumha = hg.refnumha ON COMMIT PRESERVE ROWS WITH NORECOVERY
Query Send Time:       Fri Jun 13 09:57:17 2008

E_US0F06 line 1, Syntax error on 'FROM'. The correct syntax is:
   DECLARE GLOBAL TEMPORARY TABLE tablename
      (columnname format [null_default_clause] {, ... })
      ON COMMIT PRESERVE ROWS
      WITH NORECOVERY [,with_clause]

    DECLARE GLOBAL TEMPORARY TABLE tablename
      [(columnname {, ... })]
      AS subselect
      ON COMMIT PRESERVE ROWS
  WITH NORECOVERY [,with_clause]
   (Fri Jun 13 09:57:18 2008)

Query Response Time:   Fri Jun 13 09:57:25 2008
Qry End CPU Time:      8812
------------------------------------------------
Query text:
execute immediate COPY TABLE SESSION.temp (
fromTable .= c0,
cagecdxh.= c0,
refnumha..= c0,
itnameha..= c0,
inamecha.= c5,
refnccha..= c0,
refnvcha..= c1,
dlscrcha..= c0,
docidcha..= c0,
itmmgcha..= c0,
cognsnha.= c0,
smmnsnha.= c0,
matnsnha.= c0,
fscnsnha..= c4,
niinsnha..= c0,
actnsnha..= c0,
uiconvha..= c5,
shlifeha..= c0,
slactnha..= c0,
ppslstha..= c0,
docavcha.= c0,
prdldtha..= c2,
spmaccha.= c0,
smaincha..= c0,
critcdha..= c0,
pmicodha.= c0,
saipcdha..= c0,
aaplccha..= c0,
bbplccha..= c0,
ccplccha..= c0,
ddplccha..= c0,
eeplccha..= c0,
ffplccha..= c0,
ggplccha..= c0,
hhplccha..= c0,
jjplccha..= c0,
kkplccha..= c0,
llplccha..= c0,
mmplccha.= c0,
physecha.= c0,
adpeqpha.= c1,
demiliha..= c0,
acqmetha.= c1,
amsufcha.= c0,
hmscosha.= c8,
hwdcosha.= c8,
hwscosha.= c8,
cticodha..= c0,
uweighha.= c0,
ulengtha..= c0,
uwidthha..= c0,
uheighha..= c0,
hazcodha.= c0,
unitmsha..= c0,
unitisha..= c0,
linnumha..= c0,
crititha..= c0,
indmatha..= c0,
mtleadha..= c3,
mtlwgtha..= c6,
materlha..= c0nl, sp=d1)
into .'c:\ExportTemp\ha.txt'
Query Send Time:       Fri Jun 13 09:57:25 2008

E_US0845 Table 'temp' does not exist or is not owned by you.
   (Fri Jun 13 09:57:27 2008)

Query Response Time:   Fri Jun 13 09:57:27 2008
Qry End CPU Time:      8812
------------------------------------------------
Query text:
drop table session.temp
Query Send Time:       Fri Jun 13 09:58:12 2008

E_US0ACC DROP: 'temp' is not owned by $Sess00000004.
   (Fri Jun 13 09:58:13 2008)

Query Response Time:   Fri Jun 13 09:58:19 2008
Qry End CPU Time:      10064
------------------------------------------------

Return from 'create_export_textfile' to 'dammyall'

Event Handled

Answer
Laurence,

The error that occurs is:
E_US0F06 line 1, Syntax error on 'FROM'. The correct syntax is:
  DECLARE GLOBAL TEMPORARY TABLE tablename
     (columnname format [null_default_clause] {, ... })
     ON COMMIT PRESERVE ROWS
     WITH NORECOVERY [,with_clause]
and has nothing to do with the WHERE clause. It does, however,
prevent the GTT from being created.

It is stating that the syntax for creating GLOBAL TEMP TABLE is incorrect.

Your statement basically is

'AS SELECT '''    :nameOfTable   ''' AS fromTable cagecdxh, ......

However, this roughly translates to
AS SELECT some_table_name AS fromTable cagecdxh, ......

The occurrence of a second alias after fromTable is what appears to be
the problem.

It looks like you have omitted a comma and it should be

'AS SELECT '''    :nameOfTable   ''' AS fromTable, cagecdxh, ......

instead.

Please correct the syntax, and your code should work. I would also review the other CASE statements for similar errors.

Regards,
Melvin

Add to this Answer Ask a Question