How to get additional rows based on column val

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get additional rows based on column val

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-03-13 : 13:35:00

Hello,

I am unable to figure out how to proceed after trying for more than a day. Should I add a parameter to the stored proc? How do I proceed?

I need to be able to show data for EdgeID 2,3,5,6,20,21 and so on...Right now I am showing data for 1, 4, 19 and so on based on the ReltTotID based on the result set below. This is because the table that the query below is selecting from adds up all common EdgeIDs to give one row for example

EdgeID Desc TermType ReltTotID

1 Global Edge Model w/ Fwd Earn II T 1
2 Short Term Global Edge Model w Fwd Earn II S 1
3 Long Term Global Edge Model w Fwd Earn II L 1
4 Emerging Market Edge Model w Fwd Earn T 4
5 Short Term EM Edge Model w Fwd Earn S 4
6 Long Term EM Edge Model w Fwd Earn L 4
19 SmallCap Edge Model w/ Fwd Earn T 19
20 SmallCap Short Term Edge Model w/ Fwd Earn S 19
21 SmallCap Long Term Edge Model w/ Fwd Earn L 19
35 Global+EM Edge Model w Fwd Earn T 35

The final query result is :

EdgeID Description Short Desc PerID UnivID DefID

1 Global Edge Global Developed 50062 23559 38
4 Emerging Market Emerging Markets 50063 23560 39
19 SmallCap Edge Small Cap Edge 50064 23642 44

I would like it to be :

1 Global Edge Global Developed 50062 23559 38
2 Short Term Global Developed NULL 23559 38
3 Long Term Global Developed NULL 23559 38
4 Emerging Market Emerging Markets 50063 23560 39
5 Short Term Emerging Markets NULL 23560 39
6 Long Term Emerging Markets NULL 23560 39
19 SmallCap Edge Small Cap Edge 50064 23642 44
19 Short Term Small Cap Edge NULL 23642 44
19 Long Term Small Cap Edge NULL 23642 44

The stored proc query is as below:

SELECT
EdgeModelID = em.EdgeModelID
--, EdgeModelID = em.EdgeModelID
, Description = m.Description
, ShortDescription = ISNULL(emdn.ParameterValue, m.ShortDescription)
, ViewPermissionID = emdp_perm.ParameterValue
, EdgeUniverseID = univ.UniverseID
, EdgeDefinitionID = univ.MemberID

FROM OptMod..GO_EdgeModels em

JOIN OptMod..GO_Models m
ON em.EdgeModelID = m.ModelID
AND m.ModelType = 'E'
AND Status = 1

JOIN OptMod..GO_EdgeModelDisplayParameters emdp
ON emdp.EdgeModelID = em.EdgeModelID
AND emdp.ParameterName = 'NewEdge32 Screening'

LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn
ON emdn.EdgeModelID = em.EdgeModelID
AND emdn.ParameterName = 'NewEdge32 Display Name'

LEFT JOIN OptMod..GO_ModelUniverses mu
ON em.EdgeModelID = mu.ModelID

LEFT JOIN OptMod..vUniverses univ
ON mu.UniverseID = univ.UniverseID

LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm
ON emdp_perm.EdgeModelID = em.EdgeModelID
AND emdp_perm.ParameterName = 'NewEdge32 Permissions'

WHERE em.EdgeModelID = em.RelatedTotalEdgeModelID

Thanks in advance!!!
sqlnovice123

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-03-14 : 10:29:57

Hello,

What am I doing wrong?

I have tried to store the column value(EdgeModelID) for rows that I want added in a variable using a cursor. I get the following result set:

RelatedTotalEdgeModelID
2

RelatedEdgeModelID
3

RelatedEdgeModelID
5

RelatedEdgeModelID
6

EdgeModelID Description

The query that I used is:

ALTER PROCEDURE ASP_GetEdgeModelsForComparison
AS

DECLARE @EdgeModelCompID INTEGER
DECLARE go_edgemodels CURSOR FOR
SELECT EdgeModelID
FROM OptMod..GO_EdgeModels em
JOIN OptMod..GO_Models m
ON em.EdgeModelID = m.ModelID
WHERE TermType in ('S', 'L')
AND Status = 1
AND ModelType = 'E'
--SELECT @EdgeModelCompID

OPEN go_edgemodels
FETCH NEXT FROM go_edgemodels INTO @EdgeModelCompID

IF @@FETCH_STATUS <> 0
PRINT ' <<No Edge Models>>'

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT DISTINCT RelatedTotalEdgeModelID =@EdgeModelCompID
FROM OptMod..GO_EdgeModels

FETCH NEXT FROM go_edgemodels INTO @EdgeModelCompID
END

CLOSE go_edgemodels
DEALLOCATE go_edgemodels

SELECT EdgeModelID = em.EdgeModelID
, Description = m.Description
, ShortDescription = ISNULL(emdn.ParameterValue, m.ShortDescription)
, ViewPermissionID = emdp_perm.ParameterValue
, EdgeUniverseID = univ.UniverseID
, EdgeDefinitionID = univ.MemberID

FROM OptMod..GO_EdgeModels em

JOIN OptMod..GO_Models m
ON em.EdgeModelID = m.ModelID
AND m.ModelType = 'E'
AND Status = 1

JOIN OptMod..GO_EdgeModelDisplayParameters emdp
--ON emdp.EdgeModelID = em.EdgeModelID
ON emdp.EdgeModelID = @EdgeModelCompID
AND emdp.ParameterName = 'NewEdge32 Screening'

LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn
--ON emdn.EdgeModelID = em.EdgeModelID
ON emdp.EdgeModelID = @EdgeModelCompID
AND emdn.ParameterName = 'NewEdge32 Display Name'

LEFT JOIN OptMod..GO_ModelUniverses mu
ON em.EdgeModelID = mu.ModelID

LEFT JOIN OptMod..vUniverses univ
ON mu.UniverseID = univ.UniverseID

LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm
ON emdp_perm.EdgeModelID = em.EdgeModelID
AND emdp_perm.ParameterName = 'NewEdge32 Permissions'

--WHERE em.EdgeModelID = em.RelatedTotalEdgeModelID
WHERE em.EdgeModelID = @EdgeModelCompID

RETURN 0
GO

The rows that need to be added for IDs 2,3,5,6 are:

2 Short Term Global Edge Model w Fwd Earn II
3 Long Term Global Edge Model w Fwd Earn II
5 Short Term EM Edge Model w Fwd Earn
6 Long Term EM Edge Model w Fwd Earn

The rows that are displayed by the query below:

1 Global Edge Model w/ Fwd Earn II
4 Emerging Market Edge Model w Fwd Earn

SELECT EdgeModelID = em.EdgeModelID
, Description = m.Description
, ShortDescription = ISNULL(emdn.ParameterValue, m.ShortDescription)
, ViewPermissionID = emdp_perm.ParameterValue
, EdgeUniverseID = univ.UniverseID
, EdgeDefinitionID = univ.MemberID

FROM OptMod..GO_EdgeModels em

JOIN OptMod..GO_Models m
ON em.EdgeModelID = m.ModelID
AND m.ModelType = 'E'
AND Status = 1

JOIN OptMod..GO_EdgeModelDisplayParameters emdp
ON emdp.EdgeModelID = em.EdgeModelID
AND emdp.ParameterName = 'NewEdge32 Screening'

LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn
ON emdn.EdgeModelID = em.EdgeModelID
AND emdn.ParameterName = 'NewEdge32 Display Name'

LEFT JOIN OptMod..GO_ModelUniverses mu
ON em.EdgeModelID = mu.ModelID

LEFT JOIN OptMod..vUniverses univ
ON mu.UniverseID = univ.UniverseID

LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm
ON emdp_perm.EdgeModelID = em.EdgeModelID
AND emdp_perm.ParameterName = 'NewEdge32 Permissions'

WHERE em.EdgeModelID = em.RelatedTotalEdgeModelID

Thanks in advance!!!
sqlnovice123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources