Help on this sp, it should be PERFECT!

Hi there, could someone tell me what is the wrong with the below SP's CURSOR.I exec this sp through a table when update a filed value like this :
EXECUTE THROUGH TRIGGER
exec SP_ALOCATE_PAT_CREDIT @.patid, @.creditno, @.totalamount, @.userid, @.sdesc ,@.creDate,'C'

When i run execute this sp, I got this error :
ERROR:
Another user has modified the contents of this table or view;the database row you are modifying no longer exists in the database.Databse error: '[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'INVOICE_LIST' does not exist.[Microsoft][ODBC SQL Server driver][SQL Server]The statement has terminated.'

==============================
THANX GUYZ, ALREADY SOLVED IT FINALLY:beer: , ON TODAY EARLY MORNING.WELL, I GUES U GUYS R RITE, I NEED MORE PRACTISE BUT I DID GOOD AT THIS STAGE TOO EVENTOUGH IT IS NOT SO STANDARD..HAHA ,THNX ANYWAY, WILL WORK TO BE BETTER! :angel:Wow. That is horrible.
If you want this to be perfect, you have a helluva long way to go. As a matter of fact, you would be best off scrapping absolutely everything you have done so far and starting again from the beginning. Your whole design is conceptually flawed.
You are using cursors unnecessarily, and you are calling this procedure from a trigger without any reference to what records it should act upon, and no handling for multi-record inserts either.
First, rewrite your procedure using set-based operations instead of a cursor.
Then, read the sections on triggers in Books Online until you understand the purpose and utility of the INSERTED and DELETED virtual tables.|||Thanx for ur concern BLINDMAN, As for my level, i beleive this is what I can output as a totaly new to sql world.i need this to be set up as soon as posible.With minimum guide and help i gues this is what i can come so far.So i hope a guide or few examples would be greatly helpful rather thehn ur advise.thanx Mr BLINMAN eventhough u werent help me much|||Sorry tommy boy - you might not like it much but blindman is right. Apart from it being a flawed idea in the first place the execution is all wrong too. As a self confessed newby you would be well advised to read his post and think seriously about where to go from here. There are no hints, tips or tricks to sort it out - you need a wholesale redesign.|||Oh Than Poo*, Then I shall consider it. God bless america ;)|||God bless them indeed.|||The truth hurts sometimes, TommyBoy, but the truth is what I gave you and nothing but the truth.
Better you know now that you have been heading down the wrong path, and that this is going to take some time and effort for you to implement.
On the plus side, if you take the time to explain WHAT you are trying to do, and you are willing to listen to people on this forum, then we can give you some good advice on designing and coding your process. Its OK to be a noob. We don't mind noobs on the forum. We do have little patience with people who want free advice and then insist on doing things wrong. That is just a waste of our time.
So post a new thread desribing your situation and ask for some help on engineering the process.

help on query

I need advice on how to approach this. To simplify with a sample, below is a
list of various calculations based from ITEMS table:
ITEM_NO FIELD_CALCULATION
123 cost*qty
111 cost-discount
222 sales*discount
333 cost-freight
etc...
There are 20 additional line items with different calculations.

From my SP, how do I incorporate the above to say:
SELECT ??
INTO #tmp1
FROM GL_ACCOUNT GL,
ITEMS I
WHERE GL.ITEM_NO = I.ITEM_NO

TIA!
BobHi

You could try something like:

SELECT I.Item_No,
CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
WHEN 111 THEN GL.cost-GL.discount
WHEN 222 THEN GL.sales*GL.discount
WHEN 333 THEN GL.cost-GL.freight
END AS [Calculation]
FROM GL_ACCOUNT GL,
JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO

John

"B" <no_spam@.no_spam.com> wrote in message
news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
>I need advice on how to approach this. To simplify with a sample, below is
>a
> list of various calculations based from ITEMS table:
> ITEM_NO FIELD_CALCULATION
> 123 cost*qty
> 111 cost-discount
> 222 sales*discount
> 333 cost-freight
> etc...
> There are 20 additional line items with different calculations.
> From my SP, how do I incorporate the above to say:
> SELECT ??
> INTO #tmp1
> FROM GL_ACCOUNT GL,
> ITEMS I
> WHERE GL.ITEM_NO = I.ITEM_NO
> TIA!
> Bob
>|||I was hoping to use the table created as a source without having to hardcode
since it will be used by other SP.

Thank you for your time.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:42578d55$0$26738$db0fefd9@.news.zen.co.uk...
> Hi
> You could try something like:
> SELECT I.Item_No,
> CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
> WHEN 111 THEN GL.cost-GL.discount
> WHEN 222 THEN GL.sales*GL.discount
> WHEN 333 THEN GL.cost-GL.freight
> END AS [Calculation]
> FROM GL_ACCOUNT GL,
> JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO
> John
> "B" <no_spam@.no_spam.com> wrote in message
> news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
> >I need advice on how to approach this. To simplify with a sample, below
is
> >a
> > list of various calculations based from ITEMS table:
> > ITEM_NO FIELD_CALCULATION
> > 123 cost*qty
> > 111 cost-discount
> > 222 sales*discount
> > 333 cost-freight
> > etc...
> > There are 20 additional line items with different calculations.
> > From my SP, how do I incorporate the above to say:
> > SELECT ??
> > INTO #tmp1
> > FROM GL_ACCOUNT GL,
> > ITEMS I
> > WHERE GL.ITEM_NO = I.ITEM_NO
> > TIA!
> > Bob|||Hi

Creating a view would overcome that problem.

John

"B" <no_spam@.no_spam.com> wrote in message
news:JMSdnZXT2LmwK8rfRVn-iQ@.rcn.net...
>I was hoping to use the table created as a source without having to
>hardcode
> since it will be used by other SP.
> Thank you for your time.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:42578d55$0$26738$db0fefd9@.news.zen.co.uk...
>> Hi
>>
>> You could try something like:
>>
>> SELECT I.Item_No,
>> CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
>> WHEN 111 THEN GL.cost-GL.discount
>> WHEN 222 THEN GL.sales*GL.discount
>> WHEN 333 THEN GL.cost-GL.freight
>> END AS [Calculation]
>> FROM GL_ACCOUNT GL,
>> JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO
>>
>> John
>>
>> "B" <no_spam@.no_spam.com> wrote in message
>> news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
>> >I need advice on how to approach this. To simplify with a sample, below
> is
>> >a
>> > list of various calculations based from ITEMS table:
>> > ITEM_NO FIELD_CALCULATION
>> > 123 cost*qty
>> > 111 cost-discount
>> > 222 sales*discount
>> > 333 cost-freight
>> > etc...
>> > There are 20 additional line items with different calculations.
>>> > From my SP, how do I incorporate the above to say:
>> > SELECT ??
>> > INTO #tmp1
>> > FROM GL_ACCOUNT GL,
>> > ITEMS I
>> > WHERE GL.ITEM_NO = I.ITEM_NO
>>> > TIA!
>> > Bob
>>>>>>>
>>|||Have you ever had a software engineering course? Probably not, because
this approach is a violaiton of the principle of cohesion and it
confuses data and code.

Help on Installing MSRS using a domain account.

Hi,
I am new to MSRS,Please tell me how to do the below mention things
Can we change the custom.dll to use an account from a config file instead of
the account from the app pool?
Microsoft does not support installing MSRS using a domain account.
MSRS corruption of the domain account has happened more than once in all
environments. The cost to re-install is too high.
Any Ideas?
RegardsThe dll I am referring to is DataProviders.dll.
This has code that needs to axess the dfsroot folder on the file server.
If you add code to this dll to read a config file for the username and
password and use it when opening the xml file on the file server, we would
not need to use a domain account in MSRS. Is this possible?
"gp" <gprabhakaran@.axentis.com> wrote in message
news:OuYFqGSLGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I am new to MSRS,Please tell me how to do the below mention things
> Can we change the custom.dll to use an account from a config file instead
> of the account from the app pool?
> Microsoft does not support installing MSRS using a domain account.
> MSRS corruption of the domain account has happened more than once in all
> environments. The cost to re-install is too high.
> Any Ideas?
> Regards
>

help needed with this query

i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??

im really stumped!

if (Radio_Btn_Book.Checked == true)
{
string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'";

SqlCommand Cmd_book = new SqlCommand(book_query, con);

con.Open();
Cmd_book.ExecuteNonQuery();
con.Close();
}

ERROR:

The column prefix 'Books' does not match with a table name or alias name used in the query.
The column prefix 'Books' does not match with a table name or alias name used in the query.

string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id =Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'";

whats this ? there is no reference of books table please check ... or change it to

whats is this Books ? is it a object in that case use query like this

string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = " +Books.Book_Id + " and I.Book_Name = '" + Session["Book_name"].ToString()+"'";

not sure what this Books is ...

|||

there are 2 tables in the query

Issue_Book_reference and Books

|||

1update Issue_Book_Reserve I2set I.Issue_Book_Reserve_state = 13from Books B4where I.Book_Id = B.Book_Idand B.Book_Name ='" + Session["Book_name"].ToString()+"'";

use this sql

Help need to write a Query in VB - MsSql !

Hai ,

My database is Ms Sql and I am devolping in VB
The below is my query, it seems to be working but at the last there is some problem. If possible kindly correct the query. Actually the problem is the SalesIn Quantity value is not shown correctly, it seems to be working in SP which I created in Ms Sql, so I tried the same here, I hope with some modifications it could be correctly executed.

sql = "select Item as Itemid,Itemid as SoldItemId,Date as SoldDate,"
sql = sql & "(Qty * Unitcost) as Cost,Qty as SoldQty,ItemId as StockId,"
sql = sql & "Qty as StockQty,ItemId as SalesinId,Qty as SalesInQty from"
sql = sql & " Stock,Sales,StockDetail,SalesIn where Stock.Itemid *= Sales.Itemid AND "
sql = sql & "StockDetail.Itemid = Stock.Itemid AND SalesIn.ItemId = Sales.ItemId AND "
sql = sql & "StockDetail.WareHouse ='" & Text3.Text & "' AND SalesIn.Type ='" & Text4.Text & "' AND "
sql = sql & "Date Between '" & Text1.Text & "' AND '" & Text2.Text & "' Group By"
sql = sql & " Stock.ItemId,StockDetail.ItemId,Sales.ItemId,Sales In.ItemId,Sales.Date,Sales.Qty,Sales.UnitCost,Stoc kDetail.Qty,"
sql = sql & "SalesIn.Qty order by Stock.ItemId"
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText

Everything is coming correct except the SalesIn Quantity. If I remove and try means then the other things are showing correctly. I mean the ItemId and the Soldqty and the stockqty and everything is showing correctly here just for my reference I am showing all the ItemId.

Kindly view and reply me.

SalesTable, StockTable, StockDetail, SalesIn are the Four table I am taking here. All the four tables are Linked by the ItemId.

Thank you very much,
Chockyou need to qualify the columns in the SELECT the same way you have qualified them in the GROUP BY

suggestion: switch immediately to JOIN syntax rather than the "old style" joins using that darned asterisk beside the equal sign
select Stock.Item as Itemid
, StockDetail.Itemid as SoldItemId
, Sales.Date as SoldDate
, Sales.Qty * Sales.Unitcost as Cost
, Sales.Qty as SoldQty
, Stock.ItemId as StockId
, StockDetail.Qty as StockQty
, SalesIn.ItemId as SalesinId
, SalesIn.Qty as SalesInQty
from Stock
inner
join StockDetail
on Stock.Itemid = StockDetail.Itemid
left outer
join Sales
on Stock.Itemid = Sales.Itemid
left outer
join SalesIn
on Sales.ItemId = SalesIn.ItemId
where Sales.Date Between 'Text1.Text'
and 'Text2.Text'
and StockDetail.WareHouse = 'Text3.Text'
and SalesIn.Type ='Text4.Text'
group
by Stock.ItemId
, StockDetail.ItemId
, Sales.ItemId
, SalesIn.ItemId
, Sales.Date
, Sales.Qty
, Sales.UnitCost
, StockDetail.Qty
, SalesIn.Qty
order
by Stock.ItemIdfinal tip: never use a reserved word like Date as a column name

rudy
http://r937.com/|||Hi,

I modified the Left outer join as you said, the below is the query which i am currently using.

sql = "select Itemid as StockId,Itemid as StkDetailId,
ItemId as SoldItemId,ItemId as SalesinId,
SellingDate as SoldDate,"
sql = sql & "(SoldQty * UnitCost) as Cost,SoldQty as SoldQty,"
sql = sql & "StockQty as QtyInHnd,SaleinQty as SalesInQty from"
sql = sql & " Stock inner join StockDetail on
Stock.Itemid = StockDetail.Itemid"
sql = sql & " left outer join Sales on Stock.Itemid = Sales.Itemid"
sql = sql & " left outer join SalesIn on Sales.ItemId = SalesIn.Itemid"
sql = sql & " where Sales.Date Between '" & Text1.Text &
"' AND '" & Text2.Text & "' AND StockDetail.WareHouse ='" &
Text3.Text & "' AND "
sql = sql & "SalesIn.Type ='" & Text4.Text & "' Group By "
sql = sql & "Stock.Itemid,StockDetail.Itemid,Sales.Itemid,
SalesIn.Itemid,Sales.SellingDate,Sales.SoldQty,
Sales.UnitCost,StockDetail.QtyinHand,
SalesIn.SalesInQty order by Stock.ItemId

its executing, but I didn't get the output correctly, It didn't shows the record as per the Left Outer Join. Actually I need the output as below

StkId SoldCost QtyInHand SoldQty SaleInQty SDate SalesIn WareHouse
sl001 120 4 2 2 05/01/03 00 01
sl002 0 10 0 0 00 01
sl003 30 2 10 0 05/01/03 00 01
sl004 0 120 0 0 00 01

whethere the Item Sold or not all the Item Id should be listed with their details. The Stock and StockDetail is the Master for the ItemId, So i take that as Inner Join , now what I am getting the oupt put is

sl001 60 4 1 2 05/01/03 00 01
sl001 60 4 1 2 05/01/03 00 01
sl003 30 2 10 0 05/01/03 00 01

if above is not clear, I have attatched the Excel sheet.|||hai friend,

Now I am trying like this, will it work, is the way I am writing is correct or not. kindly let me know also now I will post this to UA,

here I am facing the error

Run-time error '-2147217900(80040e14)':
The Column Prefix 'subquery' doesnot match with a tablename or alias name used in the query.

sql1 = "select distinct ItemId,Description from Stock"
sql2 = "select Itemid,SoldQty,UnitCost,SoldDate from Sales where Sales.SoldDate Between ='" & Text1.Text & "' AND '" & Text2.Text & "'"

sql3 = "select distinct ItemId,WareHouse,StockQty from StockDetail where WareHouse ='" & Text3.Text & "'"

sql4 = "select distinct SalesType,ItemId,Date,ItemId,SalesInQty from Stock Left Join SalesIn on Stock.ItemId = SalesIn.ItemId where SalesInType ='" & Text4.Text & "' AND SalesIn.Date ='" & Text1.Text & "' AND '" & Text2.Text & "'

this is the Subquery

subquery = "select sql1.StockItemId,sql1.Description,sql2.SalesItemId ,"
subquery = subquery & "(sql2.SoldQty * sql2.Cost),sql2.SoldDate,sum(sql3.StockQty),"
subquery = subquery & "sum(sql4.SalesInQty) from "
subquery = subquery & "(((sql1 Left Join Sql2 on sql1.StockItemId=sql2.SaleItemId) Left Join sql3 on sql1.StockItemId = sql3.StockDetail.ItemId) Left Join sql4 on sql1.StockItemId = sql4.SalesInItemId) order by sql1.StockItemId"
this is the main query
mainquery = "select subquery.StockItemId,subquery.Decription,subquery. SoldItemId,"
mainquery = mainquery & "(subquery.SoldQty * subquery.Cost),subquery.SoldDate,subquery.StockQty ,"
mainquery = mainquery & "subquery.SalesInQty"

kindly when you have time view and reply me

Thankyou very much,
Chock.

help me...

below is my sql query statement... hope u understand
SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.
Total_anual_maint_and_monitor
FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
WHERE p.Performance_id=m.MonitoringCost_id and
pv.PV_id=p.Performance_id and
pv.PV_id=m.MonitoringCost_id and
u.User_id =p.Performance_id and
u.User_id =pv.PV_id and
u.User_id = m.MonitoringCost_id
when i execute this query, it cannot executed because i got this error
message...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ejoy' to data type int.
what should i do?
Message posted via http://www.droptable.com
There is nothing in this query called 'ejoy' so it's impossible to even
make a guess why you are getting this error.
How did you determine this was the query that was generating the error?
What version are you using?
What tool are you using to submit this statement?
Is this statement part of a larger batch or procedure?
Are the objects in the FROM clause tables or views?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"ejoeyz_85 via droptable.com" <u40468@.uwe> wrote in message
news:7e1ef152c6b9c@.uwe...
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring,
> p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type
> int.
> what should i do?
> --
> Message posted via http://www.droptable.com
>
|||ejoeyz_85 via droptable.com wrote:
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type int.
> what should i do?
>
In the where clause, you're joining two fields, where one contains the
value "ejoy"... while on the other side it contains a number... I'm
guessing that the field that contains the string is on the right side of
one of those equal signs.... you need to find out which one it is, and
determine if you are matching it up against the correct field from the
other table.
-ca
|||What he said.
And please, learn to use modern ANSI "inner join" syntax!
J.
On Sat, 12 Jan 2008 20:44:25 -0600, "Chris Anderson [MVP-VB]"
<tg-nospam@.tannagh-dawt-com> wrote:

>ejoeyz_85 via droptable.com wrote:
>In the where clause, you're joining two fields, where one contains the
>value "ejoy"... while on the other side it contains a number... I'm
>guessing that the field that contains the string is on the right side of
>one of those equal signs.... you need to find out which one it is, and
>determine if you are matching it up against the correct field from the
>other table.
>-ca

help me...

below is my sql query statement... hope u understand
SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.
Total_anual_maint_and_monitor
FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
WHERE p.Performance_id=m.MonitoringCost_id and
pv.PV_id=p.Performance_id and
pv.PV_id=m.MonitoringCost_id and
u.User_id =p.Performance_id and
u.User_id =pv.PV_id and
u.User_id = m.MonitoringCost_id
when i execute this query, it cannot executed because i got this error
message...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ejoy' to data type int.
what should i do'
--
Message posted via http://www.sqlmonster.comThere is nothing in this query called 'ejoy' so it's impossible to even
make a guess why you are getting this error.
How did you determine this was the query that was generating the error?
What version are you using?
What tool are you using to submit this statement?
Is this statement part of a larger batch or procedure?
Are the objects in the FROM clause tables or views?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"ejoeyz_85 via SQLMonster.com" <u40468@.uwe> wrote in message
news:7e1ef152c6b9c@.uwe...
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring,
> p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type
> int.
> what should i do'
> --
> Message posted via http://www.sqlmonster.com
>|||ejoeyz_85 via SQLMonster.com wrote:
> below is my sql query statement... hope u understand
> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
> Period_of_monitoring, m.Ongoing_maintenance,
> m.Savings_for_inverter_replacement, m.Monitoring, m.
> Total_anual_maint_and_monitor
> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
> WHERE p.Performance_id=m.MonitoringCost_id and
> pv.PV_id=p.Performance_id and
> pv.PV_id=m.MonitoringCost_id and
> u.User_id =p.Performance_id and
> u.User_id =pv.PV_id and
> u.User_id = m.MonitoringCost_id
> when i execute this query, it cannot executed because i got this error
> message...
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value 'ejoy' to data type int.
> what should i do'
>
In the where clause, you're joining two fields, where one contains the
value "ejoy"... while on the other side it contains a number... I'm
guessing that the field that contains the string is on the right side of
one of those equal signs.... you need to find out which one it is, and
determine if you are matching it up against the correct field from the
other table.
-ca|||What he said.
And please, learn to use modern ANSI "inner join" syntax!
J.
On Sat, 12 Jan 2008 20:44:25 -0600, "Chris Anderson [MVP-VB]"
<tg-nospam@.tannagh-dawt-com> wrote:
>ejoeyz_85 via SQLMonster.com wrote:
>> below is my sql query statement... hope u understand
>> SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.
>> Period_of_monitoring, m.Ongoing_maintenance,
>> m.Savings_for_inverter_replacement, m.Monitoring, m.
>> Total_anual_maint_and_monitor
>> FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
>> WHERE p.Performance_id=m.MonitoringCost_id and
>> pv.PV_id=p.Performance_id and
>> pv.PV_id=m.MonitoringCost_id and
>> u.User_id =p.Performance_id and
>> u.User_id =pv.PV_id and
>> u.User_id = m.MonitoringCost_id
>> when i execute this query, it cannot executed because i got this error
>> message...
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the varchar value 'ejoy' to data type int.
>> what should i do'
>In the where clause, you're joining two fields, where one contains the
>value "ejoy"... while on the other side it contains a number... I'm
>guessing that the field that contains the string is on the right side of
>one of those equal signs.... you need to find out which one it is, and
>determine if you are matching it up against the correct field from the
>other table.
>-ca

Help me with this query

Hi,

Below is the query

Alter Proc Spr_FadDataPull
As
SET NOCOUNT ON
Begin
select distinct tbs.sessionid,
(Case
when PageDataTitle='Find A Doctor-Results' then '1'
else '0'
end) as Flag, tbPD.PageDataPath ,
convert(varchar(10),SessionCD,101) as Effective_Date,
(select tbS.SessionPageViewCount from (select count(S.SessionPageViewCount) from tbSession S
inner join tbPageAction as PA
on S.sessionid=PA.sessionid
inner join tbPageData as PD
on PD.PageDataid=PA.PageDataid
where PD.PageDateTitle='Find A Doctor-Results'
Group by S.SessionPageViewCount )) as PageViewCount
from tbSession as tbS
inner join tbPageAction as tbPA
on tbS.sessionid=tbPA.sessionid
inner join tbPageData as tbPD
on tbPD.PageDataid=tbPA.PageDataid
where convert(varchar(10),SessionCD,101) between '05/15/2007' and '06/18/2007'
where tbS.SessionCD between '05/15/2007' and '06/18/2007'
End

SET NOCOUNT OFF

the error is

Line 17: Incorrect syntax near ')'. near the Group by clause

can anyone pls help me to fix this error.

Thanks in Advance,

sg

you need an alias on your derived table within the PageViewCount item

Code Snippet

Begin

selectdistinct tbs.sessionid,

(Case

when PageDataTitle='Find A Doctor-Results'then'1'

else'0'

end)as Flag, tbPD.PageDataPath ,

convert(varchar(10),SessionCD,101)as Effective_Date,

(select tbS.SessionPageViewCount from(selectcount(S.SessionPageViewCount)from tbSession S

innerjoin tbPageAction as PA

on S.sessionid=PA.sessionid

innerjoin tbPageData as PD

on PD.PageDataid=PA.PageDataid

where PD.PageDateTitle='Find A Doctor-Results'

Groupby S.SessionPageViewCount )as x

)as PageViewCount

from tbSession as tbS

innerjoin tbPageAction as tbPA

on tbS.sessionid=tbPA.sessionid

innerjoin tbPageData as tbPD

on tbPD.PageDataid=tbPA.PageDataid

where convert(varchar(10),SessionCD,101) between '05/15/2007' and '06/18/2007'

where tbS.SessionCD between'05/15/2007'and'06/18/2007'

End

|||

Hi Dane,

thanks a lot,

i named the derived column as you Said its givinf this error

error:

No column was specified for column 1 of 'x'.

Please help.

Regards,

Vani.

|||

Your count(...) needs a valid name to be referenced.

Code Snippet

selectdistinct tbs.sessionid,

(Case

when PageDataTitle='Find A Doctor-Results'then'1'

else'0'

end)as Flag, tbPD.PageDataPath ,

convert(varchar(10),SessionCD,101)as Effective_Date,

(select tbS.SessionPageViewCount from(selectcount(S.SessionPageViewCount)as SessionPageViewCount

from tbSession S

innerjoin tbPageAction as PA

on S.sessionid=PA.sessionid

innerjoin tbPageData as PD

on PD.PageDataid=PA.PageDataid

where PD.PageDateTitle='Find A Doctor-Results'

Groupby S.SessionPageViewCount )as x

)as PageViewCount

from tbSession as tbS

innerjoin tbPageAction as tbPA

on tbS.sessionid=tbPA.sessionid

innerjoin tbPageData as tbPD

on tbPD.PageDataid=tbPA.PageDataid

where convert(varchar(10),SessionCD,101) between '05/15/2007' and '06/18/2007'

where tbS.SessionCD between'05/15/2007'and'06/18/2007'