A standard IN operation is like doing a series of OR statements in your WHERE clause. Is there anything like an IN statement that is like using a series of AND statements instead?
I tried looking into the ALL operator but that didn't seem to do it or else I just couldn't figure out how to use it correctly.
I'm using SQL Server 2012 Analysis services in Tabular mode and connected to Oracle Database and while importing, I'm getting below error after importing some rows.
OLE DB or ODBC error: Accessor is not a parameter accessor.. The current operation was cancelled because another operation in the transaction failed.
i dont know why this has slipped my mind but how would i say,
if @test = 'a' or @test = 'b' or @test = 'c'
in a shortted way?
Hello.I have a select that returns some SubCategories.SELECT SubCategoryID from SubCategories SCATINNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryIdWHERE SCAT.ParentCategoryId = X Now i will to retrieve all rows from a Table called Items Where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.What code i need to write toI think .. Select * from Items AS IT WHERE IT.Subcategory = ???I don't know, anyone can help meThanks
View 4 Replies View RelatedHi. I am new to SQL.I hope you veteran out there to help me solve the simple problem i met.
CREATE TABLE BASKET(
B# NUMBER(6) NOT NULL,
ITEM VARCHAR(6) NOT NULL,
CONSTRAINT BASKET_PKEY PRIMARY KEY(B#, ITEM) );
My statement
SELECT DISTINCT L1.ITEM, L2.ITEM,COUNT(L1.B#)
FROM BASKET L1 ,BASKET L2
WHERE L1.B# = L2.B#
AND L1.ITEM <> L2.ITEM
GROUP BY L1.ITEM,L2.ITEM;
the result is
ITEM ITEM COUNT(L1.B#)
------ ------ ------------
BEER MILK 5
BEER BREAD 4
BEER BUTTER 2
MILK BEER 5
MILK BREAD 6
MILK BUTTER 5
BREAD BEER 4
BREAD MILK 6
BREAD BUTTER 5
BUTTER BEER 2
BUTTER MILK 5
BUTTER BREAD 5
The problem is how to get rid those repeating group like (BEER,MILK) and (MILK,BREAD)?
Hello.
I have a select that returns some SubCategories.
SELECT SubCategoryID from SubCategories SCAT
INNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryId
WHERE SCAT.ParentCategoryId = X
Now i will to retrieve all rows from a Table called Items where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.
What code i need to write to ?
I think .. Select * from Items WHERE Items.Subcategory = ???
I don't know what code i need to type, anyone can help me
Thanks
Hi...
Need help with some SQL-code:
Im just interesting in how many rows in my table 'Location' that has 'New York' in the column called 'City'....
So I just want to return the number of rows that is macthing...
How do I write the sql-part for this??
I'm currently working on a match function to compare two char based columns in differnet tables to create a join.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
Thanks in advance
Hello. I'm quite new to SQL so have included as many details as i can think of here. The scenario is a wordsearch style puzzle. The user can select their answers in any order and these answers are stored in a table. I need help with the UPDATE statement to compare the users answers against the correct answers for that puzzle.
(Note: In the actual scenario there will be 10-15 answers per grid, but i have reduced the number to make testing easier - hopefully the code for a working UPDATE statement will be scalable to account for grids with different numbers of answers etc.)
The Tables:
-- These are the correct answers for a given grid (gridid).
-- Due to the nature of the puzzle, answers can be in any order.
-- Each level may contain one,two,or no 'bonusanswers' which are harder to find, so these are scored separately so bonus points can be awarded.
CREATE TABLE correctanswers
(
gridid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
answer1 char(15),
answer2 char(15),
bonusanswer1 char(15),
bonusanswer2 char(15)
)
-- These are the user submitted set of answers 'answerid' for level 'gridid'.
-- Answers may be submitted in any order.
CREATE TABLE useranswers
(
answerid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
gridid smallint,
firstanswer char(15),
secondanswer char(15),
thirdanswer char(15),
fourthanswer char(15),
)
-- A user (userid) submits their answers which get stored as answerid.
-- This table shows the scores for each set of answerid's the user has submitted.
-- A high score table for both the individual user, and all users may be created using this table.
CREATE TABLE userscores
(
userid smallint,
answerid smallint,
mainmatches smallint,
bonusmatches smallint
)
The Test Data:
-- sample test data
-- 2 users userid's '1' and '2' each have two goes on level1 (gridid 1)
-- correct answers for gridid 1
INSERT INTO correctanswers (answer1, answer2, bonusanswer1, bonusanswer2) VALUES ('cat','dog','rabbit','elephant')
-- user submitted answers for gridid 1
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','rabbit','horse','cow')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','cat','elephant','horse')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'rabbit','cat','elephant','donkey')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'horse','cat','dog','sheep')
-- scores for users attempts - columns 3 and 4 needs calculating
INSERT INTO userscores VALUES (1,1,null,null) -- one main answer and one bonus answer, so null,null should be 1,1
INSERT INTO userscores VALUES (1,2,null,null) -- two main answers and one bonus answer, so null,null should be 2,1
INSERT INTO userscores VALUES (2,3,null,null) -- one main answer and two bonus answers, so null,null should be 1,2
INSERT INTO userscores VALUES (2,4,null,null) -- two main answers and no bonus answers, so null,null should be 2,0
I have included the correct new table values for the sample data - basically filling in the two null fields in the 'userscores' table.
I haven't used SQL much but from the little i know then i think the answer will include JOIN, COUNT and IN statements as part of the UPDATE statement...but i haven't a clue where to start with the order/logic etc.
I have looked for sample solutions myself but all the examples i have found so far are to do with exact matches between two tables, whereas in my scenario i need to know how many matches, irrelevant of the order.
Many thanks.
Roger
Hi all,I have 2 files containing Id numbers and surnames (these filesessentially contain the same data) I want to select distinct() andjoin on id number to return a recordset containing every individuallisted in both the files HOWEVER, in some cases an incomplete IDnumber has been collected into one of the 2 files -is there a way tojoin on partial matches not just identical records in the same way asyou can select where LIKE '%blah, blah%'??Is hash joining an option i should investigate?TIAMark
View 4 Replies View RelatedI am pretty new to writing my own queries in SQL and am tied to Access unfortunately. Here is my query as it stands:
SELECT Count(*) AS COUNT_ACT_2_ATTEND
FROM [Main Table]
WHERE ((([Main Table].ACT_2_ATTEND)="Always" Or ([Main Table].ACT_2_ATTEND)="Frequently" Or ([Main Table].ACT_2_ATTEND)="Sometimes" Or ([Main Table].ACT_2_ATTEND)="Never" Or ([Main Table].ACT_2_ATTEND)="N/A"))
GROUP BY [Main Table].ACT_2_ATTEND;
However, if there is not match it won't retun a zero, it just returns no result and so I cannot tell which counts correspond to which matches
Does that makes sense?
Please indulge my ignorance, as I have only been using SSIS for a couple of weeks.
I'm trying to create a data warehouse using two input tables.
A column needs to be added to one table by using a lookup into the second table.
SSIS seems to handle the "no matches" and "single match" cases perfectly.
I can't for the life of me figure out how to properly handle multiple matches.
SSIS defaults to the first match, but I need to compute the "best" match.
Many thanks in advance
Scott!
I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches.
This is my code:
select d.lwia,
cast((d.closegoal + d1.opengoal) as float)denominator
from
(
select yg.lwia,
cast(count(yg.appid)as float) closegoal
from dbo.wiayouthgoals yg
where yg.lwia = @RWB
-- Attained a goal in the timeframe timely or untimely
and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @EndDte) -- Parm date for end of time frame needed
-- Goal due but not attained
or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @EndDte -- Parm date for end of time frame needed
and yg.youthattaingoal <> 1))
group by yg.lwia
)d,
(
-- Closure with open goal
select cast(count(yg.appid)as float) opengoal
from dbo.tbl_caseclosure cc,
dbo.wiayouthgoals yg
where yg.appid = cc.col_idnum
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @EndDte -- Parm date for end of time frame needed
and yg.youthattaindate is null
and yg.lwia = @RWB
group by yg.lwia
)d1
)d2
Hi group,
This morning we had an issue with a simple join between 2 tables e.g. table1 and table2.
Both tables have 1 column called 'fielda' with datatype varchar.
Let's assume the following values:
Table1:
hello
Hello
Table2:
hello
This statement joins the tables:
select a.fielda
from table1 a inner join table2 b on a.fielda = b.fielda
It returns:
hello
Hello
????????????????????????????????????????????????????????
What the $#@#$
Why doesn't it return 1 value:
hello
Any ideas?
The outcome is exactly what we want but I would expected to have to use the following:
select a.fielda
from table1 a inner join table2 b on UPPER(a.fielda) = UPPER(b.fielda)
What if I wanted to return all EXACT matches? Then what? Change collation?
Basically I have a table of users (id, name), a lookup table preferences (id, title) and the table of user-pref (idUser, idPref).Given a user id X I wanted to select a list of users, ordered by the highest amount of coincidence in the preference table...
something like
SELECT * FROM users WHERE id <> X ORDER BY -number of common preferences-
I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.
The problem is that when I run my compare I get false matches. Example:
CREATE TABLE #ABC (Acct VARCHAR(10), Que INT);
INSERT INTO #ABC VALUES
('2310947',110),
('2310947',245);
[Code] ....
Which gives me two records when I really do not want any as the tables are identical.
Hello there,
I have the following table:
ROOMTYPE AMENITY
========= =======
R001 1
R001 2
R001 3
R002 1
R002 2
R002 4
R003 1
Let's say I want to get the ROOMTYPE which contains AMENITY 1,2,4 only.
If I do this:
SELECT ROOMTYPE FROM TABLE WHERE AMENITY IN (1,2,4)
I get all the 3 RoomTypes because the IN acts like an OR but I want to get the column which contains ALL of the items in the list, i.e I want the ROOMTYPE that has ALL of the 1,2,4 but not just 1 or 1,2, etc...In this case, I want only R002 to be returned because the other RoomTypes do not contain all of the 1,2,4
NOTE: The data and list above is an example only as the table contents as well as the list will change over due course. e.g. the list can be (2,6,8,10,20,..100) etc.. So, I would need a query which can cater for any list contents...(x,y,z,...) and the query should return me the RoomTypes which have ALL elements in that particular list. If one of the RoomTypes do not have an element, it should NOT be returned.
Can anyone help me on this?
Kush
If it possible to have an if statement match multiple results, as to not have to use the OR multiple times.
Example: I want to say, if Description equals red or blue or green or yellow or orange or black or white or pink, without having to use OR and OR and OR. Description can match 10 different values.
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile).
MATCHED: (email, firstname, mobile)
But not MATCH: (lastname, phone)
MATCHED: (email, firstname, lastname)
But not MATCH: (phone, mobile)
Has anyone find solution for this problem.
i also checked
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=298056&SiteID=1 and
http://blogs.conchango.com/kristianwedberg/archive/2006/02/22/2955.aspx
Suppose have a Dimension table
DimColor
----------------------------
ColorKeyPK(smallint) ColorAlternateKey(nvarchar(30))
-1 UnknownMember
1
2 Blue
3 Red
4 Black
Color with the ID 1 is empty string
FactOrders
---------------------------
OrderID Date Color Quantity
OrderID = 1 Color = 'Black' Quantity = 10
OrderID = 2 Color = 'Red' Quantity = 20
OrderID = 3 Color = '' Quantity = 10
OrderID = 4 Color = 'Blue' Quantity = 5
OrderID = 5 Color = Black Quantity = 10
When i use the Lookup transform it cannot find the ColorKeyPK
The result of the Lookup transform is.
------------------------------
OrderID = 1 Color='Black' ColorKey=4
OrderID = 2 Color='Black' ColorKey=3
OrderID = 3 Color='Black' ColorKey=NULL ----> This is the problem Lookup cannot find empty string. It should be 1.
OrderID = 4 Color='Black' ColorKey=2
OrderID = 5 Color='Black' ColorKey=4
Thanks from now.
Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.
Note: The query has to run in Access 2000 and I will be coding it in VB.
SELECT Orders.[Orders ID],
[Orders Subtable].ID,
[Orders Subtable].Quantity,
Receiving.Quantity,
Receiving.[Component #]
FROM (Orders
LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID])
INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]
GROUP BY Orders.[Orders ID], [Orders Subtable].ID,
[Orders Subtable].Quantity, Receiving.Quantity,
Orders.[Project #], [Orders Subtable].On_Order,
[Orders Subtable].[Component #],
Receiving.[Component #]
HAVING (((Orders.[Project #])="Speed1aaaaa") AND
(([Orders Subtable].On_Order)=True) AND
(([Orders Subtable].[Component #])="R02101A") AND
((Receiving.[Component #])="R02101A"));
I have two tables:
Club - Stores all clubs (id_club, name)
Match - Store all matches (id_club1, id_club2, dateMatch, result)
The match has club 1 and club 2.
And I Have to select all matches that clubs played with an interval less than three days between games.
This is my code:
SELECT DISTINCT a.*
FROM
Matches a
INNER JOIN
Matches b ON
[Code] ....
I tried too:
select *, DATEDIFF(day, m1.date, j2.data) from matches m1, matches m2
where abs(DATEDIFF(day, m1.dateMatch, m2.dateMatch))<3
and (m1.id_club1=m2.id_club2)
But it doesn't working. Because I have two clubs in a row. It's so difficult.
Hi,
While I am trying to install SQL server 2005 I get the following message - "Machine does not have a product that matches the installation package". The installation does not occur.
I am using Windows 2000 Professional SP4 and using 32 bit installer - SQLServer2005SP2-KB921896-x86-ENU.exe
Help would be appreciated... thanks.
Regards,
Ravindranath Kini
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301
302
303
304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code:
Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0"
For i = 1 To classIDs.Count - 1
assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString
Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
This is what I wan to do: If I, for example, have a table containing articles, and I want to allow users to return any article that matches a filter, which is defined by any number of keywords, located in another table.
My tables:
Article table: [ArticleID, Headline, Content]
Filter table: [FilterID, Name]
Keyword table: [KeywordID, Word]
FiltersKeywords table: [FilterID, KeywordID]
(The FiltersKeywords table serves as a many-to-many relation table between the Keyword table and the Filter table)
In other words, a filter, which is used to selected articles with, can be made up of any number of keywords. The users would thus be able to build on the filters, adding new keywords as time goes, removing old none relevant etc. I suspect some filters could end having anywhere from 100 to 1000 keywords associated with them.
Using SQL 2005, is there anyway to query for articles in an efficient manner? How would the code look?
Thanks, Egil.
I am trying to clean up an old database someone developed. I took the data and grouped the information that matched
I then imported it into a trips table. The matching data is all in the where statement. It shrunk 16k records for just under 2k records. Now I am trying to right a query to relate the 2 tables and update the tripid# to the triplist of people who actually attended.
Declare @Trip varchar(500)
Declare @Destination Varchar(500)
Declare @LeaveDate varchar(500)
Declare @LeaveTime Varchar(500)
Declare @ReturnDate varchar(500)
Declare @ReturnTime varchar(500)
Declare @Comment varchar(500)
Declare @RescheduleDate Varchar(500)
Declare @Rec int
Select @Rec = Rec# From Trips
Select @Trip = Trip from [Trips] where Trips.rec# = @Rec
Select @Destination = Destination from [Trips] where Trips.rec# = @Rec
Select @LeaveDate = LeaveDate from [Trips] where Trips.rec# = @Rec
Select @LeaveTime = LeaveTime from [Trips] where Trips.rec# = @Rec
Select @ReturnDate = ReturnDate from [Trips] where Trips.rec# = @Rec
Select @ReturnTime = ReturnTime from [Trips] where Trips.rec# = @Rec
Select @Comment = Comment from [Trips] where Trips.rec# = @Rec
Select @RescheduleDate = RescheduleDate from [Trips] where Trips.rec# = @Rec
Update TripList
SET tripid# = @Rec
WHERE (Trip = @Trip) and (Destination = @Destination) and (LeaveDate = @LeaveDate) and (LeaveTime = @LeaveTime) and (ReturnDate = @ReturnDate) and (ReturnTime = @ReturnTime) and (Comment = @Comment) and (RescheduleDate = @RescheduleDate)
I am running SQL Server 7. I have an user that need to run the Set Identity On a table, and he gets an error message:
Server: Msg 8104. The current user is not the database or object owner of table 'tpartners'. Cannot perform SET operation.
I can grant this user with DBO user role, then he can edit anyting on the database.
Is there a work around that he can run the Set cmd and I don't need to grant him the DBO user role?
HiI have a database with full recovery model.Every night at 11.00pm performs full backup, and dayly every 3 hoursperforms differential backup, and trasnactional log backup performsevery 15 minutes during a day.I am interested what operations i can not recover from log ordifferential backup.I saw that when i drop table i can recover it from log backup, but isthere some operation that can not be recoverThanks a lotAlex*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedDear friends, I was not able to find any perfect toolbox item to use "not in" operation on my selected columns, also how can we use multiple join conditions in the merge join item? Thanks,
View 1 Replies View RelatedWhy do I get the message "Operation can't be completed?" when I try to save a stored procedure!!! When I create a new stored procedure and copy the code into it it works fine!
View 2 Replies View RelatedI have a sp that creates a #temp table and performs a bulk insert.
CREATE TABLE #template (template varchar(8000))
EXEC ('bulk INSERT #template FROM "' + @filename + '"')
SET @html = (SELECT template FROM #template)
DROP TABLE #template
...
When I access this sp with any other user than 'sa' I get this error message:
The current user is not the database or object owner of table '#template'. Cannot perform SET operation.
I've been everywhere (with no success) in the sql srv admin to look for the appropriate check box to allow another named user to access this sp.
Can anyone help?
On SQL 2k, How can I set my DTS package, which imports a 3 million row .TXT file to a table, as a NON-LOGGED operation?
(LOGGING is taking tooooo long!! I will be sure to run a full DB backup when the job completes.)
thx-
BT
If tables are in relation (foreign key), then how should one proceed for deletion of a record from a master table?
how will the database itself tackle the problem? What should be done from the front end for this?
please help.......I urgently require a solution for this.