New Challenge To Improve Searching Method!
Aug 20, 2007
Dear frenz,
One of the department in my company having data searching and responding in slow time problem. I will eloborate the current system.
Problem description:
====================
There is 4 lanes. Note :
Our focus here is only to one lane!
each lane will have 4 machines.Each machines will have local database to its corresponding sub machines. All the information from the sub machine's local database server will then finally pass all the data to the main database server after 2 weeks.
Within two weeks, one table may grow upto 600 000 rows of records and search record time from all the sub database server is varied depend upon phase by phase.
Phase by phase:
===============
Assume 4 machines label, A,B,C and D respectively. The user from location A will check one item and scan the item's barcode to get info from the db and will update the 'result' field from table in stage A whether past / fail and also update the current process whether its process status = started/halted/interrupted/succefully completed/canceled.
Assume each table [all the tables are identitical version]in 4 sub database server will now having 600 000 rows respectively.When the User A scan the barcode from that particular Item belongs to group 1.1, it will search from database A and display info. the respon time is quite ok since not much filtering in searching method.
When another items belongs to group 1.1 as well, is scanned by user B then it will search the info from database A, and displaythe item's [group 1.1] status and process status from stage A.The respond time here will be slower since it will check from db A. Same scenario will apply when any item from the similar group items' being scanned in db C and db D and check if that particular item is passed in previous stage before proceed to its current phase[A,B,C & D].
Currently, the user will have to wait for few seconds to get the info when the db is growing each time and due to this they have to clear the table and send to main database server once in 2 weeks.We hope can keep for atleast 1 month but it is delaying few seconds per scan and the respond times are varied for phase A, B ,C and D.D is slowest since it needs to check in A,B and C before it accept in its own phase which is D.
I am trying to propose to use SQL Server 2005 and try re-design the current structure in each lanes.
I am now suppose to state in my paper proposal, that one each server for roughly 600k rows contents, if apply search and use stored procedure to habndle it and return the output then what is the average time for all the 4 machines.if can, better specify by each stage one how many miliseconds/seconds will be utilized. Please someone whom have face similar case or come accross to new method , help me suggest new way of solving this problem.Also plz give me an accurate time if can for each phases as I need to specify in my proposal documents. Can someone give a accurate time for each stage also a better solution for re-designing process and estimate a new respon time.
realy appreciated , dear my frens.I trust you are genius and chosen from around the world.;) Im sure someone would have done something miracle...lol
Im in deep trouble as I have to submit the proposal in 2 days times.
Thanks a lot!!!
View 1 Replies
ADVERTISEMENT
May 10, 2007
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies
View Related
Jan 29, 2008
Hi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
View 7 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
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
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
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
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
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 8, 2002
Hi,
tell me please how I can trace the modification on the table such as "insert" record into one and syncronize mirror table at the same time once the insert has happend, BUT - no indexes no trace jobs, no any modification or objects on the master table... ha?
View 5 Replies
View Related
Oct 27, 2001
Help! We have a third party product that feeds data into an MS SQL 2000 database. We are trying to replicate this data using merge replication. On the server where the data is replicated, we can read this data but we can't write to the database using the third party product because of the addition of the unique identifiers associated with the replication. Is there any way to "trick" the third party product into believing that they are still accessing data from a non-replicated database????
View 1 Replies
View Related
Nov 17, 2004
I am not sure if select recursion is possible and thought I would throw this challenge to the dba community. The preference would be to create a view that does the work on the backend instead of writing frontend VB code.
Below is script that creates and populates a temp table along with the desired result-set.
create table #myTest
([id] int identity (1,1),
[Parent] int ,
minutes smallint
)
insert into #myTest (parent,minutes) values (null,1)
insert into #myTest (parent,minutes) values (1,2)
insert into #myTest (parent,minutes) values (1,4)
insert into #myTest (parent,minutes) values (3,8)
Desired resultset:
id Parent Totalminutes
----------- ----------- -------
1 NULL 15
2 1 2
3 1 12
4 3 8
View 4 Replies
View Related
May 5, 2006
Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.
Here is sample code for setting up the problem:create table #blindman
(pkey smallint primary key,
fkey char(1),
updateddatetime)
insert into #blindman (pkey, fkey, updated)
select1, 'A', '1/1/2006'
UNION
select2, 'B', '1/1/2006'
UNION
select3, 'A', '1/2/2006'
UNION
select4, 'B', '1/2/2006'
UNION
select5, 'A', '1/4/2006'
UNION
select6, 'B', '1/2/2006'
UNION
select7, 'A', '1/3/2006'
UNION
select8, 'B', '1/3/2006'
UNION
select9, 'A', '1/5/2006'
UNION
select10, 'B', '1/5/2006'
drop table #blindman
Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
Here is the desired output for the sample data:pkey fkey updated nextpkey
------ ---- ---------- --------
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
2 B 2006-01-01 4
4 B 2006-01-02 6
6 B 2006-01-02 8
8 B 2006-01-03 10
Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
Ideally, I want this as a VIEW.
Who's up for the challenge?
View 14 Replies
View Related
Jul 26, 2007
This is killing, me and I think that I'm failing to see something simple here:
If I have a table with logins and datetimes. I need to output any logins that have logged in more than 3 times in any 3 hour period of time, and how may times it was done. For example:
Login table:
user1 01:00
user2 01:13
user1 02:32
user2 01:17
user1 01:12
user2 07:00
user1 04:10
I would need:
user1 2 <-- (times user 1 logged in more than 3 times in 3 hours)
Because:
01:00, 02:32, 01:12 are all within 3 hours of each other
02:32, 01:12, 04:10 are all within 3 hours of each other
Obviously I have alot more data than this, but I'm failing to grasp the logic properly. Trying to do this in a Sybase stored proc.
View 3 Replies
View Related
Apr 29, 2004
I'm having problems with the query below. It works fine until the "NOT IN" part and I'm not sure why. Basically, I'm getting records where their educ_audio field is set to "no". I've messed with the parens to try to force SQL to process the NOT IN part before the other clauses without avail. Can someone shed some light?
TIA
SELECT distinct contacts.fname, contacts.lname, contacts.company, contacts.contact_id, contacts.business_phone, contacts.emailAddress, contacts.dateLastContact FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON journal.product_code = products.product_code WHERE ( journal.product_code IN ('ABLE') ) OR (( journal.product_code IN ('JOBS') ) AND ( journal.product_status IN ('12','14','15') )) OR (( products.prod_design IN ('audio') ) AND ( products.library_code IN ('hrss') )) AND (journal.journal_id NOT IN (SELECT journal.journal_id FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON products.product_code = journal.product_code where ( contacts.educ_audio IN ('no') ) )) ORDER BY contacts.lname asc, contacts.fname
View 3 Replies
View Related
Apr 30, 2004
Ok .. a challenge ...
How do you swap two columns based on some condition without using a cursor ( I mean a set based solution).
View 12 Replies
View Related
Oct 27, 2005
I have a sql query in which I need to isolate part of the columm valueand return only that isolated portion. I can only do this within theselect statement, and cannot add a function or anything like that. Iwould also like to keep this query within sql (I don't want to do thisin my programming environment)The string value would normally look like "segment1-segment2-segment3".I need to isolate segment2, but I have to be able to account forsituations in which either one or both dashes are missing (in whichcase returning "" or the whole string is OK. The best I have been ableto do reliably is to get "segment2-segment3".Anybody want to take a stab?
View 4 Replies
View Related
Oct 31, 2006
I am having a problem with the installation of SQL Express. the uninstall program says that part of the Beta is still installed. then the uninstall program crashes with a syntax error saying that in wrong place. found it deleted it and then the program will not continue. i cant find any remnants of the beta but SQL Server express will not install!! Please help.
View 2 Replies
View Related
Nov 27, 2007
I have a text file that is exported from an application and comes in a specific format. I would like to use BULK INSERT in order to load it into my own SQL 2005 database. The trick is that the file contains several different record lengths/layouts. These record layouts are identified by a specific string in column1 of each row in the text file. I would like if possible to bulk insert each specific record/row type into a seperate table.
For example, if the first column in a row contains the string "X1" I know this row contain will contain 7 tab delimited columns. As such, I would like to be able to import only rows which contain an "X1" in the first column into a specific table. If the first column in the row contains the string "X2" I know this row will contain 10 tab delimited columns. As such, I would like to be able to import only rows which contain an "X2" in the first column into a specific table. And so on and so forth. Its basically a header/detail file in a single text file.
I have not been able to find any combination of using format files with BULK INSERT or any other settings/techniques that will alow me to use BULK INSERT to achieve this.
Previously, I was using ADO from within Access to import these files by making several copies of the file first and giving them specific filenames representing each different record layout. I then had each different filename defined defined in a schema.ini file which each specific record layout. I then used a simple SQL query to retrieve for example only rows where [Column1] = "X1" when reading the X1_Filename.
Any thoughts? And please, try to keep it reasonably simple as I am not exactly an advanced user.
DougM
View 3 Replies
View Related
Nov 29, 2007
We are trying do develop a report that has 2 columns. the report is made up of many text boxes and some tables. Much of the info is in Subreports. Here is our delima... We want to keep the columns balanced. for example on the left side we have name, address, city state zip as 3 rows. in the right side we want to have a chart that is 2" tall. how do we merge rows in column 2 so we can display the chart and not impact the spacing in Column1
View 3 Replies
View Related
Aug 7, 2007
I have a questions for those familiar with the tools and work-arounds of SSRS 2005. My task is to produce a report that has page(s) consisting of three parts that are equal divided vertically amoung the 11 inches of the report height. The first section on the top section has a a check on it. The second and third sections have two seperate yet identical tables.
The trick for this is that each table (in sections part two and three) must not leak into each other's section. Essentially each section has X amount of rows that are shown on each page with the next X amount of rows on seperate pages after that. The problem, as you can most likely guess, is that you cannot force a table to continue only into it's appriopriate section on the next page. I've tried many, many combinations of tricks to try and trick SSRS into working. I know that SSRS most likely doesn't support this directly, but I'm looking for a genius or just anybody who might have a work-around. I'll update this as I find out more. Please feel free to ask for more of an explaination if needed. Thanks!
View 1 Replies
View Related
