April 29, 2008 at 10:28 am
I have an SSIs package that dumps data into a temp table, this table has one field [office] which has a corresponding [bonuspercent], the bonus percent for each office is determined by looking up another table with effective dates, then this temp table needs to be updated with the corresponding bonus for each office in each row. I then need to insert this into a sales table.
Here is some "rough" code:
-----
update the import_tbl set bonus_perc =
(select a.bonus_perc from bonus_param_table a left join IMPORT_Tbl b on a.off = b.off
where b.off not in ('099','99') and a.effective_date =
(select max(effective_date) from bonus_param_table where effective_date <= GetDate())
)
then insert all the fields in the updated import table into the main sales table.
Please help!
thanks
P
April 29, 2008 at 12:30 pm
It looks to me like your pseudo code is just a few table names and such away from being actual code.
Can you be specific as to the difficulty you're having translating it to T-SQL?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 1:06 pm
Hi, thanks for responding,
I am posting the code, for some reason, it is not working, maybe you can help me identify the culprit! I have an insert statement and I am calling a function to populate one field, which is holdback_perc.
INSERT INTO [dbo].[SALES_TABLE]
([REP_ID],[BRANCH_ID],[SALE],[ORIG_COMM],[NFSC_CLEAR_FEE]
,[CATEGORY_ID],[DESCR],[PRODUCT_ID],[SYMBOL],[CUSIP]
,[CLIENT_NAME],[CLIENT_ACCT],[CLIENT_SSN],[SETTLE_DATE],[TRADE_DATE],[PS_FLAG]
,[TRANS_TYPE],[GL_OFF],[PAYMENT_TAG],[PAYMENT_DATE],[RECORD_STATUS]
,[REV_CALC_PERC],[RECORD_IMPORT_DT],[ORIGNAL_SALE_ID],[ORG_SYSTEM]
,[PYMNT_MONTH],[PYMNT_YEAR])
Select [AE_NBR],[BRANCH_ID],[SALE],[ORIG_COMM_N],[NFSC_CLEAR_FEE]
,[CATEGORY_ID],[SECURITY_DESCR],[PRODUCT_ID],[SECURITY_NBR],[CUSIP_NO]
,[CUST_NAME],[CUST_ACCT_INFO],[CUST_SSN],[SETTLE_DATE],[TRADE_DATE],coalesce([PURCH_SALE_FLAG],'N'),coalesce([TRANS_TYPE],'NA')
,[GL_OFF_N],[PAYME_TAG],[PAYMENT_DATE],'A',
dbo.GetHoldBackPercEff_NEW(GL_OFF)
, ID,'MNO', Month(Payment_date), Year(Payment_date)
FROM [dbo].[MNO_IMPORT_TBL] where IMP_STATUS = 'V' and GL_OFF_N is not null and ((GL_OFF_N <> '99') or (Empl_ID in (SELECT Empl_Id from Employee_Details_Tbl WHERE BWIS_Title = 61)))
*****************************
CREATE FUNCTION [dbo].[GetHoldBackPercEff_New]
(
@GL_Off varchar
)
RETURNS float
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar as float
-- Add the T-SQL statements to compute the return value here
SELECT @ResultVar = Holdback_perc from holdback_param_table where effective_date = (select max(effective_date) from holdback_param_table where effective_date <= GetDate())and GL_off=@Gl_Off
-- Return the result of the function
IF (@ResultVar IS NULL)
begin
SET @ResultVar = 0
end
RETURN @ResultVar
END
April 29, 2008 at 1:42 pm
In what way is it not working?
Doesn't give you the results you expect?
Causes an error?
Something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 2:16 pm
I am not getting any errors, When I run the store procedure, nothing gets inserted, what I need to know is how to select the gl_off of each row and pass it to that function. Do I do some kind of looping and insert each row instead of the bulk insert? if so I am not sure how to do it. Thanks for your time.
April 29, 2008 at 2:26 pm
What does the select do if you run it without the insert? How about if you run it without the inline function?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 2:38 pm
The select statement would give me all the records from the import table. I am able to do the bulk insert, but the problem is for just one field, which is [REV_CALC_PERC] , this is where I need the value for each row of data.
Ideally, The function is supposed to return the value for the field [REV_CALC_PERC] and insert it for each row.
Hope I made it more clear.
April 29, 2008 at 5:24 pm
padmaja.mantha (4/29/2008)
The select statement would give me all the records from the import table. I am able to do the bulk insert, but the problem is for just one field, which is [REV_CALC_PERC] , this is where I need the value for each row of data.Ideally, The function is supposed to return the value for the field [REV_CALC_PERC] and insert it for each row.
Hope I made it more clear.
why don't you use use a default value for the column [rev_calc_perc] to get the result you needed automatically?
ex from bol:
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
April 29, 2008 at 5:39 pm
Hi, I do not understand what you mean.....I cannot have a default value, the whole idea is to get the holdback_perc for each row depending on what the gl_off is for that row...
how will a default automatically do it?
Thanks for your input
April 30, 2008 at 12:37 pm
padmaja.mantha (4/29/2008)
The select statement would give me all the records from the import table. I am able to do the bulk insert, but the problem is for just one field, which is [REV_CALC_PERC] , this is where I need the value for each row of data.Ideally, The function is supposed to return the value for the field [REV_CALC_PERC] and insert it for each row.
Hope I made it more clear.
Have you tried running the select statement separately, like I suggested? I can't quite tell from your reply.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply