0 votes
When creating dataset in dataiku from ms sql query it shows an error that parameter @someparameter is not defined, but it is defined in front of query. Is there any way to create dataset in dataiku from sql query with parameters?

Regards,

Mindaugas Valaitis
asked by
Hello, could you please add the full query to your question?
-- script parameters
      
     --   declare @LastMonth int
        declare @first_status_day_date_begining date
        declare @first_status_day_date_ending date
    --    declare @migration_date date
        declare @max_record_count int
        declare @product_short_name nvarchar(10)
        declare @is_new_paid nvarchar(5)
        DECLARE @channel_types_to_be_omitted  TABLE (channels varchar(25))
        declare @CountryCode nvarchar(5)
  
        set @first_status_day_date_begining = '2016-11-01'
        set @first_status_day_date_ending = '2017-07-01'
    --    set @migration_date = '2016-03-15'
        set @CountryCode = 'LV'
        set  @is_new_paid = 'Y'
        set @product_short_name = 'CL'
        set @max_record_count = 100
  
  IF OBJECT_ID('tempdb..#DeniedAppsCZ') IS NOT NULL DROP TABLE #DeniedAppsCZ;
 IF OBJECT_ID('tempDB..#AG','U') IS NOT NULL drop table #AG
 IF OBJECT_ID('tempDB..#google','U') IS NOT NULL drop table #google
IF OBJECT_ID('tempDB..#Application','U') IS NOT NULL drop table #Application
IF OBJECT_ID('tempDB..#AG2','U') IS NOT NULL drop table #AG2

IF OBJECT_ID('tempDB..#tempscoringinfo','U') IS NOT NULL drop table #tempscoringinfo
IF OBJECT_ID('tempDB..#tempscoringinfo2','U') IS NOT NULL drop table #tempscoringinfo2
IF OBJECT_ID('tempDB..#tempscoringinfo3','U') IS NOT NULL drop table #tempscoringinfo3
IF OBJECT_ID('tempDB..#tempscoringinfo4','U') IS NOT NULL drop table #tempscoringinfo4
IF OBJECT_ID('tempDB..#GetData','U') IS NOT NULL drop table #GetData

IF OBJECT_ID('tempDB..#1m','U') IS NOT NULL drop table #1m
IF OBJECT_ID('tempDB..#3m','U') IS NOT NULL drop table #3m
IF OBJECT_ID('tempDB..#6m','U') IS NOT NULL drop table #6m


-- temp table #Application

     SELECT top (@max_record_count)
        ------------- additional info
       
        c.name_first
    --    ,c.id as cid
        ,c.name_last
        ,c.gender
    from  dwh.[dbo].[dim_applications_lv] a  with (nolock)
    join [dwh].[dbo].[dim_products] p with (nolock) on a.product_id = p.id
    join dwh..dim_customers c with (nolock) ON a.customer_id = c.id


    where a.paid_date is not null and a.paid_date between '2016-11-01' and '2017-07-01' and a.is_new_paid = 'Y' and p.product_short_name  = 'CL' and a.loan_number is not null



Connection OK, but query failed: Failed to read data from DB, caused by: SQLServerException: Must declare the scalar variable "@max_record_count".

An error occured
Failed to read data from DB, caused by: SQLServerException: Must declare the scalar variable "@max_record_count".
You may need to ask your DSS administrator to check logs for additional information

Additional technical details

Error type:com.dataiku.dip.exceptions.DataStoreIOException

1 Answer

0 votes

Hello,

For such advanced SQL scripts with variables, we recommend using an "SQL script" recipe (in the flow) instead of an SQL query dataset. You can see an example below:

 

For creating an "SQL script" recipe you can choose as such:

Note that your recipe would not require an input dataset.

Cheers,

Alex

answered by
821 questions
844 answers
749 comments
746 users