1 2 Previous Next 15 Replies Latest reply on Oct 19, 2018 1:11 PM by Steven Hawkins

    store procedure teiid

    francesco angri Newbie

      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

        • 1. Re: store procedure teiid
          Ramesh Reddy Master

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

           

          Virtual Procedures · GitBook

          • 2. Re: store procedure teiid
            francesco angri Newbie

            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

            • 3. Re: store procedure teiid
              Steven Hawkins Master

              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 ..
              • 4. Re: store procedure teiid
                francesco angri Newbie

                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

                • 5. Re: store procedure teiid
                  francesco angri Newbie

                  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

                  • 6. Re: store procedure teiid
                    Steven Hawkins Master

                    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.

                    • 7. Re: store procedure teiid
                      Steven Hawkins Master

                      > 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.

                      • 8. Re: store procedure teiid
                        francesco angri Newbie

                        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

                        • 10. Re: store procedure teiid
                          francesco angri Newbie

                          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

                          • 11. Re: store procedure teiid
                            Steven Hawkins Master

                            > 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.

                             

                             

                            • 12. Re: store procedure teiid
                              francesco angri Newbie

                              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?

                              • 13. Re: store procedure teiid
                                Steven Hawkins Master

                                > 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.

                                • 14. Re: store procedure teiid
                                  francesco angri Newbie

                                  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

                                  1 2 Previous Next