SAP Syclo Agentry + Oracle Backend (Complex Table)

[Former Member]

Hello All , I am working on syclo agentry frontend and oracle as backend . But when i try to write query for complex table then its throwing error like Error 931 : ORA-00931 : missing identifier My query syntax is below : << if rebuild " select (first select query) " " Insert (second insert query) " else " select (first select query) " " Insert (second insert query) " >> When i run first time then if part is working but second time onwards after else its throwing error Error 931 : ORA-00931 : missing identifier But same query is working in MS SQL backend can you suggest ? What are need to change in syntex as oracle backend ? Thanks & Regards, Kunal Varaiya

[jason_latko · Accepted solution]

Kunal,

MS SQL and Oracle SQL syntax can be different depending on your query.  You don't need to change the Agentry flunky syntax tags at all when moving between databases, but you do need to make sure that when Agentry expands your flunkies, you have a valid query that will run against your specific database.  Turn on logging and examine the server or user or backend log to see the actual query that Agentry was attempting to send to Oracle.  It will probably be obvious what is wrong.  Try copying the query out of the log and running it through an Oracle client to debug the query if the syntax problem in not obvious.

Jason Latko - Senior Product Developer at SAP

[Former Member · Accepted solution]

Hell Jason , We checked query is working fine in backend but i think error in SDML tags . I am getting error like : Query : ComplexTable "ctperson" data else : 3 lines 0 non-priinting characters 78 total characters 3: SA_DBMS_API_ERROR, vendor says : > Error 931: ORA-00931 : missing identifier Thanks & Regards, Kunal Varaiya

[jason_latko · Accepted solution]

Kunal,

Yes looks like your SDML tags are wrong, somewhere around your else clause.  Unfortunately, there is no good way to debug other than to keep trying different things.  There is no syntax checker for SDML tags.  A single space in the wrong spot can break SDML and make it difficult to spot.  Please post your query here and maybe the SDML gurus here can identify the problem.

Jason Latko - Senior Product Developer at SAP

[Former Member · Accepted solution]

Hello Jason , Query is below : <> Thanks & Regards,   Kunal Varaiya

[Former Member · Accepted solution]

Hello Jason , I  sent query , but it looks like other can not see. did u get it ? Thanks & Regards, Kunal Varaiya

[jason_latko · Accepted solution]

Kunal,

Just copy and paste the ascii text for the query into a reply to this post.

Jason Latko - Senior Product Developer at SAP

[Former Member · Accepted solution]

<>

[Former Member · Accepted solution]

if rebuild select p.PERSONID,p.DISPLAYNAME from MAXIMO.person p ,MAXIMO.maxuser u where p.status='ACTIVE' and p.personid=u.personid Insert into maximo.SycloExchangeComplexTable (ID, SERVERID,TBNAME ) select p.PERSONID,'<>','PERSON' from MAXIMO.person p ,MAXIMO.maxuser u where p.status='ACTIVE' and p.personid=u.personid and not exists ( Select ID from maximo.SycloExchangeComplexTable CT where SERVERID = '<>' and CT.ID =  p.personid ) else select p.PERSONID,p.DISPLAYNAME from MAXIMO.person p ,MAXIMO.maxuser u where p.status='ACTIVE' and p.personid=u.personid and not exists ( Select ID from maximo.SycloExchangeComplexTable CT where SERVERID = '<>' and CT.ID =  p.personid ) Insert into maximo.SycloExchangeComplexTable (ID, SERVERID,TBNAME ) select p.PERSONID,'<>','PERSON' from MAXIMO.person p ,MAXIMO.maxuser u where p.status='ACTIVE' and p.personid=u.personid and not exists ( Select ID from maximo.SycloExchangeComplexTable CT where SERVERID = '<>' and CT.ID =  p.personid )

[Former Member · Accepted solution]

Hello Jason , I remove angle bracket and "" in queries above post . Thanks & Regards, Kunal Varaiya

[jason_latko · Accepted solution]

Kunal,

That doesn't help  .  Please e-mail me the full query directly.  View my profile to get my SAP e-mail address.  Please also re-post the query here, maybe by substituting other characters for those that you are removing.  The query is also very hard to read without any formatting.  Use carriage returns to make it more readable.

Jason Latko - Senior Product Developer at SAP

[Former Member · Accepted solution]

<< if rebuild " select (first select query) " " Insert (second insert query) " else " select (first select query) " " Insert (second insert query) " >>

Make sure there is no space between << and if

If the first part of the reload had two scripts don't use a " between them, because the server will stop the true section once it gets to the next " and look for the else or >>

Why are you having a select and insert query

If both parts of your script will be running an the same insert script, you can do a <<sql>> to run a script before it calls the If reload See this link on <<sql>>  &amp;lt;&amp;lt;sql&amp;gt;&amp;gt;

[Former Member · Accepted solution]

Hello Steve , I will try today in and make confirm . reason is to make delta thats why i am using two scripts in if as well as  in else condition . Because i dont have rights to make triggers in back end system . Thanks & Regards, Kuanal Varaiya