store procedure teiid

[glprobot]

Good morning,

STORE SQL PROCEDURES

I have three tables:

the first table has the file name:

FILE table:

fileid

first name

DATE

OUTCOME

the second table has the name of the detail:

DETAIL table:

IDDETTAGLIO

FIRST NAME

SURNAME

OUTCOME

FILE_FK

the third table has the public name:

PUBLIC table

IDPUBBLICA

FIRST NAME

SURNAME

I want to create a store procedure in the following ways:

the FILE table has a 1 to N relation of the DETAIL table

in practice, once the data have been entered in the detail table, with insert

in the file table there is the column called the result, this column if it is ok, checking that all the records are inserted in the detail table, ie the positive result is the insert in the public table

otherwise in the detail table if the result is negative ie some record has not been inserted into the table this column is ko and does not insert into the public table

In the public table, having the record called the result

all those who were successful posito ie ok does the insert in the PUBLIC table

I hope I explained myself better

How you do it?

Thank you

[rareddy]

Here is the document to the procedure language syntax, read this and write accordingly for your usecase Procedure Language · GitBook

Virtual Procedures · GitBook

[glprobot]

can you check where I'm wrong?

can you correct me?

BEGIN

DECLARE integer VARIABLES.iddettaglioTemp;

DECLARE string VARIABLES.statoTemp;

DECLARE integer VARIABLES.codhnTemp;

DECLARE integer VARIABLES.partitaivacfTemp;

DECLARE string VARIABLES.ragionesocialeTemp;

DECLARE string VARIABLES.indirizzoTemp;

DECLARE string VARIABLES.comuneTemp;

DECLARE string VARIABLES.provTemp;

DECLARE integer VARIABLES.capTemp;

DECLARE double VARIABLES.latitudineTemp;

DECLARE double VARIABLES.longitudineTemp;

DECLARE string VARIABLES.regioneTemp;

DECLARE integer VARIABLES.telefonoTemp;

DECLARE integer VARIABLES.faxTemp;

DECLARE string VARIABLES.emailTemp;

DECLARE string VARIABLES.esitoTemp;

DECLARE integer VARIABLES.file_fkTemp;

BEGIN

SELECT * FROM target.dettaglio tabellaDettaglio INNER JOIN target.file  tabellaFile ON tabellaDettaglio.file_fk = tabellaFile.idfile WHERE tabellaDettaglio.esito = VARIABLES.esitoTemp;

BEGIN

IF(tabellaDettaglio.iddettaglio != IS NULL && tabellaDettaglio.stato != IS NULL && tabellaDettaglio.codhn != IS NULL && tabellaDettaglio.partitaivacf != IS NULL && tabellaDettaglio.ragionesociale != IS NULL && tabellaDettaglio.indirizzo != IS NULL && tabellaDettaglio.comune != IS NULL && tabellaDettaglio.prov != IS NULL && tabellaDettaglio.cap != IS NULL && tabellaDettaglio.latitudine != IS NULL && tabellaDettaglio.longitudine != IS NULL && tabellaDettaglio.regione != IS NULL && tabellaDettaglio.telefono != IS NULL && tabellaDettaglio.fax != IS NULL && tabellaDettaglio.email != IS NULL && tabellaDettaglio.esito != IS NULL && tabellaDettaglio.file_fk != IS NULL)

IF(VARIABLES.esitoTemp == 'OK')

INSERT INTO target.published tabellaPublished (tabellaPublished.idpublished, tabellaPublished.stato,tabellaPublished.codhn,tabellaPublished.partitaivacf,tabellaPublished.ragionesociale,tabellaPublished.indirizzo,tabellaPublished.comune,tabellaPublished.prov,tabellaPublished.cap,tabellaPublished.latitudine,tabellaPublished.longitudine,tabellaPublished.regione,tabellaPublished.telefono,tabellaPublished.fax,tabellaPublished.email) VALUES(VARIABLES.iddettaglioTemp,VARIABLES.statoTemp, VARIABLES.codhnTemp,VARIABLES.partitaivacfTemp,  VARIABLES.ragionesocialeTemp, VARIABLES.indirizzoTemp,  VARIABLES.comuneTemp,  VARIABLES.provTemp, VARIABLES.capTemp,  VARIABLES.latitudineTemp, VARIABLES.longitudineTemp, VARIABLES.regioneTemp, VARIABLES.telefonoTemp,VARIABLES.faxTemp,  VARIABLES.emailTemp );

END ELSE

ERROR "tabella published esito KO, IL RECORD ha il VALORE null";

END

END

END

[shawkins]

It looks like you want to loop on the select, then filter and do an insert.  The basic structure for that is:

loop on (select ...) as my_cursor
begin
  if (condition)
  begin
    insert ...
  end
end

Unless you need the non-passing rows, it would be better to move the filtering onto the loop query itself:

loop on (select ... where condition) as my_cursor
begin
  insert ...
end

You have no assignment of values to your declared variables.  If they are just the same as the cursor column variables, then you omit using the variables entirely:

loop on (select ... where condition) as my_cursor
begin
  INSERT INTO target.published tabellaPublished (tabellaPublished.idpublished, ...) VALUES(my_cursor.iddettaglio ...)
end

Finally if that is ultimately all you need to do in this procedure, then it's actually expressible by just a single insert statement:

INSERT INTO target.published tabellaPublished (tabellaPublished.idpublished, ...) 
   SELECT iddettaglio, ... FROM target.dettaglio tabellaDettaglio INNER JOIN target.file tabellaFile ON tabellaDettaglio.file_fk = tabellaFile.idfile WHERE tabellaDettaglio.esito = 'OK' AND  tabellaDettaglio.iddettaglio != IS NULL AND ..

[glprobot]

thank you that you answered me

is it okay like the script?

can you correct me?

begin

DECLARE integer VARIABLES.iddettaglioTemp;

DECLARE string VARIABLES.statoTemp;

DECLARE integer VARIABLES.codhnTemp;

DECLARE integer VARIABLES.partitaivacfTemp;

DECLARE string VARIABLES.ragionesocialeTemp;

DECLARE string VARIABLES.indirizzoTemp;

DECLARE string VARIABLES.comuneTemp;

DECLARE string VARIABLES.provTemp;

DECLARE integer VARIABLES.capTemp;

DECLARE double VARIABLES.latitudineTemp;

DECLARE double VARIABLES.longitudineTemp;

DECLARE string VARIABLES.regioneTemp;

DECLARE integer VARIABLES.telefonoTemp;

DECLARE integer VARIABLES.faxTemp;

DECLARE string VARIABLES.emailTemp;

DECLARE string VARIABLES.esitoTemp;

DECLARE integer VARIABLES.file_fkTemp;

loop on (SELECT * FROM target.dettaglio tabellaDettaglio INNER JOIN target.file  tabellaFile ON tabellaDettaglio.file_fk = tabellaFile.idfile) as my_cursor 

begin 

  if (tabellaDettaglio.esito = 'OK') 

  begin 

   INSERT INTO target.published tabellaPublished (tabellaPublished.idpublished, tabellaPublished.stato,tabellaPublished.codhn,tabellaPublished.partitaivacf,tabellaPublished.ragionesociale,tabellaPublished.indirizzo,tabellaPublished.comune,tabellaPublished.prov,tabellaPublished.cap,tabellaPublished.latitudine,tabellaPublished.longitudine,tabellaPublished.regione,tabellaPublished.telefono,tabellaPublished.fax,tabellaPublished.email) VALUES(VARIABLES.iddettaglioTemp,VARIABLES.statoTemp, VARIABLES.codhnTemp,VARIABLES.partitaivacfTemp,  VARIABLES.ragionesocialeTemp, VARIABLES.indirizzoTemp,  VARIABLES.comuneTemp,  VARIABLES.provTemp, VARIABLES.capTemp,  VARIABLES.latitudineTemp, VARIABLES.longitudineTemp, VARIABLES.regioneTemp, VARIABLES.telefonoTemp,VARIABLES.faxTemp,  VARIABLES.emailTemp );

  end

else

ERROR "tabella published esito KO, IL RECORD ha il VALORE null";

end

end

[glprobot]

all the records in the detail table are missing.

I'll explain:

Check whether all records are entered in the detail table 2) If in the detail table the records are all filled up, insert into the public table 3) in the public table and detail table, the state name record becomes OK and the file name status record becomes OK 4) If in the detail table some records have a null value or empty field it does not insert into the public table 5) update a new status of the file table is the status becomes KO

[shawkins]

You are declaring variables and not assigning anything to them.  What is the intent for example of VARIABLES.iddettaglioTemp?

In the block associated with the loop, you have access to the columns of the loop query on the loop cursor variable - my_cursor.xxx

Where you have:

if (tabellaDettaglio.esito = 'OK')

You probably mean:

if (my_cursor.esito = 'OK')

What you have now reads as:

for each row from the join between tabellaDettaglio and tabellaFile if esito is 'OK', then insert a row into tabellaPublished esito is not 'OK', then raise an error. 

That seems a bit odd as you could process any number of inserts and then raise an error.

I also made a copy paste error in my last response.  A not null check is EXPRESSION IS NOT NULL, rather than EXPRESSION != IS NULL that you originally had.

[shawkins]

> all the records in the detail table are missing.

I would suggest not starting with the procedure sql and instead continue to write out in pseudo code exactly what you expect.  Once you have that expressed, it will be straight-forward to translate from there into procedure sql.

[glprobot]

DECLARE integer VARIABLES.iddettaglioTemp;

DECLARE stringa VARIABLES.statoTemp;

I want these variables and others to match the table record if you can do it.

that is

DECLARE integer VARIABLES.iddettaglioTemp = select iddettaglio from detail;

the same things as for other variables I have listed.

so when I do the insert into published (idpublished, state .....) values (VARIABLES.iddettaglioTemp, ..........)

since there are three tables

the detail table that was successful ok after a check of all the records goes to the published table

who has not passed the control of all the records that only a single null record is enough does not make the insert in the published table

[glprobot]

[glprobot]

BEGIN

DECLARE integer VARIABLES.iddettaglioTemp = (SELECT iddettaglio FROM target.dettaglio);

DECLARE string VARIABLES.statoTemp = (SELECT stato FROM target.dettaglio);

DECLARE integer VARIABLES.codhnTemp = (SELECT codhn FROM target.dettaglio);

DECLARE integer VARIABLES.partitaivacfTemp = (SELECT partitaivacf FROM target.dettaglio);

DECLARE string VARIABLES.ragionesocialeTemp = (SELECT ragionesociale FROM target.dettaglio);

DECLARE string VARIABLES.indirizzoTemp = (SELECT indirizzo FROM target.dettaglio);

DECLARE string VARIABLES.comuneTemp = (SELECT comune FROM target.dettaglio);

DECLARE string VARIABLES.provTemp = (SELECT prov FROM target.dettaglio);

DECLARE integer VARIABLES.capTemp = (SELECT cap FROM target.dettaglio);

DECLARE double VARIABLES.latitudineTemp = (SELECT latitudine FROM target.dettaglio);

DECLARE double VARIABLES.longitudineTemp = (SELECT longitudine FROM target.dettaglio);

DECLARE string VARIABLES.regioneTemp = (SELECT regione FROM target.dettaglio);

DECLARE integer VARIABLES.telefonoTemp = (SELECT telefono FROM target.dettaglio);

DECLARE integer VARIABLES.faxTemp = (SELECT fax FROM target.dettaglio);

DECLARE string VARIABLES.emailTemp = (SELECT email FROM target.dettaglio);

DECLARE string VARIABLES.esitoTemp = (SELECT esito FROM target.dettaglio);

DECLARE integer VARIABLES.file_fkTemp = (SELECT file_fk FROM target.dettaglio);

LOOP ON (SELECT * FROM target.dettaglio AS tabellaDettaglio INNER JOIN target.file AS tabellaFile ON tabellaDettaglio.file_fk = tabellaFile.idfile) AS my_cursor

BEGIN

IF(my_cursor.esito = 'OK')

BEGIN

INSERT INTO target.published (idpublished, stato, codhn, partitaivacf, ragionesociale, indirizzo, comune, prov, cap, latitudine, longitudine, regione, telefono, fax, email) VALUES (VARIABLES.iddettaglioTemp, VARIABLES.statoTemp, VARIABLES.codhnTemp, VARIABLES.partitaivacfTemp, VARIABLES.ragionesocialeTemp, VARIABLES.indirizzoTemp, VARIABLES.comuneTemp, VARIABLES.provTemp, VARIABLES.capTemp, VARIABLES.latitudineTemp, VARIABLES.longitudineTemp, VARIABLES.regioneTemp, VARIABLES.telefonoTemp, VARIABLES.faxTemp, VARIABLES.emailTemp);

END

ELSE

BEGIN

RAISE SQLEXCEPTION "tabella published esito KO, IL RECORD ha il VALORE null";

END

END

END

[shawkins]

> DECLARE integer VARIABLES.iddettaglioTemp = (SELECT iddettaglio FROM target.dettaglio);

That is a scalar subquery.  If there is more than one row in dettaglio that will fail.

> so when I do the insert into published (idpublished, state .....) values (VARIABLES.iddettaglioTemp, ..........)

Again in the scope of a cursor loop you have access to the cursor row variables.

To make process on the pseudo code, here's what I understand so far:

For each file

  Check whether all records are entered in the detail table

  If in the detail table the records are all filled up

      insert into the public table

      in the public table and detail table, the state name record becomes OK and the file name status record becomes OK

  If in the detail table some records have a null value or empty field it does not insert into the public table

      update a new status of the file table is the status becomes KO

You should correct and expand on this until you have it clearly expressed in terms of standard constructs (loop, if, etc.) and sql statements.  Translating from there into the appropriate procedure sql will be straight-forward.

[glprobot]

For each file

  Check whether all records are entered in the detail table

  If in the detail table the records are all filled up

      insert into the public table

      in the public table and detail table, the state name record becomes OK and the file name status record becomes OK

  If in the detail table some records have a null value or empty field it does not insert into the public table

      update a new status of the file table is the status becomes KO

yes, that's what I want to do.

How you do it?

can you help me?

since I'm not good at pl sql?

[shawkins]

> since I'm not good at pl sql?

You don't have to be, just keep expanding on the pseudo code:

Are you really doing this for all files, or will the be a parameter(s) to control which files?

What does it mean to check whether all records are entered in the detail table?

Does that mean the same thing as "If in the detail table the records are all filled up" or is that an additional check?

etc.

If you can fully specify what you are doing, then you'll be able to translate that into procedure sql. 

For example "For each file" becomes:

loop on (select * from target.file) as file_cursor

begin

  ...

end

That gives you access to file_cursor.column variables in the block to do whatever you need.

[glprobot]

I'm doing it only for the CSV file.

did I show you the diagram of my tables? did you see it?

I do not need the parameters.

Because every time I upload the csv file.

upload the data in the detail table and all that we have said before so far.

How you do it?

explain to me in code, giving an example of the whole question