Conditional select - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Conditional select

Author

Topic

krsk
Starting Member

21 Posts

Posted - 2003-03-18 : 07:04:39

Hi :-)

I would like to join two tables as done is this SQL statement:

CREATE VIEW dbo.VW_BALI_AFS_UGE_0
AS
SELECT a.* , b.oper_name as forvalgs_navn_indland, b.oper_name as forvalgs_navn_udland

FROM dbo.BALI_AFS_UGE_0 A, dbo.bali_r3502_ref_forvalg b
WHERE
(
a.forvalgs_kode_indland = b.oper_prefix_code
or a.forvalgs_kode_udland = b.oper_prefix_code
)

My problems is that:

b.oper_name as forvalgs_navn_indland most only be selected if a.forvalgs_kode_indland is null or ''

And

b.oper_name as forvalgs_navn_udland most only be selected if a.forvalgs_kode_udland is null or ''

How is this done?

/Kristian :-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-18 : 07:14:37

You could use a CASE function in your SELECT clause....

Jay White
{0}

krsk
Starting Member

21 Posts

Posted - 2003-03-18 : 08:22:46

Thx :-)

My view now looks like this and solved my problem. Al though it isn't very pretty :-)

CREATE VIEW dbo.VW_BALI_AFS_UGE_0
AS
SELECT a.* ,
forvalgs_navn_udland =
CASE b.oper_prefix_code
WHEN a.forvalgs_kode_udland THEN b.oper_name
ELSE ''
End,
forvalgs_navn_indland =
CASE b.oper_prefix_code
WHEN a.forvalgs_kode_indland THEN b.oper_name
ELSE ''
End,
c.service_prov_name as service_provider_navn,
d.kategori

FROM dbo.BALI_AFS_UGE_0 A, dbo.bali_r3502_ref_forvalg b, dbo.bali_r3502_ref_serv_prod c, dbo.bali_r3502_ref_kategori d
WHERE (UPPER(ENHED) IN
(SELECT UPPER(authValue) AS enhed
FROM dbo.RowAccessControl
WHERE userId = DBO.getSuser_Sname() AND systemId = 'BALI' AND startDate <= getDate() AND (endDate > getdate() OR
endDate IS NULL)))
AND
(
a.forvalgs_kode_indland = b.oper_prefix_code
or a.forvalgs_kode_udland = b.oper_prefix_code
)
AND a.service_provider_kode=c.source_system_code
AND a.kat=d.kat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources