Can anyone plss help me with this sql syntax plss...

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[/CODE]
in this query
[CODE]select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_ID,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[/CODE]
The Place where i need to Join is after
[CODE]iCalls_Status.Status_ID,iCalls_Status.Status_Label [/CODE]
and before
[CODE]((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID )[/CODE]
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) (as i need to bind Countrec to a repeater list ) 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[/CODE] . I HAVE TO BIND THIS "Countrec" in the repeater list.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_ID,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[/CODE]
but this syntax is not correct..Please can U get me the Correct Syntax.

Moving to Transact-SQL, as it seems the ideal place to post this question.

Thanks,

John

|||

You need to understand a thing or two about formatting your code. People will take time out of thier busy lives to extend help for free without anticiapting anything, but you need to follow certain etiquettes, like posting your question clearly, formatting the code so that it is easy to read and interpret. Ok, enough of that. Now lets look into the problem.

Code Snippet

--select 1

select

Count(*)

from

iCalls_Events

where Call_ID = " & Session("Call_ID") & "

--select 2

select

Count(*)

from

iCalls_Events

where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0

--select 3

select

iCalls_Calls.Call_ID

, iCalls_Calls.Requestor

, Type

, Scope

, iCalls_Calls.Status_ID

, iCalls_Status.Status_ID

, 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

You want to get the count of select 1 / select 2 and capture the value of this in select 3 along with other columns right, if I understand your question correctly. Then this should work for you. test it out for yourself.

select

iCalls_Calls.Call_ID

, iCalls_Calls.Requestor

, Type

, Scope

, iCalls_Calls.Status_ID

, iCalls_Status.Status_ID

, 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)

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

Also you should be able to do a small simple test before doing this a big query. Its easy when you break down the task into smaller ones.

create table test_a (col1a int, col2a int)

create table test_b (col1b int, col2b int)

create table test_c (col1c int, col2c int)

insert test_a select 1, 2

insert test_a select 1, 2

insert test_b select 2, 3

insert test_c select 3, 4

select

col1c

, col2c

, ((select count(1) from test_a) / (select count(1) from test_b)) as rec_count

from test_c