the following query is supposed to list the latest data within min date(2015-11-24) and thru date(2015-12-06) for each fi_sym in an subacct: expected 89 rows but got more than 400 - listed data for 2015-12-06 as well previous dates in the range

declare @Fi_Sym varchar(20)
declare @Fi_Sym_ORIG varchar(20)
declare @rbc_Sym varchar(20)
declare @fi_name varchar(20)
declare @fi_desc varchar(20)
declare @fi_short_desc varchar(20)
declare @CUSIP varchar(20)
declare @fi_sym_US varchar(20)
declare @fi_class varchar(20)
declare @fi_Exchg varchar(20)
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 @ignoreCost smallint
declare @excludeSubAcctAliasRegex varchar(1500)
declare @fi_symRegex varchar(1300)

select '2015-11-24', '2015-12-06', null , null , null, 0 into @min_ValuatnDt,@thru_ValuatnDt,@acct_grp_ID,  @subAcct_ID, @excludeSubAcctAliasRegex, @ignoreCost

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_ID in (select acct_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)/cost*100.0 end)
          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)
              or (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(a.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 a.valuation_dt >= @min_ValuatnDt)
                    and (@thru_ValuatnDt is null or a.valuation_dt <= @thru_ValuatnDt)
                    and (((a.cost > 0 or a.cost is null or @ignoreCost = 1) and @ignoreCost < 11) or(@ignoreCost = 11 and(a.cost <= 0 or a.cost is null))) -- 151119 added or cost is null
        )

asked 07 Dec '15, 02:39

gg99's gravatar image

gg99
193192128
accept rate: 0%

edited 07 Dec '15, 09:08

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264

Comment Text Removed

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

If the braces are as stated here, I would think here's a OR-condition that is not linked with the previous or further AND-conditions, so the date range will not be applied as desired. (In other words: the "...< 11) or(@ignoreCost...)" is not a nested condition but leads to two sets of conditions for the whole SELECT.) - I guess you might have to enclose the cited condition in its own braces, too.

Just my 2 cents, without knowing the schema...

permanent link

answered 07 Dec '15, 06:38

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

converted 07 Dec '15, 09:23

Comment Text Removed

thanks, are we talking about and (@min_ValuatnDt is null or a.valuation_dt >= @min_ValuatnDt) and (@thru_ValuatnDt is null or a.valuation_dt <= @thru_ValuatnDt) ? but in this case both min and thru date has been initialized.
the schema are

  CREATE TABLE "dba"."PF_Daily_Holdg_detl" (
    "Valuation_dt" DATE NOT NULL,
    "Acct_grp_ID" "TAcctGrp_ID" NOT NULL,
    "subAcct_ID" "TSubAcct_ID" NOT NULL,
    "Fi_Sym" VARCHAR(20) NOT NULL,
    "sub_acct_alias_dd" VARCHAR(16) NULL,
    "Qty" "TQty_stock" NULL,
    ....,
    "Cost" "Tmoney_2" NULL,
    ...,
    PRIMARY KEY ( "Valuation_dt" ASC, "Acct_grp_ID" ASC, "subAcct_ID" ASC, "Fi_Sym" ASC )
) IN "SYSTEM";

// *******

CREATE TABLE "dba"."account" ( "acct_id" INTEGER NOT NULL DEFAULT AUTOINCREMENT, "acct_grp_id" "T__AcctGrp_ID" NOT NULL, ..., "sub_Acct_Alias" VARCHAR(16) NOT NULL, "Updated_By" VARCHAR(30) NULL, "Updated" "datetime" NULL, CONSTRAINT "PK_AccountID" PRIMARY KEY ( "acct_id" ASC ) ) IN "SYSTEM";

// *******

CREATE TABLE "dba"."SubAcct" ( "subAcct_ID" "TSubAcct_ID" NOT NULL DEFAULT AUTOINCREMENT, "Acct_ID" "TAcct_ID" NOT NULL, ..., "acct_type_short_name" VARCHAR(28) NOT NULL UNIQUE, PRIMARY KEY ( "subAcct_ID" ASC ) ) IN "SYSTEM";


(07 Dec '15, 16:03) gg99
Replies hidden
2

No, that's not what I'm talking about.

My point (or guess) is: The condition I have cited in my answer should be enclosed in another pair of braces, otherwise your conditions are logically somewhat like

from dba.PF_Daily_Holdg_detl as p
          -- condition block 1
          where (@acct_grp_ID is null or p.acct_grp_ID = @acct_grp_ID)
          ... 
          and ((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11)

          or

          -- condition block 2
          (@ignoreCost = 11 and(cost <= 0 or cost is null))
          and qty > 0 -- 151119 added or cost is null
          and valuation_dt = ...

i.e. the ORed condition on "cost" separates your list of conditions into two logical blocks, and the filter on "(@min_ValuatnDt is null or valuation_dt >= @min_ValuatnDt) and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)" is only part of the first condition block.

In contrast, the somewhat similar condition on "cost" in the subquery that returns "max(a.valuation_dt)" has a further pair of braces, so there is just one block of ANDed conditions. That makes me think the condition in the main query block needs more braces, too.

(08 Dec '15, 03:33) Volker Barth

thank you, Volker.

Following your suggestion,I used an online brace match on the sql bit by bit, I found all the culprit and now works

BEGIN
//For an account grp within date range, find latest cost of each security within each subaccount in the acct grp
declare @Fi_Sym varchar(20)
declare @Fi_Sym_ORIG varchar(20)
declare @rbc_Sym varchar(20)
declare @fi_name varchar(20)
declare @fi_desc varchar(20)
declare @fi_short_desc varchar(20)
declare @CUSIP varchar(20)
declare @fi_sym_US varchar(20)
declare @fi_class varchar(20)
declare @fi_Exchg varchar(20)
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 @ignoreCost smallint
declare @excludeSubAcctAliasRegex varchar(1500)
declare @fi_symRegex varchar(1300)
select '2015-11-24', '2015-12-06', null , null , null, 0 into @min_ValuatnDt,@thru_ValuatnDt,@acct_grp_ID,  @subAcct_ID, @excludeSubAcctAliasRegex, @ignoreCost
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_ID in (select acct_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)/cost*100.0 end)
          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) or (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 and @ignoreCost = 1)) and @ignoreCost < 11  ) or (@ignoreCost = 11 and(cost <= 0 or cost is null)) )
      and qty>0
      and valuation_dt =
            (select max(a.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 a.valuation_dt >= @min_ValuatnDt)
                and (@thru_ValuatnDt is null or a.valuation_dt <= @thru_ValuatnDt)
                and (((cost > 0 or (cost is null and @ignoreCost = 1)) and @ignoreCost < 11  ) or (@ignoreCost = 11 and(cost <= 0 or cost is null)) )-- 151119 added or cost is null
            )
end
(08 Dec '15, 18:22) gg99

an online brace match on the sql bit by bit

Wow, I tend to use the old-school method: Counting the "brace level" on the fingers of my hands, I guess I've been trained by that in the early nineties when working with Turbo Vision:)

(10 Dec '15, 03:24) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×6

question asked: 07 Dec '15, 02:39

question was seen: 277 times

last updated: 10 Dec '15, 03:24