September 10, 2012 at 6:24 am
Hi
I have create a store procude with Application Id as Input parameter, and make few select statement on my store procedure.
My problem is with the two below statements, in terms of what I need to do. I'm struggling with Requirements extraction.
Please help.
1.Find Application Id of related ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application:
Select S_OPTY.NAME where S_OPTY.PAR_OPTY_ID = [Original Application Id] and S_OPTY.X_SALES_METHOD_ID identifies S_SALES_METHOD.NAME = either ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’.
2.Select from PR_MONEY_BFN:
Select record from PR_MONEY_BFN where:
PR_MONEY_BFN.APPLICATION_NO = S_OPTY.NAME identified above (ie for the actual ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application); and
PR_MONEY_BFN.BUS_FUNC_CLASS = ‘NTUT’ or ‘CANT’; and
PR_MONEY_BFN.BUS_FUNC_STATUS = 80.
September 10, 2012 at 6:33 am
which bit are you struggling with?
working backwards...
Can you create the second query with a variable
Can you create the second query with a fixed value.
Can you assign a value to the variable based on the first query
Can you validate the result of the first query
can you exit the procedure if the value from the first query is in error
can you define and pass the parameter to the stored procedure
September 10, 2012 at 6:37 am
Below is my store proc, all the information above has to be part of my where clause according to the requirements.
ALTER PROCEDURE [dbo].[prc_TopUpCancellation_xml]
@ApplicationID VARCHAR(10)
AS
SELECT
Application_Id
,Policy_Id
,Date_of_Disinvestment
,Original_Investment_Value
,Payout_Value
NB. now there from and where clause I have to extract it from that given Info
September 10, 2012 at 7:24 am
1.Find Application Id of related ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application:
Select S_OPTY.NAME where S_OPTY.PAR_OPTY_ID = [Original Application Id] and S_OPTY.X_SALES_METHOD_ID identifies S_SALES_METHOD.NAME = either ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’.
2.Select from PR_MONEY_BFN:
Select record from PR_MONEY_BFN where:
PR_MONEY_BFN.APPLICATION_NO = S_OPTY.NAME identified above (ie for the actual ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application); and
PR_MONEY_BFN.BUS_FUNC_CLASS = ‘NTUT’ or ‘CANT’; and
PR_MONEY_BFN.BUS_FUNC_STATUS = 80.
Your requirement is not clear. You have two fields called NAME; one on S_OPTY and one on S_SALES_METHOD. in requirement 2 You have said you want the one from S_OPTY (which you already have)
Select
M.*,
O.*
FROM
S_OPTY O
JOIN
PR_MONEY_BFN M on M.APLLICATION_NO = O.NAME
WHERE
O.PAR_OPTY_ID = @ApplicationID
if you want the one from S_SALES_METHOD
Select
M.*,
O.*
FROM
S_OPTY O
JOIN
S_SALES_METHOD S on S.X_SALES_METHOD_ID = O.X_SALES_METHOD_ID
PR_MONEY_BFN M on M.APLLICATION_NO = O.NAME
WHERE
O.PAR_OPTY_ID = @ApplicationID
I am making the assumption that the join field is called X_SALES_METHOD_ID in both tables
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply