Hi,
I have to choose a particular column from a table whenever a condition met. That means i have to use the column in the select query whenever the condition met otherwise i have to create a dummy column with the same column name.
I tried to use the following sample query.
Eg:
Create table temp
(test_id int,
test varchar(100))
Declare @var varchar(100)
Set @Var='Hi'
Select
case when @Var='HI' then "None" else Test_new End as Test
from Temp
But it throw error as "Invalid column 'Test_new' "
As per my situation the Test_new column will be available in temp table whenever the condition is not met.So when @var<>'HI' i have to get the details of the test_new column otherwise i have to create a dummy column with the same name.
Hey, I have a search form with a selectbox. This selectbox contains the columnnames.I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session. If you see I have put in the querystring as columnname @sescolumn which I have initialised as asp:sessionparameter.But it gives no results. When I put @sescolumn between [] like normal columnnames are it doesn't work also. Can someon put my on the right path?
<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @sescolumn like @seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @sesdatefrom and [Eindeverhuur] <= @sesdatetill)" > <SelectParameters> <asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" /> <asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" /> <asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" /> <asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" /> </SelectParameters> </asp:SqlDataSource>
Here is the issue I'm having. I am writing a stored procedure that takes a couple of parameters. Each one is the value within a specific column in one table (i.e., @part = 'o-ring' or @sub_assembly = 'hydraulic ram'). Needless to say, the columns form a hierarchy. What I am trying to achieve is to allow the user to specify one of the parameters and get a count for all records where the specified value is in the corresponding column. So, if the user puts in the parameter @part = 'o-ring', I want it to know that the where clause for the select statement should look for o-ring in the part column and not the sub_assembly column. Here is what I am trying to do, which isn't working.
IF(@sub_assembly = NULL) BEGIN IF(@part = NULL) BEGIN PRINT 'This is an error. You must have at least a part' END ELSE BEGIN SET @querycolumn = 'Part' SET @queryvalue = @part END END ELSE BEGIN SET @querycolumn = 'SubAssembly' SET @queryvalue = @sub_assembly END
SELECT SubAssembly, Part, COUNT(RecordID) FROM Table WHERE @querycolumn = @queryvalue GROUP BY SubAssembly, Part ORDER BY SubAssembly, Part
The problem is that I'm getting an error when I try to use @querycolumn to supply the column name to the WHERE clause. Any ideas or suggestions?
I'm working on a script to convert data from one software packageto another. Greatly simplified, it looks something likecreate procedure import_widget asbegininsert into our_widget (foo, bar)select baz, quux from their_db.dbo.their_widgetendgoThe problem is that the name of the source database varies fromone system to another, so I want to pass the database name as aparameter. I think I could do the following, but is there abetter way to go about it?create procedure import_widget (@db_name sysname) asbeginexec 'create view their_widget as select * from '+ @db_name + '.dbo.their_widget'insert into our_widget (foo, bar)select baz, quux from their_widgetdrop view their_widgetendgo
Hi, I need to write a report which will run based on the user's input for Server Group. Meaning, if the user wants the report to be run for one say, UAT group, the report (and therefore the stored procedure) should be executed against UAT server. Or, if the user wants it to be run on the Production machine, the stored procedure should be executed on the Production Server. Assume that both UAT and Production servers are in the same domain to rule out any authentication issue.
I think one way of doing it is to create a linked server on one server which we use to connect via the Report Manager. So, whatever Server Group is selected based on that the SP will be executed on that respective Server Group's Server, since in the query we will have given full names of the tables that we use.
But, I strongly suspect, that having linked servers will not be accepted since it requires that the server owners maintain that. So, I was thinking if there is any other way of achieving this. If anyone knows, please help me with this.
I've got a cube in SSAS 2205 with some financial measures that I want to appear conditionally in an MDX query. Here's what I mean...
The cube measures I'm interested in are actual revenue, forecast revenue and budget revenue. The dimension I'm reporting across is the [Time].[month] member. The report I'm trying to create with this query has each month across the top. But the headers show actual revenue if the current reporting month is on or before the month in question. For future months, we show the forcast revenue if there is any. For any other future revenue, we show the budget measures. So a sample report for April would have columns like...
Code SnippetJanuary February March April May June July August September Actual Actual Actual Actual Forecast Forecast Forecast Budget Budget
I need to conditionally bring back the right measure and have the measure name (e.g., column header) indicate if it's an actual, forecast or budget value.
I Hope someone can help me out with this problem.. its pretty NB.
I have a pair of multivalued parameters. So, parent and child... the child needs to get its selection based on the selection made by the user on the parent parameter ( the child parameter is hidden).
Fictional values: Parent Parameter: SelectAll, ALL , 1 , 2 Child Parameter: Home, Work, Play
If user selects "ALL" from the parent parameter I need Home and Play selected.
My idea was this: =iif(Parameters!Parent.Value(1) = "All","Home,Play") <-- in Available values.
However, the dataset that reads this value does not like the coma delimited string. This dataset is a cube created dataset, reading the child parameter.
Any help is greatly appreciated. Kind Regards, Neil
I am writing a package where the user uploads a flat file to a web folder. I need to automate this package to run everytime it sees a new file.
How can I implement this?
Can I make a call to a package or a sql server job to run from .net 2.0?
Do I need to use a service broker to look for a new file and run the package or a stored proc....I am looking for an async process where user doesnt have to wait for the package to run as it involves data validation of flat file and its huge...
Does anyone know if there is a way to dynamically set the chart type (i.e. pie, line, column, etc.) for your report in SSRS? I want the customer to be able to chose which type of chart they'd like to see.
I've looked all over the Internet and can't find anything.
Any help would be appreciated. Alan
Clarification: I guess I should clarify a little. I am using an ASP.NET 2.0 page written in C# and using a ReportViewer control.
More Clarification: The ReportViewer displays an rdlc.
suppose i have a table penalties with ticket_number, ticket_start_date datetime, ticket_end_date datetime
There are many records of same ticket_number.example: ticket_number ticket_start_date ticket_end_date 75 2/1/03 3/2/03 75 2/4/03 5/4/03
34 2/1/03 3/3/04 34 3/4/04 3/3/05
I want to write a query which will: 1. select the ticket_number which is not active as of today in case of ticket_number 75 it is closed so I want to print it in report
in case of 34 it was closed but now opened, so i don't want in my report.
set @myCursor = CURSOR FAST_FORWARD for select distinct ticket_number, ticket_start_date, ticket_end_date from penalties where ticket_end_date <= @Today
open @myCursor fetch next from @myCursor into @colA, @colB, @colC
while @@fetch_status=0 begin print @colA print @colB print @colC fetch next from @myCursor into @colA, @colB, @colC end
close @myCursor deallocate @myCursor
GO
-- ============================================= -- example to execute the store procedure -- ============================================= EXECUTE simpleTest GO -------------
however this gives closed tickets but multiple records. I want only the last record details of the ticket_number. I tried last(ticket_number) in my query, but it doesn't affect. Please suggest! thanks. I am using MS SQL server.
I have one table with 6 columns (Entrance1,Exit1,Entrance2,Exit2,Entrance3,Exit3). This columns can take null values. I want to select just the last one of this columns that is not null.
I got your email address from your web cast. I really enjoyed the web cast and found it to be very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question regarding the product. I have looked for the information on the web, but was not able to find relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets Are generated using reporting services. On examining the excel sheet, I found out that the name Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc. And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers. http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL) Declare @Source table (Name nvarchar(200) NOT NULL) Declare @Users table (Name nvarchar(200) NOT NULL) Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount begin Insert into @NumericBase(Number)Values(@Counter) set @Counter = @Counter + 1 end
Insert into @Type(Name) Select 'Type: Buick' UNION ALL Select 'Type: Cadillac' UNION ALL Select 'Type: Chevrolet' UNION ALL Select 'Type: GMC'
Insert into @Source(Name) Select 'Source: Japan' UNION ALL Select 'Source: China' UNION ALL Select 'Source: Spain' UNION ALL Select 'Source: India' UNION ALL Select 'Source: USA'
Insert into @Users(Name) Select 'keith' UNION ALL Select 'kevin' UNION ALL Select 'chris' UNION ALL Select 'chad' UNION ALL Select 'brian'
select 1 ProviderId, -- static value '' Identifier, '' ClassificationCode, (select TOP 1 Name from @Source order by newid()) Source, (select TOP 1 Name from @Type order by newid()) Type
Hi there, I am a new member of this site and I am not very much aware of T-sql's working. My question is what if I need to get one column's data to be the heading of another column. To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this StuID - - - SubID - - - -Tot - - -Obt 1 - - - - - - -1 - - - - - - -50 - - - 38 1 - - - - - - -2 - - - - - - -50 - - - 41 1 - - - - - - -3 - - - - - - -50 - - - 42 1 - - - - - - -4 - - - - - - -50 - - - 40 2 - - - - - - -1 - - - - - - -50 - - - 35 2 - - - - - - -2 - - - - - - -50 - - - 40 2 - - - - - - -3 - - - - - - -50 - - - 42 2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4 1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40 2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted. Thanks.
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
Hi All I want to rename the column name by using the dynamic value as like declare @name as varchar(12) set @name='ss' sp_rename 'dbo.employees.id3',@name, 'COLUMN'; go but it shows the error Incorrect syntax near 'sp_rename'. any idea?
Hi is possible to create dynamic column name example Declare @StartDate as dateTime Select @StartDate = '2004-06-05'
select SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)], SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)] from table1
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100) set @aw = '1990,1991' SELECT [name], [year], [data] FROM (SELECT * FROM t1 where [name] != 'name') p UNPIVOT ([data] FOR [year] IN (@aw) )AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
Hi everyone, So I have to export from a SQL 2005 table to a dBaseIV table using SSIS. Easy enough, however the catch is that the tables being exported will vary. I can send variables to the the package calling the sql table name and creating the DBF file with the same columns on the fly.
The problem is mapping the columns. From what I've been reading there is no way to alter the mapping in a package at runtime. I was hoping that there would be some sort of auto mapping setting that would match on the field names but I guess not. Anybody run in to this issue and have a work around? Thanks in advance....
Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.
I am trying to achieve something a bit complex (or at least appears to be for me).
I have the following table structure:
UID, GroupID, ColumnName, ColumnValue
here is some example data:
UID, GroupId, ColumnName, ColumnValue 1, 1, MAC Address, 8a7sd87sad 2, 1, IP Address, 192.168.1.100 3, 1, Name, John 4, 2, MAC Address, 09a8sd098as 5, 2, Name, Steven
and here is what I would like the example to come out like:
GroupID, MAC Address, IP Address, Name 1, 8a7sd87sad, 192.168.1.100, John 2, 09a8sd098as, NULL, Steven
It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...
I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:
Code Snippet
DECLARE @ColumnName varchar(128) DECLARE @Sql varchar(255) SELECT @ColumnName = [ColumnName] FROM tblSourceData SELECT @Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@ColumnName+'] FROM tblSourceData' EXEC (@Sql)
Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?
Hi All, We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100) set @aw = '1990,1991' SELECT [name], [year], [data] FROM (SELECT * FROM t1 where [name] != 'name') p UNPIVOT ([data] FOR [year] IN (@aw) )AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
How can I make this stored procedure concept work:
----------------------------------------------- Create Procedure MyProc @MyValue varchar(5) As Declare @ColumnName as varchar(11) Set @ColumnName = 'Price_' + @MyValue Select Sum(Price) As @ColumnName --???? From MyTable where Item = @MyValue Return -----------------------------------------------
Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names.
Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to.
I want to write a query that will give me a dynamic amount of columns back.What I want to do, I want to create a calendar application, in which foreach employee, I want to show if he is in the office or not.this should look like:ID, Name, 1,2,3,4,5,6,7,888,Leo,0,0,1,1,1,0,0,1The amount of columns is dynamic, and is a period of time, with a column foreach day.Any suggestions what the best approach to this could be?ThanksLeo
Ill start off by saying that I am an ASP.NET developer and I am self-taught in using SSRS2005. (quite poorly I might add) That being said I am having a problem with a report im building. This report has 10-12 different metrics I am reporting on and I have each of them displayed in their own matrix. The first matrix was created by the report wizard that launches when you add a new report.
To give you an overview of how I am setting up these reports I will describe the structure I am using on nearly all of the matrices. I am reporting on classes that are managed by my company and held at 3rd party locations. My matrix has the 3rd party location in the rows and the quarter the class was held is the column. Matrix setup looks like this:
The problem Im having is this. All matrices that weren't created by the wizard don't grow dynamically. Say I have 2 quarters of data I am reporting on, Instead of having a column for quarter1 and quarter 2, the report generates separate matrices for each quarter. This is true for each matrix that I created using a matrix control from the toolbox. The matrix that was created by the wizard however, has dynamic columns that grow to the right as expected.
I am populating my datasets using Stored Procedures and the matrix that behaves normally uses a different dataset than the 4 others I have completed thus far which share a dataset.
I have looked everywhere I can think of for a checkbox or a property I need to adjust and I cant find one. Anyone have any ideas??
in the script task component - i need to refer to Row.Column9.ToString() but the Column9 is coming from a variable. meaning - at run time it can be Column9 or Column7 at other runs. how can i write Row.ColumnName.ToString() in a dynamic way? (aka that the ColumnName is dynamic and not hard coded?)
Need to be able to change the color of the columns red for negative values and Green for positive-- Im thinking that this will have to be a custom code scenario and not a expression-- any suggestions??
