This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C75C0A.08318580
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi all
Wanting to create a report with a table inside another table. I've set = the dataset for each table to the relevant datasources. However when I = try set textboxes of the inner table to fields from dataset of the ineer = table, I get the following error message.
The value expression for the textbox 'textbox12' refers to the field = 'NAME'. Report item expressions can only refer to fields within the = current data set scope or, if inside an aggregate, the specified data = set scope.
I also get this error when trying to set a filter on the inner table to = a value on the outer table.
Not sure if this is the right newsgroup, if not someone please let me = know where I can get an answer
Using SQL Reporting services 2000, Visual Studio.NET 2003
Thanks
George
--=_NextPart_000_000B_01C75C0A.08318580
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi all
Wanting to create a report with a = table inside another table. I've set the dataset for each table to = the relevant datasources. However when I try set textboxes of the inner = table to fields from dataset of the ineer table, I get the following error message.
The value expression for the = textbox =91textbox12=92 refers to the field =91NAME=92. Report item = expressions can only refer to fields within the current data set scope or, if inside an = aggregate, the specified data set scope.
I also get this error when trying to = set a filter on the inner table to a value on the outer table.
Not sure if this is the right = newsgroup, if not someone please let me know where I can get an answer
Using SQL Reporting services 2000, = Visual Studio.NET 2003
Thanks
George
--=_NextPart_000_000B_01C75C0A.08318580--On Feb 28, 9:00 pm, "_george" <none@.nojne@.none> wrote:
> Hi all
> Wanting to create a report with a table inside another table. I've set the dataset for each table to the relevant datasources. However when I try set textboxes of the inner table to fields from dataset of the ineer table, I get the following error message.
> The value expression for the textbox 'textbox12' refers to the field 'NAME'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
> I also get this error when trying to set a filter on the inner table to a value on the outer table.
> Not sure if this is the right newsgroup, if not someone please let me know where I can get an answer
> Using SQL Reporting services 2000, Visual Studio.NET 2003
> Thanks
> George
You might have to explicitly reference the dataset. For example, if
the inside table is table 2, you might have to use an expression like
the following:
=Max(Fields!NAME.Value, "dsTable2sDataSetName")
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Tried that. Still same error. I suppose I'm asking if this is possible?
Thanks
George
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1172720239.500267.94860@.s48g2000cws.googlegroups.com...
> On Feb 28, 9:00 pm, "_george" <none@.nojne@.none> wrote:
>> Hi all
>> Wanting to create a report with a table inside another table. I've set
>> the dataset for each table to the relevant datasources. However when I
>> try set textboxes of the inner table to fields from dataset of the ineer
>> table, I get the following error message.
>> The value expression for the textbox 'textbox12' refers to the field
>> 'NAME'. Report item expressions can only refer to fields within the
>> current data set scope or, if inside an aggregate, the specified data set
>> scope.
>> I also get this error when trying to set a filter on the inner table to a
>> value on the outer table.
>> Not sure if this is the right newsgroup, if not someone please let me
>> know where I can get an answer
>> Using SQL Reporting services 2000, Visual Studio.NET 2003
>> Thanks
>> George
>
> You might have to explicitly reference the dataset. For example, if
> the inside table is table 2, you might have to use an expression like
> the following:
> =Max(Fields!NAME.Value, "dsTable2sDataSetName")
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>sql
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Nested IIF's
I need to evalute two different fields in some text boxes.
=IIF(Fields!BadAddressFlg.Value > Nothing,"Red",IIF(Fields!BadPhoneFlg.Value <> "G" OR Fields!BadPhoneFlg.Value <> Nothing, "Red","Black"))
If the valuefor bad address > nothing or Badphoneflg <> G or <>Nothing then the color needs to change to red, any of these can be true not all of them. I tried the switch, choose, all of them and if I take one of the fields out then I get it to work but I cant get them to work if they are both like this.
Thanks
Stokh
Three ORs mean that you don′t have to differ between the cases, right ?
=IIF(Fields!BadAddressFlg.Value > Nothing OR Fields!BadPhoneFlg.Value <> "G" OR Fields!BadPhoneFlg.Value <> Nothing, "Red","Black")
Did you already tried that ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
Well you would think so but not in here it doesnt apparently that doesnt work either, it turns them all red
There is only one record out of the 13 returned in the report that meets both badaddress having data in it and badphone have N instead of G so that record should be red. 3 other records only have the N in badPhone so those should be red too, the rest should be all black. Instead it turns them all red!
I thought using Nested Iffs was supposed to evaluate each one seperate and that isnt the case I tried a switch statement too and that doesnt work either.
Stokh
|||I am not quite sure about the > Nothing, did you try the <> Nothing ? As Nothing is the equivalent for NULL I would be careful with direct comparing with it like a numeric value.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Yeah I tried that to. Thats how the data comes in from the main frame which is not the way it should be but
I tried <> Nothing, Is Nothing, Trim(Fields!BadAddressFlg) you name I tried it. Nothing is working.
Stokh
|||Here is the latest one, now if I take out this part "IIF(Trim(Fields!BadPhoneFlg.Value) <> "G" Or Trim(Fields!BadPhoneFlg.Value) <> Nothing,"Red" " I get the correct ones
=IIF(Trim(Fields!BadAddressFlg.Value) > " ","Red",IIF(Trim(Fields!BadPhoneFlg.Value) <> "G" Or Trim(Fields!BadPhoneFlg.Value) <> Nothing,"Red","Black"))
|||Strange thing. Do you have the chance to send over the sample data (as it is not very much, only those 13). You can create the appropiate data insert statements with the procedure here:http://vyaskn.tripod.com/code.htm#inserts
As soon as you will post them I will create a sample project with it, we will get this thing to work :-)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Funny, there is only 13 records that are returned for this part there are 1000's of records in the table itself. and cant data privacy doesnt allow me to but I can give you the
appl_cd char 1
debtor_nbr varchar 10
debtor_nm varchar 50
debtor_xref_nm varchar 50
debtor_for nm carchar 50
address_line1 varchar 50
address_line2 varchar 50
city varchar 30
state_cd char 2
zip_cd char 5
zip_plus4_cd char 4
phone_nbr char 10
bad_address_flg char 4
bad_phone_flg char 1
last_update_dt datetime 8
Maybe that will help you not sure though.
Thanks!
|||I got the answer
=iif(trim(Fields!BadAddress.Value) <> "" or (trim(Fields!BadPhone.Value) <> "G" and trim(Fields!BadPhone.Value) <> ""),"Red","Black")
apparently it come off the mainframe with not knowing if they added spaces or something into the fields. Not sure why they do it that way but nonetheless got it done.
Thanks for the help!
Stokh
Monday, March 12, 2012
Nested Full Text Queries
Can someone help me or provide a workaround? The object is to select all
articles which are not references to other articles. The problem I am
having is when I try to get the ReferenceCount (which is the count of
articles which reference this article) it is throwing a 42000 (Error 170:
Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
nested full text query. I have tried successfully using LIKE '%' +
a.[Message-ID] + '%' which returns correctly just slow. By the way the The
References field is a varchar(500).
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
John,
I'm not exactly sure what you're trying to achieve with this query, but it
is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
what you may want to do is an INNER JOIN between the FTS query using
CONTAINSTABLE and your table tblarticles. For example, the syntax of the
rowset-based CONTAINSTABLE is:
SELECT select_list
FROM table AS FT_TBL INNER JOIN
CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
-- and an example using a Pubs database table:
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
"greene")' ) AS KEY_TBL
WHERE
FT_TBL.au_id = KEY_TBL.[KEY]
/*-- returns:
au_lname au_fname RANK
-- -- --
Greene Morningstar 80
Green Marjorie 80
Ringer Anne 64
Ringer Albert 64
(4 row(s) affected)
*/
I'd recommend altering your query to use the above CONTAINSTABLE or
FREETEXTTABLE syntax.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:oU_gc.23991$6m4.934106@.twister.southeast.rr.c om...
> Can someone help me or provide a workaround? The object is to select all
> articles which are not references to other articles. The problem I am
> having is when I try to get the ReferenceCount (which is the count of
> articles which reference this article) it is throwing a 42000 (Error 170:
> Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
> nested full text query. I have tried successfully using LIKE '%' +
> a.[Message-ID] + '%' which returns correctly just slow. By the way the
The
> References field is a varchar(500).
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS
ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
>
>
|||Thanks for replying john,
Unless I am overlooking something and please correct me if I am, you simply
provided an example of how to do a standard full text search. I do not have
a problem doing that. I thought I explained what im trying to achieve very
well.
Again, This is what im trying to do but its to slow:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
This is what I have tried:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%' "
for "CONTAINS([References], a.[Message-ID])"
"John Kane" <jt-kane@.comcast.net> wrote in message
news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
> John,
> I'm not exactly sure what you're trying to achieve with this query, but it
> is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
> what you may want to do is an INNER JOIN between the FTS query using
> CONTAINSTABLE and your table tblarticles. For example, the syntax of the
> rowset-based CONTAINSTABLE is:
> SELECT select_list
> FROM table AS FT_TBL INNER JOIN
> CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
> ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
> -- and an example using a Pubs database table:
> SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
> FROM authors as FT_TBL,
> CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
> "greene")' ) AS KEY_TBL
> WHERE
> FT_TBL.au_id = KEY_TBL.[KEY]
> /*-- returns:
> au_lname au_fname RANK
> -- -- --
> Greene Morningstar 80
> Green Marjorie 80
> Ringer Anne 64
> Ringer Albert 64
> (4 row(s) affected)
> */
> I'd recommend altering your query to use the above CONTAINSTABLE or
> FREETEXTTABLE syntax.
> Regards,
> John
|||You're welcome, John,
I've given this some more thought and have done some testing with the
authors table in the Pubs database I do not have the table schema for your
tblarticles table. First of all, the syntax you are using for the CONTAINS
predicate is incorrect, and assuming you are referencing a variable in the
search clause, your query should be re-written as follows:
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT [ID], [References], [From], [Date], [Subject],
(SELECT Count(ID) FROM tblarticles WHERE CONTAINS ([References],
@.SearchStr)) AS ReferenceCount
FROM tblarticles a
WHERE [GroupID] = @.GroupID AND [References]=''
ORDER BY [Date] Desc;
Additionally, I tested the following alternative using CONTAINSTABLE with
and INNER JOIN on the a SELECT * from CONTAINSTABLE. However, this solution
does not work with count(*) or count(au_id) for the join condition.
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL INNER JOIN
(SELECT * FROM
CONTAINSTABLE(authors,au_lname, @.SearchStr)) AS KEY_TBL
ON FT_TBL.au_id = KEY_TBL.[KEY]
/* -- returns:
au_lname au_fname RANK
--- -- --
Green Marjorie 80
White Johnson 80
(2 row(s) affected)
*/
Finally, I altered the initial query and use the assignment symbol "=" for
the SELECT * from authors where contains() and this did the trick.
declare @.SearchStr varchar(8000), @.State char(2)
SET @.SearchStr = '"green" or "white"'
SET @.State = 'CA'
SELECT au_id, au_lname, au_fname, SearchCount = (SELECT count(*) FROM
authors where CONTAINS(au_lname, @.SearchStr))
FROM authors
WHERE state = @.State and contract = 0
ORDER BY au_lname DESC
/*-- returns:
au_id au_lname au_fname
SearchCount
-- --- -- --
724-08-9931 Stringer Dirk 2
893-72-1158 McBadden Heather 2
(2 row(s) affected)
*/
You should alter your query to match the above and confirm that this works
for your tblarticles table.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:qu8hc.44033$yv.952732@.twister.southeast.rr.co m...
> Thanks for replying john,
> Unless I am overlooking something and please correct me if I am, you
simply
> provided an example of how to do a standard full text search. I do not
have
> a problem doing that. I thought I explained what im trying to achieve
very
> well.
> Again, This is what im trying to do but its to slow:
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
> ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
> This is what I have tried:
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS
ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
> Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%'
"[vbcol=seagreen]
> for "CONTAINS([References], a.[Message-ID])"
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
it[vbcol=seagreen]
*think*
>
|||Again thanks John,
The problem I am having is that where you are using a predefined (or passed)
variable @.SearchStr, I am needing this to come from the outer query. This
is why you see my variable labeled as a.[Message-ID]. The "a" is a
reference to the outer SELECT statements [Message-ID] field.
My Table looks like this:
CREATE TABLE [dbo].[tblArticles] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[FROM] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE] [datetime] NOT NULL ,
[Subject] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Message-ID] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Path] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[Article] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[References] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GroupID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And for the record this gets the desired results, its just slow and does not
make use of full text.
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount FROM tblarticles a WHERE [GroupID] = @.GroupID AND
[References]='' ORDER BY [Date] Desc;
Which again brings me back to the original question of nesting full text
queries.
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], '%' + a.[Message-ID] + '%')) AS
ReferenceCount FROM tblarticles a WHERE [GroupID] = @.GroupID AND
[References]='' ORDER BY [Date] Desc;
> --SNIP --
>
articles which are not references to other articles. The problem I am
having is when I try to get the ReferenceCount (which is the count of
articles which reference this article) it is throwing a 42000 (Error 170:
Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
nested full text query. I have tried successfully using LIKE '%' +
a.[Message-ID] + '%' which returns correctly just slow. By the way the The
References field is a varchar(500).
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
John,
I'm not exactly sure what you're trying to achieve with this query, but it
is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
what you may want to do is an INNER JOIN between the FTS query using
CONTAINSTABLE and your table tblarticles. For example, the syntax of the
rowset-based CONTAINSTABLE is:
SELECT select_list
FROM table AS FT_TBL INNER JOIN
CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
-- and an example using a Pubs database table:
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
"greene")' ) AS KEY_TBL
WHERE
FT_TBL.au_id = KEY_TBL.[KEY]
/*-- returns:
au_lname au_fname RANK
-- -- --
Greene Morningstar 80
Green Marjorie 80
Ringer Anne 64
Ringer Albert 64
(4 row(s) affected)
*/
I'd recommend altering your query to use the above CONTAINSTABLE or
FREETEXTTABLE syntax.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:oU_gc.23991$6m4.934106@.twister.southeast.rr.c om...
> Can someone help me or provide a workaround? The object is to select all
> articles which are not references to other articles. The problem I am
> having is when I try to get the ReferenceCount (which is the count of
> articles which reference this article) it is throwing a 42000 (Error 170:
> Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
> nested full text query. I have tried successfully using LIKE '%' +
> a.[Message-ID] + '%' which returns correctly just slow. By the way the
The
> References field is a varchar(500).
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS
ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
>
>
|||Thanks for replying john,
Unless I am overlooking something and please correct me if I am, you simply
provided an example of how to do a standard full text search. I do not have
a problem doing that. I thought I explained what im trying to achieve very
well.
Again, This is what im trying to do but its to slow:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
This is what I have tried:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%' "
for "CONTAINS([References], a.[Message-ID])"
"John Kane" <jt-kane@.comcast.net> wrote in message
news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
> John,
> I'm not exactly sure what you're trying to achieve with this query, but it
> is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
> what you may want to do is an INNER JOIN between the FTS query using
> CONTAINSTABLE and your table tblarticles. For example, the syntax of the
> rowset-based CONTAINSTABLE is:
> SELECT select_list
> FROM table AS FT_TBL INNER JOIN
> CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
> ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
> -- and an example using a Pubs database table:
> SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
> FROM authors as FT_TBL,
> CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
> "greene")' ) AS KEY_TBL
> WHERE
> FT_TBL.au_id = KEY_TBL.[KEY]
> /*-- returns:
> au_lname au_fname RANK
> -- -- --
> Greene Morningstar 80
> Green Marjorie 80
> Ringer Anne 64
> Ringer Albert 64
> (4 row(s) affected)
> */
> I'd recommend altering your query to use the above CONTAINSTABLE or
> FREETEXTTABLE syntax.
> Regards,
> John
|||You're welcome, John,
I've given this some more thought and have done some testing with the
authors table in the Pubs database I do not have the table schema for your
tblarticles table. First of all, the syntax you are using for the CONTAINS
predicate is incorrect, and assuming you are referencing a variable in the
search clause, your query should be re-written as follows:
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT [ID], [References], [From], [Date], [Subject],
(SELECT Count(ID) FROM tblarticles WHERE CONTAINS ([References],
@.SearchStr)) AS ReferenceCount
FROM tblarticles a
WHERE [GroupID] = @.GroupID AND [References]=''
ORDER BY [Date] Desc;
Additionally, I tested the following alternative using CONTAINSTABLE with
and INNER JOIN on the a SELECT * from CONTAINSTABLE. However, this solution
does not work with count(*) or count(au_id) for the join condition.
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL INNER JOIN
(SELECT * FROM
CONTAINSTABLE(authors,au_lname, @.SearchStr)) AS KEY_TBL
ON FT_TBL.au_id = KEY_TBL.[KEY]
/* -- returns:
au_lname au_fname RANK
--- -- --
Green Marjorie 80
White Johnson 80
(2 row(s) affected)
*/
Finally, I altered the initial query and use the assignment symbol "=" for
the SELECT * from authors where contains() and this did the trick.
declare @.SearchStr varchar(8000), @.State char(2)
SET @.SearchStr = '"green" or "white"'
SET @.State = 'CA'
SELECT au_id, au_lname, au_fname, SearchCount = (SELECT count(*) FROM
authors where CONTAINS(au_lname, @.SearchStr))
FROM authors
WHERE state = @.State and contract = 0
ORDER BY au_lname DESC
/*-- returns:
au_id au_lname au_fname
SearchCount
-- --- -- --
724-08-9931 Stringer Dirk 2
893-72-1158 McBadden Heather 2
(2 row(s) affected)
*/
You should alter your query to match the above and confirm that this works
for your tblarticles table.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:qu8hc.44033$yv.952732@.twister.southeast.rr.co m...
> Thanks for replying john,
> Unless I am overlooking something and please correct me if I am, you
simply
> provided an example of how to do a standard full text search. I do not
have
> a problem doing that. I thought I explained what im trying to achieve
very
> well.
> Again, This is what im trying to do but its to slow:
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
> ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
> This is what I have tried:
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS
ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
> Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%'
"[vbcol=seagreen]
> for "CONTAINS([References], a.[Message-ID])"
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
it[vbcol=seagreen]
*think*
>
|||Again thanks John,
The problem I am having is that where you are using a predefined (or passed)
variable @.SearchStr, I am needing this to come from the outer query. This
is why you see my variable labeled as a.[Message-ID]. The "a" is a
reference to the outer SELECT statements [Message-ID] field.
My Table looks like this:
CREATE TABLE [dbo].[tblArticles] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[FROM] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE] [datetime] NOT NULL ,
[Subject] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Message-ID] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Path] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[Article] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[References] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GroupID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And for the record this gets the desired results, its just slow and does not
make use of full text.
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount FROM tblarticles a WHERE [GroupID] = @.GroupID AND
[References]='' ORDER BY [Date] Desc;
Which again brings me back to the original question of nesting full text
queries.
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], '%' + a.[Message-ID] + '%')) AS
ReferenceCount FROM tblarticles a WHERE [GroupID] = @.GroupID AND
[References]='' ORDER BY [Date] Desc;
> --SNIP --
>
Friday, March 9, 2012
Needs to put quotes around numeric data in export text file
Hi
- I'm trying to export the data from crystal report to a disk file in a CS/ tab seperated format.
-The export file contains quotes around text fileds
- Is there any way to get quotes around numerical data?
Thanks in Advance :)
Bashayou need to have that in report itself
Create formula and write code
""""+Cstr({Field})+""""
- I'm trying to export the data from crystal report to a disk file in a CS/ tab seperated format.
-The export file contains quotes around text fileds
- Is there any way to get quotes around numerical data?
Thanks in Advance :)
Bashayou need to have that in report itself
Create formula and write code
""""+Cstr({Field})+""""
Subscribe to:
Posts (Atom)