Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

-1

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
227●29●30●38
accept rate: 10%

edited 21 Nov '15, 13:20

Hello?

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

(21 Nov '15, 14:54) Breck Carter
2 Answers:
active answersoldest answersnewest answerspopular answers
0

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
32.5k●541●726●1050
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
0

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
227●29●30●38
accept rate: 10%

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

learn more about Markdown

Question tags:

×275
×17

question asked: 21 Nov '15, 00:52

question was seen: 2,094 times

last updated: 23 Nov '15, 14:06

Related questions

Query time switches between 1x and 2x

Having table in ML publication kills update performance

What is the quickest statement?

Will heavy disk fragmentation slow down my queries?

How do I save the plan in Application Profiling?

Why does a 6000-row INTEGER index take 1.1G of disk space?

How much does the size of a database page affect its performance ?

SA-9 Using Hash Table; Degrading Performance

Internal warning: Request task 62 dispatch took 13 seconds

Query takes 2.75 seconds to count 31,800 records but takes hours to count 31,825 records...