________
so for those who don't really know what I'm on about and haven't got
access to northwind, I want the second cell to contain the returned
column of a second query (but in text format)
could anyone suggest a way this could be done? am I entering the land
of cursors?
I have a program which feeds back information to a table within my database. The annoying thing is that only 1 of the table cells has all of the information I need so essentially I have a large string of data I need to retrieve values from.I retrieve my cell with a simple select details from alerts where ID = (alert number).The results I have are as follows:
As you can see I have a list (its larger but this is a sample), the only thing I need are the values for each section so for last processed i want the 2911 so I can use it on my dashboard and the last received I want the 10 for that section.i have read about using substrings but am really not sure where to start and how I'd go about getting these values to then use elsewhere?
I have a one to many relationship between Flag and StudentFlag. I'm wanting to select each Flag.FlagID that exist as a column that contains the StudentFlag.Value per StudentFlag.StudentID (or null for that column if that StudentFlag row doesn't exist).
This single column appearing multple times in a single dataset has seemed difficult in a select statement. Is it posible in T-SQL?
Tables, Keys, and Relationships
CREATE TABLE [dbo].[Flag] ( [FlagID] [int] NOT NULL , [FlagName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[StudentFlag] ( [StudentFlagID] [int] NOT NULL , [FlagID] [int] NULL , [StudentID] [int] NOT NULL , [FlagValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Flag] WITH NOCHECK ADD CONSTRAINT [PK_Flag] PRIMARY KEY CLUSTERED ( [FlagID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[StudentFlag] WITH NOCHECK ADD CONSTRAINT [PK_StudentFlag] PRIMARY KEY CLUSTERED ( [StudentFlagID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[StudentFlag] ADD CONSTRAINT [FK_StudentFlag_Flag] FOREIGN KEY ( [FlagID] ) REFERENCES [dbo].[Flag] ( [FlagID] ) GO
I am converting a site from asp to .net and the previus writer did something I never saw.(I am somewhat new to programming all together). He put multiple values in a single cell of the database. I have always learned to use a seperate column for each value. Is this a classic asp thing and there are better ways now? Or is this something I should do myself? The columns type is text and the contents look like this:<Details Expertise="bla bla" Description="We are a three-divisional company ...," WebSite="www.blabla.com" AccountLevel="Free" AccountStatus="Active" WorkHomeZIP="22222" ContractStartDate="01/01/2003" ContractEndDate="12/31/2003"><Address Location="Work" State="Oh" ZIP="22222"/><Email Location="Work" Value=""/><Phone Location="Fax" Code="9801" Ext=""/></Details>
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...so far i created a table 'UserAccount' with column names as UserName as varchar(50)Password as varchar(50)EmailID as varchar(100)DateOfJoining as datetimeUserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..so now every user must have a list of other userid's.. as contact list..Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..THANK YOU !if it helps..iam using sql server express edition..and iam accessing using asp.net/C#
was hoping someone couild provide some insight into a problem I'm trying to solve.
I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:
CRITERIAID CRITERIA CRITERIAVALUE 1 AGE 2 2 SEX 4 3 GRADE 8 4 LOCALE 16 5 REGION 32
A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values.
I have a SQL query that goes like this "select * from Product where ProductID in (1,2,3)" How can i create a stored procedure where a single input parameter can take multiple values? Can anyone help me with this?
I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.
Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.
EXAMPLE: All in one row.
ProgramID | AttributeNames 887 | Studydesign, Control Groups, Primary Outcomes.
I have attached an image of the SQL VIEW that I need to modified so it does this.
THE QUERY:
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID, dbo.tblAttributes.AttributeName FROM dbo.tblProgramAttributes INNER JOIN dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%') ORDER BY dbo.tblProgramAttributes.ProgramID DESC
ALTER PROC [dbo].[sp_GetInvitationStatusTest] ( @invited_by NVARCHAR (50) ) AS BEGIN -- SELECT * FROM dbo.Merck_Acronym_Invitations WHERE invited_by=@invited_by select distinct t1.invited_isid as 'ISID', t1.invited_name as 'NAME',t1.invitation_status as 'STATUS',
[Code] ....
If you look at the where clause i have invited by , i get the desired output if i just provide 1 name in the execution such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj' But my requirement is to make the procedure work with more than one input variable such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj','sujith' now i should get the output for 2 people but if i run this i receive the following error Procedure or function sp_GetInvitationStatusTest has too many arguments specified.
how to make my procedure work with more than 1 input variable?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888
And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
This provides a good example for my situation. In this example, you will see a Movie dimension with four attributes; Genre, Language, Movie, and Theme. I have a similar setup except mine is Top Level Hierarchy>Categories>Values which are all under the one hierarchy.
My Question: I have the dimension setup as a multi-value parameter in one of my reports. When I filter on a value in Genre and in Language, it provides all values from that genre and all values from that language. I really only want the values that include both.
If I filter on Western and English, I get Movie1-5 when all I really want is Movie2 only. Is there any way to have this do an Intersect within the same dimension or do I have to build each one out into its own dimension?
I have a field called "Owners", and it's a child to an "Activities" table.
An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners.
I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height.
Let's say you had a User table and one of the fields was called Deceased. It's a simple closed-ended question, so a bit value could be used to satisfy the field, if the person is dead or alive. Let's say another field is called EyeColor. A person can have only one eye color and thus one answer should be stored in this value, so this is easy as well. Now, let's say I want to store all the languages that a specific user can speak. This isn't as easy as the previous examples since it's not a yes or no or a single-value answer. I haven't had much experience with working with databases so I've come up with two possible ways with my crude knowledge hehe. In terms of inputting the multi-answer values, I suppose I could use a multiple-selection listbox, cascading dropdowns, etc. Now, here are the 2 solutions that came to mind..... 1) Make a field called LanguagesSpoken in the User table. When I process the selections the user makes on the languages he knows, I can then insert into the LanguagesSpoken field a string "English, Spanish, Czech" or IDs corresponding to the languages like "1, 5, 12" (these IDs would be referenced from a separate table I guess). I would use commas so that later on, when I need to display a user's profile and show the user's languages, I can retrieve that long string from the LanguagesSpoken field, and parse the languages with the commas I've used. Using commas would just be a convention I use so I would know how to parse (I could have used "." or "|" or anything else I guess) the data. 2) Forget about the LanguagesSpoken field in the User table altogether, and just make a LanguagesSpoken table. A simple implementation would have 3 fields (primary key, userId, languageId). A row would associate a user with a language. So I would issue a query like "SELECT * FROM LanguagesSpoken WHERE userId=5" (where userId=5 is some user). Using this method would free me from having to store a string with delimited values into the User table and then to parse data when I need them. However, I'm not sure how efficient this method would be if the LanguagesSpoken table grows really large since the userIds would NOT be contiguous, the search might take a long time. I guess I would index the userId field in the LanguagesSpoken table for quicker access? OR, I may be going about this the wrong way and I'm way out on left field with these 2 solutions. Is there a better way other than those 2 methods? I haven't work extensively with databases and I'm just familiar with the basics. I'm just trying to find out the best-practice implementation for this type of situation. I'm sure in the real world, situations like this is very common and I wonder how the professionals code this. Thanks in advance.
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
HiI want to achieve the following transformation of data using a storedprocedure.Sourcecol1 col2(varchar)-------------------------1 1.11 1.22 2.12 2.22 2.3=================Desired Resultcol1 col2(varchar)--------------------------1 1.1 | 1.22 2.1 | 2.2 | 2.3=====================Thanks in advance. :)- Parth
I'm retreiving data from a SAP BW data source. In the dataset designer I can build a query that returns a single value, the data returns successfully in the designer. When I switch to the layout mode I can place the field of the datset in table / matrix / textbox. However when i switch to preview mode or deploy the report and try to see them in Report Manager the report fails to render with the following message:
Query Execution Failed for dataset 'XXX'
These columns don't currently have unique values.
Its a single cell datset?!?!
I can successfully retreive data from other SAP Infocubes and queries. I also cannot replicate the error using a SQL Server datasource. The stack trace error looks like this:
w3wp!processing!6!5/9/2007-16:57:41:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. ---> System.ArgumentException: These columns don't currently have unique values. at System.Data.ConstraintCollection.AddUniqueConstraint(UniqueConstraint constraint) at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign) at System.Data.DataTable.set_PrimaryKey(DataColumn[] value) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.CreatePrimaryKeys(DataTable table, InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Microsoft.ReportingServices.XmlaClient.IMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.ObjectMetadataCache.Microsoft.ReportingServices.XmlaClient.IObjectCache.Populate() at Microsoft.ReportingServices.XmlaClient.CacheBasedFilteredCollection.PopulateCollection() at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollectionInternal.Find(String index) at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollection.Find(String index) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.FindOptionalPropertyName(String internalColumnName, CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.GenerateColumnInformation(CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader..ctor(AdomdDataReader dataReader, CubeDef cube) at Microsoft.ReportingServices.DataExtensions.SapBw.SapBwCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery()
Any ideas why this happening and how I can get around it?
Thanks
Sanjay
ps Why would you want to put a unique constraint on a read only single table dataset?
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |... Orders -> id | idCustomer | date | ... Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total FROM Products INNER JOIN Orders ON Orders.id = Products.idOrder INNER JOIN Customers ON Customers.id = Orders.idCustomer WHERE year(date)=2014 GROUP BY customers.name ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015) -------- customer A | 1000$ | 2000$ | 3000$ customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
Query Declare @num1 char(5) Set @num1 = (SELECT ltrim(Substring(ltrim(number2), 8, CHARINDEX(' ', ltrim(number2)))) FROM Num2played) SELECT @num1 FROM Num2played
Error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any suggestions or fix?
Thank you. ========================================= The data above is in one colum. I would like to rearange the numbers in order. e.g. from "10 13 15 3 26" to "3 10 13 15 26".
Hi All,If have a sql table with 2 columns and 2 rows with values[["col1row1","col2row1"],["col1row2","col2row2"]].Using t-SQL with "for xml"How can i create a xml where the cell values (not column names) appearas elements?eg:<col1row1>col2row1</col1row1><col1row2>col2row2</col1row2>Thanks,slyi
What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?
Is it possible to stack two images on top of each other in a table ?
Let me clarify where i'm going to. I have a report where in one table i would like to display (in a graphical way) how much a sales person has sold towards the whole sales amount. To do so i would like to set my background of my cell to yellow and then use an image (just a green rectangle) where i adjust the padding according to the percentage. When i set my background to yellow and i insert my image in the cell, the textbox cell properties disapear and are replaced with the image properties. So also my background that was defined in my textbox is gone. Now i was thinking if i could set two images on top of each other (a yellow rectangle and a green rectangle). But when i do that, one image is replaced with another.
Am i doing something wrong or is there another way to achieve what i want to do?
I have a power pivot with 2 multi valued report filters student_branch & blood_group. These report filters are used to fetch the data set that contain below result set
student_branch blood_group count
Everything works fine. But, what i am looking for , is there any way to show the what are all the report filters that are selected currently by , separated in a separate cell ? below is the image for output reference.
My team is using TFS for work item management and I'd like to provide a week schedule matrix that displays: - weekdays on the x-axis - persons on the y-axis - and the assigned workitems in the cell.
This works fine if there's only one WIT (work item) per day but some WIT's are shorter and thus the resource is assigned more than one a day. My dataset may return something like this:
Date Person WIT 28/3 Mark Task1 28/3 Mark Task2 28/3 Hank Task3 29/3 Mark Task2 29/3 Hank Task3
In this case I'd like the matrix to look something like this, i.e. the two tasks Task1 and Task2 in the same cell: Mark Hank 28/3 Task1 Task3 Task2 29/3 Task2 Task3
I've set up the matrix in the report but I only get the first item per day to show. Mark Hank 28/3 Task1 Task3 29/3 Task2 Task3
The default definition in the cell was "=First(Field!TaskName.Value)", so I figured I'd solved the issue by changing it to "=Field!TaskName.Value", but it didn't.
I am trying to create an SSRS report to display a single row of Household information (from CRM Dynamics) Household being the Parent Entity, with 1:N relationship with Contact Entity.
How would I go about showing all the Firstnames (from Contact) in the same cell like the below?
