Query Challange

Im having some problems with a query Im working on and Ive googled it up and down and Im missing something. I have a subquery and I want the results, which return multiple columns, joined to the current tables. I'll try and explain a little better. One of my fields is called 'Assignee_type' and another is 'Assignee_ID'. Now, depending on the 'Assignee_type' depends on the table it looks up the 'Assignee_ID' in. Which is cool, a simple case statement inside the select. If the Assignee_type = 1 then it looks up the values in the Agent table, if its 2 then it looks in the agency table. Here is the cleaned up, help edition so its easier and cleaner to read:

DECLARE @.IDINTSET @.ID = 8--TYPE-- AGENT = 1-- AGENCY = 2SELECT A.ID,CASEWHEN A.ASSIGNEE_TYPE = 1THEN (SELECT AG.AGENT_ID ID, AG.FIRST_NAME +' ' + AG.LAST_NAME [NAME]FROM AGENT AG (NOLOCK)WHERE AG.AGENT_ID = A.ASSIGNEE_ID)WHEN A.ASSIGNEE_TYPE = 2THEN (SELECT AGY.ID, AGY.[NAME]FROM AGENCY AGY(NOLOCK)WHERE AGY.ID = A.ASSIGNEE_ID)END ASSIGNEEFROM APPOINTMENT A (NOLOCK)WHERE A.ID = @.id

Now if I take a column out of each subquery in case statement, so that it returns only 1, it works fine, but I want possibly up to 10 more fields. I tried Temporary Tables, CROSS APPLY and I cant seem to get it.Any help or suggestions would be great. TIA.

I don't know if it was ok to do it as a stored procedure, or if it has to be regular sql... but anyway, here is a stored procedure that does. If you need it as plain sql let me know, and I'll see if I can help

CREATEPROCEDURE sp_appointment

@.IDINT

AS

BEGIN

SETNOCOUNTON;

declare @.TypeINT

declare @.AssigneeIdINT

select @.Type= Assignee_Type, @.AssigneeId= Assignee_Idfrom appointmentwhere id= @.id

if @.Type= 1

begin

SELECT @.ID, AG.AGENT_ID ID,AG.FIRST_NAME+' '+ AG.LAST_NAME [NAME]

FROM AGENT AG(NOLOCK)

WHERE AG.AGENT_ID= @.AssigneeId

end

if @.Type= 2

begin

SELECT @.ID, AGY.ID,AGY.[NAME]

FROM AGENCY AGY(NOLOCK)

WHERE AGY.ID= @.AssigneeIdend

END

GO

|||

SELECT A.ID,A.ASSIGNEE_ID,NAME

FROM Appointment a

LEFT JOIN (

SELECT 1 AS [Type],AGENT_ID AS ID,FIRST_NAME + ' ' + LAST_NAME AS [NAME]

FROM Agent

UNION ALL

SELECT 2 AS [Type],ID,NAME

FROM Agency

) t1

ON t1.ID=A.ASSIGNEE_ID AND t1.[Type]=A.ASSIGNEE_TYPE

WHEREA.ID=@.ID

|||

Try selecting the main table into a temporary table and then run a series of updates, one for each type. When all updates are done, select from the temporary table, to get your output.

(By temporary table, I am referring to an in-memory table variable.)

|||

DECLARE @.IDINTDECLARE @.A_TYPEINTDECLARE @.ttable( idintnot null,name varchar(50)null)SET @.ID = 8SELECT @.A_TYPE = A.IDFROM APPOINTMENT A (NOLOCK)WHERE A.ID = @.idif(@.A_TYPE = 1)begin insert into @.tselect AG.AGENT_ID ID,AG.FIRST_NAME +' ' + AG.LAST_NAME [NAME]from AGENT AG (NOLOCK)where (AG.AGENT_ID = A.ASSIGNEE_ID)end else if(@.A_TYPE = 2)begin insert into @.tselect AGY.ID,AGY.[NAME]from AGENCY AGY(NOLOCK)where AGY.ID = A.ASSIGNEE_ID)endselect *from @.t

|||

Thank you all for your responses. It helped me overcome this challenge and get the results I was looking for and needed.

Klaus - I originally did it this way, but I noticed I would have to repeat alot of code and I knew there had to be a simpler solution, non-the-less it works.

Motley - THANKS. I liked this solution and integrated it into my sproc. UNION ALL was perfect but I didnt think of it :)

Kyle - I didnt get a chance to try your solution, but thats one thing I tried and I couldnt get it to work (table variable), but your solution looks great.

Thanks all again.

But here is the final solution for others to stumble across who is looking:

ALTER PROCEDURE dbo.SEARCH_APPOINTMENT_DETAILS_BYID ( @.idint )ASSELECT'Code: ' +CASE A.CODEWHEN''THEN'N/A'ELSE A.CODEEND CODE,CONVERT(VARCHAR(10), A.EFFECTIVE_DT, 101) EFFECTIVE_DT,ISNULL(CONVERT(VARCHAR(10), A.END_DATE, 101),'PERPETUAL') END_DATE, A.NOTES,ISNULL(AFF.[NAME],' No Affiliate') AFFILIATE_NAME, CR.[NAME] CARRIER_NAME, C.CODE CONTRACT_CODE, t1.ID ASSIGNEE_ID, t1.[NAME] ASSIGNEE_NAME, t1.ADDRESS1, t1.ADDRESS3, t1., t1.PHONEFROM APPOINTMENT A (NOLOCK)LEFTOUTER JOIN [CONTRACT] C (NOLOCK)ON C.ID = A.CONTRACT_IDLEFTOUTER JOIN CARRIER CR (NOLOCK)ON CR.ID = C.CARRIER_IDLEFTOUTER JOIN CARRIER AFF (NOLOCK)ON AFF.ID = A.APP_AFFILIATE_IDLEFTOUTER JOIN (SELECT 1AS [TYPE], AGENT_ID ID, FIRST_NAME +' ' + LAST_NAME [NAME], ADDRESS1, CITY +' ' + STATE +' ' + POSTAL_CODE ADDRESS3,ISNULL(EMAIL_ADDRESS,'N/A') EMAIL, ISNULL(DBO.FUNC_FORMATPHONENUMBER(CONTACT1_VALUE),'') PHONEFROM AGENT (NOLOCK)WHERE DELETED <> 1UNIONALLSELECT 2AS [TYPE], A.ID, A.[NAME], A.ADDRESS ADDRESS1, A.CITY +' ' + S.ABBR +' ' + A.ZIP ADDRESS3,'N/A' EMAIL,ISNULL(DBO.FUNC_FORMATPHONENUMBER(A.PHONE),'') PHONEFROM AGENCY ALEFTOUTER JOIN STATE SON S.ID = A.STATE_ID )t1ON t1.ID=A.ASSIGNEE_IDAND t1.[TYPE]=A.ASSIGNEE_TYPEWHERE A.DELETED <> 1AND A.ID = @.id

Query behaviour - (subquery results)

Hi there,
I'm experiencing some very strange effects when I trying to include a
subquery. I shall demonstrate with a simple example...
create table test
(ind int,
seq int,
message varchar(255))
insert into test (ind,seq, message) values
(1,1,'date=01/06/2006')
insert into test (ind,seq, message) values
(2,1,'date=1/12/2005')
insert into test (ind,seq, message) values
(2,2,'test')
insert into test (ind,seq, message) values
(2,3,'date=2/12/2005')
The column IND is theoretically a foreign key, the SEQ is a primary key. A
quick explanation is that this is a comment table from a main table (main
table being an 'order' table and this being a 'order comment' table.. the
relationship being (order) 1:m (comment) But for this example this doesn't
really matter.
So here are 2 queries.
select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This one simple extracts the date from the text string. It works OK. I've
had to include the IND =1 to avoid the date conversion error. It just shows
that the conversion works.
results
IND SEQ Message
1 1 date=01/06/2006
The second query...
select max(seq) from test t2
where t2.message like 'date=%'
group by ind
This is extrating the highest 'SEQ' for each 'IND'. ie the last comment
(that has got a date component) for each order
results
SEQ
1
3
So thats OK.
Now the fun starts when I try to combine the two...
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This causes a
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'.
So, its implying the date format is incorrect. If I remove the convert :-
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
then the results are OK -
IND SEQ Message
1 1 date=01/06/2006
2 3 date=2/12/2005
Any help please?
thanks
Simon(...)
--Replace the where with and AND -->
AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
HTH, jens Suessmeyer.|||Sorry my bad typo... I meant AND... The query should have been
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
this results in the error. This isn't just a syntax error.
Thanks though
"Jens" wrote:

> (...)
> --Replace the where with and AND -->
> AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> HTH, jens Suessmeyer.
>|||A flaw in the substring function:
...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
Maybe that's it.
ML
ML
http://milambda.blogspot.com/|||I don't think this is it...
If I do a
select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
from test
I get the results..
IND SEQ converted date
1 1 01/06/2006
2 1 1/12/2005
2 2 test
2 3 2/12/2005
So this shows that 3 of the rows can be converted into datetime (103 style).
I think that the problem is that the convert is being done on the whole data
set before approriate rows are excluded.
If I turn this into an inline view then I get the same error.
select * from (
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)) test
where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
<bte this is where my original typo came from ;-) >
help !
"ML" wrote:

> A flaw in the substring function:
> ...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
> Maybe that's it.
>
> ML
>
> ML
> --
> http://milambda.blogspot.com/|||I've had a quick look at this and get the same odd result.
The Where clause is being applied to all the contents of the input table.
You can see this in the showplan and can confirm it by removing the row
without a date.
Regards,
Craig
"s_clarke" wrote:
> I don't think this is it...
> If I do a
> select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
> from test
> I get the results..
> IND SEQ converted date
> 1 1 01/06/2006
> 2 1 1/12/2005
> 2 2 test
> 2 3 2/12/2005
>
> So this shows that 3 of the rows can be converted into datetime (103 style
).
> I think that the problem is that the convert is being done on the whole da
ta
> set before approriate rows are excluded.
> If I turn this into an inline view then I get the same error.
> select * from (
> select * from test t1
> where t1.seq in (select max(seq) from test t2
> where t2.ind = t1.ind
> and t2.message like 'date=%'
> group by t2.ind)) test
> where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> <bte this is where my original typo came from ;-) >
> help !
>
> "ML" wrote:
>