Hi.. I want to join this query
Code:
select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & "
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0
in this query
Code:
select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID
The Place where i need to Join is after
Code:
iCalls_Status.Status_ID,iCalls_Status.Status_Label
and before
Code:
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID )
I want to add ( / ) in between these 2 queries. The reason is for example first query will return '5' and second '10' , so the output i need is 5 / 10. And i need to put this query in a variable (Countrec) like
Code:
select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec
The Final Query would be something like this
Code:
select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label, select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID
but this syntax is not correct..Please can U get me the Correct Syntax.
Even without knowing the nature of the data involved I can tell you that your first group of queries is backwards. The 2nd query can never return a higher number than the first query, because it is identical to the first query except for an additional restriction in the where clause that can only reduce the number of records returned.
It looks like what you want to do is a concatenation, rather than addition. To do that, you need to cast or convert the results of the queries to a text type such as varchar, and put your / in quotes:
Code:
CAST( (SELECT COUNT(*) FROM iCalls_Events WHERE Call_ID = " & Session("Call_ID") & " ANDEvents_Flag <> 0) AS VarChar(5))
+ ' / '
+ CAST((SELECT COUNT(*) FROM iCalls_Events WHERE Call_ID = "& Session("Call_ID")) AS VarChar(5))
You should be able to
include that in your larger query. Don't use
join in this context. Join has a very different meaning when talking about SQL code.
Was this answer helpful ?
Yes No
Thanks for your reply. Yes i have to concatenate.. Well I need the results of queries in a single variable ( not 2 varchar). This is because If i put the result of this query in a single variable , then i need to bind this to the repeater list. The Purpose is to bind this to my repeater control and display there the value ...for example ( 3 / 5 ). I hope you got me.
Quote:
| Originally Posted by f'lar Even without knowing the nature of the data involved I can tell you that your first group of queries is backwards. The 2nd query can never return a higher number than the first query, because it is identical to the first query except for an additional restriction in the where clause that can only reduce the number of records returned.
It looks like what you want to do is a concatenation, rather than addition. To do that, you need to cast or convert the results of the queries to a text type such as varchar, and put your / in quotes:
Code:
CAST( (SELECT COUNT(*) FROM iCalls_Events WHERE Call_ID = " & Session("Call_ID") & " ANDEvents_Flag <> 0) AS VarChar(5))
+ ' / '
+ CAST((SELECT COUNT(*) FROM iCalls_Events WHERE Call_ID = "& Session("Call_ID")) AS VarChar(5))
You should be able to include that in your larger query. Don't use join in this context. Join has a very different meaning when talking about SQL code. |
Was this answer helpful ?
Yes No
If you look again, you'll see that what I posted ends up in a single column. And did you note the part about the queries being backwards?
Was this answer helpful ?
Yes No
Yes I have noted down its being backward. Thanks for this. And as i have told you before that i will be using (Binding) this in my repeater control. How should i Bind this to repeater control column .. Should i Do like this ?
Code:
<% Container.dataitem("nvarchar") %>
SHould i declare nvarchar before using in query?
like Dim nvarchar as string
Please tell me am new to this.
Thanks.
Quote:
| Originally Posted by yasinirshad Thanks for your reply. Yes i have to concatenate.. Well I need the results of queries in a single variable ( not 2 varchar). This is because If i put the result of this query in a single variable , then i need to bind this to the repeater list. The Purpose is to bind this to my repeater control and display there the value ...for example ( 3 / 5 ). I hope you got me. |
Was this answer helpful ?
Yes No
nvarchar is not the column name. As it is the column has no name.
Was this answer helpful ?
Yes No
Ok so can i add a column name to my query like this:
Code:
CAST( (SELECT COUNT(*) FROM iCalls_Events WHERE Call_ID = " & Session("Call_ID") & " ANDEvents_Flag <> 0) AS VarChar(5))
+ ' / '
+ CAST((SELECT COUNT(*) FROM iCalls_Events WHERE Call_ID = "& Session("Call_ID")) AS VarChar(5)) as Countrec
and include in my larger query???
So that i Can Bind "Countrec" to repeater list as
Code:
<% Container.dataitem("Countrec") %>
Please help me...Thanks....
Quote:
| Originally Posted by f'lar nvarchar is not the column name. As it is the column has no name. |
Was this answer helpful ?
Yes No
That looks right. You could have just tried it once...
Was this answer helpful ?
Yes No
