Interesting Challenge
Jul 12, 2005
We're having to work with some legacy data. The tables in the so-called database seem to have way more nulls than actual data. One table appears to have around 100 or more columns in it. It has close to 40,000 rows.
This Db has pretty much 0 normalization present.
IOW, your worst nightmare.
Is there a way we could run a query that would return the total number cells inthat contain NULL and another that could return the total number of data-bearing cells so we could come up with a % or a ratio.
View 9 Replies
ADVERTISEMENT
Aug 2, 2007
I am making a report in Visual Studio..Now my question is,is there anyway that i can add a tab control for my rdl??Hope to here from anyone out there with a know how..Thanks guys!!
View 13 Replies
View Related
Sep 6, 2006
I'm sure you all have seen situations where a field has a combined namein the format of "Lastname, Firstname Middlename" and I've seenexamples of parsing that type of field. But what happens when the datawithin this type of field is inconsistent? Here are some examplesApple, John A.Berry John B.CherryJohn CDonald John DHow does one parse the data when the data isn't consistent?
View 2 Replies
View Related
Jun 12, 2004
Below is the statement given in MicroSoft SQL Server migration documentation :
"When a view is defined with an outer join and is queried with a qualification on a column from the inner table of the outer join, the results from SQL Server and Oracle can differ. In most cases, Oracle views are easily translated into SQL Server views"
Please can anyone explain the above with some examples. I don't find such a 'CREATE VIEW' statement existsing in two DBs, which results in different result set. Am I wrong ?
Thanks,
Sam
View 2 Replies
View Related
Aug 26, 2004
Another tech was having the same problem with his sp which he also made into a macro. He was having the same problem as I was, he thought since he made the user the dbo owner that he woud have no problems...but since hes the one who created the db and the stored procedures when the user tried to access it he got that same error message. It went away after he went into the Enterprise manager and in the properties of the stored procedure under permission he gave the user EXEC permissions...And WAAALLLAAA problem solved :):):):)
View 10 Replies
View Related
Oct 22, 2004
Anyone else experience this? A developer just finished complaining about the performance of one of our databases. Well, he sent me the query and I couldn't understand why it was such a dog. Anyways I rewrote it. The execution plan is totally different between the two. I had no idea specifying the join made such a difference. First sql executed in 7 minutes that 2nd took 1 second. SELECT dbo.contract_co.producer_num_id, contract_co_statusFROM dbo.contract_co, dbo.v_contract_co_statusWHERE ( dbo.v_contract_co_status.contract_co_id = dbo.contract_co.contract_co_id ) AND contract_co_status = 'Pending' OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )SELECT dbo.contract_co.producer_num_id, contract_co_statusFROM dbo.contract_co INNER JOIN dbo.v_contract_co_status ON dbo.contract_co.contract_co_id = dbo.v_contract_co_status.contract_co_idWHERE contract_co_status = 'Pending' OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )
View 3 Replies
View Related
Oct 4, 2006
I just don't have the skills to work this out...maybe there is an SQL guru out there that can solve this:I can't work out how to do a recursive query so I'm using this function:Public Shared Function FindFriendsFriends(ByVal userID As Integer)Dim iUserID As Integer = userIDDim friendAdapter As New TableAdapters.FriendsTableAdapterDim oFriends As New FrontEnd.FriendsFriendsDataTableDim oTemp As New Data.DataTableDim oContainer As New Data.DataTableoFriends = friendAdapter.GetFriendsByUserID(iUserID)For InLoopCounter As Integer = 0 To oFriends.Count - 1oTemp = friendAdapter.GetFriendsByUserID(oFriends.Rows(0)("UserID"))oContainer.Merge(oTemp)NextReturn oContainerEnd FunctionWith this SQL statement in the table adapter is:SELECT tblFriends.FriendHashID, tblFriends.cCreated, tblFriends.UserOwnerID, tblUsers.UserID, tblUsers.Displayname, tblUsers.EmailAddress, tblFriends.RequestStatus, tblFriends.FriendUserID, tblUsers.ProfilePhoto FROM tblFriends INNER JOIN tblUsers ON tblFriends.FriendUserID = tblUsers.UserID WHERE (tblFriends.UserOwnerID = @UserID) AND (tblFriends.RequestStatus = 2) UNION SELECT tblFriends_1.FriendHashID, tblFriends_1.cCreated, tblFriends_1.UserOwnerID, tblUsers_1.UserID, tblUsers_1.Displayname, tblUsers_1.EmailAddress, tblFriends_1.RequestStatus, tblFriends_1.FriendUserID, tblUsers_1.ProfilePhoto FROM tblFriends AS tblFriends_1 INNER JOIN tblUsers AS tblUsers_1 ON tblFriends_1.UserOwnerID = tblUsers_1.UserID WHERE (tblFriends_1.RequestStatus = 2) AND (tblFriends_1.FriendUserID = @UserID)I want to replace the SQL statement with a recursive query that I simply pass the UserID to and get rid of the function which is very inefficient!
View 2 Replies
View Related
Sep 2, 2004
Ok, here's the problem. I have CA's Unicenter ServiceIT Enterprise Edition 5.0 running on a production box that has NT4 (SP6) and SQL server 2000 running on it.
Just before we continue, this is about backing up the database that ServiceIT connects to.
I know that you can schedule EM to make backup's of a specific database over a certain time, but this is part of the problem. What i have since discovered is that ServiceIT will not run on a database backed up and restored by EM.
It will however run on the restored backup created through the dos command pdm_backup (comes with ServiceIT).
What i am trying to figure out is to try a find a way that i can run a dos prompt command to do the following tasks at a specific time of day:
Stop the Paradigm Server Daemons (service)
Run a DBCC CHECKDB on the database AHDTEST50.
If there are no errors, create a verbose backup using the dos command pdm_backup -v -fC:ackup[todaysdate]
If there are errors run a script to correct them, then run a backup
Restart the Paradigm Server Daemons
View 5 Replies
View Related
Aug 2, 2007
How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)???
Like I have a table employee
Employee
(
colA int
colB int
colC varchar
)
If i run the tsql..
it should give me
Employee
(
colA varchar
colB varchar
colC varchar
)
View 5 Replies
View Related
Feb 1, 2007
Hi all,
I have a system in place currently that both a local and a live database, currently the sales database is kept in line with a home grown "syncronise" script that controls keeping the two tables (live and local) in sync with the use of Local and Remote ID'sFor example;Table "Sales"ID - IntRemoteID - IntVarious Data fieldsThe 'Live' System writes new records using the RemoteID field, whilst the local inserts new records using the ID field. The Sync script copies the data up/down and assigns the corresponding RemoteID/ID's to the records as they are copied accordingly.Its been decided that this process must change, in favor of Two Way Replication. I have been tasked with finding the solution to the safe handling of ID's as theres a danger that two people can be writing a record (one live, one local) and possibly create the same ID's - i know that replication has its own ID's and could probably manage this well on its own, but these 'Internal' ID's have to be unique due to business rules.So i have been considering two possibilities to address this problem,1. Staggering the ID's - for instance Live ID's begin at 1,000,001 and local ID's begin at 1.2. Having a controlling ID table which provides the next IDBoth options have their advantages and disadvantages, with option 1 we will run into a problem later when the local ID's reach the 1 million mark (a few years away, i admit) and option 2 will need careful locking/transactional code to ensure the same ID isnt given twice.Does anyone have experience with this kind of issue? or could suggest an alternative approach to this problem? Thanks in advance!
View 2 Replies
View Related
Jan 3, 2001
We've had an interesting server issue here since about the start of the year, where the dual processor Netfinity server we've been running SQL 7 on has been holding steady at almost exactly 50% CPU utilization, give or take about 5%-10%. Disk utilization in all areas is relatively low. In fact, the server will do this right after a fresh reboot, with no users accessing. The really interesting part is that a graph of the two processors mirror each other. When the utilization of one goes up, the other goes down, almost perfectly. The two processors are the same speed, but their firmware build is different. IBM says it's not a problem. What say ye?
View 1 Replies
View Related
Nov 28, 2001
If Query1 gives 1 row output as:
customer_id
1234
-------------------
Query2 gives 1 row output as:
product_id
8970
-------------------
How do we get the above results as a single result set of 2 columns side by side? as follows:
customer_id product_id
1234 8970
Is that possible in a single select statement??
Query1 and Query2 both are select statements on the same table with different where clauses.
Sheila.
View 2 Replies
View Related
Jul 14, 1999
Hi ! I am facing an interesting problem for one of my databases. When I double click on a particular table in Enterprise Manager, the Manage Tables window shows me only one or two fields from the table. (it does not show me all the fields). When I run sp_help <tablename> in isql, all the information is displayed. Why is there such inconsistency. It happens only on one particular database. I have run DBCC commands but still no help. Does anyone have any ideas on this. Thankx,
View 3 Replies
View Related
Jun 8, 2004
Hi Folks,
This is a create table statement in DB2 z/ODS and OS/390 ( mainframe versions ) :
create table test(sno int) IN DATABASE database1 AUDIT NONE DATA CAPTURE NONE
This will create a table in the named database 'database1'.
1) In SQL Server, is there any equivalent for this 'IN DATABASE' syntax in DB2 ?
2) 'Data capture none' will not store any extra info about the data replication in the log files. Is there any eqiuivalent for this in SQL Server ?
Thanks,
Sam
View 1 Replies
View Related
Jan 10, 2008
In this SQL query I get en error like this:Incorrect syntax near ','.SELECT SUM(mCount * mPrice), CONVERT(DATEPART(yyyy, mDate), varchar) + '.' + CONVERT(DATEPART(mm, mDate), varchar) AS DateSoldFROM salesWHERE mDate BETWEEN '9/10/2007 0:0:0' AND '1/10/2008 23:59:59'I think you it isnt necessary to show values in the table because it is a syntax error but I dont undertand how it can be incorrect...Thanks for help...Saren Taşçıyan
View 14 Replies
View Related
Jul 23, 2005
Imagine a table in Microsoft Sql Server 2000 named Pictures with threefields. A primary key ID(int), a Name(nvarchar) and a Picture(image)field.Lets put some records into the table. The first two fields as expectedwould take an int and a string. The third field is of type image.Instead of putting a bitmap in this field lets place an xml documentthat has been streamed into a byte array. The xml document woulddescribe the Picture using say lines. If the picture was that of asquare we would have four lines in the xml document. You could think ofthe xml document as something similar to vector graphics but thedetails are not relevant. The important fact is that the contents ofthe image field is NOT a bitmap but a binary stream of an xml document.Now imagine we have a reporting tool like Crystal Reports that can beused to report on this database table. Imagine we create a report byusing the three fields mentioned above. As far as Crystal is concernedthe first field is an int, the second a string and the third an image.If our table had ten records and we preview the report we like to see10 entries each consisting of an ID, Name and a Picture.This can only happen if the image field contains a Bitmap, but asmentioned above the field contains an xml document.Now my question...Can we write something in SQL Server 2000 (not 2005) to sit between thetable and Crystal Reports so to convert the XML document to a bitmap.The restriction is that we cannot use anything but sql server itself.The client in the above case has been Crystal Reports but it could beanything.I know SQL Server 2005 supports C# with access to the .NET frameworkwithin the database. Unfortunately, I am not using SQL server 2005.Some people have suggested the use of User Defined Functions and TSQL.I like to know from the more experienced SQL Server people if what I amtrying to achieve is possible. Maybe it has not been done but is itpossible?Any suggestions would be greatly appreciated...Many Regards
View 5 Replies
View Related
Sep 20, 2007
Hi All,
When I wanted to see ONLY today€™s records on the sql server, it took 5 minutes.
When I wanted to see records including today€™s records on the sql server, no problem. (19 Sep. is a past date which is working properly)
It is very interesting it takes 5 minutes to sort the lastday there is nothing wrong with the other days.
I ran DBCC INDEXDEFRAG method but no change anything.
I am wondering about something, everyday at 22:00 pm we are getting backup. Is this process creating a difference between before getting the back up and after got the backups?
It is really interesting issue. I do not know what I can say. It seems there is an issue with today€™s records. May be there are some locks on the today€™s records. I do not know. Why does not cause any issue for old dates but for current day ?
SELECT DISTINCT CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration
FROM (SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration
FROM CallInfo AS MAINTBL
JOIN ( SELECT CallID, DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration
FROM CallInfo
GROUP BY CallID
) AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID
) as CallInfo
WHERE CallIDChar = CallIDChar
AND (CallStartTimeStamp >= '2007-09-20T00:00:00')
AND (CallStartTimeStamp <= '2007-09-20T23:59:59')
What can be reason for this issue and how can I solve it ?
Thank you in advance,
Melih
View 15 Replies
View Related
Apr 12, 2007
I have a very simple SSIS package that imports data from ODBC source. There are two "Time" columns in source, but When this table is created in SQL2005, it give me "bigint"!!!
How can this happen?
I tried to import data to SQL2000, those two columns are created as "smalldatetime" properply.
Thanks.
View 11 Replies
View Related
Jun 23, 2007
Having no more experience than reading books online, here is an interesting idea I would like to run by you guys and you can let me know if it is feasible or tell me I need to put the crack pipe down...
We are going to increase the number of disks in our SAN, and I was speaking with the SAN administrator and he mentioned the shuffling of logical drives to match the new space. He said he is going to have to go through quite a few combinations/permutations on figuring out the best configuration for what data goes on the old vs. new to get the optimal space.
Is this something that can be modeled out? I can write something that recursively figures it out, but why not explore fun ideas with tools that may be able to do it?
Thank you in advance,
John Hennesey
View 3 Replies
View Related
Jan 8, 2008
Hello guy!
I wrote a stored procedure that searches for user on my table depending on the search option chosen by the person doing the search. But my challenge is displaying users without a picture. On my members table, i used -1 to indicate a user that doesn't have or set his/her picture, this makes it difficult for me to track that person when the person searching chooses to display members with or without pictures. What happen is that my inner join of the photos table dat reference the user primarypictureid does not exist in the photos table. How do i overcome this challenge. My script
CREATE PROCEDURE [dbo].[spSearchMember]
@username nvarchar(30),
@searchfor char(1),
@firstage int,
@secondage int,
@countryid varchar(50),
@withpicture nvarchar(5)
AS
IF(@withpicture = 'False')
SET @withpicture = '%[0-9]'
IF (@withpicture = 'True')
SET @withpicture = '%[0-9][^-]'
IF(@countryId = '-1')
SET @countryid = '%'
BEGIN
SELECT m.UserName, m.MemberId, m.Gender, m.PrimaryPictureId, DATEDIFF(year,m.BirthDate,GETDATE()) AS 'Age',
c.CountryName, p.PicFileName
FROM Members AS m
INNER JOIN Photos AS p
ON m.PrimaryPictureId = p.PictureId
INNER JOIN Countries AS c
ON m.CountryId = c.CountryId
WHERE (m.Gender = @searchfor) AND (DATEDIFF(year,m.BirthDate,GETDATE()) BETWEEN @firstage AND @secondage)
AND (m.CountryId LIKE @countryid) AND (p.PictureId LIKE @withpicture) AND (m.UserName <> @username)
ORDER BY m.BirthDate ASC
END
Thank!!!!!
View 5 Replies
View Related
Aug 26, 2005
I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe.
Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the “Where…� clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields.
My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute.
The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = “Anna�; LastName = “Williams� returns result in 0.01 sec. Searching for FirstName “Benjamin�; LastName = “Watson� returns in 5 minutes.
Any kind of help is welcome and will be highly appreciated.
- Santy
san.rely@gmail.comNote: Edited to fix white on white font.
View 1 Replies
View Related
Jun 14, 2001
I have a query that I am trying to optimize. It works on some 9000 records and runs too slow. What the query does is takes the multiple assignment of a single contact record to multiple attributes (a.k.a many-to-many). For example:
Membership table.
Contact_ID
1
2
3
Relate table
1 1
1 3
1 4
Relate Item
1 item1
2 item2
3 item3
4 item4
The query will take all ocurrences of the related items and place them in a single field while delimiting by comma "item1" , "item3", "item4"
Here is the query as it exists now:
select
CONTACT_ID,
UNION_NAME
into #tmp
from MEM_UN MU
inner join MEM_UN_REL MUR
on MU.UNION_ID = MUR.UNION_ID
order by CONTACT_ID, UNION_NAME
create TABLE #unionlist (
CONTACT_ID int primary key,
UNIONS varchar(2000) null)
insert into #unionlist (CONTACT_ID, UNIONS)
select distinct CONTACT_ID, UNIONS = '' from MEMBERSHIP
while exists(select CONTACT_ID from #tmp)
BEGIN
update #unionlist
set UNIONS = UNIONS + '"' + (
select min(UNION_NAME) from #tmp
where #unionlist.CONTACT_ID = #tmp.CONTACT_ID
) + '",'
where CONTACT_ID in (select CONTACT_ID from #tmp)
update #unionlist
set UNIONS = UNIONS + '"",'
where CONTACT_ID not in (select CONTACT_ID FROM #tmp)
delete FROM #tmp where UNION_NAME in (
select min(UNION_NAME) from #tmp tmp2
where #tmp.CONTACT_ID = tmp2.CONTACT_ID
)
END
I believe that the slow down is in the process of deleting from #tmp every time it loops through the recordset.
Any suggestions appreciated,
Thx,
Dave
View 1 Replies
View Related
Mar 27, 1999
On march 23, Micorosoft was supposed to benchmarch their SQL server 7.0 versus Oracle 8i. I didn't watch the benchmark... I figured that I would be able to read about it in the news. But it seems like there is some moritorium on the subject. I haven't found any result information whatsoever.
Does anybody know who won the challenge? Did microsoft win $1 million from oracle?
Tom Mack, MCSE
Database Administrator
Advancia Corporation
View 2 Replies
View Related
Sep 7, 2004
Hello Everybody,
I am attaching a picture of what the table should look like before and after the transformation. Can anybody help? Thank you in advance.
View 2 Replies
View Related
Nov 9, 2004
Hi guys!
Can anyone tell how I can parse the WHERE clause of an SQL statement to check for special characters such as ''' (single quotes) in fields of type varchar?
thanks
nelo
View 2 Replies
View Related
Apr 7, 2006
Well i wanted to prove to some guys that cursors are not really that important:shocked: .
:D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ).
I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .
--trashtable for real data
create table abc
(col1 tinyint,
col2 tinyint,
col3 tinyint)
--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)
--check that there are ten rows
select * from abc
--check that there are only five distinct rows
select distinct * from abc
--run code : next 15 line as a batch
declare @lp tinyint
declare @col1 tinyint,@col2 tinyint,@col3 tinyint
set @lp=1
while @lp>0
begin
if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1)
set @lp=0
else
begin
select top 1 @col1 = col1,@col2 = col2,@col3 = col3 from abc group by col1,col2,col3 having count(col1)>1
delete from abc where col1=@col1 and col2=@col2 and col3=@col3
insert into abc values(@col1,@col2,@col3)
end
end
--only distinct values left in trash table
select * from abc
--think code can be optimized
--just wanted to prove: can be done without cursors or temporary tables
View 3 Replies
View Related
Jun 16, 2008
I have a database with three tables: Employees, Assets, and Recovery.
I have the following SELECT statement:
SELECT Employees.EmployeeID, SUM(Assets.Amount) AS [Case Value:], SUM(Recovery.Recovery) AS [Recovery:]
FROM Assets INNER JOIN
Employees ON Assets.EmployeeID = Employees.EmployeeID INNER JOIN
Recovery ON Employees.EmployeeID = Recovery.EmployeeID
GROUP BY Employees.EmployeeID
Here is the challenge:
You will always have data in the Employee table, but not neccesarily in the Assets or Recovery table.
If there are no Assets or Recovery I would still like the query to show 0.00 for the Assets or Recovery for each Employees.EmployeesID
With the above SELECT statement it will only return values that have data created in each of the three tables.
How do you modify the SELECT statement so a row is returned for every Employees.EmployeeID?
Any help will be appreciated!
losstww
View 7 Replies
View Related
Sep 19, 2005
Here is the table:CREATE TABLE [child]([pk_child_id] [int] NOT NULL ,[fk_parent_id] [int] NOT NULL ,[code] [char] (2)NOT NULL ,[dt] [datetime] NOT NULL ,[newcode] [int] NULL)There is a situation where there will be more than one record with thesame [fk_parent_id] value, but different values for the [code]field.If one of those records has a [code]= 5, but the [dt] is AFTER asimilar record where [code]= 6 or [code]= 7 (but same [fk_parent_id]value), I need to set [newcode] = 10. How can I pull this off? Again,the group of records can have different [code] values, different [dt]values, but a common [fk_parent_id].Help!
View 4 Replies
View Related
Jun 6, 2006
This code is attempting to find records that have a RegJrnID that doesnot occur more than one time in the table.The reason that I want to find records with non-duplicated RegJrnIDvalues is to create "reversal" records for these such that the reversalrecord has identical values for every column except the TaxableAmountwhich will contain a negative amount. (see: example data below)./* Set up */CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumberVARCHAR(20), TaxableAmount DECIMAL(32,8))/* Example data */INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)/* Show what's in the table - just because */SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate/* Query for records to reverse */SELECT *FROM t1 a/* Ignore records that have already been reversed */WHERE a.RegJrnID != ALL/* This subselect finds reversed records (i.e. those that have aduplicate RegJrnID) */(SELECT b.RegJrnIDFROM t1 bGROUP BY b.RegJrnIDHAVING COUNT(*) > 1)/* User selection criteria are appended here *//* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' *//* Make the results look pretty (optional) */ORDER BY RegJrnID/* Housekeeping */DROP TABLE t1
View 19 Replies
View Related
Aug 9, 2006
Hello Expert,Here I am asking your help.I have a table with following data:TaskID ParentTaskID TaskName ProjectName1 1 BA Rail2 22 FA Financial3 1 BA.1 Rail4 1 BA.2 Rail5 22 FA.1 Financial6 22 FA.2 FinancialNow I want the following format:ID ParentID Name1 1 Rail2 1 BA3 2 BA.14 2 BA.25 22 Financial6 5 FA.17 5 FA.2I need to create following hierarchy if I could tranform the data theabove way in the Project Dimension:Rail--BA----BA.1----BA.2Financial--FA----FA.1----FA.2Please help and thanks in advance,Soumya
View 2 Replies
View Related
Apr 15, 2008
Hi,
I have created a report with the following input parameters
FromDate (mandatory)
ToDate (mandatory)
Installation (mandatory, Multi Value)
Code (mandatory, Multi Value)
SearchCriteria1 (Allow Null Value)
SearchCriteria2 (Allow Null Value)
The Search criteria input parameters allow the user to search for occurences of keywords in the comments field.
The query looks like this:
SELECT installation, day_, code, from_time, to_time, commentFROM LogWHERE code IN (@Code) AND day_ TO BETWEEN @FromDate AND @ToDate AND installation IN (@Installation) AND UPPER(comment) LIKE ('%'+UPPER(@SearchCriteria1)+'%') OR UPPER(comment) LIKE ('%'+UPPER(@SearchCriteria2)+'%')
The problem is it does not work when both the Search criterias are null. I have searched the web for solutions, but not found it. I came across the use of ISNULL(@parameter, field), but in conjunction with = and not LIKE. Tried to alter the last two lines of my query to
UPPER(comment) LIKE ('%'+UPPER(ISNULL(@SearchCriteria1,comment))+'%') OR UPPER(comment) LIKE ('%'+UPPER(ISNULL(@SearchCriteria2,comment))+'%')
and now it seems to run forever until it fails (approx. 20 minutes). If I specify a keyword in one or both of the search criterias it returns the result set within 30 seconds.
Would appreciate som help here.
View 2 Replies
View Related
Nov 11, 2007
Im calling all the TSQL gurus to help me with this problem.
Tables:
Contracts (ContractID,Lastname, Firstname, ContractValue, Date Created
Amendments (AmmendmentID, ContractID, Lastname, Firstname, ContractValue, EffectivityDate, DateCreated)
Amendments table is related to contracts thru ContractID.
Changes to the contract table is made by creating a record to the ammendments table. when this
amendment becomes effective it should take effect on the contract. When correcting the contractvalue
the user must input the amount to be added or subtracted.
For Example:
Current Contract: C1, PETRELLI, PITER, 150.00, created on Jan. 01, 2007
Ammendment: A1, C1, PETRELLI, PETER, 20.00, effective on Jan. 20, 2007, created on Jan. 10, 2007
Ammendment: A2, C1, PETRELLI, PETER, 15.00, effective on Mar. 01, 2007, Created on Feb. 01, 2007
The currect Contract:
As of Feb.01, 2007: C1, PETRELLI, PETER, 170.00
As of Mar.05, 2007: C1, PETRELLI, PETER, 185.00
The Challenge here is this:
How can you make an SQL VIEW that will show the list of Current Contractsand use this view to create a stored procedure (with a parameter @CurrentDate)?
The SP will show the current Contracts based on the supplied parameter.
Example:
ShowCurrentContracts(Jan. 05, 2007)
Current Contract: C1, PETRELLI, PITER, 150.00
ShowCurrentContracts(Feb. 05, 2007)
Current Contract: C1, PETRELLI, PETER, 170.00
ShowCurrentContracts(Jun. 05, 2007)
Current Contract: C1, PETRELLI, PETER, 185.00
View 11 Replies
View Related
May 26, 2008
All of you just read this article
http://www.bloombit.com/Articles/2008/05/ASCII-Encoded-Binary-String-Automated-SQL-Injection.aspx
View 1 Replies
View Related
