Re: [Help-glpk] Problems with Connecting GLPK to Excel

[aly]

Hi Xypron,

Thanks a lot for the excellent support. I sincerely appreciate it.

I tried the SQL idea you told me about. The problem now is that I get the 
following error: "String Literal Too Long" for the "Where" statement!!


My SQL is:

'SELECT a.OriginFacilityID, a.DestinationFacilityID, b.ProductID, b.PeriodID, 
b.UnitCost, d.TransformationTypeID'
'FROM Channels a, ChannelPeriodProducts b, Facilities c, TransformerTypes d'
'WHERE a.ChannelID = b.ChannelID AND a.OriginFacilityID = c.FacilityID AND 
c.FacilityType = "Supplier" AND a.DestinationFacilityID = c.FacilityID AND 
c.FacilityType = "Transformer" AND a.DestinationFacilityID = d.FacilityID':


I also tried the following alternative, and got the same error, now for the 
"FROM" statement:

'SELECT a.OriginFacilityID, d.TransformerTypeID, b.ProductID, b.PeriodID, 
b.UnitCost '

'FROM Channels a INNER JOIN ChannelPeriodProducts b ON a.ChannelID = 
b.ChannelID, Channels a INNER JOIN Facilities c1 ON a.OriginFacilityID = 
c1.FacilityID , Channel a INNER JOIN Facilities c2 ON a.DestinationFacilityID = 
c2.FacilityID, Facilities c2 INNER JOIN TransformerTypes d ON c2.FacilityID = 
d.FacilityID '
'WHERE    (c2.FacilityType = "Transformer")  AND (c1.FacilityType = 
"Supplier")':

Is that error because GLPK can not handle long SQL statements?? And is there a 
way to solve that problem? As I really would like to still have that query in 
GLPK (I do not want to manually create the table in Access and then call that 
table in GLPK) 


Any help will be really appreciated.

Best,

Aly
----- Original Message -----
From: "glpk xypron" <address@hidden>
To: address@hidden, address@hidden, "xypron glpk" <address@hidden>
Cc: address@hidden
Sent: Thursday, July 8, 2010 4:46:14 PM GMT -05:00 US/Canada Eastern
Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel

Hello Aly,

> In AMPL, there is some kind of a "cross" statement that might solve that,
> but I don't know a corresponding one in GLPK, and how to use it if it
> exists!

See glpk-4.44\doc\gmpl.pdf chapter 3.4 Set expressions

> In Access, there is a table called Channels, that contains three fields,
> one called Channel ID, another for origin facilities and a third for the
> corresponding destination facilities for all pairs that have flow between
> them. 
> 
> Then, there is a table called ChannelsProducts that has a field for
> Channel ID, another for products, a third for transportation cost (from which
> t_ijp should be read), and a fourth for the flow variable values (which is the
> one x_ijp needs to be written back in).
> 
> The question is: how can I read t_ijp from and write x_ijp to this table?
> As you see there are only two fields for the indices (channel and product),
> while t_ijp and x_ijp have three indices (origin facility, destination
> facility, and product).

You don't need operator "cross", you can use the following SQL
statements

SELECT a.orig, a.dest, b.prod, b.cost 
FROM Channels a, ChannelProducts b 
WHERE a.id = b.id;

UPDATE Channels a, ChannelProducts b SET b.flow = ? 
WHERE a.id = b.id AND a.orig = "?" AND a.dest="?" AND b.prod="?"

Best regards

Xypron



-- 
GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl.  
Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl

-- 
PhD Student

Rm. 407 Main Building
H. Milton Stewart School of Industrial and Systems Engineering
Georgia Institute of Technology
765 Ferst Dr., NW
Atlanta, Georgia 30332-0205, USA