LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Caclulated function value into an sql query node

    Employee
    Posted 06-27-2012 04:56

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: khopkins

    Hello,
    I am attempting to create a parameter that can be used in a simple sql query to filter only a certain set of records.
    I can not get a simple function to work using a declared parameter to create a calculated date from say today's date as the sql keeps creating errors

    For example
    Select * from [TableName]
    where date >= {{^parameter^}}

    I have tried declaring the parameter and setting the type as a variety of types but i keep getting errors.

    How do I do this correctly?

    Many thanks,

    Keith


  • 2.  RE: Caclulated function value into an sql query node

    Employee
    Posted 06-27-2012 07:18

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    This varies a bit from vendor to vendor, but for Oracle:

    Select * from [TableName]
    where date >= to_date('{{^parameter^}}','yyyy-mm-dd') -- match the date format here to what you put in the parameter


  • 3.  RE: Caclulated function value into an sql query node

    Employee
    Posted 06-27-2012 07:48

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: khopkins

    Hi,

    Sorry I should of mentioned that this is data stored in Dise so is in DB2.

    However it seems to be the parameter itself that is not being read in correctly, so where is the best place to put the variable eg at graph or node level and would just a simple date() function work in a parameter in an DB node

    Regards,

    Keith


  • 4.  RE: Caclulated function value into an sql query node

    Employee
    Posted 06-27-2012 08:04

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    The parameter can be defined/entered at the Graph, Composite, or Node level - any of the 3 will work.
    A quick scan of Google suggests this:

    Select * from [TableName]
    where date >= DATE('{{^parameter^}}')

    This function is not the LAE date function, but rather the date function from DB2, since you're inside a DB2 query, so you have to conform to the DB2 formatting requirements. It seems that DB2 is willing to accept several formats.

    The important part here is that the parameter is a string replacement.. to use it in DB2, you need to include the apostrophes.


  • 5.  RE: Caclulated function value into an sql query node

    Employee
    Posted 06-27-2012 08:07

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Tim Meagher

    The parameter is basically substituted in, so the result of placing it in the SQL is the same as copying the parameter value, and replacing the {{^parameter^}} with this value.

    For example, if you set the parameter to be: 2012-01-01, then your SQL would be:

    Select * from [TableName]
    where date >= 2012-01-01

    If you set the parameter to be: "2012-01-01" then your SQL would be:

    Select * from [TableName]
    where date >= "2012-01-01"

    If you just copy the parameter text directly and replace the {{^parameter^}} part of the SQL, what happens when you do this - are there still errors?

    If so, are you sure that the parameter name matches exactly to the declared parameter (including the case?)


  • 6.  RE: Caclulated function value into an sql query node

    Employee
    Posted 06-28-2012 01:50

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: khopkins

    Hi Tim,

    That would work perfectly if i only needed a static date however i need the sql to change its parameter each day it is run.

    for example i have a formula that takes the current date takes 13 months off that date and then converts this to a dise date format so that the sql only select records where the date is within a rolling 13 month window.
    I then need this value inserted into the sql via the parameter.
    this is the rough function below which at present will provide an integer result as of course dise dates are a decimal but the sql fails even if it is a string

    int("1" + substr(str(dateAdjust(date(),-13,"months")),2,2) + substr(str(dateAdjust(date(),-13,"months")),5,2) + substr(str(dateAdjust(date(),-13,"months")),8,2) )

    Can this be done in LAE or do i have to create a far more complex sql statement to do the same thing?

    Regards,

    Keith


  • 7.  RE: Caclulated function value into an sql query node

    Employee
    Posted 06-28-2012 02:15

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Tim Meagher

    Hi Keith,

    I was just saying that the LAE parameter value simply gets substituted into the SQL, therefore the problem was most likely in the SQL, not the evaluation of the LAE parameter.

    If you are using the DB Query or JDBC Query node, then the SqlQuery parameter needs to be valid SQL specific to the database you are using - as Stony pointed out.
    If you want to perform some sort of operation on a parameter, then within the SqlQuery parameter, this would need to be done using valid SQL, not BRAINscript - since the database is not going to evaluate BRAINscript.

    If you want to use BRAINscript to perform this operation on the parameter, then as I see it, you have two options.
    • The first option is if you are using JDBC.
    First, place your parameter in a Static Data node, such that your StaticData parameter looks like:
    DateParam:string
{{^parameter^}}
    Then in a subsequent Filter node, perform the necessary operations to transform the DateParam field into the format you need.
    The transformed DateParam value would then be a field in the Filter node output.
    Connect the output of the filter node as input to a JDBC Query node, construct parameterized SQL (using ? etc) in the SqlQuery parameter, then specify the DateParam field in the SqlQueryFieldBindings parameter.
    • The second option - if you are not using a JDBC connection - would be to use the DB Execute node.
    The Script parameter in the DB Excecute node is a BRAINscript parameter.
    This parameter can then be written to construct your SQL query string, using standard BRAINscript functions.
    Your SQL query string can be stored in some variable, and then you can use the sqlSelect(...) BRAINscript function and providing it with the variable name of your SQL query string.
    Hope this helps,
    Tim.


  • 8.  RE: Caclulated function value into an sql query node

    Employee
    Posted 06-28-2012 02:20

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: khopkins

    Hi Tim,

    Many thanks that helps perfectly i was just assuming that you could just create the value as a parameter in the jdbc node and then pass this into the SQL.
    Now i know the way to proceed i will go ahead with your first option.

    Regards,

    Keith


  • 9.  RE: Caclulated function value into an sql query node

    Employee
    Posted 06-28-2012 06:52

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    For readability, you can break up the expression into two variables:
    d=str(dateAdjust(date(),-13,"months"))
    disedate=int("1" + substr(d,2,2) + substr(d,5,2) + substr(d,8,2))

    This also has the effect of only doing the DateAdjust operation once.. instead of 3 times, so it's faster.

    For reference, in a DBExecute node you can do all of this as a single step:

    #build the date string
    d=str(dateAdjust(date(),-13,"months"))
    disedate=int("1" + substr(d,2,2) + substr(d,5,2) + substr(d,8,2))
    #build and execute the query
    query = "Select * from [TableName] where date >= :1"
    sqlSelect(1,query,disedate)


  • 10.  RE: Caclulated function value into an sql query node

    Employee
    Posted 06-28-2012 06:55

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: khopkins

    Many thanks again