How To Query A Number (street Number)...
I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.
View Complete Forum Thread with Replies
Related Forum Messages:
Limitations In Term Of Number Of Tasks And Number Of Columns
Hi, I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables. To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow. I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks. Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB. Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ? If not, then do you have any idea why it's so slow ? Thanks in advance for any answer.
View Replies !
The Number Of Requests For &&"XXXServerXXXUser&&" Has Exceeded The Maximum Number Allowed For A Single User
I have created a local user on Report Server Computer and the user has the administrative rights. When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ). I get the following error. Reporting Services Error -------------------------------------------------------------------------------- The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user. -------------------------------------------------------------------------------- SQL Server Reporting Services Then i try to login using a different user with administrative rights on the machine, i can logon successfully. The system is up for a month but this problem occured today?!? What could be the problem?!?
View Replies !
Maximum Number Of Tables - Is 4^15 Big A Number?
Hello people,I might sound a little bit crazy, but is there any possibility that youcan incorporate 4^15 (1,073,741,824) tables into a SQL Database?I mean, is it possible at all? There might be a question of whereanyone would want so many tables, but i'm a bioinformatics guy and I'mtrying to deal with genomic sequences and was coming up with a newalgorithm, where the only limit is the number of tables I can put intoa Database.So, can you please advise if its possible to put in so many tables intoa SQL database? Or is the Bekerley DB better?
View Replies !
Field LIKE Number,number,number
I am testing something in Visual Basic that talks to a database and I want to filter results by -> field1 like "###". However, that 'like' and '#' is VB syntax. How do you say that in SQL?
View Replies !
Row Number In A Query
Could anyone help me insert in the first column a row number of this query: (notice that it has a UNION) SELECTB.DOCDATE, B.DOCNUMBR, A.USERDEF2, (CASE WHEN B.VENDORID = '41221' OR B.VENDORID = '49697' THEN B.TRXDSCRN ELSE A.VENDNAME END) AS PROVEEDOR, 0 AS INT_EXEN, 0 AS IMP_EXEN, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) AS INTERNAS, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) AS IMPORTACIONES, (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) AS IVA, ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS RETENCION, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) + (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) + (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) + ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS TOTAL FROMPM20000 B INNER JOIN PM00200 A ON B.VENDORID = A.VENDORID WHEREB.TAXSCHID >= 'PLAN IVA' AND B.TAXSCHID <= 'PLAN TRANSP.CON' AND B.VOIDED = 0 AND B.TAXAMNT <> 0 AND UNION ALL SELECT B.DOCDATE, B.DOCNUMBR, A.USERDEF2, (CASE WHEN B.VENDORID = '41221' OR B.VENDORID = '49697' THEN B.TRXDSCRN ELSE A.VENDNAME END) AS PROVEEDOR, 0 AS INT_EXEN, 0 AS IMP_EXEN, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) AS INTERNAS, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) AS IMPORTACIONES, (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) AS IVA, ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS RETENCION, (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) + (CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) + (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) + ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS TOTAL FROMPM00200 A INNER JOIN PM30200 B ON A.VENDORID = B.VENDORID WHEREB.DOCTYPE <> 6 AND B.TAXSCHID >= 'PLAN IVA' AND B.TAXSCHID <= 'PLAN TRANSP.CON' AND B.VOIDED = 0 AND B.TAXAMNT <> 0 AND ORDER BY DOCDATE
View Replies !
Number Of Entry Query.
MSSQL2K SP4 Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs. --drop table #foo create table #foo (empID int, transDate datetime, transType varchar(10)) insert into #foo values(1, '01/01/06 01:01:01','Insert') insert into #foo values(1, '01/01/06 01:01:02','Update') insert into #foo values(1, '01/01/06 01:01:03','Delete') insert into #foo values(2, '01/01/06 01:01:01','Insert') insert into #foo values(2, '01/01/06 01:01:02','Update') select f.empID, Change = (select count(transDate) from #foo f2 where f2.empID = f.empID group by empID), f.transDate, f.transType from #foo f Current results: 132006-01-01 01:01:01.000Insert 132006-01-01 01:01:02.000Update 132006-01-01 01:01:03.000Delete 222006-01-01 01:01:01.000Insert 222006-01-01 01:01:02.000Update Desired results: 112006-01-01 01:01:01.000Insert 122006-01-01 01:01:02.000Update 132006-01-01 01:01:03.000Delete 212006-01-01 01:01:01.000Insert 222006-01-01 01:01:02.000Update TIA, CFR
View Replies !
Query Using The Record Number
happy friday... my table has 200,000 + records and I want to see the particular record which I think it is causing the problem. How could i query 195,203rd record? thank you, yanoroo
View Replies !
How To Get The Second Biggest Number Using Sql Query?
Hi, Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable. For example, I have the following records: NumColumn 1 2 3 4 10 11 18 If I use "select Max(NumColumn) from aTable", I will get the number: 18. But I need to use SQL to the second biggest number: 11. How to do that? Thanks.
View Replies !
Getting The Number Pf Rows In A Query Result
My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section: "In the forums there is X results for the word X In the articles there is X results...." And when the user click one of those lines, the list shows the specific results in that section. My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows… Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?) Great thanks
View Replies !
Query To Find A Missing Number
Hello,I need to write a query to find out a set of missing number in a givensequence.Eg : a Column in some table has the following dataCol11234568910Here I need to write a query to find out that number 7 is missing in thegiven sequence.One possible solution is by using any loop. But I am looking out if the samecan be achieved using any query.Thanks in advance.Regards,Mahesh
View Replies !
Restrict The Number Of Records From Query
Hi, ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query i want to implement a query system which will give me the first 50 records, next 50 ,... and so on can ne body help ?
View Replies !
Phone Number Lookup Query!!
i am trying to write a query for phone number lookup . The query should be able to search numbers which have anything matching .... like if the person enters 1918767899 or enters 918767899 the query should be able to find both the records. Itried using the LIKE , but it doesn't work the way it is required. Pls help !! Regards
View Replies !
Varying Number Of Inputs In A SQL Query?
Hi All, Can someone please help me find a way to run a query to create varying multiple records integers? ( a comma deliminated list/array any ideas??) an example would be great! I have a dictionary of id that need to inserted in to a joining table. all help welcome p
View Replies !
Breaking Down A Number Column From A Query?
I’m okay with simple queries but as I’m no expert and have failed to find perhaps the correct wording to describe this method, if at all possible to do, so I have come to ask here. What I would like to do is take a column from a query and then break down that column into separate results. So the full query results: 36,18/09/2007 10:00:00,NULL,000102000304,NULL The column I would like to brake down is (Unique Reference Number): 000102000304 And I would like to break it down to get the last 2 parts (0003 and 04): 0001 | 02 | 0003 | 04 Is this possible to do? If so where should I be looking or what should I be looking at? Many Thanks
View Replies !
Changing The Number Format In SQL Query
Hi I have query SELECT col1, a.Inv_Amount as Amount FROM SPS_Oustandings a I can get the result values for example as 12345.67 as result, but i need it in german format with , used as decimal point and . as thousand seperator i need output as 12.345,67 My DataGrid (ASP.NET) is directly bound to SP which have this query, i want to show amount in above format in grid. Please Help, Thanks
View Replies !
Query To See If An Int Field Starts With A Certain Number
How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!
View Replies !
Getting Order Number Into Query Grouped By Something
I have a table of transaction that includes student ids and dates. I need to select all records from the table and include a new value that is the sequential transaction numbered for each student with the oldest transaction for each student being numbered one, the next oldest numbered two and so on. So the result should look like student1, 10/1/2000, 1, student1, 10/15/2000, 2, student1, 2/12/2001, 3, student2, 9/1/1999, 1, student2 10/2/2000, 2, student2 , 12/15/2000, 3, student2, 11/4/2001, 4 and so on.
View Replies !
Help Designing Query For Any Number Of 5 Parameters
Hello everyone, I'm having trouble designing a query for an ASP.NET project I'm working on. The main portion of the query is done and works, but I need to modify it to accept 5 parameters. Below is the main query: Code Block @LocID as nvarchar(10) SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.* FROM dbo.ProfileNames LEFT JOIN dbo.AircraftGateInput ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate WHERE dbo.ProfileNames.Type = 'Gate' AND dbo.ProfileNames.Location = @LocID The changes I need to make to this are confusing me because I've had to do something like this before. There are 5 DropDownLists on the page the user can select as parameters for searching/filtering the information. The query above is what's being used now, and causes the page to dynamically generate a certain number of colums based on the number of returned columns. The parameters that are available are: Gate, Location, Offload Zone, Onload Zone and Equipment. I wwant the user to be able to select all or any number of these as search options but I'm not really sure how to design the query to do this. A co-worker suggested doing something like this: Code Block @LocID as nvarchar(10), @Gate as nvarchar(50), @Location as nvarchar(50), @OffLZ as nvarchar(50), @OnLZ as nvarchar(50) SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.* FROM dbo.ProfileNames LEFT JOIN dbo.AircraftGateInput ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate WHERE (dbo.ProfileNames.Type = 'Gate') AND (@Gate IS NULL OR dbo.ProfileNames.Name = @Gate) AND (dbo.ProfileNames.Location = @LocID) AND (@Location IS NULL OR dbo.AircraftGateInput.Location = @Location) AND (@OffLZ IS NULL OR dbo.AircraftGateInput.OffLZ = @OffLZ) AND (@OnLZ IS NULL OR dbo.AircraftGateInput.OnLZ = @OnLZ) At this point I'm not a 100% sure on how to test this query in the query designer in Management Studio 2005 (this is also a .NET 2.0 project). If anyone can help, I'd really appreciate it, thanks.
View Replies !
Insert Query And Number Of Fields
I've found out how to to the Insert into my table (col1, col2) Select (col1, col2...) from othertable where regId= @regId in my earlier question but do i have to name every column as i have about 80 in my table. Can't I use an asterisk or something....
View Replies !
Returning Number Of Records Found In Query
I am trying to return the number of records found by the query but keep seeing -1 in label1. This query should return many records. sub findcustomers(sender as object,e as eventargs) dim connection1 as sqlconnection=new sqlconnection(...) dim q1 as string="select * from tblcustomers where store='65'" dim command1 as sqlcommand=new sqlcommand(q1,connection1) dim a as integer command1.connection.open() a=command1.executenonquery() label1.text=a.tostring() command1.connection.close() end sub What am I doing wrong?
View Replies !
Creating A Sequential Row Number In A Query Resultset --- Is It Possible?
Hello everyone, I have a stored procedure that supplies rows for a front-end DataGrid that allows custom paging. The stored procedure must return the requested "page" of rows as identified by a sproc argument. Currently, I'm loading the the query's result set into a temporary table that has an identity column as primary key. I then run a second query against the temp table that uses the identity column value to strip out the requested "page" of rows and return them to the front-end DataGrid. I'd like to eliminate the temporary table. To do so I would need to create the equivalent of an identity column in the query's sorted results and reference this value in the WHERE clause in order to return only the requested rows. Does anyone know of a way to generate a sequential number (starting at 1) within a query (and after the rows have been sorted by the ORDER BY)? I don't think this can be done but I put it out for those who may know better. Thanks for your help. If you know this is impossible, that would be helpful feedback as well. BlackCatBone
View Replies !
Maximum Number Of Databases Used For Each Query In 2005?
In MS Sql Server 2000, if you run a stored procedure that query's morethan 8 databases, you get this error gets raised:Maximum number of databases used for each query has been exceeded. Themaximum allowed is 8.In 2005, does this maximum number still exist? if so, is it still 8, orhas the number been increased?Thanks,Mike
View Replies !
How To Reduce Number Of Query Execution Plans...
Hi everyone, I've executed this script to navigate through a set of records based on the min value of recordid, and perform individual column updates on a user table which is only about 550 rows in size but I'd still like to know how I can reduce the number of execution plans to just one for the query analyzer? So far, I get (1 row(s) affected) for every row that is updated and takes over 2 1/2 minutes to update this small table. The slowdown may be partially caused by the excessive paging that's going on my machine (we're trying to work that issue out). Can anyone make a suggestion on optimizing this query? thanks, Irene DECLARE @lvID int, @lvFullName VARCHAR(50) SET @lvId = (SELECT MIN(rowid) FROM irene) SELECT @LVID --SET @lvFullName = (SELECT fullname FROM irene WHERE rowid = @lvId) WHILE @lvId IS NOT NULL BEGIN UPDATE irene Set FirstName = Ltrim(Substring (@lvFullName, charindex(',', @lvFullName)+ 2, Len(@lvFullName))), LastName = Ltrim(Substring (@lvFullName, 1, charindex(',',@lvFullName)-1)) Where rowid = @lvId SET @lvId = (SELECT MIN(rowid) FROM irene WHERE rowid > @lvId) SET @lvFullName = (SELECT FullName FROM Irene WHERE rowid = @lvId) END
View Replies !
Serial Number In Query, In MS Access And SQL Server
im trying to run a query, my first column should be serial number based on results, how can i do that for example qry = "Select products, sum(prod_price), sum(prod_sellingprice) from sales group by products" or qry = "Select products, sellingperson from sales where seldate=#" & date() & "#" i want first column of both queries as serial number based on results of query.
View Replies !
Query With Row Number,Union And Dynamic Orderby.
Hi, Can some one help me to complete this query. My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time. Existing procedure: ALTER PROCEDURE [dbo].[spGetAllItems] @condition varchar(7500), @ModelId int =0, @CategoryId int=0, @SourcingType int=0, @StartRow as int = 1, @StopRow as int = 100000 AS declare @erow int declare @Cond varchar(255) select @Cond='' if @CategoryId<>0 select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar) if @SourcingType<>0 select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar) if @StopRow=-1 select @StopRow=60 set @erow=@StartRow + @StopRow exec('Declare @t_table table ( [SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL, [id] [int], [category] [int] NULL , [model] [int] NULL , [mtype] [varchar] (50) NULL, [itemclass] [varchar] (50) NULL, [itemcode] [nvarchar] (25) NULL , [ItemCondition] [tinyint] NULL , [SourcingType] int, [Title] [nvarchar] (50) NULL , [Description] [nvarchar] (4000) NULL , [InternalUse] [nvarchar] (4000) NULL, [CompatibleModels] [varchar] (8000) NULL , [Quantity] [int] NULL , [MinOrder] [int] NULL , [Warranty] [tinyint] NULL , [Price1] [decimal](6, 2) NULL , [Price2] [decimal](6, 2) NULL , [Price3] [decimal](6, 2) NULL , [Price4] [decimal](6, 2) NULL , [LotSale] [bit], [PricingRule] int , [Weight] [decimal](7, 2) NULL , [PackageSize] [int] NULL , [promo] [int] NULL , [SmallImage] [nvarchar] (255) NULL , [BigImage] [nvarchar] (255) NULL , [StartDate] [datetime] NULL , [EndDate] [datetime] NULL , [Info1] [nvarchar] (50) NULL , [Info2] [nvarchar] (50) NULL , [Info3] [nvarchar] (50) NULL , [Info4] [nvarchar] (50) NULL , [Info5] [nvarchar] (50) NULL , [Info6] [nvarchar] (50) NULL , [Info7] [nvarchar] (50) NULL , [Info8] [nvarchar] (50) NULL , [Info9] [nvarchar] (50) NULL , [Info10] [nvarchar] (50) NULL , [Info11] [nvarchar] (50) NULL , [Info12] [nvarchar] (50) NULL , [Info13] [nvarchar] (50) NULL , [Info14] [nvarchar] (50) NULL , [Info15] [nvarchar] (50) NULL , [Info16] [nvarchar] (50) NULL , [Info17] [nvarchar] (50) NULL , [Info18] [nvarchar] (50) NULL , [Info19] [nvarchar] (50) NULL , [Info20] [nvarchar] (50) NULL , [Info21] [nvarchar] (50) NULL , [Info22] [nvarchar] (50) NULL , [Info23] [nvarchar] (50) NULL , [Info24] [nvarchar] (50) NULL , [Info25] [nvarchar] (50) NULL , [Status] [tinyint] NULL , [AllowBuy] [char] (1) NULL , [PageName] [varchar] (200) NULL , [Locality] [int] NULL , [Location] [int] NULL , [CreatedBy] [int] NULL , [CreatedOn] [datetime] NULL , [UpdatedBy] [int] NULL , [UpdatedOn] [datetime] NULL, [BrandId] int, [CategoryId] int, [ModelId] int, [ModelName] varchar(255), [BrandName] varchar(255), [CategoryName] varchar(255) ); DECLARE @EndRow int DECLARE @reccount int select @reccount=count(*) from (SELECT tbItemMaster.id FROM tbItemMaster INNER JOIN tbModel ON tbItemMaster.model = tbModel.id INNER JOIN tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Union SELECT tbItemMaster.id FROM tbItemMaster INNER JOIN tbModel ON tbItemMaster.model = tbModel.id INNER JOIN tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl set @EndRow= + ' + @StartRow + '+' + @StopRow + ' --Set RowCount @EndRow; insert @t_table ( [id] , [category] , [model] , [mtype], [itemclass], [itemcode] , [ItemCondition] , [SourcingType], [Title] , [Description] , [InternalUse], [CompatibleModels], [Quantity] , [MinOrder] , [Warranty] , [Price1] , [Price2] , [Price3] , [Price4] , [LotSale] , [PricingRule], [Weight] , [PackageSize] , [promo] , [SmallImage] , [BigImage] , [StartDate] , [EndDate] , [Info1] , [Info2] , [Info3] , [Info4] , [Info5] , [Info6] , [Info7] , [Info8] , [Info9] , [Info10] , [Info11] , [Info12] , [Info13] , [Info14] , [Info15] , [Info16] , [Info17] , [Info18] , [Info19] , [Info20] , [Info21] , [Info22] , [Info23] , [Info24] , [Info25] , [Status] , [AllowBuy] , [PageName] , [Locality] , [Location] , [CreatedBy] , [CreatedOn] , [UpdatedBy] , [UpdatedOn] , [BrandId] , [CategoryId] , [ModelId], [ModelName], [BrandName], [CategoryName] ) select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels, tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize, tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4, tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12, tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18, tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24, tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName, tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn, tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName FROM tbItemMaster INNER JOIN tbModel ON tbItemMaster.model = tbModel.id INNER JOIN tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl Union SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels, tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize, tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4, tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12, tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18, tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24, tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName, tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn, tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName FROM tbItemMaster INNER JOIN tbModel ON tbItemMaster.model = tbModel.id INNER JOIN tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc; SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow ORDER BY UpdatedOn DESC ') RETURN can anybody help me pls. ITs very urgent. Thanks in advance.
View Replies !
Increasing The Number Of Characters Displayed In Query Analyzer
Hi, I am using SQL Server 7.0. I have a table with one column defined as Varchar(8000). The table is populated with rows.. However when I do a Select * from the the Table in query analyzer I only see about 200 Characters even though each column has 8000 characters. How can I increase the number of characters that are diplayed in the Query Analyzer? Thankx KP
View Replies !
Query Problem: Automatically Resetting A Sequential Number List After Row Deletion
Hello! Got a problem I hope some clever people can help me out with.. I have a web form that displays a set of records in a grid. The grid is "paged" according to a PageNum column, with a dropdown box to change pages and buttons allowing the items to be moved up or down a list within a page or moved between pages. So the backend table (simplified) looks something like this PageNum ItemNum ItemDescription ----------- ----------- ------------------- 1 1 aaaaaaa 1 2 bbbbbbb 1 3 cccccccc 2 1 ddddddd 2 2 eeeeeee 2 3 ffffffffffff 3 1 ggggggg 3 2 hhhhhhh 3 3 iiiiiiiiiiiiiiii The problem is when I want to delete a page - I need the page numbers to automatically resequence themselves, so for example, If I delete "Page 2" (i.e. delete rows where PageNum = 2), all items on "Page 3" become "Page 2" (and any items on "Page 4" become "Page 3" etc). This has proved straightforward to when deleting an item from a particular page, and can resequence ItemNum thanks to a clever bit of code found on SQLteam.com: DECLARE @intCounter int SET @intCounter = 0 UPDATE <Item Table> @intCounter = ItemNum = @intCounter + 1 WHERE Pagenumber = <Currently Selected Page> However I haven't been able to adapt this to resequence the Page number, as this involves resequencing blocks of numbers. The closest I can get is: DECLARE @intCounter int SET @intCounter = 1 UPDATE <Itemtable> SET @intCounter = PageNum = CASE WHEN @intCounter = PageNum - 1 THEN @intCounter + 1 WHEN @intCounter = PageNum - 2 THEN @intCounter + 1 ELSE @intCounter END But this doesn't quite work. Anyone got any other ideas?? Thanks Greg
View Replies !
Number/size Of Packets At A Network Level For A MSSQL Query Vs Oracle Vs DB2.
Hello all, I will try to supply all of the information available to me to help generate some answers to the question I have. I am doing some benchmark testing of an application of how it deals with various database queries, for example oracle 10g, DB2 and MSSQL 2005. I have a very simple database called "student" that has various tables, with various columns, all as mentioned very simplistic. Each table has a few hundred rows of data, the most being one table with 2000 rows. The same database (structure and dataset), is replicated across all 3 DB's. In order to do the testing, I have been recording the same queries (select * from table_a, select * from table_b etc etc) for each database at an interface which monitors traffic between the client and db server, the problem starts here; The size of the tcpdumps are vastly greater for MSSQL vs oracle or db2. Some examples: 100 query or transaction dump: mssql=3.7mb oracl10g=133kb 50,000 query or transaction dump: mssql=1.8gb oracl10g=0.6gb db2=0.6gb as mentioned each database's tables and data are identical to my knowledge and all queries are the same. The bloated dumps are making mssql performance numbers look bad. So my question is: What could be the reason for MSSQLs client/server queries to contain so much information, has anyone else come across this? Is there any setting or something I could try to minimize it? Thank you for your time.
View Replies !
Parameterized Query That Counts The Number Of Null Values In An Int32 Column
Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work: cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null)); cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn"; cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value)); cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn"; cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null)); cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn"; cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value)); cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = DBNull.Value; cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = DBNull.Value; cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn"; md.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant)); cmd.Parameters["@IntColumn"].Value = DBNull.Value; cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn"; cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant)); cmd.Parameters["@IntColumn"].Value = DBNull.Value; cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn"; Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.
View Replies !
Trouble With An ASync Query To Insert A Record And Return The Auto Number Field.
I get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. conn.Open() Dim strSql As String strSql = "INSERT INTO contacts (companyId, sourceId, firstName, lastName, middleName, birthday, dateCreated)" _ & "VALUES ('" & companyId & "', '" & sourceId & "', '" & firstName & "', '" & lastName & "', '" & middleName & "', '" & birthday & "', '" & now & "') SELECT @@IDENTITY AS 'contactId'" Dim objCmd As SqlCommand objCmd = New SqlCommand(strSql, conn) Dim aSyncResult As IAsyncResult = objCmd.BeginExecuteReader() If aSyncResult.AsyncWaitHandle.WaitOne() = True Then Dim sqlResults As SqlClient.SqlDataReader sqlResults = objCmd.EndExecuteReader(aSyncResult) Dim cid As Integer cid = sqlResults.Item("contactId") Me.id = cid conn.Close() Return cid Else Return "failed" End If
View Replies !
One Receipt Number, Query Multiple Tables Gives Multiple Data.
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version. Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate) Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number. Using the query: SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount, T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid, T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount, T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid, T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled, T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student FROM T_Receipt INNER JOIN T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount, T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount, T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo, T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student HAVING (T_Receipt.Student LIKE N'06%') Which gives a result of: RecNo. 30429 Cheque 250 Deposit 250 30429 679.98 250 This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result. So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
View Replies !
SELECT Query - Different Columns/Number Of Columns In Condition
I am working on a Statistical Reporting system where: Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1 Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2 Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases: Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance" Client = "Client A", Contract = "2007-2008 Maint", Project = all Client = "Client A", Contract = allI am totally stuck on: How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL) Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services. Thanks for all your help!
View Replies !
Number Of ROWS Of &&"Output Of Aggregate Transformation&&" Sometimes Doesn't Match The Output From T-SQL Query
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS. For example,the output of the Aggregate Transformation may be 960216 ,but the 'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*. I'm sure the Group By of the Aggregate Transformation is right! But ,when I set the "keyscale" property of the transformation,the results match! In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation. Thanks for your advice.
View Replies !
How Do I Get The Row Number?
I have tryed to find a way to include the row number in a querry but without result. I have a table select some rows ordered and now I will add a column called rank How do I get the rownumber in the field rank ? Thanks in advance Jan O
View Replies !
How To Get Id Number From Sql
i am an asp programer trying to program in aspx c# i have a litle problem geting the id number from my sql db and put it into an intiger what i am doing is that i am trying to make the picture have the same name as the id number in the db String sql = "SELECT id "; sql += " FROM PictureLibrary"; sql += " ORDER BY id desc"; int pictureNR = ??
View Replies !
|
TRACKER
