cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
Choose Language Hide Translation Bar

How do I use user defined variables to define a database query?

Hello, I am using JMP16. I have a pop up box to ask for user input to define two variables ("Entity", and "Last_X_Days") that I would like to use to query a database. I have no problem getting these variables defined, but the data pull query is in quotations and wont take inputs as variables. It just takes them literally. 

 

If "Last_X_Days" and "Entity" are previously defined as variables (based of the user input), how do I get the query to pull the variables and not just write literally the names of the variables? 

 

Note: I changed some of the irrelevant code to more generic terms in CAPS. 

 

Thank you!!!

 

 

script = PROGRAM:openDATABASEscript("\[

<PROGRAMInput>
  <Task>
    <TaskName>PROGRAM</TaskName>
    <AdditionalQueries>
      <Query>
        <TaskName>PARAMETER</TaskName>
        <AutoJoin>1</AutoJoin>
        <Parameters>TOOL</Parameters>
        <PrimaryEntity>1</PrimaryEntity>
      </Query>
    </AdditionalQueries>
    <AutoRun>1</AutoRun>
    <ExtractXML>1</ExtractXML>
    <Site>LOCATION</Site>
    <DATABASEQuery>
      <Query>
        <TaskName>DATABASEByDateRange</TaskName>
        <Parameters>PARAMETER NAME</Parameters>
        <Daterange>Last_X_Days</Daterange>
        <Entity>Entity</Entity>
        <ExcludeInvalid>0</ExcludeInvalid>
        <ExcludeNonstandard>0</ExcludeNonstandard>
        <ExcludePre>1</ExcludePre>
        <IncludeSubs>1</IncludeSubs>
        <MonitorType>ALL</MonitorType>
      </Query>
    </DATABASEQuery>
    <Technology>TECHNOLOGY</Technology>
    <ToolStyle>DATABASE</ToolStyle>
    <WriteJSL>FILE</WriteJSL>
  </Task>
</WijtInput>
]\"

);

 

 

1 REPLY 1
jthi
Super User

Re: How do I use user defined variables to define a database query?

In this case I would most likely use Eval Insert() 

Names Default To Here(1);

Last_X_Days = 1;
Entity = "ABC";

query_template = "\[
<PROGRAMInput>
  <Task>
    <TaskName>PROGRAM</TaskName>
    <AdditionalQueries>
      <Query>
        <TaskName>PARAMETER</TaskName>
        <AutoJoin>1</AutoJoin>
        <Parameters>TOOL</Parameters>
        <PrimaryEntity>1</PrimaryEntity>
      </Query>
    </AdditionalQueries>
    <AutoRun>1</AutoRun>
    <ExtractXML>1</ExtractXML>
    <Site>LOCATION</Site>
    <DATABASEQuery>
      <Query>
        <TaskName>DATABASEByDateRange</TaskName>
        <Parameters>PARAMETER NAME</Parameters>
        <Daterange>¤Last_X_Days¤</Daterange>
        <Entity>Entity</¤Entity¤>
        <ExcludeInvalid>0</ExcludeInvalid>
        <ExcludeNonstandard>0</ExcludeNonstandard>
        <ExcludePre>1</ExcludePre>
        <IncludeSubs>1</IncludeSubs>
        <MonitorType>ALL</MonitorType>
      </Query>
    </DATABASEQuery>
    <Technology>TECHNOLOGY</Technology>
    <ToolStyle>DATABASE</ToolStyle>
    <WriteJSL>FILE</WriteJSL>
  </Task>
</WijtInput>
]\";

query_str = Eval Insert(query_template, "¤");
show(query_str);

script = PROGRAM:openDATABASEscript(query_str);
-Jarmo