No Predicate Join Work Around
Oct 8, 2015
I have two tables. Table A has 2162 rows and table B has 101 rows. There isn't any join keys that I can join on these two tables. But I need everything from table B and only one column (Col1) from table A. It will result in a cross join and retruns 218362 (2162 * 101) rows, which is correct. But this takes about 30 seconds to complete. Any workaround to accomplish this? Optimizer shows no predicate joins on the Nested Loop and to me it is correct.
Here is what I have so far.
SELECT B.*, A.col1
FROM TableB B
LEFT JOIN TABLE A A
ON 1 = 1
View 9 Replies
ADVERTISEMENT
Apr 21, 2008
am encountering the NO JOIN PREDICATE warning in a query plan for a particualr SQL statement. In poking around, I've come across psotings that mention the CU 4 for SQL Server 2005. The update applies to a KB article that addresses complex queries with an outer join.
But in my case, it's not an outer join causeing the problem - they are all left joins. Here is the piece of SQL that is running extremely slowly:
Code Snippet
UPDATE @TESTTABLE
SET [@TESTTABLE].SignInCancelledNoEMR =
(SELECT q.cnt FROM
(SELECT COUNT(*) AS cnt,cl.Clinic_id AS ClinicIDq,cl.CLINIC_REG_ID AS MarketIDq
FROM dbo.tblactivity AS t
LEFT JOIN [ERIIProduction].dbo.computer c on t.machineid = c.comp_windowsname
LEFT JOIN [ERIIProduction].dbo.clinic cl on cl.clinic_id = c.comp_clinic_id
LEFT JOIN [ERIIProduction].dbo.Region r ON cl.CLINIC_REG_ID = r.REG_ID
WHERE t.status = '1'
AND cast(checkindate as datetime) >= @startDate
AND cast(checkindate as datetime) < DATEADD(d, 1, @endDate)
AND cl.clinic_reg_id IN (SELECT Item FROM dbo.Split(@regionIDList,','))
AND NOT EXISTS (SELECT 1 FROM [ERIIProduction].dbo.VisitPatient vp WHERE (t.activityid = vp.VSPAT_GalvanonActivityID)) --AND (t.memberid = vp.VSPAT_GalvanonMemberID))
GROUP BY cl.Clinic_id,cl.CLINIC_REG_ID) AS q
WHERE q.ClinicIDq = ClinicID AND q.MarketIDq = MarketID)
Before I apply the CU and enable the trace flags, I'd like to know if there is something else I can do within the query itself to make it run faster. I've tried the OPTION(FORCE ORDER ) hint, but that doesn't have any effect on the queery run time.
And as a whole, the query cost relative to the entire batch jumped from 75% to 96%.
Granted the execution time of the entire batch dropped from 9 minutes to 1 minute, this query should not even take that long.
Thanks for any input...!
View 6 Replies
View Related
Oct 25, 2004
Hi, our application is failing sometimes, with some select queries. After making traces in the database, I found the following error: Missing join predicate.
I googled that, and I only found this useless tip:
Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. The fix to this is to add a join predicate.
So, I dont know what a join predicate is... maybe I used it, but I don't know it by that name.
Thanks!
View 9 Replies
View Related
Feb 7, 2008
Hi all.
I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass 80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen during very high traffic. I have isolated the query, included at the bottom of this post (cleaned up). There is very little info on this event class out on the web. Version is SQL 2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get the MJP. I would expect that a query without a JP is a query without a JP and it would be all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can’t make it happen in testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed light? Know of good links, etc?
Thanks!!
bbRichbb
SELECT
p.Product_Id,
MIN(ae.Enum_Value) AS color,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id,
COUNT(ps.SWATCH_STATUS) AS total_available_colors
FROM
Attribute_Enum_Value ae
INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id
AND ae.Attribute_Type_Id = pae.Attribute_Type_Id
INNER JOIN Product p
INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id
INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id
INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr
AND p.Product_Id = ps.PRODUCT_ID
WHERE
(pae.Attribute_Type_Id = 500001)
AND (p.Product_Class_Id = 2)
AND (p.Status_Code = 'ACTV')
AND (ps.SWATCH_STATUS = 'ACTV')
GROUP BY
p.Sequence_Number,
p.Product_Id,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id
HAVING
(p.Product_Id = 1209645)
ORDER BY
p.Sequence_Number,
p.Product_Id
View 1 Replies
View Related
Feb 7, 2008
Hi all.
I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass
80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen
during very high traffic. I have isolated the query, included at the bottom of this post
(cleaned up). There is very little info on this event class out on the web. Version is SQL
2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall
direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get
the MJP. I would expect that a query without a JP is a query without a JP and it would be
all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can€™t make it happen in
testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed
light? Know of good links, etc?
Thanks!!
bbRichbb
SELECT
p.Product_Id,
MIN(ae.Enum_Value) AS color,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id,
COUNT(ps.SWATCH_STATUS) AS total_available_colors
FROM
Attribute_Enum_Value ae
INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id
AND ae.Attribute_Type_Id = pae.Attribute_Type_Id
INNER JOIN Product p
INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id
INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id
INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr
AND p.Product_Id = ps.PRODUCT_ID
WHERE
(pae.Attribute_Type_Id = 500001)
AND (p.Product_Class_Id = 2)
AND (p.Status_Code = 'ACTV')
AND (ps.SWATCH_STATUS = 'ACTV')
GROUP BY
p.Sequence_Number,
p.Product_Id,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id
HAVING
(p.Product_Id = 1209645)
ORDER BY
p.Sequence_Number,
p.Product_Id
View 1 Replies
View Related
Jan 27, 2015
After monitoring using SQL profiler, i found that Missing join predicate event is happening a lot.
The problem is that profiler doesn't allow me to select the textdata to know which SQL statement is causing the issue.
I tried using the spid to check what's that process is running but the problem is that application is running many sqls so when i run
select PROGRAM_NAME,hostname,qt.text from sys.sysprocesses as sps1 CROSS APPLY sys.dm_exec_sql_text(sps1.sql_handle) AS qt
where spid=169
it gets me the SQL being run at that time not the one that causing the event.
View 3 Replies
View Related
Nov 20, 2006
hi ,
what is the definition and difference between predicate and residual predicate.
give me some examples..Basically the columns used in where clause are called as predicates. Am i right.
View 1 Replies
View Related
May 14, 2008
I have been changing the placement of the brackets in the SELECT after the JOIN but keet getting error messages (table "RE_1" not present, error on "ON" or befor "LEFT"). Here is the command:
SELECT TR_1.*, RE_1.*
FROM
(SELECT TR_1.ACCOUNT, TR_1.ACTIVITY,TR_1.DONOR,TR_1.COSTCENTER,SUM(CASE WHEN TR_1.TTYPE = 'U' THEN TR_1.AmountLCU*-1 ELSE 0 END) AS Budget,SUM(CASE WHEN TR_1.TTYPE <> 'U' THEN TR_1.AmountLCU*-1 ELSE 0 END) AS Actual,TR_1.TransactionDate TR_1 Date, YEAR(TR_1.TransactionDate) TR_1 Year
FROM scalaDB.dbo.A_GL0601_PREVIOUS AS TR_1
WHERE NOT (TR_1.TTYPE='' OR TR_1.TTYPE='a' OR TR_1.TTYPE='c') AND NOT (TR_1.COSTCENTER=N'' OR TR_1.COSTCENTER=N'0000') AND ((TR_1.ACCOUNT>=N'26' AND TR_1.ACCOUNT<N'7100') OR (TR_1.ACCOUNT>N'7100' AND TR_1.ACCOUNT<=N'7999'))
GROUP BY TR_1.COSTCENTER,TR_1.ACCOUNT,TR_1.ACTIVITY,TR_1.DONOR,TR_1.TransactionDate) LEFT OUTER JOIN
((SELECT RE_1.ACT RE-ACT,RE_1.DONOR RE-DONOR,RE_1.CC RE-CC,YEAR(RE_1.DTE) RE-1 Year,RE_1.SYMBOL,RE_1.DTE,RE_1.SPLIT_AMOUNT AS RE Split in CHF,RE_1.FOREIGN_AMOUNT AS RE Foreign Amnt FROM scalaDB.dbo.RE_Donor_Gift) RE_1)
ON
TR_1.COSTCENTER=RE_1.CC AND TR_1.DONOR=RE_1.DONOR AND TR_1.ACTIVITY=RE_1.ACT AND TR_1 Year=RE-1 Year
Thanks in advance
ishaame
View 10 Replies
View Related
Jul 20, 2005
I have a book catalog where some books have more than one author, henceI've made a one-to-many table where I have the book isbn matched up tothe author id.I have a table called books that has the book title, etc. and an authortable that has the author info. I join these to on the book_to_authortable that has two columns, the isbn and the author id, where they matchup.Now I want to do a search that pulls up all the titles written by oneauthor, so that if someone sees a book they like on our site, and theywant to pull up all the titles by that author, they click on the authorsname and all the titles they have written now appear on a scroll outpage.I'm confused about how to write the join statements given the threetables. So how would I write this?Thanks for your help,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
May 30, 2008
I have two tables, one is a minimal details, the other is a full details. I'm trying to join the two based on a parameter I'll be passing in later. It SHOULD return 5 rows, but instead its multiplying the entire full details table by 5, and giving me roughly 500,000 rows.
Here is my query (i is the min details, d is the full). Actually I'm not doing a join as of now, just trying to get this to work, I'll convert it later....
Code:
select i.parcel_id,i.building_number,d.parcel_id from site_info i,site_desc d where i.building_number in
(select building_number from site_desc where building_number='523b')
View 2 Replies
View Related
May 25, 2012
I have tables A and B.
Table A has data, but B may have or not.
I have to get -- at least! -- the A table data.
It works when I dont have the where clause:
select * from A left outer join B on A.IDUser = B.IDUser
But it doesn't when I try to use the WHERE clause:
select * from A left outer join B on A.IDUser = B.IDUser
where A.IDUser = 2
View 3 Replies
View Related
Feb 17, 2004
Hi again, all...here I am again, trying to work on my CloseIndex thang again...Same subject, different tack...
Basically, I have two tables...for the sake of simplicity, let me define them as:
PortfolioIndex
PortfolioID int
CreateDate smalldatetime
CloseIndex float
PortfolioPerformance
PortfolioID int
CreateDate smalldatetime
PrevDate smalldatetime
DailyPerChg float
UPDATE PortfolioIndex
SET CloseIndex = CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
FROM PortfolioIndex AS P INNER JOIN PortfolioIndex AS PPI on (P.PortfolioID = PPI.PortfolioID), PortfolioPerformance AS PP
WHERE (P.PortfolioID = PP.PortfolioID) AND
((P.CreateDate = PP.CreateDate) AND
(P.CreateDate = @CreateDate) AND
(PPI.CreateDate = PP.PrevDate))
What I am trying to do is...get the previous day's portfolioIndex row's CloseIndex and create a new one for today's row.
As ugly as it is, it works when I execute it in the SQL Query Analyzer, but when I try to create the stored procedure, the syntax check complains that the PortfolioIndex reference at the UPDATE... part is AMBIGUOUS...yet when I define it in the SP as P.PortfolioIndex, it fails at run time saying there is no object named P.PortfolioIndex (well, of course there isn't!).
How can I make this work (and if possible, make it prettier too! *L* ;) )
View 7 Replies
View Related
Mar 6, 2008
Hey all,
Anyone have a work around for using a TSQL FULL OUTER JOIN in CE? Something like a Right and a Left Outer Join for the below?
Thanks,
billb
Here's an example:
' Connection To SQL CE
Dim ConsqlShipTo As New SqlCeConnection
ConsqlShipTo.ConnectionString = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "CRM.sdf"
'
'TSQL STATEMENT......
Dim reqSQL As New SqlCeCommand
reqSQL.CommandText = "SELECT " & _
" COALESCE(ShipTo.ShipToCust, Prospect.ProspectCust) AS [Customer ID], " & _
" COALESCE(ShipTo.ShipToName, Prospect.ProspectName) AS Name, " & _
" COALESCE(ShipTo.ShipToCity, Prospect.ProspectCity) AS City " & _
" FROM ShipTo FULL OUTER JOIN " & _
" Prospect ON ShipTo.ShipToName LIKE Prospect.ProspectName AND Prospect.ProspectCust LIKE ShipTo.ShipToCust " & _
" WHERE " & _
" (Prospect.Alias = '" & reqGetUserName & "' AND Prospect.ProspectName LIKE '%" & reqSearch & "%') " & _
" OR (ShipTo.Alias > '%%' OR ShipTo.Alias IS NULL) " & _
" AND (ShipTo.ShipToName LIKE '%" & reqSearch & "%') " & _
" GROUP BY " & _
" ShipTo.ShipToCust, Prospect.ProspectCust, ShipTo.ShipToName, Prospect.ProspectName, " & _
" ShipTo.ShipToCity, Prospect.ProspectCity " & _
" ORDER BY Name "
reqSQL.CommandType = CommandType.Text
reqSQL.Connection = ConsqlShipTo
ConsqlShipTo.Open()
'
'CREATE NEW SQL DATA ADAPTER
Dim sqlda As New SqlCeDataAdapter(reqSQL)
'
' FILL DATASET
Dim sqlds As New DataSet
sqlda.Fill(sqlds, "Customer ID")
'
' FILL DATATABLE - lightweight
Dim sqldt As New DataTable
sqlda.Fill(sqldt)
View 4 Replies
View Related
Apr 24, 2014
I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
View 9 Replies
View Related
Jan 20, 2008
I run statement down,result is "go". why goes,gone,going is not in the result?
SELECT ID, firstname, lastnameFROM [contain-1]WHERE CONTAINS(firstname, ' FORMSOF (INFLECTIONAL, go) ')
information table [contain-1]
ID firstname
1 go
2 goes
3 gone
4 going
View 2 Replies
View Related
Jan 20, 2008
what result this query?
SELECT productname
FROM product
WHERE CONTAINS(productname,'spread NEAR boysenberry' )
explain word "NEAR".
Thanks,mohsen
View 1 Replies
View Related
Jan 26, 2008
I run query down.result is null.
SELECT ID, firstname, lastname
FROM [contain-1]
WHERE CONTAINS(firstname, 'pooya NEAR yaser')
table [contains-1] information:
ID firstname
1 pooya
2 mehdi
3 peter
4 yaser
why result is null?
thanks,mohsen
View 1 Replies
View Related
Jan 30, 2008
I run query down.result is null.
SELECT ID, firstname, lastname
FROM [contain-1]
WHERE CONTAINS(firstname, 'pooya NEAR yaser')
table [contains-1] information:
ID firstname
1 pooya
2 mehdi
3 peter
4 yaser
5 pooyaandyaser
why result is null?
thanks,mohsen
View 2 Replies
View Related
Feb 1, 2008
I run query down.result is null.
Why is result null?
SELECT ID, firstnameFROM [contain-1]WHERE CONTAINS(firstname, 'yaser NEAR pooya')
table information:
ID firstname
1 ali
2 peter
3 yaser
4 yaserapooya
5 mehdi
Thanks,mohsen
View 1 Replies
View Related
Jul 20, 2005
Hi,i want to search record in a table where a varchar column is equal toa word or this word with the letter 's' or this word with the letter'e'.Today, i've this query :select * From Table Where Column = 'Word' or Column like 'Word[es]'I want to remove the 'or' of this query to have only one condition.Does someone know a solution to my problem.Excuse my poor english language.I hope you understand my problem and thks for solution.
View 2 Replies
View Related
Jun 27, 2006
Hi everybody,
I have a problem using the CONTAINS predicate. I use the contains
predicate in a web page that students utilize to look for dissertations
stored in a database (MS 2000) of the library. The search is one
big textbox where students can perform any type of reaserch. For
instance, they can insert the author, the title or few keywords of a
dissertation. After they have clicked on the button Submit, the words
that they have inserted are filtered and combined (with AND) in
one string:
Example: miami, education --> 'Miami AND Education'
An example of the query that I need to perfrom is the Query Number 1:
select docnumber,Title
FROM [Log].[dbo].[DocTable1]
where contains(SearchColumn,'miami AND education') order by docnumber
SearchColumn is a field (varchar) that is the concatenation of various
fields such as Location, Title, Author, etc... that are contained in
the same table (See below for the description of the Table DocTable1).
I'm currently testing the search and I have figured out that the query described above does not work.
For example, the query should produce the same resultsof the following one, but it does not:
select docnumber
FROM [Log].[dbo].[DocTable1]
where contains(Location,'miami') AND
contains(Keywords,'education') order by docnumber
I need to implement the first query, but i can not understand why it
does not produce the same results of the second one. Any Ideas?
Thanks,
Christian
--------------------------------------------------------------
CREATE TABLE [DocTable1] (
[DocNumber] [int] NOT NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Program] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Paper] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ANNO] [int] NULL ,
[Title] [varchar] (350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TitleSort] [varchar] (350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Keywords] [varchar] (350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SearchColumn] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_DocTable1] PRIMARY KEY CLUSTERED
(
[DocNumber]
) ON [PRIMARY] ,
CONSTRAINT [Title_Author_DocTable1] UNIQUE NONCLUSTERED
(
[Title],
[LastName],
[FirstName]
) ON [PRIMARY] ,
CONSTRAINT [FT-Constraint] CHECK NOT FOR REPLICATION ([FT] = 'Y' or [FT] = 'N')
) ON [PRIMARY]
GO
View 5 Replies
View Related
Jan 4, 2007
I am having problems with the following SQL statement. It works how I want it to work if I use = @SearchQuery but when I try to switch to LIKE I run into problems. I tried it the way it is shown below and I also tried LIKE '%'@SearchQuery'%' with no luck. Does this have something to do with the pattern to match being a parameter? How would I go about doing this correctly.
"SELECT server_info.*, network_adapter.* FROM server_info LEFT JOIN network_adapter " + "ON server_info.server_id = network_adapter.server_id WHERE " + DropDownList1.SelectedValue.ToString() + " LIKE '%@SearchQuery%'"
Thanks
View 2 Replies
View Related
Oct 3, 2007
Anybody know how to do this?
"Select something from some table where id in (@IDlist)"
where @IDlist is a comma-separated list of values (or something top that effect)
View 1 Replies
View Related
Jul 23, 2005
HiI am having a debate with one of the Postgres developers (Tom Lane)according to him ms-sql does not implement predicatelocking(SERIALIZABLE).Where predicate locking is defined as (from the postgres help):"12.2.2.1. Serializable Isolation versus True Serializability"It states: "To guarantee true mathematical serializability, it isnecessary for a database system to enforce predicate locking, whichmeans that a transaction cannot insert or modify a row that would havematched the WHERE condition of a query in another concurrenttransaction"Now I put it to him that that is exactly what SERIALIZABLE does isMS-SQL.But his response was "Only for WHERE conditions that can be expressedas a simple range constraint."Now is he correct i.e SERIALIZABLE works "Only for WHERE conditionsthat can be expressed as a simple range constraint." ?Regards,Daniel RothMCSD.NET
View 8 Replies
View Related
Aug 16, 2007
Hi guys,
I need to change some characters when reading the values of a certain columns, I am trying to use something like the code below, but the following error:
"Cannot use a CONTAINS or FREETEXT predicate on table 'JURNALTRANS' because it is not full-text indexed."
Code Snippet
CASE
WHEN Contains (JurnalTrans.DESCRIPTION, 'A') THEN Replace(JurnalTrans.DESCRIPTION,'A','A1')
WHEN Contains (JurnalTrans.DESCRIPTION, 'B') THEN Replace(JurnalTrans.DESCRIPTION,'B','B2')
ELSE JurnalTrans.DESCRIPTION
END AS 'Cost Description',
I am also trying something like this
Code SnippetSELECT DESCRIPTION AS 'X' FROM JURNALTRANS WHERE CONTAINS(DESCRIPTION, 'Y')
but getting the same error
"Cannot use a CONTAINS or FREETEXT predicate on table 'JURNALTRANS' because it is not full-text indexed."
Any help will be appreciated,
Thanks in advance,
Aldo.
View 4 Replies
View Related
Apr 14, 2004
I have a report in SQL that passes parameters at runtime entered by the user for two date ranges (beginning and ending). I'm trying to write a formula that will print a specific field *only if* the specified date range entered by the user is BETWEEN a specific value (like 200401). This is kind of reverse of a normal WHERE, BETWEEN clause.
I tried a standard BETWEEN predicate in my WHERE clause like:
IF '200401' BETWEEN ?BegPer and ?EndPer then salesanal.ptdbud01 else 0
But, it's returning an error that my Then statement is missing. I can't use a normal statement like 'IF ?BegPer >= '200401' and ?EndPer <= '200401', then.....' because users could enter a RANGE of periods, so it would be difficult to code all of the possible combinations this way. I'm actually doing this in Crystal, but if someone can give me a standard MSSQL example, I can translate that over to Crystal.
Thanks in advance,
Michelle
View 4 Replies
View Related
Jul 20, 2005
I have been searching for an escape character or a way of escapingdouble quotes that are actually in a string that I am using in thecontains predicate.Here is an exampleselect *from tablewhere contains(field, '"he said "what is wrong", that is what hesaid"')I need the double quotes in the string because they are part of thetext. Of course, Fulltext search raises the errorServer: Msg 7631, Level 15, State 1, Line 1Syntax error occurred near 'what is wrong", that is what he said'.Expected ''''' in search condition '"he said "what is wrong", that iswhat he said"'.If I remove the double quotes, the search does not return the properresults.Thanks in advance for the helpBill
View 2 Replies
View Related
Oct 22, 2007
Hi,
I am using Microsoft FullText search feature in my database for Searching
strings.
I want to use CASE statement in CONTAINS.
DECLARE @allowSearchALL int
SELECT [ID] ,[Source] ,[Description],name
FROM [Search]
WHERE CONTAINS( Source , '"mail"')
i know we can use CASE in WHERE clause...but dono how to use CONTAINS with
CASE.
My Requirement is
if @allowSearchALL is 1 then get all records otherwise use specified
hardcoded text "mail"
I have to use CONTAINS.
I would like use some thing like this,
SELECT [ID] ,[Source] ,[Description],name
FROM [Search]
WHERE Source = CASE WHEN @allowSearchALL = 1 THEN Source ELSE 'mail' END
This way i can map column name to same column name if @allowSearchALL = 1 .
I do not want to use multiple IF blocks like if ( )
begin
end
else if( )
begin
end
How this can be done in CONTAINS ? like WHERE CONTAINS(source, CASE ....ELSE
...END)
Please give me solution for this.
Thanks in advance
View 1 Replies
View Related
Jul 12, 2006
Hi,
What is the dowside of not using all uppercase for predicates and key words?
I cannot find to see a problem beside adhering to a clean coding convention. After all I already have color coding so what would be the uppercase for?
Same question for the semicolon ; at the end of a sql block. Is that real necessary not to get in trouble sometime down the road or is it a non-issue. I find like I am now a C# guys if I use these ;
Just curious, I find tedious to switch from all upper case to normal case all the time. And not forget to type the ;
Thanks,
Philippe
View 7 Replies
View Related
Apr 5, 2007
Thanks in advance....
Let me simplify what I'm trying to do....
Basically, I want to run the following query by using SqlDataSource.
SELECT data FROM table WHERE row in ('one', 'two', 'three')
Simple enough....
So, here's my code.....which obviously doesn't work properly.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnStr %>" SelectCommand="SELECT data FROM table WHERE row in (@username)">
<SelectParameters><asp:ControlParameter ControlID="TextBox1" Name="username" PropertyName="Text" Type="String" /></SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="TextBox1" runat="server" Text="'one', 'two', 'three'" Width="125px" Visible="True"></asp:Label>
Is my SelectCommand correct? Thanks.
View 3 Replies
View Related
May 9, 2006
Hey all,
I've a querie which inserts into a table, while joining on it. Something like:
insert into table1
(columnA,
columnB
)
select (columnA,columnB)
from table2
where columnA NOT EXIST(SELECT 1 from table1,table2 where table1.columnA!=table2.columnA)
As a rule of thumb, I avoid subselects and negative logic. However, sql2k seems to pick the fastest execution plan using this query, using a left anti semi join on that subselect. My question is this; what are the repercussions of inserting into a table when sql2k has already picked a query plan based on its stats? Is deadlocking possible? I haven't been able to find any evidence one way or the other. My objects are several million rows, so I am expecting some sort of side effect.
Any help would be greatly appreciated.
Regards,
-Kilka
View 2 Replies
View Related
May 2, 2009
I believe query optimizer can leverage the predicate used to define the filtered index. But I came to a situation that's not true:
USE tempdb;
GO
IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees;
GO
CREATE TABLE Employees
[Code] ....
If we take a look at the query plan. we'll find an unnecessary Nested Loop and Key Lookup operator there with the IS NULL predicate.
Ironically, If I change the IS NULL to IS NOT NULL in both the index and Query, everything goes fine.
View 9 Replies
View Related
May 8, 2015
I have to write one query to exclude data from partial string in the predicate.
Select * from table where col NOT in ('%abc%,%xyz%).
So the data where col value doesn't contain the ABC and xyz string value only should be selected. I am also getting xyz data in the result.
View 4 Replies
View Related
