dbr.keepwithnext and dynamic sql

(4 posts) (2 voices)
  1. ajdjackson, Member

    Hi

    I've have 2 dynamic statements that when run do not obey dbr.keepwithnext. From the html I see that <div class="kwnreset"></div> has been added between each of the executions.

    Is there any way around this?

    The reason I am using dynamic sql is that in MySQL I can use the following in the where clause

    case when inPid= 1 then pid="6401160128" else pid in ("7249520105","7249520210") end

    I can't work out a way to do the same in T-SQL other than by setting a variable and using dynamic sql to reference it.

    Thanks

    Jake

  2. myDBR Team, Key Master

    Why not show the full queries involved with the dbr.keepwithnext?

    You could write the query as:

    WHERE ((inPid=1 and pid="6401160128") or (inPid!=1 and pid in ("7249520105","7249520210"))

    The dbr.keepwithnext is shown between result sets, so what are your queries and how do you use dbr.keepwithnext?

    --
    myDBR Team

  3. ajdjackson, Member

    Hi

    The full queries are below:

    declare @yr int; declare @mdate date; declare @pid varchar(100); declare @t1 nvarchar(max); declare @t2 nvarchar(max);

    select @yr=year(getdate());

    select @mdate=max(act_date) from dbo.tblpr_prodact;

    exec sp_DBR_DOH_Prepare @yr,@inLogin;

    if @inPID = 1 begin set @pid = '64060128' end else begin set @pid = '''72420210'',''72420210''' end;

    set @t1=concat(' select iif(pid=''72420105'',''0.05MG'',iif(pid=''72420210'',''0.10MG'','''')) as "Strength[str]", case when cust=''XXX- Parent'' then ''ABC'' when cust=''Big 3 Totals'' then ''BIG 3'' when cust=''YYY- Parent'' then ''CH'' else ''ZZZ'' end as "[who]", val as "DoH[doh]", case when cust=''XXX- Parent'' then 1 when cust=''Big 3 Totals'' then 4 when cust=''YYY- Parent'' then 2 else 3 end as "[ord]", format(b.bdate,''MM-dd'') as "as of[adate]" from dbo.tmp_doh_daily left join (select act_cust as bcust,max(act_date) as bdate from dbo.tblpr_prodact group by act_cust) b on cust=b.bcust where cust in (''XXX - Parent'',''YYY - Parent'',''ZZZ - Parent'') and iuser=''',@inLogin,''' and stype=''5Days on Hand'' and pid in (', @pid,') order by 1,4;');

    set @t2=concat(' select iif(pid=''72420105'',''0.05MG'',iif(pid=''72420210'',''0.10MG'','''')) as "Strength[str]", case when cust=''XXX- Parent'' then ''ABC'' when cust=''Big 3 Totals'' then ''BIG 3'' when cust=''YYY- Parent'' then ''CH'' else ''ZZZ'' end as "[who]", val as "DoH[doh]", case when cust=''XXX- Parent'' then 1 when cust=''Big 3 Totals'' then 4 when cust=''YYY- Parent'' then 2 else 3 end as "[ord]" from dbo.tmp_doh_daily left join (select act_cust as bcust,max(act_date) as bdate from dbo.tblpr_prodact group by act_cust) b on cust=b.bcust where cust in (''Big 3 Totals'') and iuser=''',@inLogin,''' and stype=''5Days on Hand'' and pid in (', @pid,') order by 1,4;');

    select 'dbr.title','Big 3 Days on Hand';

    select 'dbr.crosstab','who'; select 'dbr.resultclass','b3 kwn'; select 'dbr.colstyle','str','[min-width:75px;text-align:right;font-weight:bold;]'; select 'dbr.colstyle','doh','[text-align:center]%0.1f'; select 'dbr.colstyle','adate','[text-align:center]'; select 'dbr.css','.b3 {font-size:15px;}'; select 'dbr.css','.b3 th {text-align:center;font-weight:bold;min-width:75px;}'; select 'dbr.css','table.b3 {margin:0;}'; select 'dbr.search',0; select 'dbr.hidecolumn','ord'; select 'dbr.keepwithnext',0;

    exec sp_executesql @t1;

    select 'dbr.crosstab','who'; select 'dbr.resultclass','b4 kwn'; select 'dbr.colstyle','str','[min-width:75px;text-align:right;font-weight:bold;]'; select 'dbr.colstyle','doh','[text-align:center]%0.1f'; select 'dbr.css','.b4 {font-size:15px;}'; select 'dbr.css','.b4 td:nth-last-child(1) {font-weight:bold;}'; select 'dbr.css','.b4 th {text-align:center;font-weight:bold;min-width:75px;}'; select 'dbr.css','table.b4 {margin:0;}'; select 'dbr.search',0; select 'dbr.hidecolumn','ord','str';

    exec sp_executesql @t2;

    I've used your suggestion and got rid of the the dynamic sql and it works great.

    Sorry I'm just so used to years of using MySQL and now just starting T-SQL.

    Thanks

    Jake

  4. myDBR Team, Key Master

    Not sure what you are trying to do with the separate queries. Put the result sets side by side as it would look that they are one result? This is probably not a good idea.

    No need for dynamic SQL for that (try always to avoid dynamic queries). You can write the first query as:


    select 'dbr.colstyle', 'adate', 'm-d' select
    case
    when pid='72420105' then '0.05MG'
    when pid='72420210' then '0.10MG'
    else ''
    end "Strength[str]",
    case
    when cust='XXX- Parent' then 'ABC'
    when cust='Big 3 Totals' then 'BIG 3'
    when cust='YYY- Parent' then 'CH'
    else 'ZZZ'
    end as "[who]",
    val as "DoH[doh]",
    case
    when cust='XXX- Parent' then 1
    when cust='Big 3 Totals' then 4
    when cust='YYY- Parent' then 2
    else 3
    end as "[ord]",
    b.bdate,'MM-dd' as "as of[adate]"
    from dbo.tmp_doh_daily
    left join (
    select act_cust as bcust,max(act_date) as bdate
    from dbo.tblpr_prodact
    group by act_cust
    ) b on cust=b.bcust
    where cust in ('XXX - Parent','YYY - Parent','ZZZ - Parent') and iuser=@inLogin and stype='5Days on Hand'
    and ((@inPID = 1 and pid '64060128') or (isnull(@inPID,0) != 1 and pid in ('72420210','72420210')))
    order by 1,4;

    Most likely you can skip the second query by simply combining the two as it looks that they are the same and you are just trying to set the order of the columns (an hide the date for the 'Big 3 Totals').

    --
    myDBR Team


Reply

You must log in to post.