Ventana software support forum

Posted: **Sat Nov 20, 2021 7:36 am**

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')

Posted: **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.

Posted: **Mon Nov 22, 2021 2:09 pm**

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.

Posted: **Wed Dec 15, 2021 8:15 am**

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?

Posted: **Wed Dec 15, 2021 8:30 am**

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.

Posted: **Tue Jan 18, 2022 10:49 am**

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'

Ventana software support forum

Global variables in VDI files

Global variables in VDI files

Re: Global variables in VDI files

Re: Global variables in VDI files

Re: Global variables in VDI files

Re: Global variables in VDI files

Re: Global variables in VDI files