Unable To Show 0 To Many Records With Inner Join
May 2, 2008
I am having problem with Inner joining of tables
my query is..
Select j.jobSubject,e.eOrganization ,jv.JobClick,j.jobID from dbo.tbl_Jobs jinner join dbo.tbl_Employer e on e.mId=j.jobCreatedByIDinner join dbo.tbl_JobView jv on jv.JobID=j.jobID
order by jv.JobClick desc This query returns 1 to many records
But I need the query should return 0 to many record . .yes I have already know inner join does not handle my problem so plz suggest me which type of join would solve my problem
View 3 Replies
ADVERTISEMENT
Apr 5, 2007
Hi ,
I've got two tables.. the first table carried a ProductID, and amongst other things a TradePrice
The other tbl carries a ProductID, a IndivPrice and a CustomerID
The second tbl lists prices for products for indiv Customers.
My Query needs to bring back ALL the products from the first tbl...
It also needs to show the TradePrice for that product.
I need to join my query to the second tbl...
And finally, if the second tbl has a price for that product AND the customerID is the same as one I pass into the query.. show that price also..
So here's my first query:
SELECT dbo.Products.ProductID, ProductName, ProductTradePrice, IndivPrice, dbo.Trade_PriceLists.CustomerID AS PLCustomerID FROM dbo.Products LEFT OUTER JOIN dbo.Trade_PriceLists ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID WHERE (ProductType = 'Trade' OR ProductType = 'Both') AND (Replace(Lower(ProductBrand),' ','') = 'brandname') AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '') ORDER BY TradeOrder
I thought that would work, but what happens is that, if that particular customer has no indiv prices set.. then it only shows the ones that have no records at all in that second tbl..
So unless there is a record for a particular product in that second tbl and it doesn't have a CustomerID assigned to (which would never happen as that tbl is only every for indiv customer prices) then it doesn't show.
Examples:
First Tbl
ProductID Name TradePrice
1 Jumper £1.00
2 Jeans £3.00
3 Shoes £5.00
4 Hat £2.00
Second Tbl
ProductID CustomerID IndivPrice
1 teste £0.50
2 othercustomer £2.50
3 teste £4.50
What I want in the results is:
ProductID ProductName TradePrice IndivPrice CustomerID (PLCustomerID)
1 Jumper £1.00 £0.50 teste
2 Jeans £3.00
3 Shoes £5.00 £4.50 teste
4 Hat £2.00
See? - The 2nd product should not get an indiv price as although it's in that second tbl, the customerID assigned to it is different. The 4th product should not get an indiv price as it's not in that second tbl at all.
however, with my query above I'd only get Products 1and 3... and if I did a query on a customer with no indiv prices I'd only get product 4 as it's not in the indiv at all...
HELP!!!!!
View 11 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 Replies
View Related
Apr 23, 2015
I am new to SQL but trying to do join a few tables to get result showing showing one row per unique record.
Tables include:-
1. REQ
2. RFQ
3. PO
4. DOCUMENT (contains LAST_DOCUMENT_STATUS, DOCUMENT_ID, DOCUMENT_NUMBER, for example, REQ_CANCELLED, REQ_ID, REQ_NO)
5. DOCUMENT_STATUS (contains status of document, REQ_CREATE)
6. DOCUMENT_TRAIL (contains link between documents, PARENT_DOCUMENT, CURRENT_DOCUMENT, for example, REQ_ID (PARENT_DOCUMENT), RFQ_ID (CURRENT_DOCUMENT)
7. PO_REVISION (contains PO REVISION, when link with DOCUMENT, PO_REV_NO)
Currently when i tried to join all the TABLES, i get multiple lines against REQ_NO.
I realised the multiple lines generated due to the following:-
One to many relationships:
A. RFQ - 1 or more PO
B. PO - 1 or more PO_REVISON
I was thinking how to MAX the records in PO to show only the last PO_REVISION. It seems that DOCUMENT_TRAIL will contain 1 base document PO and 1 or more PO_REVISION.
View 13 Replies
View Related
Mar 21, 2007
This is the select statement I'm currently using but I can't seem to get the browser to show any data. It just shows the table structure.
I use the g and the p as aliases. Easier maintenance this way.
strSQL = "SELECT g.GuestID, g.ProgramID, p.ProgramID AS Expr1, g.GuestName, g.GuestDescription, p.URL, p.Description FROM dbo.T_ProgramGuests g INNER JOIN dbo.T_ProgramLinks p ON g.GuestID = p.LinkID WHERE p.ProgramID = " & Request("ProgramID")
I have an ASP page where the above sql code is embedded. I have two SQL tables. One is called T_ProgramGuests and the other is called T_ProgramLinks. The asp page basically does a read only of the two tables and is suppose to return the results inside of the html tables I have in the asp page. But all I see when I test is the html and the tables are devoid of data.
HERE IS THE PAGE CODE:
<%
on error resume next
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:webserviceCompanyCompany.UDL"
set recProgram = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT g.GuestID, g.ProgramID, p.ProgramID AS Expr1, g.GuestName, g.GuestDescription, p.URL, p.Description FROM dbo.T_ProgramGuests g INNER JOIN dbo.T_ProgramLinks p ON g.GuestID = p.LinkID WHERE p.ProgramID = " & Request("ProgramID")
recProgram.Open strSQL,con
%>
<TABLE border="1" cellspacing="0" border="1" width="70%">
<p><b>Guest Name and Description:</b></p>
<tr>
<th width="35%" align="left">Guest Name</th>
<th width="45%" align="left">Guest Description</th>
<tr>
<TD headers="t2"><%=recProgram.Fields("GuestName")%></TD>
<TD headers="t3"><%=recProgram.Fields("GuestDescription")%></TD>
</tr>
</table>
<TABLE border="1" cellspacing="0" border="1" width="70%">
<br /><br />
<p><b>URL and Description:</b></p>
<tr>
<th width="35%" align="left">URL</th>
<th width="45%" align="left">Description</th>
<tr>
<TD headers="t4"><%=recProgram.Fields("URL")%></TD>
<TD headers="t5"><%=recProgram.Fields("Description")%></TD>
<%'
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
'response.write err.Description
%>
</body>
</html>
I am new to using the advanced features of SQL and could sure use some help with this.
View 2 Replies
View Related
Jan 1, 2008
Hi:I have written a SQL statement that accepts a letter and then prints out all the records in a table starting with that letter. I was wondering if there is a way that I could change the query so that if prints out all records if a blank or empty value is passed in?Here's my query: ALTER PROCEDURE [dbo].[GetMediaListByFirstLetter] ( @firstLetter char(1))AS SELECT Media_ID, OrgName FROM Media WHERE UPPER(SUBSTRING(Media.OrgName,1,1)) = @firstLetterAny help doing this would be greatly appreciated.Roger
View 5 Replies
View Related
Aug 5, 2013
I have three tables Accounts, History and Dates . What I need to do is display all the accounts from History (900) records and compare them to the accounts in Accounts table pull all the matching records based on a certain date range , but If there is no record in the History table for this period I still need to display the account from Accounts and some text saying that there is no record matching for this period.
Account History
11
22
33
4NO information for this month
55
SELECT C.ACCOUNT, CASE WHEN C.ACCOUNT = LEFT(H.NUMBER,8)
THEN LEFT(H.NUMBER,8) END FROM ACCTS C
LEFT OUTER JOIN HISTORY H ON C.ACCOUNT = LEFT(H.NUMBER,8)
INNER JOIN DATES D ON h.PERIOD = D.CUR_PERIOD
GROUP BY C.ACCOUNT, H.NUMBER
This will give me all the matching records for the period but I need somehow to show all the accounts even if they don't have records for this period.
View 5 Replies
View Related
Aug 13, 2015
I have a table with 5 columns, let say ID,PersionID, Date, Type,Qty and source data looks like this
ID  PersonID   Date           Type      Qty Â
1     1       01/01/2011      Accept     5         Â
2     1       01/01/2011      Accept     5 Â
3     2       02/01/2010      Accept     10            Â
4     2       02/01/2010      Deny       20 Â
5     3       02/01/2012      Accept     15
[Code] .....
Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.
ID PersonID   Date           Type        Qty
1   1       01/01/2011      Accept       5     (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis
same)
3   2       02/01/2010      Accept       10    (show Accept row=3,ignore deny row)
5   3       02/01/2012      Accept       15    (show Accept row=5)
6   4       05/05/2012      Accept       25    (show Accept rows=6,7 and ignore Deny & Accept rows = 8,9)
7   4       07/08/2012      Accept       20
       Â
11  6       01/01/2011      Accept       5     (show Accept rows=11,12 because Qty is different) Â
12  6       01/01/2011      Accept       15
Create Sample Table (ID int null, PersonID Int null, Date Datetime null , Type varchar(10) null, Qty int null)
Insert into sample values (1 ,1,'01/01/2011','Accept',5),
(2,1,'01/01/2011','Accept',5), Â
(3,2,'02/01/2010','Accept',10),            Â
(4,2,'02/01/2010','Deny',20), Â
(5,3,'02/01/2012','Accept',15), Â
(6,4,'05/05/2012','Accept',25), Â
(7,4,'07/08/2012','Accept',20),Â
(8,4,'07/08/2012','Deny',5),
(9,4,'09/23/2012','Accept',23),
(10,5,'09/08/2012','Deny',12),
(11,6,'01/01/2011','Accept',5),         Â
(12,6,'01/01/2011','Accept',15)
View 4 Replies
View Related
Feb 15, 2007
I have two tables, Employee and Calls. They are joined on an Employee field. In my where clause I have a value specified that only returns specific calls. What I would like to have happen is to have the query return all Employee records regardless if any records from the Calls table is present for that employee. I want something that looks like this:Employee # of Calls
Employee A 5
Employee B 0
Employee C 10
When I apply a WHERE clause to the Calls table I get this:Employee # of Calls
Employee A 5
Employee C 10
I tried a LEFT OUTER JOIN without success. Any suggestions?
View 4 Replies
View Related
Nov 6, 2014
in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').
i tried in my join query
these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')
its taking so much of time how t o solve ?
View 1 Replies
View Related
Apr 28, 2015
Basically i have 3 images capacity per record in my asp.net application. In there i am saving the image path with record id in database and image in my application folder.
Now i am creating SSRS Report in Report builder . In there i have taken one image control to show the images. In the Image properties in report builder i have chosen database under the select the image source field. then inside use this field i have chosen image url and in use this MIME Type i have selected image/jpeg. Now i have saved this report in report server folder.
Now while calling in .net web from through report viewer control. It is opening the report but wont showing the image.
View 2 Replies
View Related
Aug 29, 2006
Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (???) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance. Bill
View 2 Replies
View Related
Sep 28, 2007
Hello.I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:http://forums.asp.net/p/1159666/1913519.aspx#1913519http://forums.asp.net/p/1161930/1924264.aspxhttp://forums.asp.net/p/1116601/1732359.aspx#1732359http://forums.asp.net/t/1104718.aspxhttp://forums.asp.net/p/1096290/1655706.aspx#1655706http://forums.asp.net/p/1110162/1707952.aspx#1707952 Basically, I need a DropDownList to display only projects for which the logged in user is assigned as leader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code? * Here is the definition of the SqlDataSource: <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>" SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @Leader)" OnSelecting="SqlDataSource5_Selecting"> <SelectParameters> <asp:Parameter Name="Leader" Type="Object" /> </SelectParameters> </asp:SqlDataSource> * Here is the definition of the SqlDataSource5_Selecting method: protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters("@Leader").Value = loggedInUserId; } where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey; Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId; in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much.
View 11 Replies
View Related
Mar 10, 2015
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example:
Table 1
id Name
1 Coupon1
2 Coupon2
3 Coupon3
[code]...
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is:
SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
View 5 Replies
View Related
Aug 4, 2015
I have a query below to show all the records with joining these two tables.
SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD
FROM BF_ORGN A
INNER JOIN BF_ORGN_CNSL_TBL B
ON A.CD=B.BF_ORGN_CD
WHERE A.BF_ACTY_CD IS NOT NULL
ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD
My goal is only to show all the duplicate records.
Bf_ORGN_CD LEV5 BF_ACTY_CD
AC_21234_2 AC_21200_1 402
AC_21236_2 AC_21200_1 402
AC_21238_2 AC_21200_1 402
AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD)
AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)
[code]...
View 9 Replies
View Related
Feb 15, 2007
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
View 3 Replies
View Related
Mar 13, 2006
I have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients.
I have data in both table like
CompanyTable
ID Name
1 name1
2 name2
OrderTable
OrderId CompanyTabID
1 1
2 1
3 1
4 1
In my query I want to show all orders in single row.
ID Name Orders
1 name1 1,2,3,4
2 name2 null
Is anybody can help on it.
Thanks
Arvind
View 5 Replies
View Related
Mar 5, 2007
Hi,
I have one table which holds an ID, a name, and a parentID (which can either the same as the main ID or it's one of the other ID in that tbl)
At the moment I do a select on the records whos main ID is the same as the ParentID. This gives me an initial list of records who are the "Main" parent records (i.e they are not the child of any other record..
so far so good.
However, what I need to do is for each record work out if they themselves have any Child records. (only some have).
so at the mo I end up with a results set as this:
1 Boats
2 Jumpers
3 Trousers
4 Ships
What I want to end up is something like
1 Boats True
2 Jumpers False
3 Trousers True
4 Ships True
Where the true or false is if the record has any child records..
Any ideas?
View 6 Replies
View Related
Jul 9, 2013
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT
123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
View 6 Replies
View Related
Feb 4, 2015
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
FROM VENDFIL A, VENDFIL B, VENDFIL C
GROUP BY A.FEDTID, B.VENDOR
View 5 Replies
View Related
Feb 10, 2014
I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime. I want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.
Example:
Customer NameGoodsPriceDatePurchased
ABat2501/31/2014
ABall221/31/2014
BCarrom Board4752/2/2014
CTennis Ball502/1/2014
AFootball1502/2/2014
DBat2501/31/2014
BBall221/31/2014
AHockey Bat1252/4/2014
CChess552/4/2014
AVolley Ball552/4/2014
View 9 Replies
View Related
Nov 22, 2000
help me out , please.
i want to join two tables, with some similar columns. using DTS, how do i do it??
i want to automate the process and not just write query.
please someone help.
View 2 Replies
View Related
Sep 30, 2013
I am trying to join two tables together, on the same field except they have different data types, see the properties below
Code:
TableCOLUMN_NAMEDATA_TYPECHARACTER MAXIMUM LENGTHCHARACTER OCTET LENGTHCHARACTER SET NAMECOLLATION NAME
1itemClassnvarchar 512 1024 UNICODE Latin1_General_CI_AI
2PGCode varchar 3 3 iso_1 Latin1_General_CI_AS
in the code for the join,
Code:
left join common.dbo.qryPRDGroupDets on CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) = Cast(common.dbo.qryPRDGroupDets.PGCode as varchar(3))
I have tried using the CAST function on one side of the join then on both, to no avail...
View 6 Replies
View Related
Mar 20, 2007
I've got an application I've written that should be able to (using a table) insert, update or delete records in SQL Server 2005. It can insert and update fine but it cannot delete any records. I'm totally stumped for how to figure out what's going on. I can remote on to the Windows 2003 Enterprise Server without any trouble so I should be able to read any log files or open the SQL Managemenent tools. How do I figure out what the issue is? It's a very strange thing and I'm stumped.
View 6 Replies
View Related
Nov 23, 2004
PLease help me with a intruiging problem with Stored Procedure, which is driving me mad.......
I wrote a Stored Procedure as listed below.
I've included the options
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
the results are stored in a #Temp table
The SP executes fine on Query Analyzer, and shows the contents of the temp table. BUt when I run this SP using ASP, I got the following error
-----------
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/test_site/test.asp, line 59
------------
I've tried using Recordset.MoveNextRecordset, if at all there are more than one recordset, obviously I got the same error
CREATE PROCEDURE atul_med.[sp_rep_sale2]
(
@rep_in varchar(100)
)
AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
DECLARE
@REP int,
@temp_pack varchar(20),
@temp_ntp int,
@temp_party_code varchar(20),
@temp_party_name varchar(20),
@temp_prod_code varchar(20),
@temp_prod_desc varchar(20),
@tot_qty int,
@tot_amount int,
@iCnt int,
@tot_sale_qty int,
@net_tot_sale_qty int,
@TableName varchar(10)
select @REP= @rep_in
set @tot_sale_qty=0
set @net_tot_sale_qty=0
set @TableName='#Test2'
--select @TableName='Test'
set @page=1
set @RecsPerPage=100
Declare @SQL VarChar(1000),
@cnt int,
@iCurLine int, @SQLX VarChar(1000)
set @iCurLine=1
set @iCurLine=1
select @Cnt = (SELECT count(*) FROM VW_PART_REP WHERE (REP = @REP))
print "Nos Of Party :"+ convert(char(1),@iCnt)
Create Table #Test2 (ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int)-- ,tot_qty int ,tot_amt int )
while @iCurLine <= @cnt
begin
SELECT @SQL= "ALTER TABLE #Test2 ADD "
SELECT @SQL = @SQL + "party_qty"+convert(char(2),@iCurLine)+" int "
set @iCurLine=@iCurLine+1
Exec (@SQL)
print @sql
end
SELECT @SQL= "ALTER TABLE #Test2 ADD "
SELECT @SQL = @SQL +"tot_qty int , tot_amt int "
SELECT @SQL = @SQL
exec (@SQL)
print @SQL
select * from #Test2
--Create Table #Test1 (ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int,party_qty1 int ,party_qty2 int ,party_qty3 int ,party_qty4 int ,party_qty5 int ,tot_qty int ,tot_amt int )
set @iCurLine=1
--=============================================
--print '=================Product Name========================'
DECLARE PROD_CUR CURSOR FOR
SELECT distinct(prod_code) as prod_code,prod_desc,pack,ntp FROM VW_sales_sum
WHERE (REP = @REP)
group by prod_code,prod_desc,pack,ntp
Declare @SQL_ins VarChar(1000)
OPEN PROD_CUR
FETCH NEXT FROM PROD_CUR
INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_n tp
WHILE @@FETCH_STATUS = 0
BEGIN
--Print @temp_prod_desc+'-'+convert(varchar(10),@temp_pack)+'-'+convert(varchar(10),@temp_ntp)
--print '=================prod_code :'+convert(varchar(10),@temp_prod_code)+'========= ==============='
print "============Insert Statement============"
SET @SQL_ins = "Insert into "+@TableName+" values("
SET @SQL_ins = @SQL_ins +""""+ @temp_prod_desc+""","""+@temp_pack+""","""+convert(char(10),@temp_ntp)+""","
DECLARE Party_CUR1 CURSOR FOR
SELECT party_code,party_name FROM VW_PART_REP
WHERE (REP = @REP) group by party_code,party_name
DECLARE @SQL1 varchar(10),@SQL2 varchar(100)
set @SQL2=''
OPEN Party_CUR1
FETCH NEXT FROM Party_CUR1
INTO @temp_party_code,@temp_party_name
WHILE @@FETCH_STATUS = 0
BEGIN
print "==Party_name :"+ @temp_party_name +"===Party_Code :"+convert(char(3),@temp_party_Code)
set @tot_sale_qty=0
set @tot_sale_qty= (select sum(issuedqty)as tot_qty from vw_sales_sum
where party_code =@temp_party_code
and (REP = @REP)
and (prod_code=@temp_prod_code))
if @tot_sale_qty IS NULL
begin
set @tot_sale_qty=0
print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
end
else
begin
print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
end
SELECT @SQL1 = @tot_sale_qty
select@net_tot_sale_qty=@net_tot_sale_qty+@tot_sale_qty
--print @SQL1
Select @SQL2 = @SQL2 + @SQL1 +","
FETCH NEXT FROM Party_CUR1
INTO @temp_party_code,@temp_party_name
END
CLOSE Party_CUR1
DEALLOCATE Party_CUR1
print '==============================='
print @SQL2+convert(char(4),@net_tot_sale_qty)
--set @net_tot_sale_qty=@tot_sale_qty+@net_tot_sale_qty
--SET @SQL = @SQL+convert(char(10),@tot_sale_qty)+','
--set @tot_sale_qty=0
Select @SQL_ins = @SQL_ins+@SQL2+convert(char(8),@net_tot_sale_qty)
Select @SQL_ins = @SQL_ins+","
Select @SQL_ins = @SQL_ins+convert(char(12),(@net_tot_sale_qty*@temp _ntp))
Select @SQL_ins = @SQL_ins+")"
print @SQL_ins
Exec (@SQL_ins)
select@net_tot_sale_qty=0
FETCH NEXT FROM PROD_CUR
INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_n tp
END
CLOSE PROD_CUR
DEALLOCATE PROD_CUR
SELECT * FROM #Test2
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER ON
GO
View 8 Replies
View Related
Jul 7, 2007
I was importing records via DTSWizard, and I was having problems so I turned off Enforce Replicaton, Enforce FK Constraints on a couple of fields. I'm new with SQL Server so I'm not sure if this even caused the problem. (Do I need to turn these back on, or is this a Developer switch of some kind?)
The end result left me with duplicate records in the table, and I'm not able to delete any of them. This is the Error I got...
A problem occurred attempting to delete row 1.
Error Source: Microsoft.VisualStudio,Datatools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they after multiple rows(2 rows).
If someone could tell me what I need to do so I can delete the records I'd really appreciate it.
Thanks,
Bill
View 11 Replies
View Related
Feb 12, 2014
I created a view with a simple join query
there are 2 rows with orderid as null, i am unable to fetch the details when i give WHERE Condition as
"select orderid from joins where orderid=NULL"
Query :
create view joins as
select A.customerid,A.Companyname,A.Contactname,A.City,B.OrderId from Customers A
left join orders B
on A.Customerid=B.Customerid
View 1 Replies
View Related
Jun 13, 2007
Hello,
I have been serching for weeks to resolve this problem. I am new to ASP.NET and trying to make the migration from ASP which I have programmed in for years. I am using Microsoft Visual Web Developer 2005 Express Edition and SQL Express Edtion. I have been working through the Microsoft Video Training at http://msdn.microsoft.com/vstudio/express/beginner/learningpath/ and created a web site using Tier 3 Lesson 8 as the model. My new web site which is a simple phone book applicaiton lets me read the table and select the record without any problem. But the update form lets me edit but when I attempt to Apply the update I get the following error.
Server Error in '/Phonebook' Application.
ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'Update' that has parameters: FirstName, LastName, PhoneNumber, BossGroup, Department, BossPickup, ShowInPhonebook, Type, Original_FirstName, Original_LastName, Original_PhoneNumber, Original_BossGroup, Original_Department, Original_BossPickup, Original_ShowInPhoneBook, Original_Type, Original_ItemID.
Description: An unhundled expception occured during the execution of the current web request. Please review the stack trace for more information about the error and where it originiated in the code.
The Stack Trace basiclly showes the same error as above.
Also, when I attempt to delete the record I do not get an error but the record does not delete.
What is interesting is that I can add a record so I do not believe that it is a security permissions issue. I have the ISS Authinication Method Enable Anonymous Access set on with full control.
If anyone has any insight as to why this is occuring please let me know.
View 13 Replies
View Related
Jul 20, 2005
Hi,I have an sql database that has the primary key set to three fields,but has not been set as unique(I didn't create the table).I have 1 record that has 2 duplicates and I am unable to delete theduplicate entries.If I try to delete any of the three records(they are identical) I getthe message 'key column is insufficient or incorrect. Too many rowswere affected by update'.I am trying to do this within Enterprise Mgr.Any suggestion?Thanks much
View 2 Replies
View Related
Nov 15, 2007
Hi,
We need to generate a report using SQL Server 2005 Reporting Services which can have 1 million plus records. So we have created a report with a simple select query. This query when run against our database returned 1110018 records.
We deployed the report onto our server and tried accessing the report through its URL. What we observed was that the report ran for about 15 minutes and then it prompted for user id and password (Windows authentication). On giving the user ID and password it continued running for another 15 minutes and at the end of it, the browser again prompted for user id and password. This cycle continued twice and at the end of it we got the message €œThe page cannot be displayed€? (i.e. the usual message displayed by the browser when it cannot load a web page). We were not able to load the report at all.
Are there any specific considerations when loading reports with such high no. of records. Any suggestion, solutions are welcome.
Thanks,
CodeKracker.
View 4 Replies
View Related
Oct 27, 2006
Hi!I am able to add records to my SQL 2005 database using bound forms in myAccess application, but if I try to edit any records (not only the newrecords I create) using the same bound forms, I receive a write conflict.For the record, I am currently the only user of this application and my SQLServer database has only one userID. I used this userID to create all ofthe objects in the database and to connect to the database from Access. Thedata sources for the bound forms are linked tables, the record source typeis set to "Dynaset" and the record locks are set to "No Locks". "Opendatabases using record-level locking" is checked.The write error says that another user has changed the current record sinceit was opened (since I'm currently the only user this isn't possible) andasks me to copy my changes to the clipboard or drop the changes.Can anyone suggest what might be causing this? Thanks!
View 1 Replies
View Related
Nov 18, 2006
I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
View 3 Replies
View Related
Sep 22, 2014
We are using a presence checker to register when our employees show up at work, when they leave and whenever they change their working section.
The raw data has this format:
TABLE
-----
TIMESTAMP USERID SECTION INCIDENCE
------------------------------------------------------
2014-06-27 14:52:40.000 77675669 7 0
2014-06-27 15:08:33.000 77675669 6 8
2014-06-27 15:30:58.000 77675669 7 12
2014-06-27 18:52:00.000 77675669 11 15
2014-06-27 19:47:38.000 77675669 7 12
2014-06-27 23:30:18.000 77675669 7 0
In order to determine the exact time people had been working in the sections I have to match every record with the record of the successive timestamp I do:
SELECT TIMESTAMP as START, USERID, SECTION, INCIDENCE, MIN(TIME_END) as STOP
FROM (
SELECT *, TABLE2.TIMESTAMP as TIME_END
FROM TABLE
LEFT OUTER JOIN TABLE as TABLE2 on TABLE.USERID = TABLE2.USERID and
TABLE2.TIMESTAMP > Table.TIMESTAMP and
[Code] ....
How do I get the incidence of the successive timestamp in my query?
The key trick is that I first do a self join which increases the amount of records, including senseless matches from the first timestamp with the last one of that day. Afterwards I group by all columns of TABLE in order to get only the successive timestamp.
But once I include TABLE2.INCIDENCE in the GROUP BY clause, of course the query stops working correctly.
I can't use the TIMESTAMP isself as it is not singular (there are several terminals in the plant); and I prefer not to use Timestamp in combination with Userid, as the userid is actually not stored directly and has to be retrieved through a couple of joined tables. Any smarter way to include a second column once the successive timestamp has been determined.
View 2 Replies
View Related
