How To Solve This Sql Query

Mar 26, 2008

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_idcust_name owner_rep_id
1harry 3
2msas 2


AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_idab_name owner_rep_id
1abc1
2def2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_idot_nameowner_rep_id
1xyz2
2cdf3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_idowner_name
1henry
2hologa
3tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_iddt_idproject_typeproject_idtime_st
103/23/08AB Corporate11.20
2 03/23/08AB Corporate23.25
303/21/08AB Corporate 12.25
403/23/08AB Plant14.35
503/23/08AB Plant24.50
603/23/08Other Project13.24
703/23/08Other Project24.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_iddt_id project_type project_id time_st owner_rep_id owner_name
103/23/08 AB Corporate 11.203tmw
2 03/23/08 AB Corporate23.252hologa
303/21/08 AB Corporate 12.253tmw
403/23/08 AB Plant14.351henry
503/23/08 AB Plant24.502hologa
603/23/08 Other Project13.242hologa
703/23/08 Other Project24.353tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac. owner_rep_id,ab. owner_rep_id,ow. owner_rep_id,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.




uday

View 10 Replies


ADVERTISEMENT

Solve This Query (mind Blowing Query)

Mar 23, 2000

Table Name= t1

uid subject marks
1 physics 68
1 chemistry 70
1 maths 80
1 english 75
2 physics 78
2 chemistry 56
2 maths 68
2 english 59
3 physics 54
3 chemistry 67
3 maths 77
3 english 59

query: i need sum of marks of physics,chemistry and maths
of each uid (english not included)

Thanks in Advance
Harry

View 1 Replies View Related

How To Solve This Query?

Oct 1, 2007

Hi,there are three tables:1) department---------------ID (primary key)name2) reports----------ID (p. key)depIDuserID3) users--------ID (p.key)nameI want to get in one query how many distinct users have made a report for each department.E.g.: table reports may look like this:ID    depID    userID1        1            12        1            13        1            34        3            65        4            8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe

View 5 Replies View Related

Solve This Query

Jun 8, 2007

Hello friends,

I want to find only fifth row from a table please give me better solution.

thanks in adavance

regards
Harsh

View 4 Replies View Related

Plz Solve This Query

Feb 5, 2004

chiranjeevi
raveendra
sreenivasarao
koteswar
manoj
sangeetharanika


Here my query is
in the above names i want to find out the persons whoz names consist of exact 2 a's
eg output:
raveendra

assume that the names are ename and table name is emp.

Thanks in advance
Plz mail this qury to chiru_y2k@yahoo.com

Chiranjeevi

View 2 Replies View Related

Solve This Query

Feb 9, 2004

Hi,
I have two database servers and in this one is consists of tables and another one is for stored procedures ...
Here is my proble...
I want to access the stored procedures from my application(.net) ...

How to do...

Thanks in Advance

Chiru

View 1 Replies View Related

Please Solve This Query

Feb 21, 2007

please solve this query

first table second table

loginname categoryid
categoryid categoryname



first table data

nisar (1,2,3,4)

second table data

1 Bearbase
2 Amunation
3 Training
4 Workorder


Now I want cross tab report like that which will show user name nisar has this category




Nisar bearbase amunation traiaing workorder

View 3 Replies View Related

Please Solve This Query

Feb 21, 2007

please solve this query

first table ============= second table

loginname ============= categoryid
categoryid ============= categoryname



first table data

nisar=====1,2,3,4)

second table data

1==== Bearbase
2==== Amunation
3==== Training
4==== Workorder


Now I want cross tab report like that which will show user name nisar has this category




Nisar ==bearbase=== amunation === traiaing === workorder

View 1 Replies View Related

Please Solve This Query

Apr 24, 2007

hi anyone can please solve this query

I have table1 like this

locationid locationname
1 london
2 bombay



and in table2 like this

personname fromlocation tolocation
john 1 2
ryan 2 3


now i want to udpated table 2's from location and tolocation colum with all the all related value in table1

Regards
Monika

View 4 Replies View Related

Please Solve This Query

May 19, 2007

Hi please solve this


DBF file

Id 1
Building 21b
Population 4


Sql server table

Building 21b
Population8



Now I want to execute a sql script when ever I will run this script
It will not only transfer data between sql server 2000 and dbase file but Also update the corresponding population value in dbf table, which has same building value In sql server table

View 1 Replies View Related

Can Anyone Solve This Sql Query?

Oct 16, 2006

hi friends i need help in this sql queryi have table like,id fid__ _____autonumber textand i am storing values likeid fid___________________________________1 1,2,3,4,52 11,12,13,14,15now to find values i am using querysql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')only problem in this query is it is selecting 1 and 11 and i requireonly 1 as i am giving one in %1%now any one have answer of this question then plz plz tell me ........

View 4 Replies View Related

How To Use Joins To Solve This Sql Query

Mar 27, 2008

My tables and data as follows

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2


AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
1 henry
2 hologa
3 tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name
1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw
2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa
3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw
4 03/23/08 AB Plant 1 4.35 -- 1 -- henry
5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa
6 03/23/08 Other Project 1 3.24 -- -- 2 hologa
7 03/23/08 Other Project 2 4.35 -- -- 3 tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.







uday

View 1 Replies View Related

Using Joins How To Solve This Sql Query

Mar 27, 2008

My tables and data as follows

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2


AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
1 henry
2 hologa
3 tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name
1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw
2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa
3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw
4 03/23/08 AB Plant 1 4.35 -- 1 -- henry
5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa
6 03/23/08 Other Project 1 3.24 -- -- 2 hologa
7 03/23/08 Other Project 2 4.35 -- -- 3 tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=€™ AB Corporate€™ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=€™ AB Plant€™ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=€™ Other Project€™ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.

<!--[endif]-->

View 1 Replies View Related

Plz Help To Solve This Sql Query (MS Sql 2005)

Mar 26, 2008

my tables as follows and data as follows


AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2


AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
<!--[if !supportLists]-->1 <!--[endif]-->henry
<!--[if !supportLists]-->2 <!--[endif]-->hologa
<!--[if !supportLists]-->3 <!--[endif]-->tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st owner_rep_id owner_name
1 03/23/08 AB Corporate 1 1.20 3 tmw
2 03/23/08 AB Corporate 2 3.25 2 hologa
3 03/21/08 AB Corporate 1 2.25 3 tmw
4 03/23/08 AB Plant 1 4.35 1 henry
5 03/23/08 AB Plant 2 4.50 2 hologa
6 03/23/08 Other Project 1 3.24 2 hologa
7 03/23/08 Other Project 2 4.35 3 tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac. owner_rep_id,ab. owner_rep_id,ow. owner_rep_id,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=€™ AB Corporate€™ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=€™ AB Plant€™ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=€™ Other Project€™ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.



View 3 Replies View Related

Whats The Best Query To Solve This Problem?

Apr 5, 2008



Hi Guys,

I have create a sales delivery printout with the following example information
example below gives a delivery and the two sales orders that delievry is related to.

Two tables are T1 = delivery, T2 = orders

Delivery (T1)

linenum Item delievry Qty Ordernum orderlinenum DeliveryNumber
1 A 10 600 1 5001
2 B 5 600 2 5001
3 c 12 605 1 5001
4 P 1 5001


ORDER (T2) number 600

linemum Item OrderQty orderno
1 A 20 600
2 B 10 600
3 A 5 600
ORDER (T2) number 605
linemum Item OrderQty orderno
1 C 20 605
2 B 20 605
3 F 5 605

I want to print a delivery note with the all items on that delivery (delivered items) plus other items on orders which ware not delivered. so the result of the query should look like below

Result
NO Item delievry Qty Ordernum orderlinenum ORDER QTY
1 A 10 600 1 20
2 B 5 600 2 10
3 c 12 605 1 20
4 P 1
5 A 600 3 5
6 B 605 2 20
7 F 605 3 5

You can ignore the first column of results which is just the row number.

other conditions

1. Some times the delivery is not based on any sales order
2. Delivery can base on 0,1,2,3 or any number of sales orders.
3. result should provide all rows from the delivery and all rows from sales orders except the rows in the delivery.

can any one help?

Cheers

Sonny

View 1 Replies View Related

DB Engine :: How To Solve Multiple Rows Result From Inner Join Query

Dec 3, 2015

I have 3 tables:
 
TABLE [dbo].[Tbl_Products](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Product_Name] [nvarchar](50) NOT NULL,
[Catagory_ID] [int] NOT NULL,
[Entry_Date] [date] NOT NULL,

[Code] ....

I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )

But am getting a multiple result if the order purchase has more than 1 item :

SELECT DISTINCT B.Product_Name,A.AllPieceBoxes,
A.BuyPrice,A.TotalPrice,A.BuyPrice
FROM
Tbl_Products B INNER JOIN Tbl_PurchaseHeader C
ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A
ON A.PurchaseOrder=C.purchaseOrder
WHERE A.PurchaseOrder=3

View 5 Replies View Related

How I Solve This ?

Jan 3, 2007

I get this error when i try to connect with my sql,
I created my SQL with MS SQL 2005 Workgroup,
 
 
the error is:
 

Server Error in '/' Application.


The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Configuration.Provider.ProviderException: The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

View 2 Replies View Related

Solve This

Mar 25, 2006

When i used MySql in my appliaction its working well, but now i shifted my web application on SQL Server2000 , its gives problem.
It gives this error at runtime...
The specified module could not be found.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IO.FileNotFoundException: The specified module could not be found.Source Error:



Line 79: SqlCommand OcCom=new SqlCommand(Query,OdCon);
Line 80: SqlDataReader OdDat;
Line 81: OdCon.Open();
Line 82: OdDat=OcCom.ExecuteReader(CommandBehavior.CloseConnection);
Line 83: return OdDat;
 
i read ths stuff from all over and reinstall the Visual studio .NET2003 again . but still some is not solved.
So Pls help Me!!!!!!!!!!!!!!
Thanx
Manish

View 3 Replies View Related

How Would You Solve This?

Sep 5, 2001

Lets say I want to delete a record in tableA. tableA has 5 foreign key tables.

Before I delete the row in tableA, I want to make sure I dont get any foreign key violation error. I dont want to make a cascading delete, since the record should not be deleted if it has child records.

Of course I could check all those tables before the delete. Anybody know of a better approach?

Regards,

Stefan

View 1 Replies View Related

Why - How Solve?

Jan 23, 2004

hi
I have a question:
why when we call a stored procedure in delphi
that the return in Null (Example Not Match data with our Select)
Error occured?
Please help me .
thanks alot.bye.

View 2 Replies View Related

Plz Solve This One

Jan 1, 2008

Hi! i need to know which tables in my database are not used by any stored procedure in my database.i need ur help.

View 2 Replies View Related

Solve This One Plz..........

Jan 10, 2008

hi,
i have a doubt whether constraints(for eg:default constraint ) can be applied on table variable or not. plz let me know. iam presenting the table variable which iam using.

declare @t table (a int constraint d default 1 ,b varchar(10) constraint a default 'india' )
insert into @t values ( 1,'ic')


thanks

View 2 Replies View Related

Please Solve It

Mar 5, 2008

hi,

I need to get values in a column of different rows to be comma seperated.

example:

table a

col1 col2 col3
1 a b
2 c d
3 e f

I want output like this

col1 col2 col3
1,2,3 a,c,e b,d,f


Thanks in advance.........

View 7 Replies View Related

How To Solve This

Mar 24, 2008

Help me out how to solve this requirement

Table_1
Col1Col2Col3Col4
1101Usr1WRK1
2101Usr2WRK1

Table_2
Col1Msg
101User %1 Logged on %2

Expected Output

User Usr1 logged on WRK1
User Usr2 logged on WRK1

View 2 Replies View Related

Help To Solve This Problem!

Mar 2, 2007

I'm a beginer!
I host my web on a hosting service provider. But when I access an error occur:

Server Error in '/' Application.


An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:



An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:



[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.DetailsView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.DetailsView.EnsureDataBound() +181
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360



Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

View 1 Replies View Related

How Can I Solve This Errror??

Nov 12, 2007

Hi, I am trying to update my table... but when i try to save my query.. i am getting the following error
 Column 'SourceDBF.DEFPCT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. This is my sproc...
 
 UPDATE
Statements..ParticipantSourceSummary
SET
DeferralPct = DEFPCT,
BeginingBal = BEGBAL,
Deposits = DEPOSITS,
Withdraw = WITHDRAW,
GainLoss = GAINLOSS,
Others = OTHER,
EndingBal = ENDBALANCE,
VestPercent = VESTPCT,
VestBal = VESTBALANC
FROM
Statements..ParticipantSourceSummary ps
Join (
SELECT
p.ParticipantId,
cp.PlanId,
cs.SourceId,
@PeriodId PeriodId,
s.DEFPCT,
s.BEGBAL,
s.DEPOSITS,
s.WITHDRAW,
s.GAINLOSS,
s.OTHER,
s.ENDBALANCE,
s.VESTPCT,
s.VESTBALANC
FROM
SourceDBF s
INNER JOIN Statements..ClientPlan cp
ON s.PLAN_NUM = cp.ClientPlanId
INNER JOIN Statements..ClientSources cs
ON s.SOURCE_NUM = cs.ClientSourceId and cs.ClientId = @ClientId
Inner Join Statements..Participant p
on s.PART_ID = p.SSN
Where
s.Import = 1
GROUP BY
p.ParticipantId,
cp.PlanId,
cs.SourceId
) s
ONps.ParticipantId = s.ParticipantId
AND
ps.PlanId = s.PlanId
AND
ps.SourceId = s.SourceId
AND
ps.PeriodId = s.PeriodId  any help will be appreciated.RegardsKaren 

View 1 Replies View Related

Need Help To Solve This Problem

May 15, 2008

Hi all
i have a problem in my stored procedureUSE [eLocalize_System]
GO
/****** Object: StoredProcedure [dbo].[Insert_Branch] Script Date: 05/15/2008 16:03:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================ALTER PROCEDURE [dbo].[Insert_Branch]
(
@Building_No int
,@Street_Name nvarchar(50)
,@City nvarchar(50)
,@Postal_Code nvarchar(50)
,@Telephone nvarchar(50)
,@Fax nvarchar(50)
,@Client_Id int
,@Country_Id int
,@Row int output)
AS
Declare
@BranchCode int,
@Branch_Name nvarchar(50)
set @BranchCode=isnull((select Max(BranchCode) from dbo.Branch where Client_Id=(select Id from dbo.Client where Id=@Client_Id)),0);
set @BranchCode=@BranchCode+1
set @Branch_Name=(select City from dbo.Branch)+'+'+(select Name from dbo.Country where Id=(select Country_Id from dbo.Branch))INSERT INTO [eLocalize_System].[dbo].[Branch]
([Branch_Name],[BranchCode]
,[Building_No],[Street_Name]
,[City],[Postal_Code]
,[Telephone],[Fax]
,[Client_Id]
,[Country_Id])
VALUES
(@Branch_Name,@BranchCode ,
@Building_No ,@Street_Name
,@City ,@Postal_Code
,@Telephone ,@Fax
,@Client_Id
,@Country_Id)
set @Row = (select scope_identity())
return @Row
i want Branch_Name = City + Country
and how i do it if there is any update
Thanks for help 
 

View 1 Replies View Related

How Can I Solve This Problem?

Jul 26, 2000

Subscriptions were created successfully at the following Subscribers:
SERVER330



SQL Server Enterprise Manager could not start the service 'MSDTC' on server 'SERVER330'.

5 - (Access is denied)

The subscription at Subscriber 'SERVER330' cannot be initialized immediately because the snapshot for this publication is not yet available. To initialize the subscription, start the Distribution Agent after the snapshot is available.

View 1 Replies View Related

Plz Solve For This Tasks

May 13, 2006

could some one help in giveing solution for this tasks..
[CODE]
Task 1 : Get the first day and last day of the week


Ex: input : '04/12/2006'

Required ouput :

start_date End_date
04/09/200604/15/2006

Note :1. start_date should be sunday
2. end_date should be saturday




Task 2 -- Find the product sales week wise


Find the product sales week wise

Output Required:

gasstationID productClass week1 week2 week3 week4
-------------------- ------------ ----------- ----------- ----------- -----------
111 DSL 1 0 0 0
111 GAS 2 0 1 1
222 DSL 1 0 1 1
222 GAS 1 0 0 0


create table trial_sales(
gasstationIDbigint,
salesDatesmalldatetime,
gallonsbigint,
productClass varchar(4))


insert into trial_sales values(111,'11/01/2005',10000,'GAS')
insert into trial_sales values(111,'11/02/2005',1000,'GAS')
insert into trial_sales values(111,'11/02/2005',20000,'DSL')
insert into trial_sales values(222,'11/01/2005',10000,'GAS')
insert into trial_sales values(222,'11/02/2005',10000,'DSL')
insert into trial_sales values(111,'11/15/2005',1000,'GAS')
insert into trial_sales values(222,'11/15/2005',10000,'DSL')
insert into trial_sales values(111,'11/25/2005',1000,'GAS')
insert into trial_sales values(222,'11/25/2005',10000,'DSL')





Task 3 Display unique values


Display unique values in

create table t (colA varchar(25))

insert into t values('apple')
insert into t values('bat')
insert into t values('car')

ouput required:

a
b
c
e
l
p
r
t



Task : 4

I have a table in the database I am working with with the following columns:
(I have simplified this down for the sake of clarity)
int p1
int p2
int p3
int s1
date d1
string s1

There is no primary key and the table more resembles a log than anything.
e.g.

p1 p2 p3 s1 d1 s1
1 12 14 100 23May comment 2
1 12 14 800 22May comment 1
1 12 14 300 24May comment 3
1 12 15 100 22May comment 2
1 12 15 800 21May comment 1
2 12 15 100 23May comment 1

What I want to do is exttract the rows from the table where the combination
of p1 p2 and p3 is distinct/unique and the date is the most recent one for
all combinations of p1, p2, p3. So from the table above I would end up with
the resultset:

p1 p2 p3 s1 d1 s1
1 12 14 300 24May comment 3
1 12 15 100 22May comment 2
2 12 15 100 23May comment 1





Task 5


say i have the following data...
uId(int PK), productcode(int), quantity(int)
1,12,5
2,12,3
3,12,5
4,11,9


....and i have an order for productcode 12 with quantity of 6 products.
i need to select only enough rows to reach a quantity of 6.
in this case, i would want the first two rows which would give me a sum of
8.
how can i do this without looping through each record, getting the quantity
and exiting the loop when i have a sum >= 6?



Task 6

I have two tables as below, TABLE1 and TABLE2.


TABLE 1: Base

IDPNamePPrice
-------------------------------------
1A30
2B20

TABLE 2: History

IDLdateAmount
-------------------------------------
12005/8/750


The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign key.
What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
it will return the result as below:


LdatePNameAmount
------------------------------------
2005/8/7A50
2005/8/7Bnull


and when I pass the date of 2005/8/8, it will return the result as below:

LdatePNameAmount
------------------------------------
2005/8/8Anull
2005/8/8Bnull



Task : 7

Reverse the Name

create table sample_judge(
judge_namevarchar(50))
go

insert into sample_judge values ('J Smith')
insert into sample_judge values ('M Samuel')
insert into sample_judge values ('S Shan')
insert into sample_judge values ('K Anand')
insert into sample_judge values ('K K K K Anand')
insert into sample_judge values ('K R Shanth Anand')
insert into sample_judge values ('K R Shanth Anand Kumara Velu')

go

Expected Output :

Smith J
Samuel M
Shan S
Anand K
Anand K K K K
Shanth Anand K R
Shanth Anand Kumara Velu K R


[CODE]

thanks in adv
chakri

View 5 Replies View Related

Tried To Solve A Problem And I Got Bit

Apr 18, 2004

I was working on the thread "Call stored procedure within SELECT statement "

but a funny thing happend
i was going to suggest using a function instead of a proc but i cant seem to get it to run

create function dbo.Fitem(@item int)
Returns table
as
Return (select item, Result=(value * value) from tba
where item = @item)
GO

select dbo.Fitem(1)

then i get this

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Fitem'

am i just too tired or what?

View 2 Replies View Related

Plz Solve My Problem

Jan 10, 2008

1) bothe numeric and decemal are equl. Why we were using both.
we can go throgh one of them.
Both are under same exact numeric category. In which situation we go for decimal in which situation we go for nummerics?

View 2 Replies View Related

Please Solve This Problem

Mar 10, 2008

hi,
i have a two tables like this.
employees table
empid, emp_name, emp_designationid, emp_departid
1 'rex' 1 3
2 'robert' 2 3

strings table
id, name
1 'manager'
2 'lead'
3 'software'

i want a sql query to get the data like this

emp_name, emp_designation , emp_depart
rex manager software
robert lead software




Raghu sunkara.

View 3 Replies View Related

Hi Please Solve This Problem

Mar 12, 2008

hi,
i have a table like this.
employees
emp_id, emp_name , emp_active, emp_departPID, emp_salary
1 'robert' 1 1 200
2 'Rose' 0 1 100

Department
DepartmentPID, DepartmentName
1 software

i need an sql query like.
i want to inner join employee table with department table when emp_derpatmentPID IS 1
can you help me out please.



Raghu sunkara.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved