Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code.value
Thanks in advance
TakeCare
Love
Amit
dev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegr oups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code.value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code.value
> Thanks in advance
> TakeCare
> Love
> Amit
>

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID = Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID = Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID = dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
Thanks in advance
TakeCare
Love
Amitdev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegroups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>sql

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
Thanks in advance
TakeCare
Love
Amitdev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegroups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:

> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =
> Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =
> Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =
> dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>

can this be done just in database (I have given codes)

Hi , I have a database that records the users entrance to a building.The dates are recorded also .I have written some codes that to detect the period of dates that the person has entered.Lets say that a person named as jhon has entered the building on the days 02/08/2006 and 05/08/2006 and 11/08/2006 .Those dates are formated as dd/mm/yyyy . So that john has entered building for 3 times and the periods for the dates are one after another entrance is 3 days + 6 days =9 days .If you divide 9 by 3 we get the arithmetic average number 3 .So that we can say as john visits this building every 3 days and we can guess the next day that he may come.
I have coded this and works great .I will paste the codes to the end of my message.But a master programmer friend of mine has said that I should have get this result by without writing code , by just using sql database .But some kind of stored procedure I mustn't use.So that I thought it can be done by views in sqldb .By using sql server enterprise manager I tried to use views but I could not succees.
Can anyone guess this ?
Here are my vb codes ...

database data types :
entry_id : int , identity
user_id : int
movie : nvarchar (50)
dateenter : datetime

stored procedure that selects the data from db:
CREATE PROCEDURE veri_al

(
@.user_id int
)
AS
SELECT entry_id ,user_id, movie, dateenter from uye_aktiviteleri
where (user_id=@.user_id) ORDER BY entry_id ASC
GO

Code:

Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = "data source=localhost;initial catalog=AFM;uid=nusret;pwd=araz"
Dim command As New SqlClient.SqlCommand
command.CommandText = "[veri_al]"
command.CommandType = CommandType.StoredProcedure

command.Connection = conn
command.Parameters.Add("@.user_id", SqlDbType.Int, 4).Value = Val(TextBox1.Text)
If Not IsNumeric(TextBox1.Text) Then
Exit Sub
End If

Try
Dim adapter As New SqlClient.SqlDataAdapter
adapter.SelectCommand = command

Dim ds As New DataSet

adapter.Fill(ds, "uyeler")
DataGrid1.DataSource = ds.Tables("uyeler")
Dim recordcount As Integer 'Found the recordcount
recordcount = ds.Tables("uyeler").Rows.Count

Dim mydatarow_ilk As DataRow
Dim mydatarow_son As DataRow

'in stored procedure I used ORDER BY entry_id ASC so that first datarow
'will always be the first visit date and the last record of the
'datarow will be the last visit date
mydatarow_first= ds.Tables("uyeler").Rows(0)
mydatarow_last = ds.Tables("uyeler").Rows(recordcount - 1)
Dim first_date As String
Dim last_date As String

first_date = mydatarow_first("dateenter")
last_date =mydatarow_last("dateenter")
Dim average_ As Integer
'What is the aveare of the visits
ortalama = DateDiff("d", first_date , last_date ) / recordcount
Label2.Text = "Member had visited for " & recordcount & "times"
Label3.Text = "by average he/she comes here " & ortalama & " day to another."
Dim last_time_visit As Integer
last_time_visit = DateDiff("d", last-date , DateTime.Today())
Label4.Text = "Until the last visit it had been" & last_time_visit & " days"
Label5.Text = "Guess for the next visit is" & DateAdd("d", average_ , last_date)
' MsgBox("average visits" & ortalama & " days" )
Catch z As Exception
MsgBox("error : " & z.Message())
Finally
MsgBox("Successfully calculated")
End Try

End Sub

SELECT d.user_id,COUNT(d.dateenter)as totalEnterTimes,AVG(CASEWHEN c.DiffsISNULLTHEN 0ELSE c.DiffsEND)as avgIntervalsFROM entrydateas dLEFTJOIN(SELECT a.user_id, a.dateenteras HighDate, b.dateenteras lowDate,

DATEDIFF(day, a.dateenter, b.dateenter)AS DiffsFROM(SELECTuser_id, dateenter,(selectcount(*)From entrydatewhereuser_id= T.user_idand dateenter<= T.dateenter)+ 1as Rank1

from entrydateas T) aINNERJOIN(SELECTuser_id, dateenter,(selectcount(*)From entrydatewhereuser_id= T1.user_idand dateenter<=T1.dateenter)as Rank2

from entrydateas T1) bON b.user_id=a.user_idand a.Rank1=b.Rank2) cON c.user_id= d.user_idAND c.lowDate=d.dateenter

GROUPBY d.user_id

--Let me know if you have question.

Can stored procs run after handle is closed?

I have written a stored proceedure for MSSQL that needs to run for hours at
a time. I need to execute it from C++ code. The current code does:

nRet = SQLDIRECTEXEC(hstmt, "exec stored_proc", SQL_NTS)

followed shortly after by a

Free_Stmt_Handle(hstmt) //roughly

The stored proc currently dies with the statement handle, not fully
populating the table I need it to.

I need to either know when the proc finishes so I can close the handle after
that, or allow the proc to run independently on the server no matter what
the program is doing (is exited, etc), either of these is fine.

Please Help! Thanks in advance!
JosephI know nothing about C++, but if the proc runs for a very long time, it
might be better to implement it as a scheduled job. The client could
set a flag or insert a row into a 'queue' table, then you have a job
which runs every few minutes or whatever, and if the flag is set, it
then starts the stored proc.

Simon|||That is an interesting approach, ideally I would like to stay as far away
from the database as I can but it sounds like this could be the best way...
my stored procedure is running for the exact same number of instructions and
then dying, whereas if I run it via Query Analyzer it runs to completion.

I finally caved and just copy-pasted from Q.Analyzer into code to confirm
this. I will investigate a little further before taking that plunge.

Thanks
Joseph

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1112950699.762977.19700@.o13g2000cwo.googlegro ups.com...
>I know nothing about C++, but if the proc runs for a very long time, it
> might be better to implement it as a scheduled job. The client could
> set a flag or insert a row into a 'queue' table, then you have a job
> which runs every few minutes or whatever, and if the flag is set, it
> then starts the stored proc.
> Simon

Can scripts be written in C#?

Can scripts be written in C#?If you mean expressions, only VB.NET is supported. If you mean RS
programming, it can be done in VB.NET or C#.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Justin" <stupy1@.hotmail.com> wrote in message
news:2e90a66b.0407090611.e0066ff@.posting.google.com...
> .|||Thanks Ravi.