Solved

SQL stored procedure

  • 25 March 2020
  • 5 replies
  • 141 views
erika.hashiguti

I’m trying something like this, but compound sets does not exists anymore. Someone could please help me?

 

 

icon

Best answer by deannezhang 28 March 2020, 18:57

For every i,j in the sql procedure result, you want to obtain the distance and such route exists, right? If so, a parameter with following definition should work.

routeexists(i,j):= (distance(i,j) > 0)

so you do not need the last row in the mapping, but calculate the parameter after reading  from DB.

View original
Erika Hashiguti

5 replies

B

Thank you, @mateusarakawa, that’s a good idea! In order to write the relation to the table, I would probably need to construct a sql-insert-statement myself and use DirectSQL. I’m still wondering if there’s an “AIMMS-way” to do it. 

mateusarakawa
Userlevel 5

Hi, @Benedikt. In order to read this table, I’d use database procedure and query attribute: 

select from,to,1 as relation from table

from → i_City
to → j_City

relation → P_YourParameter(i_City,j_City)

Then you can use this parameter to create your query and write to database.

I don’t quite remember if there is a better approach to this, maybe AIMMS team can help us further.

Mateus Nascimento @ UniSoma, Brazil
B

I’m facing the same problem where I need to read from and write to a database table, which consists of only two columns, which form a relation (like the cities “from” and “to” in the above example). I tried using a relation (not a compound set), but when reading from the database table, the relation remains empty.

Is something like the following still supported in AIMMS?

"from" --> i_City,
"to" --> j_City,
("from","to") --> S_CityToCityRelation

 

erika.hashiguti

Thanks deannezhang, but the problems are:

1 - I have a stored procedure instead of a table

2 - My stored procedure only provide me the columns i and j. What means that if the combination (i, j) exists the route also exists.

 

That’s why I was asking about the compound set way to achieve this information.

Erika Hashiguti
deannezhang
Userlevel 4

For every i,j in the sql procedure result, you want to obtain the distance and such route exists, right? If so, a parameter with following definition should work.

routeexists(i,j):= (distance(i,j) > 0)

so you do not need the last row in the mapping, but calculate the parameter after reading  from DB.

Reply


Didn't find what you were looking for? Try searching on our documentation pages:

AIMMS Developer & PRO | AIMMS How-To | AIMMS SC Navigator