Challenging Likes Wildcard

Jul 19, 2006

Hi all,

I need to search the field containing the word I enter, but this word is bracketed by "{{" and "}}". For example, I would like to search the keyword apple, I need to search by the following sql statement.

select name from table where name likes '%{{apple}}%'

But the case is that that column may contain some space between the bracket and the keyword. i.e. {{     apple  }}. How can I write a sql statement to search name from the keyword?

Thanks

Spencer

View 6 Replies


ADVERTISEMENT

Sql Query Uisng Likes

Nov 8, 2004

I have this query that is searching using the hotel name as its first parameter and the hotels location (e.g. london) ast is second parameter.

My client wants to search on part of the hotel name and part of the location name (so I used the like statement)

Oh and the location searches using like on 3 location fields.

My client is complaining when he types hilton and leeds he gets all the hilton hotel in Leeds plus a few extra hotels that aint hilton hotels. I think the query is to senesitive for its own good but he's pushing me on this issue so could anyone advise me if this query could be made to only select the hotels with a name like hilton in leeds. I've pasted this query below. Thanks for your time.

By the way one hotel it pulls up which is wrong is Heath Cottage hotel whos location field is leeds.

SELECT *
FROM hotel
WHERE name LIKE + '%' @Hotelname + '%' AND
location1 LIKE '%' + @city + '%' OR
location2 LIKE '%' + @city + '%' OR
location3 LIKE '%' + @city + '%'

View 2 Replies View Related

Simple Sql Select With 2 Likes

May 20, 2005

Hi all might seem simple but im having trouble.
how do i include 2 likes in a select:

SELECT from TABLE WHERE name like 'SIB' or 'MIWS' ORDERBY name;

is this valid

Craig

View 1 Replies View Related

Replace Multiple LIKEs

Sep 30, 2006

I have a query below that performs horribly:@KeywordOne char(6),@KeywordTwo char(6),@KeywordThree char(6),@KeywordFour char(6),@KeywordFive char(6)SELECTc.SomethingFROMdbo.tblStuff cWHEREc.SomeColumnName = 0AND (c.Keyword LIKE '%' + @KeywordOne + '%' OR @KeywordOne is Null)AND (c.Keyword LIKE '%' + @KeywordTwo + '%' OR @KeywordTwo is Null)AND (c.Keyword LIKE '%' + @KeywordThree + '%' OR @KeywordThree isNull)AND (c.Keyword LIKE '%' + @KeywordFour + '%' OR @KeywordFour = isNull)AND (c.Keyword LIKE '%' + @KeywordFive + '%' OR @KeywordFive = isNull)The contents of column c.Keyword looks like this:Row1: 123456,321654,987987,345987Row2:Row3: 123456,987987etc.What can I do to get this to perform reasonably? I cannot use full-textsearch.Any help is appreciated.lq

View 4 Replies View Related

Sql Syntax For Multiple Likes Phrases In Where Clause

Nov 16, 2007



You know how you can go:



Code Block
where control_id in (11111,22222,33333,44444)




or




Code Block
where TextName in ('11111','22222','33333','44444')




and you can do this:




Code Block
where TextName like '11%' or TextName like '22%'





well how do you do this? Or can you... or can we right a function to do it... or are we just hosed writing like after like.

where TextName like in ('11%','22%',... and so on)? is anything like that possible?

or better yet
where TextName like in ( select substring(columnName, 1, 2) + '%' from whatever )

can you imagine the dynamics if that syntax actually worked?

Can a function be written to mimic this functionality? so I can do something like this:
where TextName = function('11%,22%,33%')

View 13 Replies View Related

Configuration File Locations - Likes To Say It Can't Load Them In Designer

Feb 28, 2006

I have a package and added a configuration file to it to hold settings such as connection strings etc.

When I open the package up in the designer and check where the configuration file is from, it likes to specify it with a full path like this:

\myserverprojectspackageslah.xml

Where blah.xml is the configuration file and the package file is in the same exact directory.

This creates an issue when I use build and then try to install the package on a server. When installing, the server tries to read the config from \myserverprojectspackageslah.xml which it can not.

Conversely I've tried editing the location in the designer changing it to just be "blah.xml". Then after using the build option I am able to install the package successfully on the server (it can not find the xml file).

However when I then load the package later in the designer to try to edit or chagne it, the designer complains that the "configuration file could not be loaded" and it can't find it even though it is in the same directory that the package definition file is in.

What's going on with this and how do i solve it?

View 1 Replies View Related

Challenging Query...need Help

Apr 18, 2008

Hi,I need help to generate following query....------------------------------------------------------------------------------------------------------------- Tables and their columns:Table name: Employee Columns for Employee Table : (1) EmpID (2) EmpName (3) EcatID (4) ProjectIDSecond Table Name: EmpskillColumns for Empskill Table: (1) EmpID (2) SelfRating (3) EvalRatingInformation: (1) If EcatID = 1 means he is Project Manager [if not then he is a software Engineer]  (2) Both Project Manager and his subordinates have the same ProjectID------------------------------------------------------------------------------------------------------------- Result Needed:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PM Name          Software Enggs Nme under that PM         No of Self Rated Projects (for SEs)        No of Evaluator rated Projects (for SEs)  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                    Rajesh                                                      10                                                                        8 Suguna                         Mahesh                                                      5                                                                        5                                    Roopesh                                                     3                                                                        3--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------In Short: List the Project Mangers name, in the next column list their subordinates (for one PM there are many sub ords), in the next column list the no of self rated projects of the sub ordinates, in the last column, list the No fo evaluator rated projects  Can Some One Help me ??Thanks in AdvanceHave a nice day 

View 20 Replies View Related

T-SQL Challenging Problem

Jun 14, 2005

  I am a bit into this one and it is kind of bugging me as i have not figured it out yet after 1 hour.  I have a web task in a Sql Server 2K that generates ASP code and ftps it to a remote location. This is nothing fancy: selects a few fields from a few tables and creates an HTML page into a file with a *.asp extension. It is much like a report that gets ship from the intranet to the Internet side of our business. You can do something similar by using the Tools>>Wizards>>Management>>Web Assistant Wizard... inside Enterprise Manager.   The page looks fine but the problem (my) is that the rows all have the same color - white the default. I am using a template [*.tpl] and the alternating color for the rows <tr> has to come from the data in the query.  Now what I would like to do is something like this to get alternating colors in my table rows:       declare @bgcolor nvarchar(12)       set @bgcolor = 'white'       select                              @bgcolor =                CASE @bgcolor                  WHEN 'white' THEN 'gray'                  WHEN 'gray' THEN 'white'                   ELSE 'white'               END,               , field1               , field2               , field3       from table1 inner join table2....       where ....  And, maybe get results like these:    'white', 'field1res', 2, 3    'gray', 'field1res, 3, 3    'white', ....etc.  The above DOES NOT WORK. Maybe because T-SQL is a set based language trying to do something like this is not appropriate - aka procedural. It does, however, still bugs the hell out of me. :)  Maybe I should investigate using Reporting Services to handle this requirement.   If anyone has any suggestions, please let me know.

View 6 Replies View Related

Challenging Trigger

Apr 15, 2005

Dear all, i have a challenging problem in writing a trigger here,,

here is the database script

-----------------------SCRIPT------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories_Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories_Properties]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Properties]
GO

CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentID] [int] NULL ,
[CategoryDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MainCategory] [bit] NOT NULL ,
[HavePhoto] [bit] NOT NULL ,
[MaxPhotos] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Categories_Properties] (
[Categories_PropertiesID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[PropertyID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Properties] (
[PropertyID] [int] IDENTITY (1, 1) NOT NULL ,
[PropertyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
------------------------END SCRIPT--------------------------

Categories and Categories_Properties are joined by CategoryID
Properties and Categories_Properties are joined by PropertyID

there is a relation between the table Categories and itself, the PrimaryKey is CategoryID, the ForiegnKey is ParentID

the problem is i need a trigger to fire when adding a record in Categories_Properties to add this property to all the children for this Category, for eexample if the categories tree

Autos >> Cars >> FIAT

when adding the Property BRAND to to Autos, this Property had to be inserted to Cars and FIAT too, i wrote some trigger but it adds this property to just the direct children, just to cars,
How to update it to include all the children in all levels till the leaf ones??

here is what i wrote
-----------------------Trigger-------------------------
CREATE TRIGGER [inherete_property] ON [dbo].[Categories_Properties]
FOR INSERT
AS
declare @CatID integer,@PropID integer
--set @CatID=select CategoryID from inserted
set @PropID=(select top 1(PropertyID) from inserted)
DECLARE cat_cursor CURSOR FOR
select CategoryID from Categories where ParentID in (select CategoryID from inserted)
open cat_cursor
fetch next from cat_cursor
into @CatID
WHILE @@FETCH_STATUS = 0
BEGIN
insert into categories_Properties(CategoryID,PropertyID) values (@CatID,@PropID)

FETCH NEXT FROM cat_cursor
INTO @CatID
END
CLOSE cat_cursor
DEALLOCATE cat_cursor
-----------------------End Trigger---------------------

thank you all

View 1 Replies View Related

Challenging : VB-SQL Connectivity

Mar 12, 1999

This problem occurs in a Visual Basic 5 application which accesses the SQL Server 6.5 database through Jet Engine(DAO).

If a user starts a transaction and then his application terminates abnormally (before rolling back or committing), say due to power failure or if the user kills the application with Ctrl+Alt+Delete, then the transaction started by him is not terminated (rolled back) by SQL Server. Even the process cannot be killed using the "Kill" command and the user keeps holding all the locks that he has already acquired. Also nobody can use Enterprise Manager. This happens when you have lock on more than 8 tables including tables in tempdb. The only way to solve this problem is to restart the server which is never expected in a multiuser system.

One can know the threadID of the process that needs to be killed using "Sysprocesses" system table. Is it possible to kill the thread in Windows NT?

Is there any setting due to which SQL SERVER will automatically kill a process or rollback a transaction started by a disconnected client ?

Is it possible that a process or transaction will have to release lock after the specified time ?

Is there any other way to tackle this problem?

-Gunvant Patil
gunvantp@mail.usa.com

View 2 Replies View Related

A Challenging Question

Feb 19, 2006

How does one figure out, I mean compare the results after some changes have been done to the SQL Server, I mean the method in which the comparision is done, lets say I have a query I ran it it gave me the results in 40 seconds , I ran it again this time it took 30 seconds, then I again ran it it took 35 seconds, I then created an index , this time the query ran in 30 seconds ... how does one compare such things , I mean i need to give stats as too what kind of performace has takern place ... please helpo , I need to knw as a DBa how would you convince your maanger that becasue of some changes the performance has improved, cause when you ask the users they say its ok , we dont see the differnece & stuff , please help.

View 4 Replies View Related

Particularly Challenging SQL Problem.

Jul 23, 2005

Table DDL:create table test(inId int primary key identity,inFK int not null,inSeq int not null,dtDate datetime)Data Insert:insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 2,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 2,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 3,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 4,1, getdate()If we select on this table:inId inFK inSeq dtDate----------- ----------- -----------------------------------------------------------------1 1 1 2005-02-01 12:54:40.9672 1 1 2005-02-01 12:54:41.9673 1 1 2005-02-01 12:54:42.9674 2 1 2005-02-01 12:54:43.9675 2 1 2005-02-01 12:54:44.9676 3 1 2005-02-01 12:54:45.9837 4 1 2005-02-01 12:54:47.077(7 row(s) affected)Problem:What I would like to do (using SQL and not a cursor) is to update thevalue of inSeq to its ordinal position, this will be based on the # ofoccurences of inFK. For Example, I would like to run a sql statementthat would transform the data to:update testset inSEQ = (some sql)select * from test - This would then produce:inId inFK inSeq dtDate----------- ----------- -----------------------------------------------------------------1 1 1 2005-02-01 12:54:40.9672 1 2 2005-02-01 12:54:41.9673 1 3 2005-02-01 12:54:42.9674 2 1 2005-02-01 12:54:43.9675 2 2 2005-02-01 12:54:44.9676 3 1 2005-02-01 12:54:45.9837 4 1 2005-02-01 12:54:47.077(7 row(s) affected)Any help would be greatly appreciated.TFD

View 17 Replies View Related

Challenging Sql Queries

Jul 20, 2005

here is a structure of tables in a databaseCUSTOMERS (CustID*, CompanyName, ContactName, ContactTitle, Address,City, Region, PostalCode, Country, Phone, Fax)EMPLOYEES (EmployeeID*, Lastname, Firstname, Title, TitleofCourtesy,Birthdate, Hiredate, Address, City, Region, Postalcode, Country,Homephone, Extension, Reportsto)ORDERS (OrderID*, CustID, EmployeeID, OrderDate, RequiredDate,ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,Shipregion, ShipPostalCode, ShipCountry)ORDER_DETAILS (OrderID*, ProductID*, UnitPrice, Quantity, Discount)PRODUCTS (ProductID*, ProductName, SupplierID, PL_ID, QuantityPerUnit,UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)PRODUCT_LINES (PL_Id*, PL_Name, Description)SHIPPERS (ShipperID*, CompanyName, Phone)SUPPLIERS (Supplierid*, Companyname, Contactname, Contacttitle,Address, City, Region, Postalcode, Country,Phone,Fax)* - Primary keysqueries (these are problem questions given in a competition!!!)1.) In which month of 1997 was the most number of orders placed? Listthe month, number of orders placed during that month , number ofcustomers who placed orders in that month , gross (i.e. disregarddiscount) total value of orders placed .2.)Determine the products for which there has been at least oneorder during each month of 1997. Display for these products: theProduct ID, Product name, Product Line Name, number of orders placedduring 1997 , number of customers who have ordered the product in 1997, and gross (i.e. disregarding discount) total value of orders placedfor the product in 1997 .3.) Find the customers who have placed orders during 1996 & 1997 forproducts in every product line AND who have had their orders handledby either all employees or all but one employee. Display the customerID, company name, address, city, country and number of orders placed4.) For each product line, find the top three customers (based onmaximum total purchase value; ignore discounts). Display the ProductLine name, the Customer ID & Company name, and the total purchasevalue for that customer in the product line . Sort the output byProduct Line and within that, the line revenue figure (highest first).i think rank() shud b usedany clues/ideas ???

View 6 Replies View Related

Challenging Insert Trigger

Apr 9, 2006

First, please reference the following document which shows the layout of the database and tables:
http://www.eastproject.org/cody/table_layout.pdf
Here's the deal--
I have two databases, SiteData and CommunityServer2.  Both run on the same MS SQL 2000 Server.  We're concerned about two tables on each database:  SiteData.dbo.Users, SiteData.dbo.UM_User_Access_Type, CommunityServer2.dbo.aspnet_Users and CommunityServer2.dbo.aspnet_UsersInRoles.
Whever a user is inserted into the CommunityServer2.dbo.aspnet_Users table, I need to give the user appropriate rules (e.g., moderator, student, etc.) by inserting the correct RoleID in the SiteData.dbo.aspnet_UsersInRoles table.
Have I lost you yet?   Good.
Please reference the following trigger:
ALTER TRIGGER trig_UpdateForumRoles   ON  CommunityServer2.dbo.aspnet_Users    AFTER INSERTAS DECLARE @Username VARCHAR(100) --variable to hold the username from CommunityServer2.aspnet_UsersDECLARE @UserType VARCHAR(50) --variable to hold the username from SiteData.dbo.UM_User_Access_TypeDECLARE @UserID uniqueidentifier --variable to hold the username from CommunityServer2.aspnet_UsersBEGIN SELECT @Username=Username, @UserID=UserID FROM Inserted --should only return one record, meaning we'd only insert one record in aspnet_Users at a time.
SELECT @UserType=UserTypeFROM SiteData.dbo.UM_User_Access_Type UM, SiteData.dbo.Users U, aspnet_Users AU, aspnet_Roles AR, aspnet_UsersInRoles AUIRWHERE U.Username=@UserName and U.ID = UM.Student_ID and U.Username = AU.UserName and AU.UserID = AUIR.UserID and AUIR.RoleID = AR.RoleID    -- if usertype is a SuperAdmin as determined from SiteData.dbo.UM_User_Access_Type, we insert the appropriate RoleID in aspnet_UsersInRoles IF (@UserType = 'SuperAdmin') BEGIN UPDATE aspnet_UsersInRole SET RoleID = 'EDA73944-1B67-4DAA-B6CB-792847B6C694' WHERE UserID = @UserID END
 IF (@UserType = 'StaffAdmin' or @UserType='Partner') BEGIN UPDATE aspnet_UsersInRole SET RoleID = '7B317FF8-7C3C-4D95-AC44-E27E849D4520' WHERE UserID = @UserID END
 IF (@UserType = 'Facilitator') BEGIN UPDATE aspnet_UsersInRole SET RoleID = 'D04FEA9F-35E9-41A5-B062-B9C1524D4266' WHERE UserID = @UserID END
 IF (@UserType = 'Student') BEGIN UPDATE aspnet_UsersInRole SET RoleID = '4F94ABB9-1371-4834-95D6-E8364824F484' WHERE UserID = @UserID END  ENDGO
However, it's not yet working.  I can create the trigger fine (so no syntax problems); however, it's not updating the aspnet_UsersInRoles table.
 
Any ideas?Thank you very much!-Cody

View 5 Replies View Related

Challenging Scenerio - All Are Welcome For Ideas

Feb 7, 2000

I have about 20 remote databases that I need a query/scheduled task to run that generates a .rpt file. That part I can get.

However, I need to get this file to another company (via email or ftp, etc) and make it automated.

For example:

Scheduled task runs twice a month and generates a file that then needs to be emailed to an individual or ftp'd to a ftpsite. Is there a way to do this in SQL 7.0 (or 6.5)?

Thanks for all your help,
Laura

View 2 Replies View Related

Challenging Query Problem

Nov 9, 2001

Challenging Query Problem

I am using SQL Server7 to store many thousands of distinct attributes for a product. Because SQL Server7 has a columns-per-table limitation of 1024, I had to decide between spreading the attributes among multiple tables or try a "link table" approach which would only use two tables. I chose the "link table" approach because I thought it would be a more elegant solution. I have had success using this approach up to the point of doing complex filtering of the records. This is where I need help.

Here is this "link table" approach database schema:

tblAttributes
=====================
attributeid
attributename


tblProductsAdditional
=====================
productsadditionalid
productid
attributeid
attributevalue

Instead of having tables with many columns, I use a single table to list the thousands of attributes. I then use another table to associate an attribute with a product and a attribute value.

The problem i am having is building a filter query that will get products that have multiple attribute values. Example, I want to find all products that have a attribute value of 'AAA' and an attribute value of 'BBB'. Because the attribute values are not in separate columns, a simple AND in a WHERE clause does not work- i get zero records returned. Here is sample SQL:

SELECT DISTINCT ProductItemID
FROM tblProductItemAdditional
WHERE ((AttributeValue = 'AAA') AND (AttributeValue = 'BBB'));

My question to you is- How do I filter the records to get the results I want?

I have started looking in cascading select statements. I am worried about cascading select statements because of performance problems that are associated with many nested subqueries. SQL Server7 also has a nested subquery limitation of 32 (which limits how large my search filter can be- I would like to make a filter using around 100 different values).

Is it possible to do what I want in practical way, or did I come to a "dead-end" in my database design? What should I do?

Thank you!

--Llyal

View 4 Replies View Related

Challenging : Create View

Feb 6, 1999

Hi,

I tried to create a view on following 4 tables with two different syntaxes. One with ANSI syntax and other with conventional(using WHERE clause). Both SELECT work fine and return same result sets. Views are created in both cases. SELECT from Conventional_View works fine But SELECT from the ANSI_View returns error message without Error Number as :

'DB-Library Process Dead - Connection Broken'

CREATE TABLE dbo.SILT01 (
SILF01A varchar (8) NOT NULL ,
SILF01B varchar (25) NULL ,
SILF01C smallint NULL ,
CONSTRAINT SILT01_PK PRIMARY KEY CLUSTERED
(SILF01A)
)
GO

CREATE TABLE dbo.SILT19 (
SILF23A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT19_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF01A
)
)
GO

CREATE TABLE dbo.SILT20 (
SILF23A varchar (8) NOT NULL ,
SILF15A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT20_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF15A,
SILF01A
)
)
GO

CREATE TABLE dbo.SILT23 (
SILF23A varchar (8) NOT NULL ,
SILF23B varchar (30) NULL ,
SILF23C varchar (40) NULL ,
SILF23D varchar (8) NULL ,
SILF23E varchar (1) NULL ,
SILF23F text NULL ,
SILF23G varchar (25) NULL ,
SILF23H varchar (8) NULL ,
SILF23I varchar (25) NULL ,
SILF23J varchar (25) NULL ,
SILF23K text NULL ,
SILF23L varchar (25) NULL ,
SILF23M varchar (8) NULL ,
SILF23N varchar (25) NULL ,
SILF23O varchar (25) NULL ,
SILF23P text NULL ,
SILF23Q varchar (25) NULL ,
SILF23R varchar (8) NULL ,
SILF23S varchar (25) NULL ,
SILF23T varchar (25) NULL ,
SILF23U varchar (15) NULL ,
SILF23V varchar (15) NULL ,
SILF23W varchar (15) NULL ,
SILF23X varchar (15) NULL ,
SILF23Y varchar (15) NULL ,
SILF23Z varchar (15) NULL ,
SILF23A1 varchar (15) NULL ,
SILF23A2 varchar (15) NULL ,
SILF23A3 varchar (15) NULL ,
SILF23A4 varchar (15) NULL ,
SILF23A5 varchar (15) NULL ,
SILF23A6 varchar (3) NULL ,
CONSTRAINT aaaaaSILT23_PK PRIMARY KEY CLUSTERED
(
SILF23A
)
)
GO


create view ANSI_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM ((SILT01 INNER JOIN SILT19 ON SILT01.SILF01A = SILT19.SILF01A)
INNER JOIN SILT23 ON SILT19.SILF23A = SILT23.SILF23A)
LEFT JOIN SILT20 ON (SILT19.SILF01A = SILT20.SILF01A) AND
(SILT19.SILF23A = SILT20.SILF23A)


create view Conventional_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM SILT01, SILT19,SILT23, SILT20
WHERE ( SILT01.SILF01A = SILT19.SILF01A AND
SILT19.SILF23A = SILT23.SILF23A ) AND
( ( SILT19.SILF01A *= SILT20.SILF01A) AND
(SILT19.SILF23A *= SILT20.SILF23A) )


SELECT * FROM ANSI_VIEW
'DB-Library Process Dead - Connection Broken'

SELECT * FROM Conventional_View (Works fine)

Expecting Help or Suggestions from experts.

Thanx,

Gunvant Patil( gunvantp@mail.usa.com )

View 1 Replies View Related

Challenging In Customising Report

May 10, 2007

I am new to Reporting Services. I built a tabular report using report build wizard, comprise 10 columns, including a year column from year1 to year10.

Now my client requests 3 of the columns need to display different value accordingly:


From year 1- 5, display the field value from data source,
From year 6 onward, display zero value, which there is value exist in data source.

The subtotal calculated column 10 applies different formula as well:

From year 1-5, sum (column 3, 4, 6)
From year 6 onward column 10 less sum (column 3, 4)
Could anyone help to shed some light for how to overcome this challenge?
Many thanks in advance,

Liwei
from Australia

View 1 Replies View Related

Challenging Select Statement

Jan 10, 2008

Here is the table A:
Week Year Name
1 2007 x
2 2007 b
3 2007 d
4 2007 d
5 2007 d
6 2007 d
7 2007 d
1 2008 h

This table goes all the way to week 52. I have only 2007 and 2008. so when the user pass start week :1 end week:4 start year 2007 end year 2007
The report will come out something like this

Week Year Name
1 07
2 07
3 07
4 07


Here is the select statement for that
Select week,year,name from dbo.A
Where Week between @startWeek and @endWeek in this case 1 and 4

The real challenge is if the user enters the following input
start week :49 end week:1 start year 2007 end year 2008
in the case the report should look something like this

Week Year Name
49 07
50 07
51 07
52 07
1 08

How can I accomplish such thing in my select statement? I did create a function to handle such scenario but it did not work out.
Any thoughts!
Thanks

View 9 Replies View Related

Challenging Date Spans Problem

Oct 4, 2001

Challenging Date Spans Problem:

A health insurance plan stores information about covered members in the following table:

CREATE TABLE enrollment
(
ssn CHAR(9) NOT NULL,
startdate DATETIME NOT NULL,
enddate DATETIME NOT NULL,
policy INTEGER NOT NULL,
CONSTRAINT pk_enrollment PRIMARY KEY CLUSTERED (ssn, startdate) ,
CONSTRAINT ck_noreversedate CHECK (enddate>=startdate))

INSERT enrollment
VALUES (‘111111111’, ‘2000-01-01’, ‘2000-04-30’, 1)

INSERT enrollment
VALUES (‘111111111’, ‘2000-06-01’, ‘2001-12-31’, 1)

INSERT enrollment
VALUES (‘222222222’, ‘2000-01-01’, ‘2000-06-30’, 1)

INSERT enrollment
VALUES (‘222222222’, ‘2000-07-01’, ‘2001-12-31’, 2)

INSERT enrollment
VALUES (‘333333333’, ‘2000-01-01’, ‘2000-06-30’, 1)

INSERT enrollment
VALUES (‘333333333’, ‘2000-07-01’, ‘2001-12-31’, 1)

INSERT enrollment
VALUES (‘444444444’, ‘2000-01-01’, ‘2000-06-30’, 1)

INSERT enrollment
VALUES (‘444444444’, ‘2000-07-01’, ‘2000-11-30’, 1)

INSERT enrollment
VALUES (‘444444444’, ‘2001-03-01’, ‘2001-06-30’, 1)

INSERT enrollment
VALUES (‘444444444’, ‘2001-07-01’, ‘2001-12-31’, 1)

Assume that there are no overlapping enrollments. We are able to enforce this at the time the records are inserted (e.g., through the front-end application).

Members can have multiple enrollments. These enrollments can be “adjacent” (e.g, member 222-22-2222 has one enrollment which terminates on 06/30/2000 and another that begins on 07/01/2000) or there can be gaps (e.g., member 111-11-1111 has one enrollment which terminates on 04/30/2000, the subsequent enrollment begins 06/01/2000).

Our task is to write a SELECT statement that will coalesce all “adjacent” enrollments where the policy is the same. E.g., member 333-33-3333 has two records in the source table, these records would be combined into a single enrollment with start date = 01/01/2000 and end date = 12/31/2001.


SELECT e.ssn, e.policy, MIN(e.startdate) AS startdate, MAX(e.enddate) AS enddate FROM enrollment e GROUP BY e.ssn, e.policy WHERE EXISTS (SELECT * FROM enrollment e2 WHERE e.ssn = e2.ssn AND e.policy = e2.policy AND e.startdate = DATEADD(y,1,e2.enddate)) OR EXISTS (SELECT * FROM enrollment e3 WHERE e.ssn = e3.ssn AND e.policy = e3.policy AND e.enddate = DATEADD(y,-1,e3.startdate))

UNION

SELECT e4.ssn, e4.policy, e4.startdate AS startdate, e4.enddate FROM enrollment e4 WHERE NOT EXISTS (SELECT * FROM enrollment e5 WHERE e4.ssn = e5.ssn AND e4.policy = e5.policy AND e4.startdate = DATEADD(y,1,e5.enddate)) AND NOT EXISTS (SELECT * FROM enrollment e6 WHERE e4.ssn = e6.ssn AND e4.policy = e6.policy AND e4.enddate = DATEADD(y,-1,e6.startdate))

This works for member 333-33-3333 who has only one set of “adjacent” enrollments.

However it fails for member 444-44-4444 who has two sets of “adjacent” enrollments. Can anyone suggest an approach that would return two rows for 444-44-4444, one with start date 01/01/2000 and end date 11/30/2000 and another with start date 03/01/2001 and end date 12/31/2001?

View 2 Replies View Related

A Challenging CUBE Related Question

Dec 8, 2004

Hi,

I have data coming in from our OLTP's view which has the following sales related information:

1) Date of Sale
2) Product Sold
3) Customer to which the Product was sold
4) QTy. sold
5) Total Sale Amount


I am bringing in this information into my "staging area" and from there I am building my Customer, Product, and Time Dimension and obviously my FACT table. The measures in my fact table were TotalSalesand Total QtySold and everything was working fine and I was able to analyze, slice, dice my cube in many ways...

Now the problem:

I also have planning related data available from another OLTP based view which contains the following information:

1) Plan Year
2) Plan Month (using Plan Year and Plan month,I "derived" a date which for any month was the 1st of that month)
3) Planned Product
4) Planned Sale (Amount)
5) Planned Qty (for sale)

You would have noticed that planning is NOT done at "Customer Level" and the planning data is only for product and time level (and that too only upto month level).

After populating my FAct table from the Sales data, I appended the planning related data into my fact table hoping that as long as I do not select any thing in the Customer dimesion and only go as low as the month level on my Time dimension, I will be able to see all of the following (measures) for any/all products:

Planned Sale, Actual Sale, Planned Qty., Actual Qty,

However I am unable to get anything displayed in the Planned Sale and Planned Qty despite applying all of the little knowledge that Ihave so far in the world of BI and making data marts
....

I really hope that my questions and requirement is clear.Can someone please help me get to

the solution.



Many many TIA.

View 1 Replies View Related

Challenging: Cross Tab In SQL 2000 (Urgent)

Oct 8, 2007



I have the following table dbo.Test

Name Week Type Hours
Steve 1 Section_1 1

Steve 1 Section_1 2

Steve 2 Section_2 5

Sarah 1 Section_1 1

Sarah 2 Section_2 2

Sarah 2 Section_3 3

Mike 3 Section_3 1

Mike 1 Section_3 1


I know for a fact i am going to have no more than 3 sections

here is the output i would like to see


Name Week1 Section_1 Section_2 Section_3 Total

Steve 1 3 3

Steve 2 5 5

Sarah 1 1 1

Sarah 2 2 3 5

Mike 1 1 1

Mike 3 1 1


I read some articles but i am still confused.

thanks in advance for your help

View 6 Replies View Related

Challenging Select Statement (urgent)

Feb 6, 2008

Here is table dbo.vw_TimebyProjectAll

proj_name res_name yr SumOfHours
x Mike 2007 1
x Mike 2008 2
x Mike 2008 3


Here is table dbo._LastWeekTotalHours

proj_name res_name yr td_hours Week
x Mike 2007 1 1
x Mike 2008 2 8
x Mike 2008 3 9

now here is what the output should look like

Lets assume the user well enter 8 for week number

proj_name res_name totalHours LastWeekHours
x Mike 6 2
Y AJ .. €¦.

I am trying to write a select statement for that:

Here is what I have:

SELECT a.proj_name, a.res_name,a.TotalHours,g.TotalHours as LastWeekhours
FROM

Line 1( SELECT r.proj_name,r.res_name, SUM(r.td_hours) AS TotalHours FROM
line 2 dbo._LastWeekTotalHours as r, _vw_Employee as e
Line 3 WHERE e.RES_NAME = r.res_name and r.Week = 5 and r.yr = 2008
Line 4 GROUP BY r.proj_name, r.res_name ) as g

Line 5(SELECT r.proj_name, r.res_name, r.TotalHours
Line 6 FROM
Line 7 (SELECT proj_name, res_name, SUM(td_hours) AS TotalHours FROM
line 8 dbo._LastWeekTotalHours
line 9 GROUP BY proj_name, res_name ) r ,
line 10 (SELECT proj_name, res_name, SUM(SumOfHours) AS TotalHours FROM
dbo.vw_TimebyProjectAll
line13 GROUP BY proj_name, res_name )w
)a

if i excute line 1 - 4 i will get the last week hour correctly
if i excute line 5 - 13 i will get the totalhours correctly





my problem i can't put these two pieces work togather. feel free to re-write the select statment
thanks

View 14 Replies View Related

Challenging Drilldown/visibility Problem

May 12, 2008

Hi,
I have a table with three group headers. I want the header row in the third section to be toggled by a textbox in the second header. This works as expected. However, there are times when there is no data in the third section. When this happens, the third section header row shows even though there is no data. I can also create an expression for the third section header's hidden property that looks for the existence of data, but then the toggle is deactivated. I need some way to say, "here's the toggle item. Show the header if there is data for it."


Any ideas are welcome.

View 2 Replies View Related

How To Create Performance Statistics (was A Challenging Question)

Feb 19, 2006

How does one figure out, I mean compare the results after some changes have been done to the SQL Server, I mean the method in which the comparision is done, lets say I have a query I ran it it gave me the results in 40 seconds , I ran it again this time it took 30 seconds, then I again ran it it took 35 seconds, I then created an index , this time the query ran in 30 seconds ... how does one compare such things , I mean i need to give stats as too what kind of performace has takern place ... please helpo , I need to knw as a DBa how would you convince your maanger that becasue of some changes the performance has improved, cause when you ask the users they say its ok , we dont see the differnece & stuff , please help.

View 1 Replies View Related

Challenging Search Task Is Not Working As Expected

Sep 14, 2007



Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1
----------------------
-searchword- column name
--------------------------------------------
Learn more about melons row0
--------------------------------------------
%.txt row1
-------------------------------------------


table2
------------------------------
-testname- column name
--------------------------------------------
FKOV43C6.EXE
-------------------------------------------
frusdr.txt
-------------------------------------------
FRUSDR.TXT
------------------------------------------
SPGP_FWPkg_66G.zip
------------------------------------------
readme.txt
-----------------------------------------
README.TXT
----------------------------------------
watermelon.exe
----------------------------------------
Learn more about melons read me.txt
-------------------------------------------------------


Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================


select * from @table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @table1 ta

JOIN @table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @table1)



)
===============================================================================

script to create tables
============================================================================

DECLARE @table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @table1 (

searchword

) VALUES ( '%.txt' )



DECLARE @table2 TABLE (

testname VARCHAR(255)

)



INSERT INTO @table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @table2 (

testname

) VALUES ( 'readme.txt' )



INSERT INTO @table2 (testname

) VALUES ('README.TXT' )





INSERT INTO @table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @table2

DECLARE @table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )






===================================================================================





View 4 Replies View Related

Recurring Appointments Calendar Design (was The Most Challenging Project)

Jan 14, 2005

Hello,
This is my first project at the company I recently joined. It is the most challenging project I ever had.

It is a kind of web-scheduler.
it let you save appointments which could be one time or periodic.
For example, it could be like every monday, every other monday, first tuesday every monday, and so on.

once they are saved, it should be able to display only those appointments the user have on a specific day.

I'd like to know how database has to be designed to meet such a challenging requirement?
I'd like to hear anything from you if you have had similar project before.

Thank you,
Charlie

View 2 Replies View Related

Really Challenging Complex Column Level Processing Question

Oct 19, 2007

Hi,

I have to implement a complex algorithm that processes each row and each column.I have bunch complex steps at the end of which a table gets populated with the processed columns.


My question is as to what is the best way to do this?CLR integration or T-SQL?
Also I would appreciate any ideas as to how to go about using either approaches.

One of the steps of processing involved per column is as follows:-
1)For each column,find successive invalid values from the start of the column.Invalid value= any value <0
2)find the invalid value depth of each column(no of successive invalid values from start)
3)If after these invalid vlaues,there is a valid value and another invalid value,replace current invalid value with valid value.
ie replace invalid value only if it has a valid value above it.
4)find the column with maximum invalue value depth and delete that many rows from the table.

Here's am example:-
Suppose there are 2 columsn colA and ColB.The columns have different datatypes eg decimal,int,string etc.
For simplicity colA and colB are ints.
RowID is keeping track of the row number.

suppose colA has the following data

RowID ColA
-----------
1 0
2 -5
3 -3
4 1
5 4
6 -9
7 5
8 8

Step1)successive invalid values from start=0,-5,-3
Step2)Invalid value depth=3(because there are 3 rows from step 1)
Step3)0,-5,-3 do not have any valid value above them.But -9 has a valid value 4 above it.So replace -9 with 4.

so colA after the algo will look as follows
RowID ColA
------------
1 0
2 -5
3 -3
4 1
5 4
6 4(replaced -9 with 4)
7 5
8 8

Now do the next column colB
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 -5
8 -8

Step1)successive invalid values from start=-6,0,0,-7
Step2)depth of invalid values=4
Step3)Next invalid value -5 occurs at RowID 7
and has a valid value 8 above it.
Replace -5 with previous valid vlaue ie 8.

RowID 8 has invalid value -8 .Its previous invalid value(-5) got replaced by a valid value 8.So replace RowID8 also with value of RowID 7 ie 8

Output at the end of these steps
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 8(replaced -5 with 8)
8 8(replaced -8 with 8)

Step4:Get the maximum invalid value depth
In this case ColB had depth=4 which is greater than ColA which had dept=3.so delete 4 rows from the beginning of the table
So the table will be

RowID colA colB
----------------------------------------
5 4 4
6 4(replaced -9 with 4) 8
7 5 8 (replaced -5 with 8)
8 8 8(replaced -8 with 8)

Thanks in advance for your help

coolaqua


View 8 Replies View Related

How To Form A Single SQL Statement In A Datalist For A Challenging And Convoluted Problem ?

Aug 26, 2007

Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem.   I'm relatively new to SQL, so this may not be so tough for some of you.  Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him.  The list is presented to him via a datalist.  Each item of the datalist contains a table of 2 columns and 1 row as pictured below.  The first box contains the user photo and user name of the person who SENT him the message (not the logged in user).  The second box contains the subject title of the message.
      FROM        |    SUBJECT   |  User Photo     |                    ||                       |    Subject     ||  User Name     |                    |
Here is the list of the relevant 4 tables of my database and the relevant fields.....
aspnet_Users tableUserId (used to link to Member table)UserName 
Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table)
Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive)
Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max))
So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user.  The senders will be determined by the Member.fromMember fields.  Intuitive enough so far, I guess.   This is the SQL statement I have so far.....
SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember
Note that I'm grabbing the logged in user info from Profile.UserName.  So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user.  HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)?  Do you see the core of my dilemna here?  I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER).  Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender.
And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ??  I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe.   The SQL statement so far asks to retrieve information based on the logged in user.  But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now?  And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement.  This is indeed very very complicated for me lol.
This problem has been giving me migraines this whole weekend.  Has anyone been through such a problem before?  Any help would be greatly appreciated - thanks in advance.

View 22 Replies View Related

A Challenging Question, ...Please Help (insufficient Result Space To Convert MONEY Value To CHAR)

Dec 8, 1998

Msg 234, Level 16, State 2
There is insufficient result space to convert MONEY value to CHAR.

hi, I have one procedure that insert data into a table, I called the procedure 3 times ,and run it in one step, then got the above erorr message.
what can I do to avoid this error message?
and why I am getting it... thanks for your help
regards

Ali

View 2 Replies View Related

Wildcard Use

Jun 30, 2006

Hello everyone and thanks for your help in advance.  I am working on an application that does a property search off of a database that contains approximately 40000 records.  The search criteria allows the use to specify a minimum and maximum price, subdivision name, number of bedrooms, etc.  I set up a stored procedure to query the databse.  if one of the parameters is not specified, i simply pass it a "%".  However, when i execute this sproc in Query Analyzer, it takes in excess of 10 seconds to return the records, even  if only one or two are returned.  I am assuming this is due to the use of the wildcard character when the user does not have a preference, but I am not sure of any other way to do this.  Any help on this topic would be grealy appreciated.

View 3 Replies View Related

Wildcard

Sep 9, 2006

hi,i have an sqldatasource a gridview and dropdownlistthe gridview is updated on the selectedIndexChanged Event of the dropdownlistmy goal is to add an item in the dropdownlist with the name ALLwhich should matches all the records in the databasei tried to put the value of the all item = * and % but neither seems to workany help on what could be going wrong would be appreciated

View 7 Replies View Related

Looking For A Wildcard

May 23, 2003

hi,

how do i contsruct a SQL query to search for the % character in a data field?

thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved