0

For every table record I must find/check that record in second table and based on substring condition put results back in one field separated with delimiter if more then one.

table2->someRef always start with two char + something else like two numbers up to 14 or some string

function GetMeResult(param_ID) is working but it is very slow as using cursor

Would be great if this can be done with some join or some subquery or concat etc

Example tables:

Table1 
------------------------
ID        Field1 Field2
-------------------------
100       text1  text2

Table2
=======================================
table2_ID, someCode, someRef
---------------------------------------
100        110       SB
100        222       SB1
100        150       SB14
100        200       PB9

Function GetMeResult(param_ID)
BEGIN
  DECLARE resultSomeCode VARCHAR(100);
  DECLARE tmpSomeCode VARCHAR(4);
  DECLARE delimeter VARCHAR(2);

  DECLARE tmpCur CURSOR FOR Stmt;

  set delimeter =', ';

  PREPARE Stmt FROM 'SELECT someCode FROM Table2 
  WHERE table2_ID=? and substring(table2.someRef, 3, 2) in   (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'',''10'',''11'',''12'',''13'',''14'')'; 

     OPEN tmpCur USING param_ID;


      FETCH FIRST FROM tmpCur('someCode') INTO resultSomeCode ;
      WHILE NOT EOF(tmpCur) DO
         if length(tmpSomeCode)>0 then
           set resultSomeCode = resultSomeCode + delimeter + tmpSomeCode;
         end if;

         FETCH NEXT FROM tmpCur('someCode') INTO tmpSomeCode;
      END WHILE;

      CLOSE tmpCur;

      RETURN resultSomeCode;

END

USAGE:

SELECT ID, Field1, Field2, GetMeResult(ID) as SomeNameForField from Table1 

Result should be like 
======================
ID   Field1  Field2  SomeNameForField
-------------------------------------
100, text1,  text2,  '222, 150, 200'

P.S. field and other names are just for example purpose :)

Thank you

Kind Regards

TK Zan
  • 35
  • 3
  • Don't know this ElevateDb DBMS, but maybe something could be done with the [LIST](https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=LIST) aggregate function? – LukStorms Nov 09 '19 at 10:28
  • Yup @LukStorms … you know saying "Can’t see the forest for the trees" . The LIST function will do the trick, just need small adaption as LIST need GROUP BY .Thank you – TK Zan Nov 11 '19 at 11:35
  • Glad that it helped. Well, such function also exists in some other DBMS, but each has a different name. There's no standard for that one. The problem with giving a solution for a DBMS like ElevateDb is that there's no online tester that supports it. And writing some SQL that could work in theory, but that one can't actually test, it just doesn't feel right. So it's harder to get good answers for the less popular ElevateDb. – LukStorms Nov 11 '19 at 11:50

0 Answers0