Is this the best way to do this ?

I have a query that has several optional fields to filtered the returned data from. They can either be null or have a value. This is the query that I have written. It works fine for filtered the data. But for some reason the query only ever returns 9 rows of data. No matter what parameter values I pass for @.startRowIndex and @.maximumRows.

Help.

SELECT CustomerName, Filename, UserName, DateAdded, PhotoID
FROM (SELECT CustomerName, Filename, UserName, DateAdded, PhotoID, ROW_NUMBER() OVER (ORDER BY Filename) AS RowNum
FROM (SELECT DISTINCT Photos.CustomerName, Photos.Filename, Photos.UserName, Photos.DateAdded, Photos.PhotoID
FROM Photos INNER JOIN
IndustryCatalog ON Photos.PhotoID = IndustryCatalog.PhotoID INNER JOIN
OptionCatalog ON Photos.PhotoID = OptionCatalog.PhotoID
WHERE (Photos.CustomerName LIKE '%' + @.CustomerName + '%' OR
@.CustomerName IS NULL) AND (Photos.UserName LIKE '%' + @.UserName + '%' OR
@.UserName IS NULL) AND (Photos.State LIKE '%' + @.State + '%' OR
@.State IS NULL) AND (Photos.City LIKE '%' + @.City + '%' OR
@.City IS NULL) AND (Photos.WorkOrderNumber = @.WorkOrder OR
@.WorkOrder IS NULL) AND (Photos.Series = @.Series OR
@.Series IS NULL) AND (Photos.ColorID = @.ColorID OR
@.ColorID IS NULL) AND (Photos.StructureWidth = @.StructureWidth OR
@.StructureWidth IS NULL) AND (Photos.StructureLength = @.StructureLength OR
@.StructureLength IS NULL) AND (IndustryCatalog.IndustryID = @.IndustryID OR
@.IndustryID IS NULL) AND (IndustryCatalog.AppID = @.AppID OR
@.AppID IS NULL) AND (OptionCatalog.CategoryID = @.CategoryID OR
@.CategoryID IS NULL) AND (OptionCatalog.OptionID = @.OptionID OR
@.OptionID IS NULL) AND (Photos.Country LIKE '%' + @.Country + '%' OR
@.Country IS NULL) AND (Photos.PhotoFinishNumber = @.PhotoFinishNumber OR
@.PhotoFinishNumber IS NULL) AND (Photos.Description LIKE '%' + @.Description + '%' OR
@.Description IS NULL) AND (Photos.Resolution > @.Resolution OR
@.Resolution IS NULL)) AS FilteredPhotos) AS Paged
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1

Hi, I simplied you query and did a test in my database, the 2 parameters (@.startRowIndex and @.maximumRows) did work. There must be some other thing that caused the 2 parameters ineffective. Have you set the ROWCOUNT option? You can turn off the option by using this statement:

SET ROWCOUNT 0

|||Turns out I was using the wrong type of join in my query and thats what was screwing up my sql... Thanks for hte help though.

Is this syntax correct: (',' + @country_code + ',' like '%,' + rtrim(member_country)

I need some help in trying to build this SQL
select count(*)
from
tMember
where
(@.country_code is null
or (',' + @.country_code + ',' like '%,' + rtrim(member_country) + ',%'))
There is 1 record in tMember with member_country column's value = US.
@.country_code varchar(5)
1. This Returns 0 Count (Incorrect)
--
@.country_code = 'FR, US'
2. This Returns 0 Count (Incorrect)
--
@.country_code = 'FR,US,UK'
3. This Returns 0 Count (Incorrect)
--
@.country_code = 'FR,UK,US'
4. This Returns 1 Count (CORRECT)
--
@.country_code = 'US,FR'
How can I make the query return the right count even if US is not the first
element.
Oddly, if I hard code the values 'FR,US' in the SQL it returns the correct c
ount.
select member_id, member_country
from
tMember
where ',' + 'FR,US'+ ',' like '%,' + rtrim(member_country) + ',%'
TIA,
RajanYou say that @.country_code is varchar(5). Every one of the examples
you describe as Incorrect has a value for @.country_code longer than 5
characters. Perhaps making @.country_code long enough to hold the data
would help?
Also, given the embedded blank in the first example, consider removing
blanks using REPLACE:
or (',' + REPLACE(@.country_code,' ','') + ',' like '%,' +
rtrim(member_country) + ',%'))
Roy
On Sat, 25 Feb 2006 18:41:03 -0800, "Rajan" <roger@.yahoo.com> wrote:

>I need some help in trying to build this SQL
>select count(*)
>from
>tMember
>where
>(@.country_code is null
> or (',' + @.country_code + ',' like '%,' + rtrim(member_country) + ',%'))
>
>There is 1 record in tMember with member_country column's value = US.
>@.country_code varchar(5)
>
>1. This Returns 0 Count (Incorrect)
>--
>@.country_code = 'FR, US'
>
>2. This Returns 0 Count (Incorrect)
>--
>@.country_code = 'FR,US,UK'
>
>3. This Returns 0 Count (Incorrect)
>--
>@.country_code = 'FR,UK,US'
>
>4. This Returns 1 Count (CORRECT)
>--
>@.country_code = 'US,FR'
>
>How can I make the query return the right count even if US is not the first
element.
>
>Oddly, if I hard code the values 'FR,US' in the SQL it returns the correct
count.
>select member_id, member_country
>from
>tMember
>where ',' + 'FR,US'+ ',' like '%,' + rtrim(member_country) + ',%'
>TIA,
>Rajan|||>>
Perhaps making @.country_code long enough to hold the data
would help?
Yes Roy, that was it. I made it varchar(255) and it fixed the problem.
Thanks very much for your help.
Sincerely,
Rajan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message news:9k6202lum5vp1cklkg1hqbspmapd168km9
@.4ax.com...
> You say that @.country_code is varchar(5). Every one of the examples
> you describe as Incorrect has a value for @.country_code longer than 5
> characters. Perhaps making @.country_code long enough to hold the data
> would help?
>
> Also, given the embedded blank in the first example, consider removing
> blanks using REPLACE:
>
> or (',' + REPLACE(@.country_code,' ','') + ',' like '%,' +
> rtrim(member_country) + ',%'))
>
> Roy
>
>
> On Sat, 25 Feb 2006 18:41:03 -0800, "Rajan" <roger@.yahoo.com> wrote:
>

Is this correct?

hi

if row.col1 = nothing then ...

instead of (sql2k) if dtssource("col1") = null then...

TIA

No!

If you type:

If Row.

then an intellisense box will pop up. In there you will see a function called col1_IsNull()

That function returns a boolean indicating whether or not the field is empty.

-Jamie

|||Thanks Jamie.

Is this an SQL bug (SQL 2000)?

Hi....
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegroups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Is this an SQL bug (SQL 2000)?

Hi....
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegr oups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Is this an SQL bug (SQL 2000)?

Hi....
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegroups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Is this A Valid Table Schema

GO

CREATE TABLE [dbo].[CmnLanguage]
(
[Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY,
[UniqueName] [varchar](26) NOT NULL,
[NativeName] [nvarchar](26) NOT NULL,
[DirectionType] [smallint] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

GO

CREATE TABLE [dbo].[CmnLink]
(
[Id] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkCmnLink_Id PRIMARY KEY,
[UniqueName] [varchar](52) NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

GO

CREATE TABLE [dbo].[CmnLinkCmnLanguage]
(
[LinkId] [int] NOT NULL CONSTRAINT FkCmnLinkCmnLanguage_LinkId FOREIGN KEY (LinkId) REFERENCES CmnLink(Id) ON DELETE CASCADE,
[LanguageId] [char](2) NOT NULL CONSTRAINT FkCmnLinkCmnLanguage_LanguageId FOREIGN KEY (LanguageId) REFERENCES CmnLanguage(Id) ON UPDATE CASCADE ON DELETE CASCADE,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

Well, thats not really a schema at all. Thats a script. Sometimes also called a Create Script or Change script. I suppose someone may refer to it as a schema since it really is a deffinition of a set of tables (3 of them)

Try it out, see if it works. Run this inside a project and see if your tables and relationships get generated correctly.

|||

I already test it. But I am asking Is it valid in respect of rules.

|||

I think what you're asking is: Does it follow best pratices. There's no way for us to know what rules you'd like it to follow, but best pratices are kind of dictated by the most elegant way of doing something.

I've run your scripts on a database, and had it create the three tables, and 2 relationships. I'm not quite sure what you're trying to accomplish. It appears to be some sort of localization mapping set of tables, but I am not sure I understand why the CmnLinkCmnLanguage table exists, it doesnt make sense to me why it's there?

Could you explain further what you're end goal is, and what you hope to accomplish with these sets of tables?

|||

Ok I will explain it from start.

I am working on a completely multilingual website.

Now at this point I am working on Database end.

As this is a multilingual website so I need the Language Table as show below.

----------

Language

----------

Id

RomanName

NativeName

Direction

IsVisible

----------

Next.

I have Book Table.

----------

Book

----------

Id

RomanName

NativeName

IsVisible

----------

Every thing is fine till here.

But I have a limited type of Books and each book is avalaible in different languages.

For Example I add a book translated in Arabic, Urdu and English.

These three books have different ID. But these are the translation of Same book.

And when I have this Book in Urdu By default. And need all the available languages for this book then problem occurs.

To resovle this issue I modify the Book Table and break it into 2 Tables.

----------

Book

----------

Id

RomanName

IsVisible

----------

----------

BookNative

----------

BookId

LanguageId

NativeName

----------

Then an ID will assign for the book and all available language editions have not the ID.

At this end the above mentioned goal will got.

But I explain it on another post

http://forums.asp.net/t/1145293.aspx

And they replied its not correct.

Then again I think it for some time.

And another solution will come in mind.

That make a single Table for Book

----------

Book

----------

Id

Name

IsVisible

BookGroupID

----------

And for the above mentioned goal make a separate BookGroup Table

----------

BookGroup

----------

Id

Name

IsVisible

----------

After this solution Book Table is alone.

That was all the story.

Hope you will pick it.

And reply me with some great idea.

Waiting for your reply.

Is there such thing as cross-table index or something?

CREATE TABLE [SalesForecast] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[F1] [money] NOT NULL
) ON [PRIMARY]
CREATE TABLE [Sales] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[S1] [money] NOT NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX SalesForecast_CustID ON SalesForecast (CustID)
CREATE NONCLUSTERED INDEX Sales_CustID ON Sales (CustID)
CREATE NONCLUSTERED INDEX SalesForecast_SaleDate ON SalesForecast (SaleDate)
CREATE NONCLUSTERED INDEX Sales_SaleDate ON Sales (SaleDate)
When I mix a query with both tables like this its really slow:
SELECT A.S1 * B.F1
FROM Sales A, SalesForecast B
WHERE A.SaleDate = B.SaleDate
AND A.CustID = B.CustID
AND A.SaleDate > '20050101'
What am I doing wrong, this seems to be correct..It would help to have a clustered index on the date column. You don't look
like you have any clustered indexes at all, which is not a good practice.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rich" <no@.spam.invalid> wrote in message
news:44HGe.53673$4o.23499@.fed1read06...
> CREATE TABLE [SalesForecast] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [F1] [money] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [S1] [money] NOT NULL
> ) ON [PRIMARY]
>
> CREATE NONCLUSTERED INDEX SalesForecast_CustID ON SalesForecast (CustID)
> CREATE NONCLUSTERED INDEX Sales_CustID ON Sales (CustID)
> CREATE NONCLUSTERED INDEX SalesForecast_SaleDate ON SalesForecast
> (SaleDate)
> CREATE NONCLUSTERED INDEX Sales_SaleDate ON Sales (SaleDate)
>
> When I mix a query with both tables like this its really slow:
> SELECT A.S1 * B.F1
> FROM Sales A, SalesForecast B
> WHERE A.SaleDate = B.SaleDate
> AND A.CustID = B.CustID
> AND A.SaleDate > '20050101'
> What am I doing wrong, this seems to be correct..
>|||What are the keys in each case? Do you have any? A unique key could
make a significant difference.
David Portas
SQL Server MVP
--|||Do not use the proprietary MONEY data type. Declare a primary key for
the tables. Most codes are fixed length to make them easier to validate
so I find it hard to believe that your customer id is really VARCHAR(n)
CREATE TABLE SalesForecast
(sale_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL,
forecast_amt DECIMAL (12,4) NOT NULL,
PRIMARY KEY (sale_date, cust_id));
CREATE TABLE Sales
(sale_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL,
sales_amt DECIMAL (12,4) NOT NULL,
PRIMARY KEY (sale_date, cust_id));
This gives you a covering index for your query. If the join is still
slow, use a clustered option on the keys.|||Thanks I'll give that a try. CustID is anywhere from 6 to 10 characters
long.. Most of the time it is 6, but sometimes it is 9 or 10! Should I still
use char instead of varchar?
Thanks.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122732638.691124.46520@.g14g2000cwa.googlegroups.com...
> Do not use the proprietary MONEY data type. Declare a primary key for
> the tables. Most codes are fixed length to make them easier to validate
> so I find it hard to believe that your customer id is really VARCHAR(n)
> CREATE TABLE SalesForecast
> (sale_date DATETIME NOT NULL,
> cust_id CHAR(10) NOT NULL,
> forecast_amt DECIMAL (12,4) NOT NULL,
> PRIMARY KEY (sale_date, cust_id));
> CREATE TABLE Sales
> (sale_date DATETIME NOT NULL,
> cust_id CHAR(10) NOT NULL,
> sales_amt DECIMAL (12,4) NOT NULL,
> PRIMARY KEY (sale_date, cust_id));
> This gives you a covering index for your query. If the join is still
> slow, use a clustered option on the keys.
>

Is there any way to show or hide the Document Map dynamically depend on parameters?

hi, all
I have a class report with class no as paramter.
This parameter could be null. So, if users enter null, it will show all
of them for all classes. Otherwise, it will only show the data for that
class.
So, what I am thinking about is, if the parameter is null, report shows
all data, it will be very nice to show the Document Map on the left
side. If users entered class no, then there is only one class in this
report, no use to show or use the Document map, right?
Does anyone know how to do that?One idea is to call your report through a URL and pass rc=DocMap=true or
false
Med bouchenafa
"Nick" <nick_1394@.yahoo.com.cn> a écrit dans le message de news:
1137522580.946890.33140@.f14g2000cwb.googlegroups.com...
> hi, all
> I have a class report with class no as paramter.
> This parameter could be null. So, if users enter null, it will show all
> of them for all classes. Otherwise, it will only show the data for that
> class.
> So, what I am thinking about is, if the parameter is null, report shows
> all data, it will be very nice to show the Document Map on the left
> side. If users entered class no, then there is only one class in this
> report, no use to show or use the Document map, right?
> Does anyone know how to do that?
>

Is there any way to show or hide the Document Map dynamically depend on parameters?

hi, all
I have a class report with class no as paramter.
This parameter could be null. So, if users enter null, it will show all of them for all classes. Otherwise, it will only show the data for that class.
So, what I am thinking about is, if the parameter is null, report shows all data, it will be very nice to show the Document Map on the left side. If users entered class no, then there is only one class in this report, no use to show or use the Document map, right?
Does anyone know how to do that?

If you are using the ReportViewer controls that shipped with VS 2005, you can read the values of the parameters from ReportViewer.ServerReport.GetParameters (or LocalReport depending on the mode you are using) and set ReportViewer.DocumentMapCollapsed programmatically.

If you are using url access to the server, you could use rc:DocMap=false to hide the document map, but this would require you knowing the value of the parameter beforehand, so it wouldn't work with the built-in parameter prompting.