I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report. However, I only want to select specific data from the dataset to fill textboxes and others. I cannot change the stored procedure, but the sample of the data is shown below:-
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories. In my first table, I tried putting the cells as follows:- (expressions are highlighted in italics and bold)
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value. Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
and it give me an error The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
We have a t-sql statement in a SP that generates on average between 50 €“ 60 rows of data, pretty small! The statement references a View, some tables and temporary # table which has been created in the SP.
Everything works a treat and runs sub second until you put a Insert Into in front of the above statement scenario. The SP then takes a about a minute to run which happens to be about the same amount of time to generate all the data in the View.
I have not attached T-Sql statement at this stage as it runs ok without the Insert Into but would be happy to post it if need be.
I have a case where I need to randomly select two different serial numbers (a list of serial number are already in a table) and assign it to two variables. Or select a specific row from a list of results. Is there a direct way to do this?
e.g. declare @var1 varchar(10), @var2 varchar(10)
select top 1 @var1=Serial from Table order by Serial select top 1 @var2=Serial from Table where ServTag not in (@var1) order by Serial
I've a select SELECT Field1, Field2, Field3 FROM Tablethe problem is that I can have filled Field1 OR Field2 ... I would like to create a new column in the result for see only the filled field if Field1 is = null or = ' ' I want to see Field2 if Field2 is = null or = ' ' I want to see Field1
I have one table , suppose TableA which contains following columns..
I want to select records from this table where time difference is more than 2 min. (tec_insert_date) This means first record time and second one, then second one and third one..and so on..
How to select these records...pls give me some sql query...
I need to check if the latest record in a table for the current client, based on EFFECTIVE_DATE, has a value of 'Y' in the STATUS field. Sample records for client #19:
I wrote the following and it works, but it may be causing 'Insufficient Disk Space' error from the database. Is there any way to write this query without temp table?
How to select specific character when you have a different length of characters. For example I have a columm where I have character ":". How to select only ":"?
I have tried the following out and would appreciate feedback from experienced users regarding if the following is a good/bad approach:
After bring all the data in my Data Mart, I have created a view which has all the data in a big flat table (totally unnormalized). Then based on this BIG FLAT UNNORMALIZED VIEW :) I have created my various dimensions using the 1st option i.e. Star Schema.
Based on the little testing that I have done, I seem to be getting the correct results across various dimensions... However, can someone kindly comment on this approach and the pros/cons.
Hi all,Right now I have this SELECT statement:SELECT MUSIC_PK, MUSIC_TITLE, MUSIC_TITLE2, MUSIC_ORIGINAL_SINGER Now I want to add a second SELECT statement (same as above, but with an additional column) if the record was added within the 10 days (while still select other records, I just need to select an additional column). How would I do that? The additional column that I want to select is called MUSIC_NEW (so I can display if the record is new within 10 days.) I have a column in my table named DATE_ADDED, how do I go to calculate if it's within the 10 days since the day it was added to the current time?Thank you very much,Kenny.
I am working on a multi-page datagrid that pulls data from a database. The issue I am running into is the SQL select query. What I have is a table to 55 items. What I need to do is grab the first 35 of those items and bind the resulting DataReader to the grid, print the page, then grab the remaining 20 items, bind to the grid and print the page.
I can use "SELECT TOP 35 FROM Table" to get the first 35 items, but I don't know how to get the remaining 20 items. Is there a way to say something like "SELECT TOP 20 FROM Table" but specify only the rows that begin after row 35?
I tried doing this with an ArrayList but couldn't bind it to the DataGrid.
Dear GroupI wonder whether you can give me a syntax example for a SQL Statement.Lets assume I've a table containing three columns ContactID (Primary Key),Firstname and Lastname.I would like to write a stored procedure which returns me the first tenrecords and increments an outside variable each time it runs.E.g If I run it the first time I pass the variable as 0 and it will returnme the first ten records and returns the variable value 1.When run a second time, I will pass the variable as 1 and it will return merecords 11-20 and sets the variable to 2 and so on...The difficult thing is how to tell to return me records 11-20. I can't usethe ContactID as someone might have deleted a row and e.g. ContactID 18 ismissing. In this case I only would get 9 rows returned. It always should beten.Thanks very much for your time and efforts!Kind Regards,Martin"There are 10 types of people in this world: Those that understand binaryarithmetic, and those that don't."
Year    Month    Day    RELH    ------   --------    ------   ------- 1973    4       1       50 ...... etc 1973    4       30      20 .....etc 1974    5       1        50 ....etc 1974     5       30       99
I need to get years where average of RELH  of  months within year meetÂ
My clients are not intersted in using Auto generated keys. They are also get data from many sources they would like to use something like customerID. Dose anybody know of any reason why we should not do that? Also they are concern about sql server not being able to handle the datawarehouse in future because they are expecting it to grow in terabites. Dose anyone have advice on that? I was thinking of putting the fact table on a different file group don't if it will help.
DECLARE @strComma VARCHAR(1000) SET @strComma = 'cmpnt_name,ASC,1,2' SELECT CAST(RIGHT(LEFT(@strComma,Number-1), CHARINDEX(',',REVERSE(LEFT(','+@strComma,Number-1)))) AS CHAR(30)) FROM master..spt_values WHERE Type = 'P' AND Number BETWEEN 1 AND LEN(@strComma)+1 AND (SUBSTRING(@strComma,Number,1) = ',' OR SUBSTRING(@strComma,Number,1) = '')
however i getting the data in one field which is like
cmpnt_name ASC 1 2
i would like them to be in their specific column that is 4 different field
Here is tested schemaif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[TestTable]GOCREATE TABLE [dbo].[TestTable] ([SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOinsert into testtable values ('123','pass',null,'fail','skip')insert into testtable values ('456','fail',null,'pass','skip')insert into testtable values ('789',null,'fail','skip','pass')insert into testtable values ('345','pass','pass','pass','fail')I would like to fetch the COLUMNNAME where the value is fail.Basically I need to know which test failed, test1, test2, test3 ortest4?Is this possible?
I am re-engineering the data warehouse and my client is currently using autogenerate keys, their concern is that after a certain amount of keys (can't remember the figure) sql server starts having problems, dose anyone know how i should handle it when i am doing the designing? thanks any input will be appreciated
I'm designing a DW, and i have some doubts relative to the Distributed Transaction when modeling a star schemma.
My problem is: I have a main dtsx package in wich i call all the child packages in order to create the (Fact and Dimension Tables).
(1) First i have several child packages that create and populate all the Dimension Tables (with the latest values from the relational DB).
(2)Then i have several child packages that create all the fact tables, in this process i use the surrogate keys from the dimension tables (obtained in step 1).
The problem here is , " How do i use the multiple transaction ?" , if i put a "required" Transaction Option on the parent package, then after calling the child packages that creates the dimension tables. The values are not commited, so they are not available when i later execute the childs packages related with the fact tables.
How can i use transaction when modelling a star schemma, in order to have a full roll back or a full commit in all tables (Dimensions and Fact Tables).
is there a way to do this in create/alter table without the overhead of a trigger? I want to ensure that one of the 'key' values (even though it may be duplicated) cannot change after it's been inserted.
In MySQL it's simple to insert new columns in specific locations in a table like this
sql Code:
Original - sql Code
alter table foo add column bar integer not null default 0 after baz
ALTER TABLE foo ADD COLUMN bar integer NOT NULL DEFAULT 0 AFTER baz
But attempting to do the same thing in MSSQL fails with a syntax error on the word "after." I can insert columns in MSSQL but only if they are added to the end. I looked at the Microsoft insert documentation here but don't see any way to replicate the above MySQL command.
Is there any way to do this in MSSQL? Or do I have to completely recreate all my tables from scratch?