Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Store Procedure Requirement extraction Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 6:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 6:45 AM
Points: 193, Visits: 328
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.
Post #1356685
Posted Monday, September 10, 2012 6:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:16 AM
Points: 461, Visits: 693
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

Post #1356689
Posted Monday, September 10, 2012 6:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 6:45 AM
Points: 193, Visits: 328
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
Post #1356693
Posted Monday, September 10, 2012 7:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:16 AM
Points: 461, Visits: 693
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

Post #1356721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse