March 8, 2012 at 5:01 pm
Ok I'll probably screw this up but....
This snippet came out of a SP which send data to forms...
The Tag_data would be the client name ...
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT TAG_NAME = 'CUST_CLIENTNAME', TAG_DATA = (SELECT C.FNAME + CASE WHEN C.MNAME IS NULL THEN '' ELSE ' ' + C.MNAME END + ' ' + C.LNAME
FROM CLIENT C (NOLOCK)
WHERE C.OID = @CLIENT_OID);
Now if a client has an alias_type of "Secure Client" then I want the Tag_Data for TAG_NAME = 'CUST_CLIENTNAME'
to do the following code which is for getting the secure clients alias..
TAG_DATA = (SELECT A.FNAME + CASE WHEN A.MNAME IS NULL THEN '' ELSE ' ' + A.MNAME END + ' ' + A.LNAME FROM ALIAS A (NOLOCK)
INNER JOIN CLIENT_TO_ALIAS_COLLECTION AC (NOLOCK) ON AC.OID_LINK = A.OID
INNER JOIN ALIAS_TYPE AT (NOLOCK) ON AT.OID = A.ALIAS_TYPE_MONIKER
WHERE AT.ABBRNAME = 'Secure Client'
And AC.OID=@CLIENT_OID
AND A.EXPDATE IS NULL);
For example
Client name is JohnSmith and does not have an alias type of 'Secure Client' so tag_data for tag_NAme 'CUST_CLIENTNAME' = 'John Smith'
Client name Jane Doe DOES have an alias_type of 'Secure Client' so
so tag_data for tag_NAme 'CUST_CLIENTNAME' = 'Barbara Jones' her Alias name from the second snippet
Thanks
Joe
March 8, 2012 at 5:24 pm
jbalbo (3/8/2012)
SELECT TAG_NAME = 'CUST_CLIENTNAME', TAG_DATA = (SELECT C.FNAME + CASE WHEN C.MNAME IS NULL THEN '' ELSE ' ' + C.MNAME END + ' ' + C.LNAMEFROM CLIENT C (NOLOCK)
WHERE C.OID = @CLIENT_OID);
Easier way for this: TAG_DATA = (SELECT C.FName + ISNULL( C.MName, '') + C.LName FROM Client C WHERE c.OID = @Client_OID)
We'll get to the rest, but that'll help readability.
TAG_DATA = (SELECT A.FNAME + CASE WHEN A.MNAME IS NULL THEN '' ELSE ' ' + A.MNAME END + ' ' + A.LNAME FROM ALIAS A (NOLOCK)
INNER JOIN CLIENT_TO_ALIAS_COLLECTION AC (NOLOCK) ON AC.OID_LINK = A.OID
INNER JOIN ALIAS_TYPE AT (NOLOCK) ON AT.OID = A.ALIAS_TYPE_MONIKER
WHERE AT.ABBRNAME = 'Secure Client'
And AC.OID=@CLIENT_OID
AND A.EXPDATE IS NULL);
Okay, the problem here is that you can't get at both types at once. In this case, you want to CASE your expression. Offhand, is there a reason this is being built from a correllated subquery instead of a join?
Anyway, here's closer to what you want, I believe:
TAG_DATA = (SELECT
CASE WHEN at.ABBRName = 'Secure Client'
THEN a.FName + isnull( a.MName, '') + a.lname
ELSE c.FName + ISNULL( c.MName, '') + c.LName
END AS Tag_Data
FROM
Client AS C
LEFT JOIN
-- Note the order of the clausing here. It causes an
-- inner join to the rest of the tables BEFORE the
-- outer join is included.
Client_To_Alias_Collection AS AC
JOIN
Alias AS A
ONAC.OID_Link = A.OID
JOIN
Alias_Type AS AT
ONAT.OID = A.Alias_Type_Moniker
ONc.oid = ac.oid
AND A.EXPDate IS NULL
WHERE
c.OID = @Client_OID
)
I think that's what you're looking for. If you can provide some sample schema/data I can test the code instead of trying to off-the-cuff it.
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
March 8, 2012 at 5:46 pm
Just curious, but why did you start another thread for the same question? We still need the same information I asked you for in the other thread if you want to get better answers.
March 8, 2012 at 6:06 pm
Sorry Lynn,
I'm new at this...
Wow not only doi need a SQL class..
I need a Forum Class!!! lol
March 9, 2012 at 7:17 am
Evil,
This works great and I even understand it!! lol
Thanks
Joe
March 9, 2012 at 11:44 am
jbalbo (3/9/2012)
Evil,This works great and I even understand it!! lol
Thanks
Joe
Great, I'm glad that helped. 🙂 Good luck.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply