Anybody knows the syntax? Thanks

declare @.lastmonth char(7)
select comment from tbldescription
where themonth = code.getlastmonth() <-- syntax wrong
getlastmonth() is a function returning last month in form like '2006.02'
Anybody knows the right syntax, please email me back. Thanks!It is not completely clear what are you trying to get.
1. If you want to use you custom code function, then
just use general sql pane (command type=text) and as you data source use:
="select comment from tbldescription where themonth ='"+code.getlastmonth()
+"'"
2. use just a plain sql query (command type=table direct)
select comment from tbldescription where YEAR(yourdatetime_field) =YEAR(GETDATE()) and MONTH(yourdatetime_field) = MONTH (GETDATE()) -1
Hope it helps
Oleg Yevteyev,
San Diego, CA
It is OK to contact me with a contracting opportunity.
"myfirstname"001atgmaildotcom.
Replace "myfirstname" with Oleg.
--
"Henry Chen" <HenryChen@.discussions.microsoft.com> wrote in message
news:5A98BD40-5FB4-4383-B307-4D38D7507690@.microsoft.com...
> declare @.lastmonth char(7)
> select comment from tbldescription
> where themonth = code.getlastmonth() <-- syntax wrong
>
> getlastmonth() is a function returning last month in form like '2006.02'
> Anybody knows the right syntax, please email me back. Thanks!
>
>|||I am creating a data connection and the three line codes are in the dataset
panel, then I click on refresh tab, it generated an error, why? that is my
question.
"Henry Chen" wrote:
> declare @.lastmonth char(7)
> select comment from tbldescription
> where themonth = code.getlastmonth() <-- syntax wrong
>
> getlastmonth() is a function returning last month in form like '2006.02'
> Anybody knows the right syntax, please email me back. Thanks!
>
>|||Hi,
"Why?" That's because SQL server doesn't understand what you are trying to
write and which is not in the SQL SERVER scope. This is used inside the
report.(code.getlastmonth()) Instead use sqlserver commands to get the last
month in your format. Can take it from the last post.
Amarnath
"Henry Chen" wrote:
> I am creating a data connection and the three line codes are in the dataset
> panel, then I click on refresh tab, it generated an error, why? that is my
> question.
> "Henry Chen" wrote:
> > declare @.lastmonth char(7)
> >
> > select comment from tbldescription
> > where themonth = code.getlastmonth() <-- syntax wrong
> >
> >
> > getlastmonth() is a function returning last month in form like '2006.02'
> >
> > Anybody knows the right syntax, please email me back. Thanks!
> >
> >
> >|||the correct answer is
="select comment from tbldescription where themonth = '" +
code.getlastmonth() + "'"
in ONE LINE.
so you are right.
Thanks!
"Henry Chen" wrote:
> declare @.lastmonth char(7)
> select comment from tbldescription
> where themonth = code.getlastmonth() <-- syntax wrong
>
> getlastmonth() is a function returning last month in form like '2006.02'
> Anybody knows the right syntax, please email me back. Thanks!
>
>sql

Any work around for 4000 char maximum limitation in Expression

Hello,

I have to build dynamic sql statement in an SQL task.

The SQL statement is way over 4000 char.

The expression builder complains the length of the expression.

Any work around to this limitation?

Thanks a lot!

Add multiple varchar(4000) together.

DECLARE @.sql1 varchar(4000), @.sql2 varchar(4000), @.sql3 varchar(4000)

SET @.sql1 = 'stuff'
SET @.sql2 = 'more stuff'
SET @.sql3 = 'even more stuff'

EXEC (@.sql1+@.sql2+@.sql3)

|||Are you building the expression using variables and other things, or can you stick that SQL statement in a view?|||Instead of using an expression, you could build your SQL statement in a Script Task and write the result out to a file. The Execute SQL Task can read statements from a file connection. The OLE DB Source component cannot, but you could use the Execute SQL Task to put the results in a variable, then shred the results in your Data Flow using a Script Source Component.

Any work around for 4000 char maximum limitation in Expression

Hello,

I have to build dynamic sql statement in an SQL task.

The SQL statement is way over 4000 char.

The expression builder complains the length of the expression.

Any work around to this limitation?

Thanks a lot!

Add multiple varchar(4000) together.

DECLARE @.sql1 varchar(4000), @.sql2 varchar(4000), @.sql3 varchar(4000)

SET @.sql1 = 'stuff'
SET @.sql2 = 'more stuff'
SET @.sql3 = 'even more stuff'

EXEC (@.sql1+@.sql2+@.sql3)

|||Are you building the expression using variables and other things, or can you stick that SQL statement in a view?|||Instead of using an expression, you could build your SQL statement in a Script Task and write the result out to a file. The Execute SQL Task can read statements from a file connection. The OLE DB Source component cannot, but you could use the Execute SQL Task to put the results in a variable, then shred the results in your Data Flow using a Script Source Component.
sql

Any ideas on how to speed up this sp?

it is working but takes about 3-4 seconds per exec.

CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as

if (@.p_asofdate <= '00000000')
begin
set @.p_asofdate = '99999999'
end

delete from XAPAPTTOT
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate

insert into XAPAPTTOT
select apph_comp, apph_vend, apph_type, apph_id, @.p_asofdate,
sum(apph_paymnts),
sum(apph_discts),
sum(apph_adjts),
count(apph_paymnts),
sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) +
b.apt_gross,
0,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0'))
from APPHISTF.a join APTRANF.b on b.apt_comp = a.apph_comp and b.apt_vend = a.apph_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id
where ((a.apph_comp = @.p_comp) and (a.apph_vend = @.p_vend) and (a.apph_unpost_dt = 0)
and (str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate))
or ((a.apph_unpost_dt > 0 and a.apph_unpost_dt <= @.p_asofdate and b.apt_unposted_fg = 1 and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
or (((str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate) and a.apph_unpost_dt > @.p_asofdate and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
group by apph_comp, apph_vend, apph_type, apph_id

update XAPAPTTOT
set xapt_last_payck =
(select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GOPost the DDL and the indexes for the tables. Read the hint sticky at the top of the forum. Sample data might help as well, but what kind of volume are we talking about?|||the volume varies(multiple customers)
1,000-10,000 APTRANF and APPHISTF records
the Relationship between the tables is for each APTRANF record
you can have 0 to 9999 APPHISTF records.(generally only 0 or 1)
in unusually instances the APPHISTF might have 2-5 records.
the APPHISTF is a payment history(detail) to the APTRANF(master)
we allow unposting of a payment (apph_unpost_dt) and reissueing a new payment.
not sure about DDL ?|||Read this link here

http://www.dbforums.com/t1196943.html|||Hi,
Since I don't have any idea of your table structures and indexes thereon, I would go with eliminating redundencies in your code to reduce time. Several of the conditions and calculations are repeated and have now been changed to occur once. The code is given below. Hope this helps:

CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as

if (@.p_asofdate <= '00000000')
set @.p_asofdate = '99999999'

delete from XAPAPTTOT
where xapt_comp = @.p_comp
and xapt_vend = @.p_vend
and xapt_asof_date = @.p_asofdate

insert into XAPAPTTOT
select apph_comp
,apph_vend
,apph_type
,apph_id
,@.p_asofdate
,sum(apph_paymnts)
,sum(apph_discts)
,sum(apph_adjts)
,count(apph_paymnts)
,sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) + b.apt_gross
,0
,max(str_1)
from (select apph_comp
,apph_vend
,apph_type
,apph_id
,apph_paymnts
,apph_discts
,apph_adjts
,apph_paymnts
,apph_unpost_dt
,str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') str_1
from APPHISTF
where apph_comp = @.p_comp
and apph_vend = @.p_vend) a

inner join

(select apt_gross
,apt_type
,apt_id
,apt_unposted_fg
from APTRANF
where bapt_comp = @.p_comp
and apt_vend = @.p_vend) b

on ( b.apt_type = a.apph_type
and b.apt_id = a.apph_id)

where (a.apph_unpost_dt = 0
and a.str_1 <= @.p_asofdate)
or (a.apph_unpost_dt > 0
and a.apph_unpost_dt <= @.p_asofdate
and b.apt_unposted_fg = 1)
or (a.str_1 <= @.p_asofdate
and a.apph_unpost_dt > @.p_asofdate)

group by apph_comp, apph_vend, apph_type, apph_id

update XAPAPTTOT
set xapt_last_payck = (select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp
and apph_vend = xapt_vend
and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GO


--Scalability Experts.