i have a dimension named 'sdoc' which has 2 members
1) symptom word( first hierarchical level) and
2) doc id (second hierarchical level)
more than one 'symptom word' can have the same 'doc id'.
i want to know which all symptom words have a particular doc id (say 10.)
if i give [10].parent then it returns only the first parent whereas it actually has more than one parent.
how to retrieve all the parents.
Is it possible to retrieve the value of a subreport's field or control from the parent report? I'm doing some grouping in the subreport and need to retrieve the group by's data value from the subreport.
Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.
Thanks,
Garick
I have the below query which in turn goes in the where clause
Code:
SELECT CASE WHEN EXISTS (SELECT child.id FROM InspectionType parent,
InspectionType child WHERE parent.id = child.parentId AND
parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
THEN (SELECT child.id FROM InspectionType parent, InspectionType child
WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129)
END
I am getting below error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I tried to solve this problem with If and else but it is not going well with in-clause
Code:
select * from InspectionType where id in (select IF EXISTS (SELECT child.id FROM InspectionType parent,
InspectionType child WHERE parent.id = child.parentId AND
parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
BEGIN (SELECT child.id FROM InspectionType parent, InspectionType child
WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
END
ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) )
I am using select * from InspectionType where id in() for illustation only. The sub-query results will go another query
Code:
SELECT child.id FROM InspectionType parent, InspectionType child
WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129
This returns more than one row.I know why is the error message but need to achieve this functionality.
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
[Code] .....
How can I efficiently retrieve the top x managers within the top y regions within the top z states within the top q countries?
The only way I have been able to do this is to first find the top q countries, and store the list in an IN clause. Then for each of those, find the top z states and create another IN clause that contains the country+state concatenated. Then for each of those, find the top y regions and concatenate country+state+region. Then finally find the top x managers within this IN clause.
This works fine for a few hundred records, but once it reaches the thousands, it takes much too long. The final IN clause contains thousands of entries.
Isn't there a simpler way to approach this, especially with SQL Server 2005?
i am using a stored procedure, lets say spTemp, that calls sp_executesql within it. in the sp_executesql procedure i am passing parameters from the stored procedure 'spTemp'. i want to retrieve the result of the sp_executesql statement so as to perform further calculations in spTemp. is there a way of doing this?
ex:
create procedure spTemp
(
@abc int
)
as
declare @temp int
exec sp_executesql 'select @temp = Id from Product where @abc > 10'
...
how to get the value @temp from this statement?
Huh?
I've got good RI data...BUT..a developer loaded the tables in alpha table order....
Such that the child loaded BEFORE the parent....
Huh?
Got a test being set up now to mess with the child file to add a key that doesn't exist in the parent...
But Why is this allowed?
In DB2 you can specify
LOAD DATA REPLACE NO CHECK....
On the load card...you then need to run a check after to verify the data...
Is that what's going on? Is there such a utility in SQL Server to run a check post load?
I'm confused....
Any comments appreciated.
Thanks
Brett
8-)
i get a error message like this: Table RENTAL_ITEM in RENTALB23 does not have a matching parent key.
this is the sql:
CREATE TABLE RENTALB23/RENTAL_ITEM (RENTALITEM# INT PRIMARY KEY,
RENTALTYPE CHAR(3), TITLE CHAR(30), CATEGORY CHAR(15), DUEDATE DATE,
FOREIGN KEY(RENTALTYPE) REFERENCES
RENTALB23/RENTAL_CHARGE(RENTALTYPE), FOREIGN KEY(CATEGORY)
REFERENCES RENTALB23/RENTAL_CHARGE(CATEGORY))
this is the sql for the rental_charge table:
CREATE TABLE RENTALB23/RENTAL_CHARGE (RENTALTYPE CHAR(3),
CATEGORY CHAR(15), CHARGE DECIMAL(5,2), PRIMARY KEY(RENTALTYPE,
CATEGORY))
any ideas what can cause this?
I am now retrieving the PDF from the database and I am getting an error:
Error 1 'GetImages.GetImage(int)': not all code paths return a value
I have:
int imageid = Convert.ToInt32(Request.QueryString["ACTUAL_IMAGE_PDF"]);
And...
private SqlDataReader GetImage(int imageid)
{
Sql Statement...
}
Could someone help please??
Hi
I've got a module that contains the following function.
Imports System.Data.SqlClient
Module SQL_Sprocs
Function ListUsers()
Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("DBConn"))
Dim cmd As SqlCommand = New SqlCommand("list_users", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
ListUsers = dr
dr.Close()
conn.Close()
End Function
End Module
I then want to call this function from my webform. So I'm using the following
Dim dr As SqlClient.SqlDataReader
dr = Timetracker.SQLProcs.ListUsers()
Do While dr.Read
Label1.Text &= dr("first_name") & " " & dr("last_name") & ", "
Loop
dr.Close()
But it's not working. I want to have a module that contains all my sprocs and be able to call them from the individual webpages.
What am I doing wrong?
Lbob
Hi, I have a Stored procedure doind an INSERT which then returns @@Identity.
I have set this parameters direction to output, however, when i run it I get an error saying procedure is expecting this output parameter..
Not sure where I am going wrong...
Can someone please help with retrieving the ID after an insert. What is the correct code in .NET?
Many Thanks
I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.
Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.
i have a table in MS SQL Server,i need to show two results at a time,and on click of a button nxt two results have to b shown,how do i accomplish this (i mean retrieving the results two at a time)thanx in advance
View 1 Replies View RelatedFaculty member writes:
"I meant to delete just one assignment, doing which was giving me difficulty.
I deleted a whole category by accident which had most of my daily grades in it for a course with quite a few of them. I was negligent in backing them up, so I cannot restore them. I have an XLS file from the midterm, but am missing about 6 grades after that.
Is there any way to recover the grades I had in the deleted category. It has been a couple of days since I entered any new grades. HELP!"
My current backup strategy is to do a full backup 5 pm, differentials every 2 hours, logs every 30 min, all so they arenot simultaneous. Backing these all to same backup device via three different jobs.
Then the server is backed up to tape shortly after the 5 pm full backup.
Obviously, I can't restore from the backup for this situation because it would hurt other data.
But - is there a way I can take last night's backup from tape and restore it to a different database than our production database? (Such a database doesn't at this time exist. Would I detach, copy over the files, and then reattach on prod and attach on the devl side, and then restore from the backup device to the devl copy?)
I've never had to perform this kind of rescue and am wondering if my backup strategy is flawed since it isn't immediately evident how I can easily do this.
I needed a similar thing from our Oracle DBA yesterday, and he went to tape, copied out a table and put it on the Oracle database under a different owner, and I easily fixed the few records that I had botched.
That's exactly what I need to do now with MS SQL 2000.
As the faculty dude said, "HELP!"
And Thanks.
Hi,
I was hoping someone would be able to help me, I trying to go through several databases, 111 to be exact, and get the data out of a particular table which is present in all the databases on the same server.
I know how to get the names of the databases from the INFORMATION_SCHEMA, but how can use the name for it to cycle through all the databases and get data out of the one particular table.
Hi,
I have a giant SQL query that retrieves a bunch of information from a couple of tables for use on a page, and I would like some help on constructing the SQL to get exactly what I have in mind. I have a table - called scContacts - that contains a list of contacts with detailed information. The contacts in the table are regular sales contacts, sales reps, and sales managers. Another table - called scCompany - contains a list of companies with detailed information.
For each company, we have a certain sales manager (or user) pertaining to that company as well as potentially a certain rep. The problem is that the contact tied to the company, the sales manager tied to the company, and the rep tied to the company all come from the same table. Here is the base SQL code I've used to get everything but the sales rep's name and sales manager's name:
----- SQL CODE -----
SELECT DISTINCT
scContacts.ID,
scContacts.inheritCoAddr,
scContacts.fName,
scContacts.lName,
scContacts.contact,
scContacts.billToAddress1,
scContacts.billToAddress2,
scContacts.billToState,
scContacts.billToZip,
scContacts.billToCity,
scContacts.billToCounty,
scContacts.billToCountry,
scCompany.ID AS companyID,
scCompany.companyName,
scCompany.companyURL,
scCompany.billToAddress1 AS companyAddress1,
scCompany.billToAddress2 AS companyAddress2,
scCompany.billToCity AS companyCity,
scCompany.billToState AS companyState,
scCompany.billToZip AS companyZip,
scCompany.billToCounty AS companyCounty,
scCompany.billToCountry AS companyCountry,
scCompany.businessType,
scCompany.phoneExt,
scCompany.phoneNum,
scCompany.faxNum,
scCompany.minEmployees,
scCompany.maxEmployees,
scCompany.actionTypeMAX,
scCompany.actionDateMAX,
scCompany.actionTimeMAX,
scCompany.statusID,
scCompany.userID,
scCompany.repID
FROM
scCompany,
scContacts
INNER JOIN
scStatus ON
scStatus.ID = scCompany.statusID
WHERE
scgroupContacts.contactID = [insert cookie value]
AND
scgroups.ID = [insert cookie value]
AND
scCompany.ID = scContacts.companyID
AND
scCompany.groupID = scgroups.ID
----- END SQL CODE -----
As indicated right now, this SQL code will retrieve all of the contact and company information for a certain contact ID which comes from a cookie value (inserted in ASP). I want to get the [fName] and [lName] fields from scContacts table where the scContacts.ID = scCompany.userID, and I also want those same fields from scContacts table again where the scContacts.ID = scCompany.repID. It would be simplest and most efficient if I could do this all at once (and I'm sure it's possible). How would I change the SQL to bring in that information from the same table two more times, tying their ID's to ID's in the company table?
thanks,
mellamokb
bobby writes "I have a Table from which i get the ID of customers(Customers are people who have written an online exam).The date of completion of the exams are stored in the corresponding table with the name of the exam.There are more than one exams and the names of the exams are stored in an other table.Now my question is how to get the date of cpletion with the given Id from the first table?"
View 2 Replies View RelatedHi, I need some help. I have this query where i need to pull up students that have more than or equal to 3 absences in a row. Now I am getting all those students that are absent but not in a row. But i was wondering if there is a way to tell if a student was absent three days in a row . There is a date field that is being used to identify the day of the class and a status field that identifies if the student was absent or present. Please help someone.
Birju
i lost a table with lots of data - there's around 10,000 records inserted daily.
my problem was i only realized this morning (and i lost it last night) and i have the backups backing up each day every hour - so the backup had already overwritten it seems the db that was missing the table)
my backup type is set to append - so i though i could retrieve by date time but it only let me retrieve the backup at 11pm(tune if last backup) and no earlier time. is there anyway of getting the data from the 7 pm backup -- again it backsup each hour to the same file - set to append so i thought that meant i could choose a time but it doesn't seem to be working.
please advise.
insert into newTABLE(newCOLUMN)
Select
Case When [Column 11] like '%DOB%' Then
SubString( [Column 11],
CharIndex('DOB',[Column 11],1)+3,
Case When CharIndex(';',[Column 11],1)= 0 Then
CharIndex('.',[Column 11],1) Else CharIndex(';',[Column 11],1)
End-(CharIndex('DOB',[Column 11],1)+3)) Else '' End,[column 11]
from table
Following is da example of return result
through the image, we can found that some of them got ddmmyy and some got only year.Currently, i want to insert those record to newtable newcolumn with only those got propal date(unwanted for only year).What should i do following?
Hi AllBit of a situation so hopefully someone can advise.I've a client who has a sql 2000 database that *unfortunately* hasn'thad a backup procedure in place. Its been running for just over a yearand on Monday it got, well, screwed. They have a 50 gig ldf file.Yes, 50 gig. Is it possible to put in place a database maintenanceplan that will allow me to roll back to, say, last friday just from theldf file?Thoughts? Any advice would be welcome.MTIAMark
View 3 Replies View RelatedHi all,I have an application that creates a publication on the server, andhave multiple mobile devices creating annonymous subscriptions to thatpublications. I need to write a report that checks if each device havethe replication synchronized successfully. I can rundistribution.dbo.sp_MSenum_merge or look intodistribution.dbo.MSmerge_history to get at the data for _all_subscriptions to a given publication, but to look at a particularsubscription, I need to filter by either the subscriber_db or agent_idcolumn. The problem is, how do I get either one of these informationfrom the device? Or is there other way of retrieving the merge historyfor a particular device/annonymous subscription?Thanks in advance,Harold
View 1 Replies View RelatedHi,Is it possible to get metadata (i.e. descriptions of tables etc.) insql-server? In Oracle you can retrieve this information with tables likeall_objects, user_tables, user_views etc. For example, this query selectsthe owner of the table 'ret_ods_test' (in Oracle!):select ownerfrom all_objectswhere object_name = 'ret_ods_test'What's the equivalent in sql server?Thanks a lot.
View 2 Replies View RelatedDear fellows,
Up to the moment I've got enough knowledge for read data stored into .LDF files by dbcc log and so on. It's very useful and interesting. Now, I wonder how to retrieve the same information but on MDF files.
At first, I want to make sure that is not possible by mean of traditional methods (dbcc or something like that) I suppose so but I'd like to hear opinions regarding that.
Thanks in advance for any idea, though or further information.
whats the best way to delete a parent record? for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?my code below: The T-SQL Stored ProcedureALTER PROCEDURE dbo.DeleteInventory
(
@InventoryID int
)
AS
DELETE FROM Booking
WHERE InventoryID = @InventoryID
DELETE FROM Inventory
WHERE InventoryID = @InventoryID
The code in the Code Behind to execute when a button(delete) click Try
Dim temp As Integer = SqlDataSource4.Delete
If temp > 0 Then
lblDeleteDtatus.ForeColor = Drawing.Color.Blue
lblDeleteDtatus.Text = temp & " Records/Rows Deleted."
Else
lblDeleteDtatus.ForeColor = Drawing.Color.Orange
lblDeleteDtatus.Text = "No records Deleted!"
End If
Catch ex As Exception
lblDeleteDtatus.ForeColor = Drawing.Color.Red
lblDeleteDtatus.Text = "An Error Occured.<br />" & ex.Message.ToString
End Try any better methods anyone wants to share?
hi,
i have two tables i want the identity value of the parent table to be inserted into the chile table
here is my code,but i don't know why it isn't working !
protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string pcontent = TextBox1.Text; string data = TextBox2.Text; addtopic(pcontent,connectionString); addfile(data, connectionString); } public void addtopic(string subject,string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) { SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" + "Values(@content)", connection); command.Parameters.Add("@content", SqlDbType.Text).Value = subject; connection.Open(); command.ExecuteNonQuery(); } } public void addchild(string name, string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) {Guid id = Guid.NewGuid(); SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" + "Values(@@IDENTITY,@data,@uid)", connection); commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name; commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id;
thanks in advance :)
connection.Open(); commandd.ExecuteNonQuery(); }
}
I'm building a page that will display link categories with the links under each one. I have that part working however there is a code issue that I cant quite figure out related to the SQL query.
I want to be able to have the category only display if the 'linkcolumn' is set to 1 in the database. (so I can later separate which ones show in each column of the page.
However, I get an error if I try to make my 'select' statement have a 'where' in it. I think it does this because it ends up not showing all the records but I'm not sure why.
Exception Details: System.ArgumentException: This constraint cannot be enabled as not all values have corresponding parent values.
My codebehind file:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.Sql;using System.Data.SqlTypes;
public partial class Modules_NCG_NCGLinks : System.Web.UI.UserControl
{public void Page_Load(object sender, EventArgs e)
{
//Create the connection and DataAdapter for the Category table.SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["mystringname"].ConnectionString);
SqlDataAdapter cmd1 = new SqlDataAdapter("select * from NCG_Links_Category Where LinkCategory = 'Test'",cnn);
//Create and fill the DataSet.DataSet ds = new DataSet();
cmd1.Fill(ds, "NCG_Links_Category");
//Create a second DataAdapter for the Links table.SqlDataAdapter cmd2 = new SqlDataAdapter("select * from NCG_Links",cnn);
cmd2.Fill(ds,"NCG_Links");
//Create the relation bewtween the Category and Links tables.ds.Relations.Add("myrelation",
ds.Tables["NCG_Links_Category"].Columns["LinkCategory"],ds.Tables["NCG_Links"].Columns["LinkCategory"]);
//Bind the Category table to the parent Repeater control, and call DataBind.parentRepeater.DataSource = ds.Tables["NCG_Links_Category"];
Page.DataBind();
//Close the connection.
cnn.Close();
}private void Page_Init(object sender, EventArgs e)
{
InitializeComponent();
}private void InitializeComponent()
{ this.Load += new System.EventHandler(this.Page_Load);
}
}
I have a parent/child relationship in a relational database broken out like this:
Table Name: categories[category_id] int (primary_key NOT NULL),[category_name] varchar(50),[parent_fk] int
The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this:
select category_id, category, parent_fk from categories where category_id not in ( select parent_fk from categories)
Each bottom-level category has a count attached to it.
The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts).
My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything).
Thanks!
Here is the table - Company with fields:
CompanyID, ParentCompanyID (both integers)
Given a CompanyID - I want to get all the children for the Company.
I did similar procedures but somehow, could not get this to work.
I need to update the price of one row based on another. In this case it takes the price from a sku which has a value in the "ShadowOf" field. Shadow Of is a parent sku. It takes the parent sku price, adds 2% and applies it to the child sku. It's all working well except I'm not sure how to handle it when a parent has more than one child. It updates the first & sets the rest to null.Enclosed is the sample data + query.
DECLARE @tmpTable TABLE(ID NVARCHAR(50), BuyDotComPrice DECIMAL(18,2), ShadowOf NVARCHAR(50), CompanyID INT);
INSERT INTO @tmpTable VALUES
('ALPINE SWR823D', 99.99, '', 344),
('APPLE IPAD2GN16GBWFWRB', 99.99, '', 344),
[code]....
I do not want to rollback child procedure when i am rollbacking parent procedure after execution of child procedure. in child procedure i am committing transaction and in parent rollbacking transaction. Is it possible. Is there any solution to avoid rollbacking child procedure when i rollbacking parent procedure.
View 5 Replies View RelatedI want to find all the child of a node in a tree . A child can have multiple parent i.e 2 can be place under multiple parent . The folling is the data:
ParentID ChildID
-------------------------
10 8
8 6
8 7
7 2
7 5
5 1
5 2
6 2
child of parent if input 10
8
7
6
2
2
5
1
(2) can be neglected
input 7
2
5
1
(2) can be neglected
input 8
7
2
5
(2) can be neglected
Plz help we to write the sql statements
Thanks
I am designing a table to represent data in hierarchy structure, I use id and parent id to represent the data in hierarchy form:
Id | parent_id
---+-----------
1 | 0
2 | 0
3 | 0
4 | 1
5 | 1
6 | 4
7 | 4
8 | 7
9 | 7
This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id.
Is this a good relational database design ? kindly suggest.
Id | parent_id | root_id
---+-----------+---------
1 | 0 |
2 | 0 |
3 | 0 |
4 | 1 | 1
5 | 1 | 1
6 | 4 | 1
7 | 4 | 1
8 | 7 | 1
9 | 7 | 1
10 | 2 | 2
11 | 2 | 2
12 | 10 | 2
13 | 10 | 2
Rgds
Vijay