June 13, 2013 at 2:16 pm
Trying to accomplish something like the following:
Select
(SELECT
Begin the loop:
LTRIM(RTRIM(RIGHT(coalesce(fielda,'') + coalesce(fieldb,'') + coalesce(fieldc,''))
,LEN(coalesce(fielda,'') + coalesce(fieldb,'') + coalesce(fieldc,''))
,-CHARINDEX('-',(coalesce(fielda,'') + coalesce(fieldb,'') + coalesce(fieldc,''))))))
when result = '1a' Then 145
when result = '1b' Then 146
when result = '2a' Then 147
when result = '2b' Then 148
End the loop
FROM tablename2) as Col1
,Col2
,Col3
From tablename
June 13, 2013 at 2:22 pm
Huh?
Well, first, you can't loop inside a select statement, so that's out. You CAN subquery, but I'm not sure that'll get you where you're trying to go... well, that's because I'm just not sure where you're trying to go, actually.
Can you create a bit of sample schema/data and an example of the result you're trying to get from that?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 13, 2013 at 4:02 pm
It should be something similar to this I guess. You need to identify the columns for the table join though:
Select (SELECT
case LTRIM(RTRIM(RIGHT(coalesce(t2.fielda,'') + coalesce(t2.fieldb,'') + coalesce(t2.fieldc,''))
,LEN(coalesce(t2.fielda,'') + coalesce(t2.fieldb,'') + coalesce(t2.fieldc,''))
,-CHARINDEX('-',(coalesce(t2.fielda,'') + coalesce(t2.fieldb,'') + coalesce(t2.fieldc,''))))))
when '1a' Then 145
when '1b' Then 146
when '2a' Then 147
when '2b' Then 148 end as Col1
FROM tablename2 t2 where t2.your_pk_or_fk = t1.your_pk) as Col1, t1.Col2 ,t1.Col3
From tablename t1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply