Global variables in VDI files

Use this forum to post Vensim related questions.
Post Reply
pav80
Member
Posts: 32
Joined: Fri Jan 24, 2014 11:09 am
Vensim version: DSS

Global variables in VDI files

Post by pav80 »

Hello,

Below is an example of some VDI code.
Wondering if it is possible to declare in the VDI file at the start a global variable that you can then call in the SQL statements in the VDI file, so as to make the VDI code easier to update with such global variables.
In the below case for example can we have a global variable called 'myScenario' and therefore you could replace statements like

Code: Select all

 IN ('EO2022')\
by
[Code PUBLICATION] IN (myScenario)\

if at the top of the VDI file mySCenario is defined as 'EO2022' ?

Thanks

>>>>

:CONNECT DRIVER=SQL Server;SERVER=;Trusted_Connection=Yes;APP=Vensim family of products;

:DATA
	:VARNAME='VDFX wem AGGREGATE REGION'
		:SUB=[Code FLOW]
		:SUB=[Code PRODUCT]
		:SUB=[Code REGION]
		:SUB=[Code CATEGORY]
		:SUB=[Code UNIT]
		:TIME=[YEAR]
		:VALUE=[VALUE]		
		:SELECT DW.rep.V_DIVISION_INPUT_OG\
		WHERE( [Code PUBLICATION] IN ('EO2022')\
		AND [Code SCENARIO] IN ('STEPS')\
		AND [Code FLOW] IN ('CONV')\
		AND [YEAR] BETWEEN '1970' AND '2050')\
		OR( [Code PUBLICATION] IN ('EO2022')\
		AND [Code SCENARIO] IN ('STEPS')\
		AND [Code FLOW] IN ('TOTTPEDADJ1')\
		AND [YEAR] BETWEEN '2022' AND '2050')
	
:DATA
	:VARNAME='VDFX wem AGGREGATE COUNTRY'
		:SUB=[Code FLOW]
		:SUB=[Code PRODUCT]
		:SUB=[Code REGION]
		:SUB=[Code CATEGORY]
		:SUB=[Code UNIT]
		:TIME=[YEAR]
		:VALUE=[VALUE]		
		:SELECT DW.rep.V_DIVISION_INPUT_OG\
		WHERE( [Code PUBLICATION] IN ('EO2022')\
		AND [Code SCENARIO] IN ('STEPS')\
		AND [Code FLOW] IN ('GDPPP')\
		AND [YEAR] BETWEEN '1970' AND '2050')\
		OR( [Code PUBLICATION] IN ('EO2022')\
		AND [Code SCENARIO] IN ('STEPS')\
		AND [Code FLOW] IN ('POPULATION')\
		AND [YEAR] BETWEEN '1970' AND '2050')
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Global variables in VDI files

Post by Administrator »

I'm fairly certain it cannot be done at present. But it seems like it could be easy enough to do. I'll create a ticket in our system to add it in a future release.
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
pav80
Member
Posts: 32
Joined: Fri Jan 24, 2014 11:09 am
Vensim version: DSS

Re: Global variables in VDI files

Post by pav80 »

I'm fairly certain it cannot be done at present. But it seems like it could be easy enough to do. I'll create a ticket in our system to add it in a future release.
Thank you Administrator, that would be very much appreciated as would render the VDI further efficient when using large SQL statements.
pav80
Member
Posts: 32
Joined: Fri Jan 24, 2014 11:09 am
Vensim version: DSS

Re: Global variables in VDI files

Post by pav80 »

Administrator wrote: Mon Nov 22, 2021 8:51 am I'm fairly certain it cannot be done at present. But it seems like it could be easy enough to do. I'll create a ticket in our system to add it in a future release.
Dear Administrator, just checking if this was included in Vensim 9.1.0 release?
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Global variables in VDI files

Post by Administrator »

No, I haven't had the time to implement it yet. It is in our system though so will be done at some point. I've also made a note to notify you when it's done.
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Global variables in VDI files

Post by Administrator »

Revisiting this, you might already be able to do it.

I've got an example here using MySQL. I've got the following working.

Code: Select all

:EXECUTE SET @scenario = "current";
:EXECUTE SET @s = CONCAT("SELECT ", @scenario, " FROM mytable");
:EXECUTE PREPARE stmt FROM @s;
:EXECUTE EXECUTE stmt;
Can you do something similar with MSSQL? I found this after a quick search.

Code: Select all

DECLARE @TestVariable AS VARCHAR(100)='Save Our Planet'
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
Post Reply