• Replies 7 replies
  • Subscribers 337 subscribers
  • Views 40 views
  • Users 0 members are here
Related

Is it possible to define variables in List Manager?

Vicky Wenhan Yu
$organization

Is it possible to define variables in List Manager?

DECLARE @the_date datetime
SET @the_date = getdate()
SELECT DISTINCT a.customer_no
    FROM T_CUSTOMER a
        WHERE a.inactive = 1
        AND a.cust_type = 1 
and a.create_dt <= @the_date

I tried to create a list by manual edit, but the above script won't work.

The error message says,
"Illegal SQL statement"


Any advice would be appreciated

 



[edited by: Vicky Wenhan Yu at 12:17 PM (GMT -6) on 29 Mar 2010]
Parents
  • $organization

    HI Vicky,

    Good day.

    I think you can use function and view to achieve what you try to do.

    you put the DECLARE part in a function and hide the logic into a view.

    create function dbo.myfunction

    return datetime

    as

    begin

    DECLARE @the_date datetime
    SET @the_date = getdate()


    return @the_date

    end

     

    -----==== this part can be put in a view

    SELECT DISTINCT a.customer_no
        FROM T_CUSTOMER a
            WHERE  a.create_dt <= dbo.myfunction()

    -----=====

     and  a.inactive = 1
            AND a.cust_type = 1 

     

    have fun

    Ben

     



    [edited by: Ben Gu at 12:50 AM (GMT -6) on 30 Mar 2010]
Reply
  • $organization

    HI Vicky,

    Good day.

    I think you can use function and view to achieve what you try to do.

    you put the DECLARE part in a function and hide the logic into a view.

    create function dbo.myfunction

    return datetime

    as

    begin

    DECLARE @the_date datetime
    SET @the_date = getdate()


    return @the_date

    end

     

    -----==== this part can be put in a view

    SELECT DISTINCT a.customer_no
        FROM T_CUSTOMER a
            WHERE  a.create_dt <= dbo.myfunction()

    -----=====

     and  a.inactive = 1
            AND a.cust_type = 1 

     

    have fun

    Ben

     



    [edited by: Ben Gu at 12:50 AM (GMT -6) on 30 Mar 2010]
Children