what is the error in my sql? - got result outside specified date range and sluggish

[g_g_99]

I got far more than I bargained for, I just fail to see the error I made

begin

Declare @FI_sym varchar(1024)

Declare @acct_grp_ID integer

Declare @acct_ID integer

Declare @subAcct_ID integer

Declare @excludeSubAcctAliasFilterLst varchar(1300)

Declare @likeEscpChar varchar(1)

Declare @msg varchar(512)

Declare @Min_ValuatnDt date

Declare @thru_valuatnDt date

declare @excludeSubAcctAliasRegex varchar(1500)

declare @FI_symRegex varchar(1300)

declare @ignoreCost smallint

select @msg = '',@excludeSubAcctAliasRegex = null,@fi_symRegex = null

select null,null,null, null, '2015-10-15', '2015-11-19', '%', 11 into @acct_grp_ID, @acct_ID, @subacct_ID, @FI_sym, @Min_ValuatnDt,@thru_valuatnDt, @likeEscpChar, @ignoreCost

set @msg = ''

select subAcct_ID into #subAcct_IDs from dba.subacct where (@acct_ID is null or acct_id = @acct_ID) and (@subacct_ID is null or subAcct_id=@subacct_ID ) and ( @acct_grp_ID is null or @acct_grp_ID in (select acct_grp_ID from dba.account where acct_grp_ID =@acct_grp_ID))

select ,'Cost/share'=(select case when qty = 0 then null else cost/qty end), Pct=(select case when cost = 0 then null else(mkt_value-cost)/cost100.0 end) ,@min_ValuatnDt, @thru_ValuatnDt

      from dba.PF_Daily_Holdg_detl as p

      where((@acct_grp_ID is null or p.acct_grp_ID = @acct_grp_ID)

      and(subAcct_ID = @subAcct_Id or(@acct_Id is not null and subAcct_Id = any(select subAcct_ID from #subAcct_IDs)) or @acct_ID is null)
      and(@excludeSubAcctAliasRegex is null or $Regex_match(p.sub_Acct_alias_dd,@excludeSubAcctAliasRegex) = 0)
      and(@min_ValuatnDt is null or valuation_dt >= @Min_ValuatnDt)
      and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)
      and((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11) or(@ignoreCost = 11 and(cost <= 0 or cost is null)) and qty > 0 -- 151119 added or cost is null
      and valuation_dt || subAcct_ID||fi_sym
       = any(select max(valuation_dt) || subAcct_ID||fi_sym from dba.PF_Daily_Holdg_detl
        where(subacct_ID = p.subacct_ID and(fi_sym = p.fi_sym)
        and acct_grp_ID = p.acct_grp_ID

        and(@min_ValuatnDt is null or valuation_dt >= @Min_ValuatnDt)
        and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)

        and((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11) or(@ignoreCost = 11 and(cost <= 0 or cost is null)) and qty > 0) -- 151119 added or cost is null

        group by acct_grp_ID,subAcct_id,fi_sym))

end

I have been walking thru the sql and made all adjustments I could think of for the afternoon still can't rid of the bug your help is appreciated

[Breck_Carter]

Hello?

Are you going to fix the syntax error, or not?

[g_g_99]

sorry for the mis-post finally found the problem mismatched and wrongly place parenthesis made the whole sql different from what is intended. here is the simplified and properly matched code that works.

Please don't try in regular database unless it has the external functions $xxx... used below.

begin

Declare @fi_sym varchar(1024)

Declare @acct_grp_ID integer

Declare @acct_ID integer

Declare @subAcct_ID integer

Declare @excludeSubAcctAliasFilterLst varchar(1300)

Declare @likeEscpChar varchar(1)

Declare @Msg varchar(512)

Declare @min_ValuatnDt date

Declare @thru_valuatnDt date

declare @excludeSubAcctAliasRegex varchar(1500)

declare @fi_symRegex varchar(1300)

declare @ignoreCost smallint select @Msg = '',@excludeSubAcctAliasRegex = null,@fi_symRegex = null

select null,null,null, null, '2015-10-15', '2015-11-19', '%', 11 into @acct_grp_ID, @acct_ID, @subacct_ID, @fi_sym, @min_ValuatnDt,@thru_valuatnDt, @likeEscpChar, @ignoreCost

set @Msg = ''

select subAcct_ID into #subAcct_IDs from dba.subacct where (@acct_ID is null or acct_id = @acct_ID) and (@subacct_ID is null or subAcct_id=@subacct_ID ) and ( @acct_grp_ID is null or @acct_grp_ID in (select acct_grp_ID from dba.account where acct_grp_ID =@acct_grp_ID)) select @fi_sym = rtrim(@fi_sym)

select *,'Cost/share'=(select case when qty = 0 then null else cost/qty end),

      Pct=(select case when cost = 0 then null else(mkt_value-cost)/cost*100.0 end)

  ,@min_ValuatnDt, @thru_ValuatnDt

  from dba.PF_Daily_Holdg_detl as p

  where (@acct_grp_ID is null or p.acct_grp_ID = @acct_grp_ID)

      and(subAcct_ID = @subAcct_Id or (@subAcct_Id is null and @acct_grp_ID is null and @acct_Id is not null and p.subAcct_Id in (select subAcct_ID from #subAcct_IDs)))

      and(@excludeSubAcctAliasRegex is null or $Regex_match(p.sub_Acct_alias_dd,@excludeSubAcctAliasRegex) = 0)

      and(@min_ValuatnDt is null or valuation_dt >= @min_ValuatnDt)

      and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)

      and((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11) or(@ignoreCost = 11 and(cost <= 0 or cost is null))

      and qty > 0 -- 151119 added or cost is null

      and valuation_dt =

          (select max(valuation_dt) from dba.PF_Daily_Holdg_detl as a

            where a.subacct_ID = p.subacct_ID and a.fi_sym = p.fi_sym and a.acct_grp_ID = p.acct_grp_ID

            and (@min_ValuatnDt is null or valuation_dt >= @min_ValuatnDt)

            and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)

            and (((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11) or(@ignoreCost = 11 and(cost <= 0 or cost is null))) -- 151119 added or cost is null

          )

end

BTW: the above will have syntax error in database that does not have external functions $xxx... used above. PS. some reason the quoted code gets reformatted incorrectly when posted from this windows 8.1 with latest firefox

[Breck_Carter]

It's rude to post code with a syntax error...

Could not execute statement.

Syntax error near ',' on line 34 (Transact-SQL)
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1

begin
Declare @fi_sym varchar(1024)

Declare @acct_grp_ID integer

Declare @acct_ID integer

Declare @subAcct_ID integer

Declare @excludeSubAcctAliasFilterLst varchar(1300)

Declare @likeEscpChar varchar(1)

Declare @Msg varchar(512)

Declare @min_ValuatnDt date

Declare @thru_valuatnDt date

declare @excludeSubAcctAliasRegex varchar(1500)

declare @fi_symRegex varchar(1300)

declare @ignoreCost smallint

select @Msg = '',@excludeSubAcctAliasRegex = null,@fi_symRegex = null

select null,null,null, null, '2015-10-15', '2015-11-19', '%', 11 into @acct_grp_ID, @acct_ID, @subacct_ID, @fi_sym, @min_ValuatnDt,@thru_valuatnDt, @likeEscpChar, @ignoreCost

set @Msg = ''

select subAcct_ID into #subAcct_IDs from dba.subacct where (@acct_ID is null or acct_id = @acct_ID) and (@subacct_ID is null or subAcct_id=@subacct_ID ) and ( @acct_grp_ID is null or @acct_grp_ID in (select acct_grp_ID from dba.account where acct_grp_ID =@acct_grp_ID))

select ,'Cost/share'=(select case when qty = 0 then null else cost/qty end), Pct=(select case when cost = 0 then null else(mkt_value-cost)/cost100.0 end) ,@min_ValuatnDt, @thru_ValuatnDt

      from dba.PF_Daily_Holdg_detl as p

      where((@acct_grp_ID is null or p.acct_grp_ID = @acct_grp_ID)

      and(subAcct_ID = @subAcct_Id or(@acct_Id is not null and subAcct_Id = any(select subAcct_ID from #subAcct_IDs)) or @acct_ID is null)
      and(@excludeSubAcctAliasRegex is null or $Regex_match(p.sub_Acct_alias_dd,@excludeSubAcctAliasRegex) = 0)
      and(@min_ValuatnDt is null or valuation_dt >= @min_ValuatnDt)
      and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)
      and((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11) or(@ignoreCost = 11 and(cost <= 0 or cost is null)) and qty > 0 -- 151119 added or cost is null
      and valuation_dt || subAcct_ID||fi_sym
       = any(select max(valuation_dt) || subAcct_ID||fi_sym from dba.PF_Daily_Holdg_detl
        where(subacct_ID = p.subacct_ID and(fi_sym = p.fi_sym)
        and acct_grp_ID = p.acct_grp_ID

        and(@min_ValuatnDt is null or valuation_dt >= @min_ValuatnDt)
        and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)

        and((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11) or(@ignoreCost = 11 and(cost <= 0 or cost is null)) and qty > 0) -- 151119 added or cost is null

        group by acct_grp_ID,subAcct_id,fi_sym))

end

[g_g_99]

sorry, I did not post correctly. I should have also mentioned that there are custom function ( start with $...) from external DLL. hence the code will always have syntax error on other database I found my problem with nested paranthesis

the corrected code that runs smoothly is posted below