help with store procedure

Question

help with store procedure

navie22

SSC-Addicted

Points: 494
More actions
May 20, 2014 at 6:13 pm

#307488

i need to fix existing procedure, which is written by someoneelse. i don't have lot of details. only think they gave me is requirement. Create/adjust stored procedure (RD_SOMart.dbo.spFCOProductHierachyCode) to UPDATE RD_SOMart.dbo.SO_STAR_FACTSEXT_FLS & SET PROD_HIER_CODE = logic in SP and SET PROD_HIER_CODE_FLSUPDATEDATE = getdate()
existing procedure:
SELECT SOFACTS.RDFACTSEXTNUMID
,SOFACTS.RDSOURCENUMID
,SOFACTS.RDFACTSEXTCHARID
,SOFACTS.RDFACTSHDRNUMID
,SOFACTS.RDFACTSHDRCHARID
,SOFACTS.RDSOURCETBLID
,SOFACTS.RDTRANSTYPENUMID
,
--SETS.SETNAME,
--FCOPRODHIER_ITEMCAT.RDATTRIBUTE001CHARCODE,
CASE
WHEN CHARINDEX('AP-', Upper(SETS.SETNAME)) > 0
THEN 'DKZZZZZZ'
ELSE ISNULL(FCOPRODHIER_ITEMCAT.RDATTRIBUTE001CHARCODE, 'ZZZZZZZZ')
END 'PROD_HIER_CODE'
,SOFACTS.RDINSERTDATE 'SOFACTS_RDINSERTDATE'
,SETS.RDINSERTDATE 'SETS_RDINSERTDATE'
,FCOPRODHIER_ITEMCAT.RDINSERTDATE 'ITEMCAT_RDINSERTDATE'
,SOFACTS.[RDINSERTINDICATOR]
,SOFACTS.[RDINSERTDATE]
,SOFACTS.[RDCHANGEINDICATOR]
,SOFACTS.[RDCHANGEDATE]
,SOFACTS.[RDDELETEINDICATOR]
,SOFACTS.[RDDELETEDATE]
,SOFACTS.[RDPURGEDATE]
FROM RD_CMMart.dbo.STAR_ITEMCATEGORY FCOPRODHIER_ITEMCAT
RIGHT OUTER JOIN RD_CMMart.dbo.STAR_ITEM_CATEGORY_XREF FCOPRODHIER_ITEMCATXREF ON (
FCOPRODHIER_ITEMCAT.RDITEMCATEGORYNUMID = FCOPRODHIER_ITEMCATXREF.DFN002
AND FCOPRODHIER_ITEMCAT.LANGUAGE = 'US'
)
RIGHT OUTER JOIN RD_CMMart.dbo.STAR_ITEM_01 ITEM_EBS ON (
FCOPRODHIER_ITEMCATXREF.RDITEMNUMID = ITEM_EBS.RDITEMNUMID
AND FCOPRODHIER_ITEMCATXREF.CATEGORYSETID = 1100001823
)
INNER JOIN RD_SOMart.dbo.SO_STAR_FACTS SOFACTS ON (ITEM_EBS.RDITEMNUMID = SOFACTS.DFN004)
INNER JOIN RD_SOMart.dbo.SO_STAR_FACTSEXT_EBS FACTS_EXT_EBS ON (SOFACTS.RDFACTSEXTNUMID = FACTS_EXT_EBS.RDFACTSEXTNUMID)
INNER JOIN RD_SOMart.dbo.SO_STAR_SETS SETS ON (
SETS.RDSETNUMID = FACTS_EXT_EBS.DFN002
AND SETS.SETTYPE <> 'FULFILLMENT_SET'
)
WHERE SOFACTS.RDSOURCENUMID = 20003.
i am not good with procedures. please help me to fix this one.
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

There's not much we can do for you. You didn't share the stored procedure, you just copied a select statement. We don't know your environment and the requirement is unclear.

If you don't understand the requirements yourself, ask for clarification in your company. If you don't know how to work with stored procedures or how to create an update, don't execute anything as you can mess things up.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

navie22 SSC-Addicted Points: 494 More actions · Answer 2

their is only select statement in SP.

ALTER PROCEDURE [dbo].[ALTER PROCEDURE [dbo].[zUSP_PROD_HIER_CODE]

AS

BEGIN

SELECT SOFACTS.RDFACTSEXTNUMID

,SOFACTS.RDSOURCENUMID

,SOFACTS.RDFACTSEXTCHARID

,SOFACTS.RDFACTSHDRNUMID

,SOFACTS.RDFACTSHDRCHARID

,SOFACTS.RDSOURCETBLID

,SOFACTS.RDTRANSTYPENUMID

,

--SETS.SETNAME,

--FCOPRODHIER_ITEMCAT.RDATTRIBUTE001CHARCODE,

CASE

WHEN CHARINDEX('AP-', Upper(SETS.SETNAME)) > 0

THEN 'DKZZZZZZ'

ELSE ISNULL(FCOPRODHIER_ITEMCAT.RDATTRIBUTE001CHARCODE, 'ZZZZZZZZ')

END 'PROD_HIER_CODE'

,SOFACTS.RDINSERTDATE 'SOFACTS_RDINSERTDATE'

,SETS.RDINSERTDATE 'SETS_RDINSERTDATE'

,FCOPRODHIER_ITEMCAT.RDINSERTDATE 'ITEMCAT_RDINSERTDATE'

,SOFACTS.[RDINSERTINDICATOR]

,SOFACTS.[RDINSERTDATE]

,SOFACTS.[RDCHANGEINDICATOR]

,SOFACTS.[RDCHANGEDATE]

,SOFACTS.[RDDELETEINDICATOR]

,SOFACTS.[RDDELETEDATE]

,SOFACTS.[RDPURGEDATE]

FROM RD_CMMart.dbo.STAR_ITEMCATEGORY FCOPRODHIER_ITEMCAT

RIGHT OUTER JOIN RD_CMMart.dbo.STAR_ITEM_CATEGORY_XREF FCOPRODHIER_ITEMCATXREF ON (

FCOPRODHIER_ITEMCAT.RDITEMCATEGORYNUMID = FCOPRODHIER_ITEMCATXREF.DFN002

AND FCOPRODHIER_ITEMCAT.LANGUAGE = 'US'

)

RIGHT OUTER JOIN RD_CMMart.dbo.STAR_ITEM_01 ITEM_EBS ON (

FCOPRODHIER_ITEMCATXREF.RDITEMNUMID = ITEM_EBS.RDITEMNUMID

AND FCOPRODHIER_ITEMCATXREF.CATEGORYSETID = 1100001823

)

INNER JOIN RD_SOMart.dbo.SO_STAR_FACTS SOFACTS ON (ITEM_EBS.RDITEMNUMID = SOFACTS.DFN004)

INNER JOIN RD_SOMart.dbo.SO_STAR_FACTSEXT_EBS FACTS_EXT_EBS ON (SOFACTS.RDFACTSEXTNUMID = FACTS_EXT_EBS.RDFACTSEXTNUMID)

INNER JOIN RD_SOMart.dbo.SO_STAR_SETS SETS ON (

SETS.RDSETNUMID = FACTS_EXT_EBS.DFN002

AND SETS.SETTYPE <> 'FULFILLMENT_SET'

)

WHERE SOFACTS.RDSOURCENUMID = 40003

END

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

There's still nothing I can do to help because I don't understand what do you need. You said something about an update, but there's nothing there that could be modified to update a column. Remember, I can't see what you see.

Read the article linked in my signature about best practices to get better help.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

You really need to show us more and you really need to try and do this yourself first. We have no problems helping you with it but we really can't do the work for you since you have to be able to support this procedure not us.