how to show records with JOIN?

Hi ,

I've got two tables.. the first table carried a ProductID, and amongst other things a TradePrice

The other tbl carries a ProductID, a IndivPrice and a CustomerID

The second tbl lists prices for products for indiv Customers.

My Query needs to bring back ALL the products from the first tbl...

It also needs to show the TradePrice for that product.

I need to join my query to the second tbl...

And finally, if the second tbl has a price for that product AND the customerID is the same as one I pass into the query.. show that price also..

So here's my first query:

SELECT dbo.Products.ProductID, ProductName, ProductTradePrice, IndivPrice, dbo.Trade_PriceLists.CustomerID AS PLCustomerID FROM dbo.Products LEFT OUTER JOIN dbo.Trade_PriceLists ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID WHERE (ProductType = 'Trade' OR ProductType = 'Both') AND (Replace(Lower(ProductBrand),' ','') = 'brandname') AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '') ORDER BY TradeOrder

I thought that would work, but what happens is that, if that particular customer has no indiv prices set.. then it only shows the ones that have no records at all in that second tbl..

So unless there is a record for a particular product in that second tbl and it doesn't have a CustomerID assigned to (which would never happen as that tbl is only every for indiv customer prices) then it doesn't show.

Examples:

First Tbl

ProductID Name TradePrice

1 Jumper ￡1.00

2 Jeans ￡3.00

3 Shoes ￡5.00

4 Hat ￡2.00

Second Tbl

ProductID CustomerID IndivPrice

1 teste ￡0.50

2 othercustomer ￡2.50

3 teste ￡4.50

What I want in the results is:

ProductID ProductName TradePrice IndivPrice CustomerID (PLCustomerID)

1 Jumper ￡1.00 ￡0.50 teste

2 Jeans ￡3.00

3 Shoes ￡5.00 ￡4.50 teste

4 Hat ￡2.00

See? - The 2nd product should not get an indiv price as although it's in that second tbl, the customerID assigned to it is different. The 4th product should not get an indiv price as it's not in that second tbl at all.

however, with my query above I'd only get Products 1and 3... and if I did a query on a customer with no indiv prices I'd only get product 4 as it's not in the indiv at all...

HELP!!!!!

Give a look to the SELECT article in books online. Maybe something like:

declare @.firstTbl table
( ProductId integer,
Name varchar(10),
Price money
)
insert into @.firstTbl
select 1, 'Jumper', $1.0 union all
select 2, 'Jeans', $3.0 union all
select 3, 'Shoes', $5.0 union all
select 4, 'Hat', $2.0
--select * from @.firstTbl

declare @.secondTbl table
( ProductId integer,
CustomerId varchar(15),
IndivPrice money
)
insert into @.secondTbl
select 1, 'teste', $0.5 union all
select 2, 'othercustomer', $2.5 union all
select 3, 'teste', $4.5
--select * from @.secondTbl

select a.productId,
a.name as [ProductName],
a.Price as [Trade Price],
coalesce(convert(varchar(21),b.IndivPrice), '') as IndivPrice,
coalesce(convert(varchar(21),b.CustomerId), '') as [CustomerId (PlCustomerId)]
from @.firstTbl a
left join @.secondTbl b
on a.productId = b.productId

/*
productId ProductName Trade Price IndivPrice CustomerId (PlCustomerId)
-- -- -- -- -
1 Jumper 1.0000 0.50 teste
2 Jeans 3.0000 2.50 othercustomer
3 Shoes 5.0000 4.50 teste
4 Hat 2.0000
*/

|||

Hi Kent,

Thanks for the reply..

Your select statement looks virtually the same as mine!

However your's is missing the WHERE clause - and this is what is causing the problem.

My WHERE clause needs to bring back records that either have no CustomerID, or the specific CustomeID I ask for in the WHERE statement.

However the WHERE clause I have inserted (see my first post) simply does not do it. If I put in a Customer ID into my where clause that doesn't have an indivprice records.. then it simply returns nothing...

whereas it should return all of the records... but obivously these would not have an indivprice for them

|||

Is this better:

declare @.firstTbl table
( ProductId integer,
Name varchar(10),
Price money
)
insert into @.firstTbl
select 1, 'Jumper', $1.0 union all
select 2, 'Jeans', $3.0 union all
select 3, 'Shoes', $5.0 union all
select 4, 'Hat', $2.0
--select * from @.firstTbl

declare @.secondTbl table
( ProductId integer,
CustomerId varchar(15),
IndivPrice money
)
insert into @.secondTbl
select 1, 'teste', $0.5 union all
select 2, 'othercustomer', $2.5 union all
select 3, 'teste', $4.5
--select * from @.secondTbl

select a.productId,
a.name as [ProductName],
a.Price as [Trade Price],
coalesce(convert(varchar(21),b.IndivPrice), '') as IndivPrice,
coalesce(convert(varchar(21),b.CustomerId), '') as [CustomerId (PlCustomerId)]
from @.firstTbl a
left join @.secondTbl b
on a.productId = b.productId
and customerId = 'teste'

/*
productId ProductName Trade Price IndivPrice CustomerId (PlCustomerId)
-- -- -- -- -
1 Jumper 1.0000 0.50 teste
2 Jeans 3.0000
3 Shoes 5.0000 4.50 teste
4 Hat 2.0000
*/

I have a question: Are you wanting results 2 and 4 removed?

|||

thanks!! - that's perfect!!

Can you let me know what is different about your query.. I see that indivprice has a convert on it and there is a Coalesce statement..

What are these for?

|||

Hang on and I'll give it a go.

The COALESCE / VARCHAR business is to replace an output that would otherwise say NULL with blank space.

After I reworked your original query and used your table names what I got was:

SELECT dbo.Products.ProductID,
ProductName,
ProductTradePrice,
coalesce(convert(varchar(21), IndivPrice), '') as IndivPrice,
coalesce(dbo.Trade_PriceLists.CustomerID, '') AS PLCustomerID
FROM dbo.Products
LEFT OUTER JOIN dbo.Trade_PriceLists
ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID
WHERE (ProductType = 'Trade'
OR ProductType = 'Both')
AND (Replace(Lower(ProductBrand),' ','') = 'brandname')
AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '')

/*
ProductID ProductName ProductTradePrice IndivPrice PLCustomerID
-- --
1 Jumper 1.0000 0.50 teste
3 Shoes 5.0000 4.50 teste
4 Hat 2.0000
*/

|||

- excellent - thank you very much...

|||

one final question.

If I use your query - then the Indiv price is now converted into a varchar and no longer is a money field.

I need this to be a money field for my code (it's used in a shopping basket...

How would I do this..

(I've tried converting it in my Server Side ASP using CLng, but it won't do it..

|||Then don't use the VARCHAR / COALESCE on this particular field -- provided that your application can handle the situation when the field is a NULL output. I just realized that when I reworked your query I dropped a row; is that wanted?|||

just noticed that myself... no I need all results back..

why did it drop a record?

|||

To avoid dropping that record one of the lineds needs to be moved up and become part of the JOIN instead of part of the WHERE clause. Also, I removed the conversion of the PRICE field:

SELECT dbo.Products.ProductID,
ProductName,
ProductTradePrice,
IndivPrice,
coalesce(dbo.Trade_PriceLists.CustomerID, '') AS PLCustomerID
FROM dbo.Products
LEFT OUTER JOIN dbo.Trade_PriceLists
ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID
AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '')
WHERE (ProductType = 'Trade'
OR ProductType = 'Both')
AND (Replace(Lower(ProductBrand),' ','') = 'brandname')

/*
ProductID ProductName ProductTradePrice IndivPrice PLCustomerID
-- --
1 Jumper 1.0000 .5000 teste
2 Jeans 3.0000 NULL
3 Shoes 5.0000 4.5000 teste
4 Hat 2.0000 NULL
*/

How to show only records that belong to the logged in user.

Hello.

I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:

http://forums.asp.net/p/1159666/1913519.aspx#1913519
http://forums.asp.net/p/1161930/1924264.aspx
http://forums.asp.net/p/1116601/1732359.aspx#1732359
http://forums.asp.net/t/1104718.aspx
http://forums.asp.net/p/1096290/1655706.aspx#1655706
http://forums.asp.net/p/1110162/1707952.aspx#1707952

Basically, I need aDropDownListto displayonly projectsfor which thelogged in user is assigned asleader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code?

* Here is the definition of the SqlDataSource: 

<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>"
 SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @.Leader)" OnSelecting="SqlDataSource5_Selecting">
 <SelectParameters>
 <asp:Parameter Name="Leader" Type="Object" />
 </SelectParameters>
 </asp:SqlDataSource>

 * Here is the definition of the SqlDataSource5_Selecting method:

  protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
 {
 e.Command.Parameters("@.Leader").Value = loggedInUserId;
 }
 
where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey;
 
Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." 
The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId;
in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'."
 
I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much.
 

change it to

e.Command.Parameters["@.Leader"].Value = loggedInUserId;

Brackets not paranthesis

|||

Thank you very much for your reply. It now compiles with seemingly no problems, but the DropDownList is empty. Without the WHERE clause, it would display all projects available.

I'm probably wrong, but it could be a databind problem. Any other mistakes you could spot?

Thanks.

|||

not sure try changing the type on the parameter to the type of that param so like

<asp:Parameter Name="Leader" Type="Int32" />

Do you have access rights to the DB so that you can run the SQL Profiler?

Try that and see what query is running against the DB...

Or try debugging it to see what that param is maybe it's not getting set right and you're actually setting it to 0

|||

Thanks again, PureWeen, for trying to help. I debugged the method that determines the Id of the logged-in user, and it correctly gave me {9a6fdee6-fe40-4545-b18a-c05a278080b5}, a valid uniqueidentifier.

Now I tried looking for the SQL Profiler by searching for it in the C drive, but it seems I don't have it installed. I'm using Microsoft SQL Server Management Studio Express.

|||

I also tried to insert the statement SqlDataSource5.SelectParameters["Leader"].DefaultValue = loggedInUserId.ToString(); in the Page_Load event, but the compiler complained, "error CS0029: Cannot implicitly convert type 'System.Guid' to 'string'."

So, I changed the statement to SqlDataSource5.SelectParameters["Leader"].DefaultValue = loggedInUserId.ToString(); and the DropDownList was still empty. I wonder if that statement is needed here in Page_Load.

|||

well you don't really have to set it in Page_Load event that might be your problem... Did you debug the Selecting Event to make sure that loggedInUserID does indeed equal the GUID at that point?

Instead of setting a value why not use a accessor method like

private Guid LoggedInUserID{
get{
return (System.Guid)Membership.GetUser().ProviderUserKey;

}

}

If you just delete the parameter and don't filter the query does the dropdownlist populate?

Can you try filtering on a different field in the table to test if that works?

|||

if that doesn't work maybe post more of your code or all of it :)

|||

Sorry for the late reply; I didn't have access to the source codefor the whole day, and I still don't. I'll try to answer from memory.

Basically,I can get it to work with strings, but not Guids. So I created a newfield in the table and called it ProjectLeaderName (a string), whichwould store the UserName of the user whose Id is ProjectLeader (auniqueidentifier). It worked fine this way. But when I modify the type fromString toObject, and use ProjectLeader (the Guid) instead ofProjectLeaderName (the String), the DropDownList displays nothing. Iwonder if the type "Object" is not the correct choice.

PureWeen:

wellyou don't really have to set it in Page_Load event that might be yourproblem... Did you debug the Selecting Event to make sure thatloggedInUserID does indeed equal the GUID at that point?

Iadded this chunk of code in Page_Load while I was desperate, and as itdidn't change a thing, I commented it out. The only code is now in theSqlDataSource5_Selecting method. And yes, the global variableloggedInUserId did get the right value as the one in the aspnet_Userstable when I was in debug mode.

PureWeen:

Instead of setting a value why not use a accessor method like

private Guid LoggedInUserID{
get{
return (System.Guid)Membership.GetUser().ProviderUserKey;

}

}

That's a tough one. I think the original intent wasthat I wanted to use a variable (loggedInUserId) as part of the SQLquery, but I failed miserably. Regardless, Its value is calculated in asimple method (which is called in Page_Load) thatsets theglobal variable but does not return it. I'm not overly familiar withaccessors or properties, although it looks like a quick, yet viable change I could make.

PureWeen:

If you just delete the parameter and don't filter the query does the dropdownlist populate?

Yep. Removing the WHERE clause shows all projects ever created in the DropDownList.

PureWeen:

Can you try filtering on a different field in the table to test if that works?

Yes,it works fine on strings (ProjectName) and ints (ProjectId), but not onGuids (ProjectLeader). That's why I added the new string fieldrepresenting thename of the project leader with the stored Id(ProjectLeaderName), and filtering by that works fine. I just call theproperty User.Identity.Name to return the name of the logged-in user.

PureWeen:

if that doesn't work maybe post more of your code or all of it :)

Sure. I won't have access to the code until Monday, so I'll post more relevant parts of it then.

Once again, your help is always appreciated. Thanks a lot.

|||

Can you build your own SqlCommand and pass it a Guid that returns filtered rows?

If you can then in the selecting event just replace the sqlcommand with yours..

or
MAybe specify the type on the parameter in the selecting event

cmd.Parameters[0].DbType = System.Data.DbType.Guid;

or
Maybe just skip having the parameter specified on the SqlDataSource parameter list at all then add it yourself ...
cmd.Parameters.Add("Name", System.Data.SqlDbType.UniqueIdentifier)

the SQL Profiler is in the management studio under Tools->Sql Profiler but I'm not sure if express has it

|||

Those are some interesting ideas I might have to try out. Unfortunately, I just realized I'm not very familiar with the logic or syntax of the statements above, so I think what I have to do is do some more reading until I can effectively tackle this out. As of right now, I'm content that it's working, even though I'm only using strings. Once I'm done reading, I should be more capable of correctly implementing your suggestions. I already have a good feeling one of them may work. I'll probably be ready in a week or so, so I'll keep you informed.

Thank you very much.

|||

PureWeen:

Can you build your own SqlCommand and pass it a Guid that returns filtered rows?

If you can then in the selecting event just replace the sqlcommand with yours..

I tried to pass the SqlCommand, but for some reason it's not reading it. It may be a timing issue where I need to pass it before the Page_Load event, but it wouldn't. The DropDownList was still empty.

I did some more reading and searching, and one of the sources claimed that all I needed to do was delete the Object type attribute, simply because Guids are... special.

So what I had to do is simply replace<asp:Parameter Name="LeaderId"Type="Object"/> with<asp:Parameter Name="LeaderId"/>, and it worked fine.

PureWeen:

the SQL Profiler is in the management studio under Tools->Sql Profiler but I'm not sure if express has it

Too bad I can't find it here in the Express Edition.

So as a quick recap, the solution was to remove Type="Object" from the parameter, and it worked great.

Thank you, PureWeen, for setting me on the right track all that time. I appreciate your selfless efforts all that time.

Cheers.

How to show multiple main reports with their subreports one set by

Hi,
I have made a SQL Reporting Services report with the subreport. I need
my report to show multiple records with their subreport at the same time.
At now, I can generate multiple main reports altogether and then they are
followed by the subreport with multiple subreport details. It's just like
the following:
1st Page: 1st record in the Main report
2nd Page: 2nd record in the Main report
3rd Page: 1st Subreport detail (use Table object)
2nd Subreport detail (use Table object)
Inside the table of the 3rd page, it shows the 1st
Subreport details and then 2nd Subreport details.
What I actually need is I want my report to show one record in the main
report and then followed by the corresponding subreport details, then next
page for the next record in the main report and then followed by the
corresponding subreport details.
1st Page: 1st record in the Main report
2nd Page: 1st Subreport detail
3rd Page: 2nd record in the Main report
4th Page: 2nd Subreport detail
Is there anyone who can help me to solve this problem ?
Thanks in advance.
RogerHave you tried putting your subreport inside a table cell? The table could
contain your main rows and your subreports inside the same table as
repeating elements.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:17AC53EB-D966-476E-8B86-30B0864DE28B@.microsoft.com...
> Hi,
> I have made a SQL Reporting Services report with the subreport. I
> need
> my report to show multiple records with their subreport at the same time.
> At now, I can generate multiple main reports altogether and then they are
> followed by the subreport with multiple subreport details. It's just
> like
> the following:
> 1st Page: 1st record in the Main report
> 2nd Page: 2nd record in the Main report
> 3rd Page: 1st Subreport detail (use Table object)
> 2nd Subreport detail (use Table object)
> Inside the table of the 3rd page, it shows the 1st
> Subreport details and then 2nd Subreport details.
> What I actually need is I want my report to show one record in the
> main
> report and then followed by the corresponding subreport details, then next
> page for the next record in the main report and then followed by the
> corresponding subreport details.
> 1st Page: 1st record in the Main report
> 2nd Page: 1st Subreport detail
> 3rd Page: 2nd record in the Main report
> 4th Page: 2nd Subreport detail
> Is there anyone who can help me to solve this problem ?
> Thanks in advance.
> Roger
>

How to show group header/footer again after hiding them?

I have started to work on a report with table data region, and there
was need to sort the records withing a group, so I created a new
group, assigned the appropriate sort order, and then cleared "Include
group header" and "Include group footer" checkboxes. Because of this
the group did not show up in the layout window. But now I want to make
some changes to the sort, and I could not find a way to get to the
group definition again. Please help. Thanks.Found the solution. From Table Properties, go to Groups tab, and then
you can manage the groups there.

How to setup a many-to-many schema to favor retreival speed.

I'm am novice in the RDMS world. I would like to set up a database that will scale with a large dataset of over 1,000,000 unique records. In a nutshell, I have about a million+ documents that are stored on a file server as TIFF files. Each document has 2 or more persons (names) associated with that document like "John Smith" or "Mr. John Doe" etc.. A person can be related to many documents (depending on how many transactions they conducted over the years) and any document can be related to many people as most of the transactions were from multiple sellers and multiple buyers. The end users almost always searches for documents by using the first and last name of the person related to the document along with other document fields such as DATE and TYPE to narrow the search. I would like to design and index the database for fast searching as updates are not as time sensitive. I think I need at least 3 tables:

1) NAME_TABLE normalized into NameID(PK) NameFirst, NameMiddle and NameLast

2) DOC_TABLE normalized into DocID(PK), DocTransNum, DocFilePath, DocDate, DocType, (and other attributes)

3) NAME_DOC table to relate the above two tables in a many to many relationship.

My questions are: Where should I put the field that identifies the person as a buyer or seller as it relates to the document? John Smith can be both a buyer and seller on different documents so I don't think it belongs in the NAME table. If I have a another table that splits the names into SELLER_NAMES and BUYER_NAMES it would create some duplicate names and increase the search time right? I want to search for a document using a name and the option to specify if the person name search is limited to buyers, sellers or both. Do I need to do anything special in setting up the tables for fast searching other than indexing all the name fields and the DocID field? What indexing needs to be done on the many-to-many table if any? If fast searching on a mostly static database is a top requirement, should I denormalize the document table to include the names even though there will be duplicate names throughout? How large of a test set of records do I need to load to see any real deltas in performance as I modify and tweak?

Sorry for all the questions but I believe they are related to the overall problem to solve.

Regards,

DeBug

1) Where to indicate buyer versus seller? It seems the most appropriate place is in the Name_Doc table. It is unlikely that a person is both a buyer and a seller on a given document. If they could be, then you could have two bit fields isBuyer and isSeller, both on the Name_Doc table. Queries would be straightforward with this arrangement. Do not split the Name table into buyers and sellers. You are correct that it will lead to duplication and does not model the real problem.

2) How should you index? Use the performance wizard to aid in indexing. More indexes are not necessarily better. It will have good recommendations on clustered versus non-clustered indexes.

3) Should you denormalize? No. First, it will be hard to denormalize a many-to-many relationship. Second, 1 million records seems like a large number, but in reality with today's memory and optimization engines it is not that large.

4) Dataset sizes? You will probably want 50,000 records to allow the wizard to make good suggestions. Others will have better insights than I do.

|||

Hi,

I don′t if I got you right, so I will repeat the story first to see if we are in sync. You have two entities which can be compared to books and authors. One book can written by many authors and one authors can write many books (thats the many to many RS). What you now want to do is to sell a book / documents and want to reuse the information of the first persons table, right ? So the question, why are the perons related to the documents / book ? Did they write or bought them ? Is it another realtionship than you already defined in the n-m RS ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the response. You are correct that a buyer and seller are two different people and that a single person would not sell to himself. I can not think of an execption to this unless a parent sells to his child so that "John Smith" was the seller and "John Smith" was a buyer. This could also be true in the event that two people with the same name are conducting business. Rare, but it could happen. So I guess the sofware should not care if a "Doug DeBug" sold to a "Doug DeBug" as long as the user could enter "Doug DeBug" and the business logic would return the correct document, then all is well. All index information is keyed from the actual image so this should not be a problem in that a string of characters is simply associated with a document image. So if I understand you, I could add a field to the Name_Doc table like "IsBuyer" as a TRUE or FALSE and use that logic to search for John Smith the buyer and or John Smith the seller?

I'm using SQL Express so I'm not sure it contains the performance wizard. However, I have a subscription to MS's action pack which includes a version of SQL that I'm sure will contain the wizard. I would guess that I could tune the database using that as a tool and then model the results to the Express version for production.

I will try the 50K records level and see what Mr. Wizard suggests.

Thanks again for your reply.

DeBug

|||

Jens,

Yes, the books and authors is a good example of what I'm doing. The documents would be like the books and the buyers and sellers would be like the authors. The exception is that I need to also search on a name based on if the person is a seller or a buyer. So there is some additional work there. Also, your post mentions the reuse of the information of the first person. I really don't need to do that but simply want to be able to search for the correct document using a person's name and type (was this person a buyer or seller) to correctly locate and display the right TIFF file to the user. Kind of like an electronic filing cabinet type of application. I've written many of these before using Access and Visual Basic 6 but this project is much larger. When SQL Express was introduced, I was just waiting for the right project to come along to take advantage of this wonderful (and free) tool. To answer your second question, the named persons are simple the owners and buyers and the document is a scanned copy of the sales contract. When a disputed document is need to resolve a problem between the buyer and seller, a user will ask the person making the request if they were the buyer or seller and use this to narrow down the search. Most requestors have nothing to start with other than the persons name. In fact, sometimes they don't know if the person was the buyer or seller and thus the need to be able to search for all documents related to a specific name. Hope this helps clarify the problem.

Thanks for responding to my questions.

DeBug

|||

Hi,

ok I *think* I got you :-) There can be multiple buyers and sellers based on one document. That should be something like:

CREATE TABLE Document
(
DocumentId
<additional columns here>
)

CREATE TABLE InvolvedPersons
(
PersonId
<additional columns here>
)

CREATE TABLE AssociatedPersonsToDocuments
(
DocumentId,
PersonId,
AssociationType
<additional columns here>
)

CREATE TABLE AssociationType
(
AssociationType --This can be buyer "B" or seller "S" or additional flag that you want to set in the future, e.g. author of the contract, etc.
<additional columns here>
)

The names of the columns are sure replaceable, this was just for my own understanding and to show you how this fits all together

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I don't follow your logic with the additional tables. Here is where I'm am on the problem:

CREATE TABLE [dbo].[Names](
[NameID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MiddleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Suffix] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED
(
[NameID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Documents](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[DocPath] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocFileDate] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
[DocID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[NamesDocuments](
[NameDocID] [int] IDENTITY(1,1) NOT NULL,
[NameID] [int] NOT NULL,
[DocID] [int] NOT NULL,
[Type] [bit] NOT NULL,
CONSTRAINT [PK_NamesDocuments] PRIMARY KEY CLUSTERED
(
[NameDocID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[NamesDocuments] WITH CHECK ADD CONSTRAINT [FK_NamesDocuments_Documents] FOREIGN KEY([DocID])
REFERENCES [dbo].[Documents] ([DocID])
GO
ALTER TABLE [dbo].[NamesDocuments] CHECK CONSTRAINT [FK_NamesDocuments_Documents]
GO
ALTER TABLE [dbo].[NamesDocuments] WITH CHECK ADD CONSTRAINT [FK_NamesDocuments_Names] FOREIGN KEY([NameID])
REFERENCES [dbo].[Names] ([NameID])
GO
ALTER TABLE [dbo].[NamesDocuments] CHECK CONSTRAINT [FK_NamesDocuments_Names]

The above script creates the two tables for the names and documents. The many-to-many table contains the linking info with the additional column to identify if the nameIDtoDocumentID for that record was a buyer of seller for that transaction. What advantages do I have in using your example that contained an additional table to identify if the named person(s) were buyers or sellers....I'm confused :)

DeBug

|||

See my response to Jens for how I set up the tables and if that is what you would expect. Also, the NamesDocuments table will be about 3 to 3.5 times the size of the documents table so table size will be more like 3,250,000 rows and not the 1 million as I first suggested.

Again, thanks for your help on this!

DeBug

|||

OK, lets abstract my samples. As I saw from your tables you are only prepared for storing two types of people, buyers and sellers (which is stored in the Type column, right ? ) . If you only want this, then you are ok. If you want to store more information about the document like other persons which are involved in the document (like lawyers etc, mentioned in my sample you would have to extend your database) If you would store the Type as a integer (instead of a bit) you would be able to map the type to the additional table (mentioned in my sample) and extened your solution to include various type of perople involved in the document. Lets make a sample of your NamesDocuments table

[NameDocID] [NameID] [DocID] [Type]
1 1 1 1
2 2 1 2
3 3 1 3

Being Type like the following (1=buyer, 2=seller,3=lawyer), you will be able to extend this making something like 4=referals,5=reviewers etc.

That would be a great advantage to the solution with the bit option.

Hope I didn′t confused you too much :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I like your idea of extending the named persons beyond just buyer and seller. Adding another type like "reviewer" might be something I would want to add if the business rules were to change, which could happen at any time. Currently, a log is kept as the documents move through the workflow that audit the creation and review process but is not part of the long term document history. Is there a performance penality if I add another table "Types" with TypeID (PK int), Description (vchar(20))? Would this would require an additional JOIN to extract the TYPE of person and slow down performance? Or do I simply change the data type to "int", and use business logic to say that a 1 is "buyer" and that a "2" is "seller", etc. and not add an additional table? Also, I was able to set the one-to-many relationships (at least I think I set them up correctly) from the NamesDocuments table to the respective Names and Documents tables. What do I need to do to set up referential rules and or constraints to prevent orphaned records? I think I can change or correct a row from the Names table without changing any of the related records. Also true of the Documents table. However, even as a novice, I know you should not delete rows from the Names or Documents table that are part of the many-to-many table. In our business, it is common to find a document that was not correctly indexed and the administrator would need to ability to update a document to add/remove/change buyers and or sellers to correct any errors. My next task is to figure out a way to load about 25,000 random names to the Names table. I will most likely use VB.NET and some name data from the 1990 Census to simulate that table. The documents table can simply be a TIFF file used over and over again with a new file name so no problem there. Thank you for your help and suggestions!

Regards,

DeBug

How to setup a many-to-many schema to favor retreival speed.

I'm am novice in the RDMS world. I would like to set up a database that will scale with a large dataset of over 1,000,000 unique records. In a nutshell, I have about a million+ documents that are stored on a file server as TIFF files. Each document has 2 or more persons (names) associated with that document like "John Smith" or "Mr. John Doe" etc.. A person can be related to many documents (depending on how many transactions they conducted over the years) and any document can be related to many people as most of the transactions were from multiple sellers and multiple buyers. The end users almost always searches for documents by using the first and last name of the person related to the document along with other document fields such as DATE and TYPE to narrow the search. I would like to design and index the database for fast searching as updates are not as time sensitive. I think I need at least 3 tables:

1) NAME_TABLE normalized into NameID(PK) NameFirst, NameMiddle and NameLast

2) DOC_TABLE normalized into DocID(PK), DocTransNum, DocFilePath, DocDate, DocType, (and other attributes)

3) NAME_DOC table to relate the above two tables in a many to many relationship.

My questions are: Where should I put the field that identifies the person as a buyer or seller as it relates to the document? John Smith can be both a buyer and seller on different documents so I don't think it belongs in the NAME table. If I have a another table that splits the names into SELLER_NAMES and BUYER_NAMES it would create some duplicate names and increase the search time right? I want to search for a document using a name and the option to specify if the person name search is limited to buyers, sellers or both. Do I need to do anything special in setting up the tables for fast searching other than indexing all the name fields and the DocID field? What indexing needs to be done on the many-to-many table if any? If fast searching on a mostly static database is a top requirement, should I denormalize the document table to include the names even though there will be duplicate names throughout? How large of a test set of records do I need to load to see any real deltas in performance as I modify and tweak?

Sorry for all the questions but I believe they are related to the overall problem to solve.

Regards,

DeBug

1) Where to indicate buyer versus seller? It seems the most appropriate place is in the Name_Doc table. It is unlikely that a person is both a buyer and a seller on a given document. If they could be, then you could have two bit fields isBuyer and isSeller, both on the Name_Doc table. Queries would be straightforward with this arrangement. Do not split the Name table into buyers and sellers. You are correct that it will lead to duplication and does not model the real problem.

2) How should you index? Use the performance wizard to aid in indexing. More indexes are not necessarily better. It will have good recommendations on clustered versus non-clustered indexes.

3) Should you denormalize? No. First, it will be hard to denormalize a many-to-many relationship. Second, 1 million records seems like a large number, but in reality with today's memory and optimization engines it is not that large.

4) Dataset sizes? You will probably want 50,000 records to allow the wizard to make good suggestions. Others will have better insights than I do.

|||

Hi,

I don′t if I got you right, so I will repeat the story first to see if we are in sync. You have two entities which can be compared to books and authors. One book can written by many authors and one authors can write many books (thats the many to many RS). What you now want to do is to sell a book / documents and want to reuse the information of the first persons table, right ? So the question, why are the perons related to the documents / book ? Did they write or bought them ? Is it another realtionship than you already defined in the n-m RS ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the response. You are correct that a buyer and seller are two different people and that a single person would not sell to himself. I can not think of an execption to this unless a parent sells to his child so that "John Smith" was the seller and "John Smith" was a buyer. This could also be true in the event that two people with the same name are conducting business. Rare, but it could happen. So I guess the sofware should not care if a "Doug DeBug" sold to a "Doug DeBug" as long as the user could enter "Doug DeBug" and the business logic would return the correct document, then all is well. All index information is keyed from the actual image so this should not be a problem in that a string of characters is simply associated with a document image. So if I understand you, I could add a field to the Name_Doc table like "IsBuyer" as a TRUE or FALSE and use that logic to search for John Smith the buyer and or John Smith the seller?

I'm using SQL Express so I'm not sure it contains the performance wizard. However, I have a subscription to MS's action pack which includes a version of SQL that I'm sure will contain the wizard. I would guess that I could tune the database using that as a tool and then model the results to the Express version for production.

I will try the 50K records level and see what Mr. Wizard suggests.

Thanks again for your reply.

DeBug

|||

Jens,

Yes, the books and authors is a good example of what I'm doing. The documents would be like the books and the buyers and sellers would be like the authors. The exception is that I need to also search on a name based on if the person is a seller or a buyer. So there is some additional work there. Also, your post mentions the reuse of the information of the first person. I really don't need to do that but simply want to be able to search for the correct document using a person's name and type (was this person a buyer or seller) to correctly locate and display the right TIFF file to the user. Kind of like an electronic filing cabinet type of application. I've written many of these before using Access and Visual Basic 6 but this project is much larger. When SQL Express was introduced, I was just waiting for the right project to come along to take advantage of this wonderful (and free) tool. To answer your second question, the named persons are simple the owners and buyers and the document is a scanned copy of the sales contract. When a disputed document is need to resolve a problem between the buyer and seller, a user will ask the person making the request if they were the buyer or seller and use this to narrow down the search. Most requestors have nothing to start with other than the persons name. In fact, sometimes they don't know if the person was the buyer or seller and thus the need to be able to search for all documents related to a specific name. Hope this helps clarify the problem.

Thanks for responding to my questions.

DeBug

|||

Hi,

ok I *think* I got you :-) There can be multiple buyers and sellers based on one document. That should be something like:

CREATE TABLE Document
(
DocumentId
<additional columns here>
)

CREATE TABLE InvolvedPersons
(
PersonId
<additional columns here>
)

CREATE TABLE AssociatedPersonsToDocuments
(
DocumentId,
PersonId,
AssociationType
<additional columns here>
)

CREATE TABLE AssociationType
(
AssociationType --This can be buyer "B" or seller "S" or additional flag that you want to set in the future, e.g. author of the contract, etc.
<additional columns here>
)

The names of the columns are sure replaceable, this was just for my own understanding and to show you how this fits all together

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I don't follow your logic with the additional tables. Here is where I'm am on the problem:

CREATE TABLE [dbo].[Names](
[NameID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MiddleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Suffix] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED
(
[NameID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Documents](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[DocPath] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocFileDate] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
[DocID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[NamesDocuments](
[NameDocID] [int] IDENTITY(1,1) NOT NULL,
[NameID] [int] NOT NULL,
[DocID] [int] NOT NULL,
[Type] [bit] NOT NULL,
CONSTRAINT [PK_NamesDocuments] PRIMARY KEY CLUSTERED
(
[NameDocID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[NamesDocuments] WITH CHECK ADD CONSTRAINT [FK_NamesDocuments_Documents] FOREIGN KEY([DocID])
REFERENCES [dbo].[Documents] ([DocID])
GO
ALTER TABLE [dbo].[NamesDocuments] CHECK CONSTRAINT [FK_NamesDocuments_Documents]
GO
ALTER TABLE [dbo].[NamesDocuments] WITH CHECK ADD CONSTRAINT [FK_NamesDocuments_Names] FOREIGN KEY([NameID])
REFERENCES [dbo].[Names] ([NameID])
GO
ALTER TABLE [dbo].[NamesDocuments] CHECK CONSTRAINT [FK_NamesDocuments_Names]

The above script creates the two tables for the names and documents. The many-to-many table contains the linking info with the additional column to identify if the nameIDtoDocumentID for that record was a buyer of seller for that transaction. What advantages do I have in using your example that contained an additional table to identify if the named person(s) were buyers or sellers....I'm confused :)

DeBug

|||

See my response to Jens for how I set up the tables and if that is what you would expect. Also, the NamesDocuments table will be about 3 to 3.5 times the size of the documents table so table size will be more like 3,250,000 rows and not the 1 million as I first suggested.

Again, thanks for your help on this!

DeBug

|||

OK, lets abstract my samples. As I saw from your tables you are only prepared for storing two types of people, buyers and sellers (which is stored in the Type column, right ? ) . If you only want this, then you are ok. If you want to store more information about the document like other persons which are involved in the document (like lawyers etc, mentioned in my sample you would have to extend your database) If you would store the Type as a integer (instead of a bit) you would be able to map the type to the additional table (mentioned in my sample) and extened your solution to include various type of perople involved in the document. Lets make a sample of your NamesDocuments table

[NameDocID] [NameID] [DocID] [Type]
1 1 1 1
2 2 1 2
3 3 1 3

Being Type like the following (1=buyer, 2=seller,3=lawyer), you will be able to extend this making something like 4=referals,5=reviewers etc.

That would be a great advantage to the solution with the bit option.

Hope I didn′t confused you too much :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I like your idea of extending the named persons beyond just buyer and seller. Adding another type like "reviewer" might be something I would want to add if the business rules were to change, which could happen at any time. Currently, a log is kept as the documents move through the workflow that audit the creation and review process but is not part of the long term document history. Is there a performance penality if I add another table "Types" with TypeID (PK int), Description (vchar(20))? Would this would require an additional JOIN to extract the TYPE of person and slow down performance? Or do I simply change the data type to "int", and use business logic to say that a 1 is "buyer" and that a "2" is "seller", etc. and not add an additional table? Also, I was able to set the one-to-many relationships (at least I think I set them up correctly) from the NamesDocuments table to the respective Names and Documents tables. What do I need to do to set up referential rules and or constraints to prevent orphaned records? I think I can change or correct a row from the Names table without changing any of the related records. Also true of the Documents table. However, even as a novice, I know you should not delete rows from the Names or Documents table that are part of the many-to-many table. In our business, it is common to find a document that was not correctly indexed and the administrator would need to ability to update a document to add/remove/change buyers and or sellers to correct any errors. My next task is to figure out a way to load about 25,000 random names to the Names table. I will most likely use VB.NET and some name data from the 1990 Census to simulate that table. The documents table can simply be a TIFF file used over and over again with a new file name so no problem there. Thank you for your help and suggestions!

Regards,

DeBug

How to Set Start Index in a Data Control which is attahced to a Data Component

Hello All,

I have a SQLDataSource "sdsX" which has four records. There is a DataList "dlX" which is binded to this SQLDataSource "sdsX". Now I want that my datalist "dlX" take third record of "sdsX" as its first element. Is there any property which can be used.

Thanks

Hi ShailAtlas,

Base on my understanding, you want to move the third row to first row in DataList. For example:

Here is a DataList rendered likes below:

----------

1 | name1 |

----------

2 | name2 |

----------

3 | name3 |

----------

4 | name4 |

----------

You want to render it likes below without any change of datasource.

----------

3 | name3 |

----------

1 | name1 |

----------

2 | name2 |

----------

4 | name4 |

----------

If I have misunderstood your concern, please feel free to let me know.

DataList doesn't have property for this special request. If you want to implement it, you should add your own code in DataList_PreRender event handler. Here is the sample code:

int i = 0;

protectedvoid Page_Load(object sender,EventArgs e)

{

DataList1.DataBind();

}

protectedvoid Button1_Click(object sender,EventArgs e)

{

i =int.Parse(TextBox1.Text);

}

protectedvoid DataList1_PreRender(object sender,EventArgs e)

{

if (i != 0)

{

int j = i - 1;

string backupid = ((Label)DataList1.Items[j].Controls[1]).Text;

string backupname = ((Label)DataList1.Items[j].Controls[3]).Text;

for (; j > 0; j--)

{

((Label)DataList1.Items[j].Controls[1]).Text = ((Label)DataList1.Items[j-1].Controls[1]).Text;

((Label)DataList1.Items[j].Controls[3]).Text = ((Label)DataList1.Items[j-1].Controls[3]).Text;

}

((Label)DataList1.Items[0].Controls[1]).Text = backupid;

((Label)DataList1.Items[0].Controls[3]).Text = backupname;

}

}

When you input 3 in TextBox1 and then press Button1. DataList begin to render with the special order.

|||

Hello Ben,

Lets understand like this.I have a SQLDataSource which has 4 records. Now I have 2 datalist say DataListX and DataListY.

I want that DataListX should show record number 1 & 2, and DataListY should show records 3 & 4

SQLDataSource has records like this

----------

1 | name1 |

----------

2 | name2 |

----------

3 | name3 |

----------

4 | name4 |

----------

Now DataListX will show like this

----------

1 | name1 |

----------

2 | name2 |

And DataListY should be like this

----------

3 | name3 |

----------

4 | name4 |

Intention is that I do not want to use 2 SQLDataSource for same type of records

Thanks for your Reply

Shail

How to set No of rows per page in ssrs

Hi All,

By default in ssrs there are some fixed records..per page.......i mean each page contains (n) no of records.........per page..........?I want to display 100 records per page...for this what can i do ..please suggest .......

Thank's in Advance....

Hi
Add group with the following expression = Ceiling(RonNumber(Nothing)/N). Here N is the No of records in per page. And Select the Check box "Page Break either after/before"

Thanks

How to set in Details part by default 5 records even records are not available

Hi all,

I am facing a problem with the students report customization. Students will be selecting different subjects for different term(or semester). So for each semester also they can select max up to 5 subjects. So few students will take 2 subjects , few 5 subjects or 3 subjects... so the problem is while taking the report the report must not come according to their selection but according to 5 subjects or more.. so the students who took 1 subject or 5 subject will be getting the same format.. if u couldnt able to understand me... i am ready to send u the database and that report. I am attaching a report which was prepared by some one else... into that report it is working u can see the blanck space if the student took only 1 subject the remaining 4 space are coming blanck...i want in this way...pls help me if u knowHi,

I'm also searching solution For this. If u find the solution post here.

how to set column name dynamically?

HI chaps

my scenario is that, i have a table in which i have column such as col01,col02,col03....col31

i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col?') and i have to do integer increment in last two character (?01...?31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?

waiting for your reply

regards

Something like the following will work for you:

create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columns

declare @.query varchar(255)
set @.query = 'select '

declare @.counter int
set @.counter = 1

while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end

-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'

print @.query

The result is:

select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols

|||

fafnir wrote:

Something like the following will work for you:

create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columns

declare @.query varchar(255)
set @.query = 'select '

declare @.counter int
set @.counter = 1

while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end

-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'

print @.query

The result is:

select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols

thanx for your reply....

yes i can do it that way but still it will be string (varchar) can you tell me how to execute that statement? i can print the statement but i dont know how will I able to execute a query which is stored in the varaible

|||

sp_executesql @.query

|||

i have written the follwoing script

DECLARE @.CURRENTDATE DATETIME

DECLARE @.STARTDATE DATETIME

DECLARE @.LOOPDATE DATETIME

DECLARE @.NOOFMONTHS INT

DECLARE @.NOOFDAYS INT

DECLARE @.DAYCOUNTER TINYINT

DECLARE @.I INT

DECLARE @.J TINYINT

DECLARE @.DAYNUMBER VARCHAR(6)

/*INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR(BPCODE)

SELECT DISTINCT LTRIM(RTRIM(CZMMCU)) FROM STAGING.DBO.F0007*/

SET @.CURRENTDATE = (SELECT CONVERT(DATETIME,CAST(DATE AS VARCHAR(10))) FROM PRESENTATIONEUROPE.DBO.CALENDAR

WHERE CURRENTDAY = 0 )

--Check for first time execution and set start date accordingly

IF (SELECT MAX(DATE) FROM PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR) IS NOT NULL

SET @.STARTDATE = CONVERT(DATETIME,(SELECT CONVERT(VARCHAR(8),MAX(DATE)) FROM

PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR))

ELSE

SET @.STARTDATE = (SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4),(CAST(MIN(CZYR)AS INT) + 2000))+'-'+

CONVERT(VARCHAR(2),MIN(CZMT))+'-01') FROM STAGING.DBO.F0007 WHERE CZYR = (SELECT MIN(CZYR) FROM STAGING.DBO.F0007))

--GET THE NO OF MONTH DIFFERENCE

SET @.NOOFMONTHS = DATEDIFF(MM,@.STARTDATE,@.CURRENTDATE)

SET @.I = 1

SET @.LOOPDATE = @.STARTDATE

DECLARE @.LOOPDATE2 INT

DECLARE @.QUERY VARCHAR(500)

WHILE (@.I<=@.NOOFMONTHS)

BEGIN

SET @.LOOPDATE2 = (CONVERT(VARCHAR(8),CONVERT(DATETIME, CONVERT(VARCHAR(4),YEAR(@.LOOPDATE))

+'-'+ CONVERT(VARCHAR(2),MONTH(@.LOOPDATE))+'-01'),112))

SET @.J = 1

WHILE (@.J<=31)

BEGIN

IF (@.J<10)

BEGIN

SET @.QUERY = 'INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR (BPCODE, DATE, DATEE1, WORKINGDAY ) SELECT LTRIM(RTRIM(CZMMCU)), @.LOOPDATE2, (SELECT DATEE1 FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE DATE ='+CONVERT(VARCHAR(8),@.LOOPDATE,112)+'),

CZTD0'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2

ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+

'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+

'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))

END

ELSE

BEGIN

SET @.QUERY = 'INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR (BPCODE, DATE, DATEE1, WORKINGDAY ) SELECT LTRIM(RTRIM(CZMMCU)), @.LOOPDATE2, (SELECT DATEE1 FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE DATE ='+ CONVERT(VARCHAR(8),@.LOOPDATE,112) +'),

CZTD'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2

ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+

'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+

'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))

END

SP_EXECUTESQL @.QUERY

SET @.J = @.J+1

END

PRINT @.LOOPDATE

SET @.I = @.I + 1

END

and getting follwing error

Msg 102, Level 15, State 1, Line 62

Incorrect syntax near 'SP_EXECUTESQL'.

|||

use:

exec sp_executesql @.query

How to set a parameter for filtering not blank records

In my report I want an optional parameter to filter all records with a specific field that is not blank. I tried several scenario's without result...

In the parameter I want to set a text value like "exampletext".

In the filter I want a check: if the parameter value is "exampletext", only show the records where field "abc" is not blank.

On the tab Filters from the Table properties I can set three values: Expression, Operator and Value.

Please help!

I've found the solution.

Expression: =Iif(Parameters!Parameter.Value="exampletext",(Fields!Fieldname),"NULL")

Operator: >

Value: 0

How to set a parameter for filtering not blank records

In my report I want an optional parameter to filter all records with a specific field that is not blank. I tried several scenario's without result...

In the parameter I want to set a text value like "exampletext".

In the filter I want a check: if the parameter value is "exampletext", only show the records where field "abc" is not blank.

On the tab Filters from the Table properties I can set three values: Expression, Operator and Value.

Please help!

I've found the solution.

Expression: =Iif(Parameters!Parameter.Value="exampletext",(Fields!Fieldname),"NULL")

Operator: >

Value: 0