query help - SQL Server Forums

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

query help

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2009-03-19 : 05:55:41

i have following table

table1

LID LType LLoss LNAV
1 L_Type1 100 800
2 L_Type2 900 890

MID MType MLoss MNAV
1 M_Type1 200 800
2 M_Type2 1000 790

I am getting I/P as type may be LType or MType and depending on the Type, I want to select (LLoss, LNAV ) or (MLoss, MNAV)

for ex - if Type = L_Type1 then my query will return
L_Type1 100 800

if Type = M_Type2 then my query will return
M_Type2 1000 790

I have to handle this in view.

How I can do this?

SQLTeam

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-19 : 06:05:04

I can't get your Table Structure? will u explain?

Regards

Senthil.C
----------------------------------------------------------------------------
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

shubhada
Posting Yak Master

117 Posts

Posted - 2009-03-19 : 06:50:42

Table structure as follow

PrimaryTable

LoanID LID MID SelectedType
---------- -------- ---------- -------------
1 1 1 NULL
2 2 2 LType1
3 3 3 MType2

table1

LID LType LLoss LNAV
----- ----- -------- ----------
1 L_Type1 100 800
2 L_Type2 900 890

table2
MID MType MLoss MNAV
----- ------- -------- ----------
1 M_Type1 200 800
2 M_Type2 1000 790

i have one view follwoing is the view query

SELECT
PrimaryTable.LoanID,
PrimaryTable.SelectedType,
SValues.Description
FROM
PrimaryTable
INNER JOIN
SValues
ON
PrimaryTable.SelectedOptionControlId = SValues.ValueCode
WHERE
SValues.Table_Name = 'PrimaryTable'
AND
SValues.Column_Name = 'SelectedType'

here SValues is a lookup table. Consider my view is returning following data

2 LType1 NULL

from same view I want to select LLoss and LNAV from table1 as my ype is LType1
so my final o/p of my view should be

SELECT
PrimaryTable.LoanID,
PrimaryTable.SelectedType,
SValues.Description,
Loss,
NAV

FROM
PrimaryTable
INNER JOIN
SValues
ON
PrimaryTable.SelectedOptionControlId = SValues.ValueCode
WHERE
SValues.Table_Name = 'PrimaryTable'
AND
SValues.Column_Name = 'SelectedType'

O/P

2 LType1 NULL 100 800

My Loss and NAV value is depend on the value of current view query.

Plz tell me how I can get Loss and Nav in same view?

SQLTeam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 10:55:24

[code]
SELECT
PrimaryTable.LoanID,
PrimaryTable.SelectedType,
SValues.Description,
tmp.Val,
tmp.NAV
FROM
PrimaryTable
INNER JOIN
SValues
ON
PrimaryTable.SelectedOptionControlId = SValues.ValueCode
WHERE
SValues.Table_Name = 'PrimaryTable'
AND
SValues.Column_Name = 'SelectedType'
LEFT JOIN (SELECT LID AS ID, LType AS Type, LLoss AS Val, LNAV AS NAV
FROM table1
UNION ALL
SELECT MID MType MLoss MNAV
FROM table2) tmp
ON tmp.Type=PrimaryTable.SelectedType
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources