The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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

asked 21 Nov '15, 00:52

gg99's gravatar image

gg99
193192128
accept rate: 0%

edited 21 Nov '15, 13:20

Hello?

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

(21 Nov '15, 14:54) 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
permanent link

answered 21 Nov '15, 12:22

Breck%20Carter's gravatar image

Breck Carter
26.8k422580826
accept rate: 20%

Comment Text Removed

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

(22 Nov '15, 01:38) gg99

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

permanent link

answered 22 Nov '15, 01:10

gg99's gravatar image

gg99
193192128
accept rate: 0%

edited 23 Nov '15, 14:06

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:

×239
×14

question asked: 21 Nov '15, 00:52

question was seen: 401 times

last updated: 23 Nov '15, 14:06