Re-naming a columns or descriptions

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

Re-naming a columns or descriptions

Author

Topic

cherman
Starting Member

15 Posts

Posted - 2011-08-09 : 16:46:19

I have a table that with a column called udf.char5. Within the udf.char5 column there are several different names as follows

SOLE DISCRETION WITH CONSULT
SOLE DISCRETION
SHARED-APPROVAL REQUIRED
SHARED DISCRETION

I would like to create some sort of sub select with each of these names. I've tried where udf.char5 AS SHARED DISCRETION, tried where udf.char5 = SHARED DISCRETION...but need some help here.

There's no other tables that have the same info.

What I need to do is bring this into Crystal to be able to sort.

Please help. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-09 : 18:47:17

where udf.char5 = 'SHARED DISCRETION'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cherman
Starting Member

15 Posts

Posted - 2011-08-10 : 08:58:10

I tried... where f.udf_char5 = 'SOLE DISCRETION' as sole_discretion
and it gives me the following error message..."Incorrect syntax near the keyword 'as'"

cherman
Starting Member

15 Posts

Posted - 2011-08-10 : 08:59:46

here's the whole select statement where I'm getting the incorrect syntax:

select f.udf_char5
from cs_fund f
where f.udf_char5 = 'SOLE DISCRETION' as sole_discretion

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-10 : 09:01:09

quote:
I would like to create some sort of sub select with each of these names

can you explain further what are you trying to achieve here ?

KH
[spoiler]Time is always against us[/spoiler]

cherman
Starting Member

15 Posts

Posted - 2011-08-10 : 09:02:53

but, if I change the statement to:

(select f.udf_char5
from cs_fund f
where f.udf_char5 = ('SOLE DISCRETION')) as sole_discretion

then I get the following message:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

how do I need to change my query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 09:17:19

it should be


select f.udf_char5 as sole_discretion
from cs_fund f
where f.udf_char5 = 'SOLE DISCRETION'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cherman
Starting Member

15 Posts

Posted - 2011-08-10 : 10:22:27

I'm still getting an incorrect syntax error near the keyword 'from' on a line further down.

Since this is a sub-select query and the 'from cs_fund f' is already further down in my query, do I need to do something different with this sub query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-10 : 11:28:17

Why did you add "AS" to what I suggested?

Post exactly the query you are using so we can correct the syntax.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cherman
Starting Member

15 Posts

Posted - 2011-08-10 : 11:46:38

select acct_cd, acct_name, u.user_name as manager, def_mdl_type, default_model_cd, def_mdl_id,
(select display_name from ts_mdl_tree_node where def_mdl_id = mdl_tree_id) as aa_model,
udf_char2, udf_char5, tot_assets,
cash =(select sum(p.cur_mkt_val_sod)
from cs_position p, csm_security s
where cusip in ('PRIN_CASH', 'INC_CASH', 'LIQ_CASH')
and p.acct_cd = f.acct_cd
and s.sec_id = p.sec_id),
pcash =(select sum(p.cur_mkt_val_sod)
from cs_position p, csm_security s
where cusip = 'PRIN_CASH'
and p.acct_cd = f.acct_cd
and s.sec_id = p.sec_id),
icash =(select sum(p.cur_mkt_val_sod)
from cs_position p, csm_security s
where cusip = 'INC_CASH'
and p.acct_cd = f.acct_cd
and s.sec_id = p.sec_id),
lcash =(select sum(p.cur_mkt_val_sod)
from cs_position p, csm_security s
where cusip = 'LIQ_CASH'
and p.acct_cd = f.acct_cd
and s.sec_id = p.sec_id),

fixed = (select sum(p.cur_mkt_val_sod)
from cs_position p, csm_security s, csm_security_industry se, csm_industry i
where indust_name = 'T1 - 10 FIXED INCOME'
and i.indust_cd = '10'
and sec_typ_cd <> 'curr'
and s.sec_id = se.sec_id
and se.INDUST_CLASS_CD = i.INDUST_CLASS_CD
and se.INDUST_CD = i.INDUST_CD
and s.sec_id = p.sec_id
and p.acct_cd = f.acct_cd),

equity = (select sum(p.cur_mkt_val_sod)
from cs_position p, csm_security s, csm_industry i, csm_security_industry se
where indust_name = 'T1 - 20 equity'
and i.indust_cd = '20'
and s.sec_id = se.sec_id
and se.sec_id = p.sec_id
and se.INDUST_CLASS_CD = i.INDUST_CLASS_CD
and se.INDUST_CD = i.INDUST_CD
and p.acct_cd = f.acct_cd),

realestate = (select sum(p.cur_mkt_val_sod)
from cs_position p, csm_security s, csm_security_industry se, csm_industry i
where indust_name = 'T1 - 30 REAL ESTATE'
and i.indust_cd = '30'
and s.sec_id = se.sec_id
and se.INDUST_CLASS_CD = i.INDUST_CLASS_CD
and se.INDUST_CD = i.INDUST_CD
and s.sec_id = p.sec_id
and p.acct_cd = f.acct_cd),

commodities = (select sum(p.cur_mkt_val_sod)
from cs_position p, csm_security s, csm_security_industry se, csm_industry i
where indust_name = 'T1 - 40 COMMODITIES'
and i.indust_cd = '40'
and s.sec_id = se.sec_id
and se.INDUST_CLASS_CD = i.INDUST_CLASS_CD
and se.INDUST_CD = i.INDUST_CD
and s.sec_id = p.sec_id
and p.acct_cd = f.acct_cd),

other = (select sum(p.cur_mkt_val_sod)
from cs_position p, csm_security s, csm_security_industry se, csm_industry i
where indust_name = 'T1 - 90 OTHER'
and i.indust_cd = '90'
and s.sec_id = se.sec_id
and se.INDUST_CLASS_CD = i.INDUST_CLASS_CD
and se.INDUST_CD = i.INDUST_CD
and s.sec_id = p.sec_id
and p.acct_cd = f.acct_cd)

select f.udf_char5 as sole_discretion
from cs_fund f
where f.udf_char5 = 'SOLE DISCRETION';

from cs_fund f, pdf_user a, pdf_user u
where f.manager = a.user_cd and a.alt_email_addr = u.user_cd
order by acct_cd asc

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-10 : 12:11:41

Assuming your sub-query doesn't return more than one row, I think this will work for you:

<snip>and p.acct_cd = f.acct_cd),
(
select f.udf_char5 
from cs_fund f
where f.udf_char5 = 'SOLE DISCRETION'
) AS sole_discretion

from cs_fund f, pdf_user a, pdf_user u
where f.manager = a.user_cd and a.alt_email_addr = u.user_cd
order by acct_cd asc

Two comments about using ANSI syntax:
1. You should be using the AS clause to change the name or assign a name to a derived column not the "=" syntax. From BOL: The AS clause is the syntax defined in the ISO standard for assigning a name to a result set column. This is the preferred syntax to use in SQL Server.
2. Use ANSI style joins instead of just comma-separating tables.

One more comment about all the sub-queries: It looks like they are all very similar. I suspect you can do them all in one query and using CASE expressions, you can get your desired results, for example:

SUM
(
    CASE 
        WHEN indust_name = 'T1 - 40 COMMODITIES' AND i.indust_cd = '40' 
            THEN p.cur_mkt_val_sod 
        ELSE NULL 
    END
) AS   commodities,
SUM
(
    CASE 
        WHEN indust_name = 'T1 - 90 OTHER' AND i.indust_cd = '90' 
            THEN p.cur_mkt_val_sod 
        ELSE NULL 
    END
) AS   other
from cs_position p, csm_security s, csm_security_industry se, csm_industry i
where 
s.sec_id = se.sec_id
and se.INDUST_CLASS_CD = i.INDUST_CLASS_CD
and se.INDUST_CD = i.INDUST_CD
and s.sec_id = p.sec_id
and p.acct_cd = f.acct_cd

cherman
Starting Member

15 Posts

Posted - 2011-08-10 : 15:21:43

I'm still getting the following message..."Subquery returned more than 1 value."

when using...

(
select f.udf_char5
from cs_fund f
where f.udf_char5 = 'SOLE DISCRETION'
) AS sole_discretion

cherman
Starting Member

15 Posts

Posted - 2011-08-10 : 15:21:47

I'm still getting the following message..."Subquery returned more than 1 value."

when using...

(
select f.udf_char5
from cs_fund f
where f.udf_char5 = 'SOLE DISCRETION'
) AS sole_discretion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-10 : 15:56:49

The query is returning more than one row. Which one do you want?

cherman
Starting Member

15 Posts

Posted - 2011-08-10 : 16:32:24

There are 50,584 rows with 'sole discretion' and I only need to show 1 row.

How do I only have it return 1 row?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-10 : 17:43:02

There ar e abunch of ways, probably the easiest is to do a SELECT TOP 1, for example:

select TOP 1 f.udf_char5 
from cs_fund f
where f.udf_char5 = 'SOLE DISCRETION'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources