Hi,
I am trying to insert the follows rows to my production database... and this the sample data
RowPlan PART_ID FUND_ID TOT_ACT1 TOT_ACT2 Number Num1170925 129602759 19765P471 BB4928.47 CT0.00 DV26.30 GL153.75 TF0.00 WD0.00 OT0.00 EB5108.52 205.0110 24.04 206.0720 24.79 2170925 129602759 35472P406 BB2663.64 CT325.00 DV87.46 GL26.42 TF530.92 WD0.00 OT0.00 EB3633.44 189.0450 14.09 254.6210 14.27 3170925 129602759 LOAN BB1506.88 CT0.00 DV25.48 GL0.00 TF-530.92 WD0.00 OT0.00 EB1001.44 1506.88 1.00 1001.44 1.00 4170925 148603737 19765L587 BB25.14 CT0.00 DV0.46 GL-0.45 TF0.00 WD0.00 OT0.00 EB25.15 5.3830 4.67 5.4790 4.59 5170925 148603737 19765P471 BB7.48 CT0.00 DV0.05 GL0.23 TF0.00 WD0.00 OT0.00 EB7.76 0.3110 24.04 0.3130 24.79 6170925 148603737 35472P208 BB12.53 CT0.00 DV0.28 GL0.09 TF0.00 WD0.00 OT0.00 EB12.90 0.9360 13.39 0.9570 13.48 7170925 148603737 35472P604 BB7.48 CT0.00 DV0.24 GL0.15 TF0.00 WD0.00 OT0.00 EB7.87 0.4720 15.85 0.4870 16.16 8170925 148603737 315805549 BB29.72 CT0.00 DV0.00 GL2.15 TF0.00 WD0.00 OT0.00 EB31.87 1.5320 19.40 1.5320 20.80 9170925 148603737 197199102 BB5.00 CT0.00 DV0.06 GL0.27 TF0.00 WD0.00 OT0.00 EB5.33 0.1650 30.32 0.1670 31.94
So the number of rows in this table is 1007 right now my insert query inserts all the data but excepts LOAN and i want Loans inserted in a seperate column in my production dataabse but thats not happening so can some one pls take a look at this query and see whats wrong... My query is as follows
1INSERT INTO Statements..ParticipantPlanFundBalances12(3PlanId,4ParticipantId,5PeriodId,6FundId,7 Loans,8--PortfolioId,9Act1,10TotAct1,11Act2,12TotAct2,13Act3,14TotAct3,15Act4,16TotAct4,17Act5,18TotAct5,19Act6,20TotAct6,21Act7,22TotAct7,23Act8,24TotAct8,25Act9,26TotAct9,27Act10,28TotAct10,29Act11,30TotAct11,31Act12,32TotAct12,33Act13,34TotAct13,35Act14,36TotAct14,37Act15,38TotAct15,39Act16,40TotAct16,41Act17,42TotAct17,43Act18,44TotAct18,45Act19,46TotAct19,47Act20,48TotAct20,49OpeningUnits,50OPricePerUnit,51ClosingUnits,52CPricePerUnit,53AllocationPercent54)55SELECT56cp.PlanId,57p.ParticipantId,58@.PeriodId,59CaseWhen a.FUND_ID <>'LOAN'Then f.FundIdELSE 0END,60CASEWhen a.FUND_ID ='LOAN'Then'LOAN'END as Loanfunds,61--planinfo.PortfolioId,62CaseWHEN a.ACT_ID1 ='BB'Then 1END,63a.TOT_ACT1,64CaseWHEN a.ACT_ID2 ='CT'Then 2END,65a.TOT_ACT2,66CASEWhen a.ACT_ID3 ='DV'then 3END,67a.TOT_ACT3,68CASEWhen a.ACT_ID4 ='GL'Then 4End,69a.TOT_ACT4,70CAseWhen a.ACT_ID5 ='TF'THEN 5END,71 a.TOT_ACT5,72CASEWhen a.ACT_ID6 ='WD'THEN 6END,73a.TOT_ACT6,74CASEWHEN a.ACT_ID7 ='OT'THEN 7END,75a.TOT_ACT7,76CASEWhen a.ACT_ID8 ='EB'THEN 8END,77a.TOT_ACT8,78a.ACT_ID9,79a.TOT_ACT9,80a.ACT_ID10,81a.TOT_ACT10,82a.ACT_ID11,83a.TOT_ACT11,84a.ACT_ID12,85a.TOT_ACT12,86a.ACT_ID13,87a.TOT_ACT13,88a.ACT_ID14,89a.TOT_ACT14,90a.ACT_ID15,91a.TOT_ACT15,92a.ACT_ID16,93a.TOT_ACT16,94a.ACT_ID17,95a.TOT_ACT17,96a.ACT_ID18,97a.TOT_ACT18,98a.ACT_ID19,99a.TOT_ACT19,100a.ACT_ID20,101a.TOT_ACT20,102a.UNIT_OP,103a.PRICE_OP,104a.UNIT_CL,105a.PRICE_CL,106IsNull(i.ALLOC_PER1,'0.00')107FROM108ASDBF a109110--Derive the unique Plan Id111INNERJOIN Statements..ClientPlan cp112ONa.PLAN_NUM = cp.ClientPlanId113AND114cp.ClientId = @.ClientId115--Derive the unique ParticipantId from the Participant table116INNERJOIN Statements..Participant p117ONa.PART_ID = p.PartId118-- Derive the unique fund id from the Fund Table119INNERJOIN Statements..Fund f120ONa.FUND_ID = f.Cusip121OR122a.FUND_ID = f.Ticker123OR124a.FUND_ID = f.ClientFundId125LeftOuter JOIN INVSRC i126ONa.FUND_ID = i.INV_ID127AND128a.PLAN_NUM = i.Plan_Number129AND130a.PART_ID = i.PART_ID131--Get the unique portfolio name ffor the PArticipant Funds..132WHERE133--Ignore rows that failed the scrub.134a.Import = 1135AND136--Import only those that are not already in the ParticipantPlanFundBalances table137NOT EXISTS (138SELECT *139FROM140Statements..ParticipantPlanFundBalances1 pfb141WHERE142pfb.PlanId = cp.PlanId143AND144pfb.ParticipantId = p.ParticipantId145AND146pfb.PeriodId = @.PeriodId147AND148pfb.FundId = f.FundId149)
any help is appreciated.
Regards
Karen
Is there any error msg? Also can you explain this:
INNERJOIN Statements..Fund f ON a.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker ORa.FUND_ID = f.ClientFundId
Thanks for your answer, no i am not getting any error message
INNERJOIN Statements..Fund f ON a.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker ORa.FUND_ID = f.ClientFundId
and this one means... i am getting the fundId from that table and inserting it to the PlanFundbalances..
for example in the sample data i have provided.. FUND_ID can be a 5 letter word saying DODGX,(Ticker) or some alphanumeric data whose length is and ClientFund(what ever the client wants and not in our database)
So fUND_ID 19765P471 will have a fundId of 15 or whatever..
But the word Loan isnt there in the Statements..Fund f table
Hope this helps.
Regards
Karen
|||The way to debug would be to selectively comment out lines.. comment out the INSERT INTO line and just run the SELECT part. Start with the first ASDBF JOIN with ClientPlan and see if you get results. Then include the join with Participant and see if you get expected results..keep including each of the tables and see which part of the query is throwing you off. Otherwise there's really no way for us to tell what the issue is.. unless we see some sample data from each of the tables in the query and expected data into the final table...
|||This is the first 10 rows of my final table..
1178241875271041NULLNULL1425.320020.000030.0000417.400050.000060.000070.00008442.720000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.486050.12008.486052.170025.002178241875276204NULLNULL1120.090020.000034.040042.100050.000060.000070.00008126.230000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.323022.56005.498022.96000.0031782418752710302NULLNULL1119.590020.000031.6900410.410050.000060.000070.00008131.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.328014.36008.436015.610010.0041782418752711010NULLNULL1125.060020.000030.330048.830050.000060.000070.00008134.220000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00004.344028.79004.355030.820010.0051782418752711024NULLNULL1126.850020.000030.7700410.070050.000060.000070.00008137.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00003.003042.24003.020045.590010.0061782418752712040NULLNULL1121.380020.000030.0000410.520050.000060.000070.00008131.900000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.340022.73005.340024.700010.0071782418752714449NULLNULL1123.490020.000030.000049.800050.000060.000070.00008133.290000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00006.402019.29006.402020.820010.0081782418752714463NULLNULL1685.230020.000032.21004-75.820050.000060.000070.00008611.620000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000029.384023.320029.490020.740025.009178241875473493NULLNULL14320.20002110.0000349.35004-82.23005-210.800060.000070.000084186.520000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000443.55209.7400438.38009.550010.0010178241875473504NULLNULL14650.94002110.00003207.8800432.47005-648.680060.000070.000084352.610000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000305.180015.2400284.113015.320010.00
Suppose if PartID 18752 had loans i want the information to be displayed like in Line number 9
1178241875271041NULLNULL1425.320020.000030.0000417.400050.000060.000070.00008442.720000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.486050.12008.486052.170025.002178241875276204NULLNULL1120.090020.000034.040042.100050.000060.000070.00008126.230000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.323022.56005.498022.96000.0031782418752710302NULLNULL1119.590020.000031.6900410.410050.000060.000070.00008131.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.328014.36008.436015.610010.0041782418752711010NULLNULL1125.060020.000030.330048.830050.000060.000070.00008134.220000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00004.344028.79004.355030.820010.0051782418752711024NULLNULL1126.850020.000030.7700410.070050.000060.000070.00008137.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00003.003042.24003.020045.590010.0061782418752712040NULLNULL1121.380020.000030.0000410.520050.000060.000070.00008131.900000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.340022.73005.340024.700010.0071782418752714449NULLNULL1123.490020.000030.000049.800050.000060.000070.00008133.290000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00006.402019.29006.402020.820010.0081782418752714463NULLNULL1685.230020.000032.21004-75.820050.000060.000070.00008611.620000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000029.384023.320029.490020.740025.00917824 18752 7 0 LOAN other columns10178241875473493NULLNULL14320.20002110.0000349.35004-82.23005-210.800060.000070.000084186.520000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000443.55209.7400438.38009.550010.0011178241875473504NULLNULL14650.94002110.00003207.8800432.47005-648.680060.000070.000084352.610000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000305.180015.2400284.113015.320010.00
I will try debugging the sproc and see what i can acheive
Regards,
Karen
|||ndinikar hit it spot on. Are there any records in the ASDBF table that have a FUND_ID ='LOAN'?
If not, that's your problem.
If yes, then one of the joins you have is filtering them out.
|||Yes i do have 18 rows of Data where FUND_ID = 'LOAN'
|||after debugging it...
When i include this Join
JOIN Statements..Fund fON a.FUND_ID= f.Cusip
OR
a.FUND_ID= f.TickerOR
a.FUND_ID= f.ClientFundId
i am getting a problem and i solved it by giving
LeftOuterJOIN Statements..Fund fON a.FUND_ID= f.Cusip
OR
a.FUND_ID= f.TickerOR
a.FUND_ID= f.ClientFundId
Thanks a lot...
Regards
Karen
No comments:
Post a Comment