Need to add merge article

SQL 2000 sp4 - I have an existing merge publication, I want a new table. I
assume I would do the following, however I have some questions:
I assume I call sp_addmergearticle, for the @.article param, specify the new
table.
I also assume I would need to call sp_addmergesubscription, correct?
I do the similar for transactional repl, however when I call
sp_addsubscription I can specify the new article in the @.article param, set
sync_type to automatic, run the snapshot agent and a snapshot is generated
for only the new article. My concern is I do not see an @.article param for
sp_addmergesubscription... When the snapshot agent fires, will it run for the
new article only?
Any insight would be appreciated.
Thanks,
Chris
Scratch this... all you need is sp_addmergearticle, unfortunately a snapshot
is generated for ALL articles (locking up my prod tables), however only the
added article is sent thru the merge agents...
Thanks anyway.
"Chris" wrote:

> SQL 2000 sp4 - I have an existing merge publication, I want a new table. I
> assume I would do the following, however I have some questions:
> I assume I call sp_addmergearticle, for the @.article param, specify the new
> table.
> I also assume I would need to call sp_addmergesubscription, correct?
> I do the similar for transactional repl, however when I call
> sp_addsubscription I can specify the new article in the @.article param, set
> sync_type to automatic, run the snapshot agent and a snapshot is generated
> for only the new article. My concern is I do not see an @.article param for
> sp_addmergesubscription... When the snapshot agent fires, will it run for the
> new article only?
> Any insight would be appreciated.
> Thanks,
> Chris
|||Besides sp_addmergesubscriptions, don't you also need to run a new snaphot?
Or does this get replicated on each subscriber?
David
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E6D1BF6E-A6C5-4D21-BF5B-1529F71F18B5@.microsoft.com...
> SQL 2000 sp4 - I have an existing merge publication, I want a new table. I
> assume I would do the following, however I have some questions:
> I assume I call sp_addmergearticle, for the @.article param, specify the
> new
> table.
> I also assume I would need to call sp_addmergesubscription, correct?
> I do the similar for transactional repl, however when I call
> sp_addsubscription I can specify the new article in the @.article param,
> set
> sync_type to automatic, run the snapshot agent and a snapshot is generated
> for only the new article. My concern is I do not see an @.article param for
> sp_addmergesubscription... When the snapshot agent fires, will it run for
> the
> new article only?
> Any insight would be appreciated.
> Thanks,
> Chris

Need SQL Help

I have following query which is joining couple of tables. i have a field "Status" in MeetingAttendees table. I have to add one more check (probably one more case statement) that if MA.Status=4 then Count(A.AttendeeID) as NoofAttendees. So NoofRSVPs (doesn't matter what is the status in MeetingAttendees table) will return total RSVPs and NoofAttendee will return only # of Attendees. how can i add do that? please help...

SELECT

M.State AS MeetingState,

CASE
WHEN MA.AttendeeType = 1 THEN 'Participant'
WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'
WHEN MA.AttendeeType = 3 THEN 'Client'
WHEN MA.AttendeeType = 4 THEN 'Staff'
END AS AttendeeType,

Count(A.AttendeeID) as NoofRSVPs

FROM
Programs P
INNER
JOIN eCDReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
left outer
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
left outer
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
left outer
JOIN Regions R
ON MA.RegionCode = R.RegionCode
WHERE
P.SubCompanyCode = @.SubCompanyCode AND
P.ProgramCode = @.ProgramCode




GROUP BY

M.State,

MA.AttendeeType

ORDER BY

MA.AttendeeTypeCOUNT(CASE WHEN MA.Status=4 THEN 'present' END) as NoofAttendees

Need SQL for distinguishing SQL 2005 from 2000 and 7.0

I want to use 'ALTER LOGIN' if the SQL Server is running 2005, but have to use sp_password if it's SQL 2000 or 7.0

Currently I use the following SQL to determine the version:

int nVersion = 7;
// this will throw an exception on 7.0
rs = con.execute("SELECT SERVERPROPERTY('productversion')");
if (rs != null && !rs.getEOF())
{
strVersion = rs.getField(0).getString();
if (strVersion == null)
strVersion = "7.";
strVersion = strVersion.trim();
int nIdx = strVersion.indexOf(".");
strVersion = strVersion.substring(0, nIdx);
nVersion = Integer.parseInt(strVersion);
}

Can anyone suggest a cleaner way?

Hi,

if you have the capabilities to use SQLDMO, this would be a snippet for it:

Set oSQLObj = CreateObject("SQLDMO.SQLServer")
version = oSQLObj.PingSQLServerVersion(sServer)

Otherwise if you have SQL Server 2005 and the SMO dll use can also use SQLSMO from .NET.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de

Need some suggestion

I have a stored procedure to retrieve records from a database, each record contains the following fields: merchant_id, shop_id, shop_sales_amount

each merchant can have several shops and the corresponding shop_sales_amount

the format of the report(detail section) is like this:

merchant1 $10000
merchant1-shop1 $5000
merchant1-shop2 $5000
merchant2 $1000
merchant2-shop1 $500
merchant2-shop2 $500
merchant3 $100
merchant3-shop1 $50
merchant3-shop2 $50
.....

merchant1 is group 1, merchant 2 is group 2, merchant 3 is group 3...etc, the amount following the merchant name is the total sales amount of each shop under the merchant, the ordering of the group is in descending order of this total sales amount...

It is quite complicated, seriously need some suggestions or hints, I am quite new to crystal reports. Hope someone could help me out, thanksSo you should create two Crystal report groups, Merchant (#1) and Shop (#2), an order on descending sales amount beneath these (using the record sort expert). Insert the sum of the sales amount grouped at the shop level (Insert Summary, field to summarize: shop_sales_amount; calculate this summary: Sum; summary location: group 2) and move from the group2 footer to the header.

Viola! (if I understand you correctly.)sql

Need some helps on this task

A user photo table with the following fields:

username
path
caption
status

where the status field indicates where the photo is the main one or not. Each user only can have one main photo.

For the deletion operation, another, if there is one, photo's status needs to be changed to main if the removing photo is the main one. What is the best approache to carry out this task: SQL and function/trigger?

Similar situation occur when a user want to change a non-main photo to become a main one.

Any advice?

Thanks,

v.While a trigger could be made to do this, it would not be trivial - would have to deal with the mutating table problem for one thing.

I would prefer to hide the logic in a packaged procedure, and force the user (i.e. the application) to delete via the procedure rather than an update or delete statement.|||Hi, Tony,

Thanks for your response and suggestion.

My thought on the issue is that it is somehow like a DB table constraint and not a business logic. Therefore, it shoud be resolved in the DB layer.

I can implement your suggestion to pass the information what the application knows about whether the photo is a main one or not. I think this solution is a suitable one.

Thanks again.

v.|||Here is a solution I just come out.

For the deletion operation, if the deleting photo placement is larger than 1, not the first/main one, only execute the deletion statement.

Otherwise, after the deletion statement, run the following query:

UPDATE photo SET main = 'true' WHERE userid = 'xxx' AND path IN (SELECT path FROM photo WHERE userid = 'xxx' )

This solution basically is on the DB side with a little help from the application (logic).

My feeling of the above query is the subquery can be in a better form, but can't think out one at this moment.

v.

Need some help regarding duplicate values....

Hi,

I am using access database and cystal reports XI. Since I am facing some problems.

The following are the details:

Table1 = Sales
Table2 = Collections

Sales (table fields):

1. CustomerName
2. InvoiceNumber
3. InvoiceDate
4. InvoiceAmount

Collections (table fields):

1. CustomerName
2. DepositDate
3. ReceivedAmount

I need these fields in a report. There is no relationship in the database for the CustomerName field.

I tried my best to get the report. But there are repeated values (Duplicate values which are not existing in the table/database) present on the report. Beside to the date values there shows 00:00:00 (time) as well which I dont need.

How to solve this problem. Please help.

Sweetie.The following jpg file shows the duplicate/repeated data.|||Make sure "select Distinct Records" is turned on under the Database menu.|||Make sure "select Distinct Records" is turned on under the Database menu.

Hi, thought I have selected the option "select Distinct Records" the same thing happens that the data in collections.AmountReceived repeats.

Any further help please. (Using Crystal Reports XI).

Sweetei.|||click on database menu, select database expert and go to link tab.
drag customername from one table to other to make a link.

then u will have a join which ensures data is not repeated.|||click on database menu, select database expert and go to link tab.
drag customername from one table to other to make a link.

then u will have a join which ensures data is not repeated.

Hi, Raheem! I did the same as you have advised but it didn't work. Actually though the both Sales & Collections have CustomerName field but there is no relationship between them in the database.

Could you please help me out of this problem?

Sweetie.|||Hello Sweetie,
It was not the CR code problem, it's your database structure.

While you try to connect the Sales table to the Collection table using CustomerName (seems like the only relationship you can make between the 2), every distinct record in the Sales table will connect to all records in the Collection table which has the same CustomerName. If you look at the DepositDate on your report, you'll see the pattern. It looks like a duplicate problem. In deed, it just a coincident that for each of the 3 customers, you have 2 records in the Collection table.

You can check this out by adding another record for one of the 3 customers in your Collection table. You'll see your report showing triplicate for this customer.

Now before I can help you to solve the problem, you'll need to be more specific about what you're looking for:

1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link.

2. If a collection has to refer to an invoice, you must have an InvoiceNumber field in your Collection table. Use both CustomerName & InvoiceNumber to link the 2 tables.|||Hello Sweetie,
It was not the CR code problem, it's your database structure.

Now before I can help you to solve the problem, you'll need to be more specific about what you're looking for:

1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link.

2. If a collection has to refer to an invoice, you must have an InvoiceNumber field in your Collection table. Use both CustomerName & InvoiceNumber to link the 2 tables.

Hi, thanks for your kind help. Actually I need the both ways you have mentioned above. But I need to know it step by step to understand easily.
So, Could you please let me know the first way .... ?

"1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link."

Sweetie.|||Sweetie, problem is with your database.

even if u join customername and salesperson u will get duplicate, u need to
include some distinguishing column in collection table, maybe invoice no. on which amount is collected.|||Sweetie, problem is with your database.

even if u join customername and salesperson u will get duplicate, u need to
include some distinguishing column in collection table, maybe invoice no. on which amount is collected.

Hi, Raheem. Thanks for the advise. But the problem is, assume that if the customer doesn't pay the full payment for each invoice and pay it in 3 installments and by the time he contines purchasing some items (means he will have another 1 - 2 invoices before he pay the first invoice amount). Then it will be messed.

The example is attached.

Regards,

Seema.|||Hi Seema,
Use FIFO method of adjustment i.e, payments shd be updated in same invoice until balance is 0 then adjust remaining amount with next invoice.
Raheem|||Hi Seema,
Use FIFO method of adjustment i.e, payments shd be updated in same invoice until balance is 0 then adjust remaining amount with next invoice.
Raheem

Wow! is it ? Actually what I needed for my program is the same. This is the first time that I heard about that method. Could you please explain me in detail, only if you dont mind?

Seema_S|||when i get free i will reply you.|||when i get free i will reply you.

Hi, Raheem!

I feel happy to get your reply which can solve my problem.

Thanks in advance.

Sweetie.|||Hi, guys!

I am waiting for the reply to my querry. Could somebody help me in this regard?

Sweetie.|||Sweetie,

I see you've been patiently waiting for over two weeks now!

It has to be said that the 'database' structure is a problem and very prone to errors.
One such error is the city 'JAYPUR' in Collections when it's 'JAIPUR' in Customers and Sales - explains the lack of duplication for that customer!
And what's the FileNumber column in Sales and Collections about? It seems to uniquely identify a customer but this isn't on the customer table. Messy.
You might want to consider ditching all the duplicated rubbish and putting the CustomerID into the Sales and Collections tables at least.

I've knocked together something I think is more like what you're after.See the attached report.
It assumes that the customer name is unique. If you put the CustomerID into the tables then you should use that instead as it really is (should be) unique.

Anyway, have a play with it to see what it does and how. Hope it's of some use.|||Sweetie,

I see you've been patiently waiting for over two weeks now!

It has to be said that the 'database' structure is a problem and very prone to errors.

Anyway, have a play with it to see what it does and how. Hope it's of some use.

Hi,

Thanks for your kind help. I am happy that at last I got my querry answered. There is one problem which is attached as jpg file.

Thanks again.

Sweetie.|||But that's the point - as you have stated before, there is no correlation between sales and collections.

The collections of 17,000 and 4,000 are applied in a First In First Out (FIFO) process to the sales (invoices).

The first invoice (103) was for 8,000 and the second invoice (109) was for 17,000.
The first collection was 17,000 so 8,000 of this is applied to invoice 103.
The remaining 9,000 is left over and applied to the next invoice, 109.
The next collection was for 4,000 and is also applied to invoice 109.

If you want to make it clear that a single collection has been split over multiple invoices then you might want to add the original collection amount / collection id to the report.|||But that's the point - as you have stated before, there is no correlation between sales and collections.

Thanks. It is better that it should show the full amount received.

Regards.

Sweetie.|||Hi sweetie,
i was busy all these days,i am attaching ur database zip file check it.

In Collections table, I added invoice number in it and check two

queries. JaganEllis did the same thing. if u remove invoice date then u will get correct collections amounts displayed.

collecionn 1 for 4000 adjusted in 105
collection 2 for 2000 adjusted in 105
collection 3 for 5200 adjustted in 105 and 111

so if u want to omit invoice date, then u can use query2 to make ur report ..else u u can use query1..

Atlast Hope I helped you.

Raheem|||Hi sweetie,
i was busy all these days,i am attaching ur database zip file check it.
Raheem

Thanks for your kind help. I need to learn more about Normalizing database and Crystal reports. If you know any good tutorials site for the above, plz let me know.

Regards.

Sweetie.sql

Need some assistance

Hi,
I am importing some phone numbers from a lagacy system and I find some of
them having the following exeptions:
1. 2122121222
2. 212 212 2122
3. 212/212 2122
4. 212/2122122
5. 212.212 2122
6. 212.2122122
7. 212.212.2122
8. 212-212-2122
9. (212) 212-2122
10. 212-2122122
11. (212)-212-2122
12. TEXT
13. 212 2122122
14. 212 -212-2122
15. 212 212 21222
16. 212) 212-2122
17. 2122122122#212
18. 1212 212 2122
How can I format them to (212) 212-2122? I was thinking a function but can
someone assist?
ThanksHi Chris
The code I have supplied below works in a controlled test environment and
may work for you BUT use with extreme caution.
Test the code by using selects before trying the inserts to verify that you
are getting the results required.
You may also like to backup your table before attempting this.
---
drop table #temp
create table #temp(pnumber varchar(50))
insert into #temp values('2122121222')
insert into #temp values('212 212 2122')
insert into #temp values('212/212 2122')
insert into #temp values('212/2122122')
insert into #temp values('212.212 2122')
insert into #temp values('212.2122122')
insert into #temp values('212.212.2122')
insert into #temp values('212-212-2122')
insert into #temp values('(212) 212-2122')
insert into #temp values('212-2122122')
insert into #temp values('(212)-212-2122')
insert into #temp values('TEXT')
insert into #temp values('212 2122122')
insert into #temp values('212 -212-2122')
insert into #temp values('212 212 21222')
insert into #temp values('212) 212-2122')
insert into #temp values('17. 2122122122#212')
insert into #temp values('1212 212 2122')
-- remove know extraneous characters
update #temp
set pnumber = replace(pnumber, ' ', '')
update #temp
set pnumber = replace(pnumber, '.', '')
update #temp
set pnumber = replace(pnumber, '/', '')
update #temp
set pnumber = replace(pnumber, '-', '')
update #temp
set pnumber = replace(pnumber, '(', '')
update #temp
set pnumber = replace(pnumber, ')', '')
update #temp
set pnumber = replace(pnumber, '#', '')
-- set to null where number length is not 10 --
update #temp
set pnumber = null where len(pnumber) <> 10
-- add braces and dash
update #temp
set pnumber = '(' + pnumber
update #temp
set pnumber = left(pnumber, 4) + ') ' + right(pnumber, 7)
update #temp
set pnumber = left(pnumber, 9) + '-' + right(pnumber, 4)
select * from #temp
---
Regards
Peter Hamilton
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||What are you using to import them? Is this in a table? Or a spreadsheet?
Is this a one time thing. Why are all of your phone numbers the same
number! (kidding about the last one :)
If you want to do it in sql (the algorithm would be the same anywhere) just
strip out all of the characters you don't want, leaving you with only
numbers. Then simply put it back together. I would suggest you might want
to put the data in three columns, but certainly a check constraint is
needed. You ought ot get a list of valid area codes to validate against as
swell, if this data is important to you (if it is I would have expected it
would be a bit better taken care of, but you never know.
Note that I just toss off the (likely) extension information, and just using
the 10 characters.
drop table test
go
create table test
(
phoneNumber varchar(15)
)
insert into test
select '2122121222'
union all
select '212 212 2122'
union all
select '212/212 2122'
union all
select '212/2122122'
union all
select '212.212 2122'
union all
select '212.2122122'
union all
select '212.212.2122'
union all
select '212-212-2122'
union all
select '(212) 212-2122'
union all
select '212-2122122'
union all
select '(212)-212-2122'
union all
select 'TEXT'
union all
select '212 2122122'
union all
select '212 -212-2122'
union all
select '212 212 21222'
union all
select '212) 212-2122'
union all
select '2122122122#212'
union all
select '1212 212 2122'
select case when cleaned like '%[a-z]%' then 'INVALID DATA'
when cleaned like '1%' then '(' + substring(cleaned,2,3) + ')' +
substring(cleaned,5,3) + '-' + substring(cleaned,8,4)
else '(' + substring(cleaned,1,3) + ')' + substring(cleaned,4,3) + '-' +
substring(cleaned,7,4) end
--this bit strips out the offensive characters and spaces
from (select
replace(replace(replace(replace(replace(
replace(replace(phoneNumber,'
',''),'(',''),')',''),'-',''),'.',''),'/',''),'#','') as cleaned
from test) as numbers
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks|||Probably better to validate this data externally and/or prior to acceptance
into the database. But here's one brute force method.
CREATE TABLE #t
(
phone VARCHAR(32)
);
SET NOCOUNT ON;
INSERT #t SELECT '2122121222';
INSERT #t SELECT '212 212 2122';
INSERT #t SELECT '212/212 2122';
INSERT #t SELECT '212/2122122';
INSERT #t SELECT '212.212 2122';
INSERT #t SELECT '212.2122122';
INSERT #t SELECT '212.212.2122';
INSERT #t SELECT '212-212-2122';
INSERT #t SELECT '(212) 212-2122';
INSERT #t SELECT '212-2122122';
INSERT #t SELECT '(212)-212-2122';
INSERT #t SELECT 'TEXT';
INSERT #t SELECT '212 2122122';
INSERT #t SELECT '212 -212-2122';
INSERT #t SELECT '212 212 21222';
INSERT #t SELECT '212) 212-2122';
INSERT #t SELECT '2122122122#212';
INSERT #t SELECT '1212 212 2122';
UPDATE #t
SET phone =
LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(
phone,
-- you may want to add more characters here
' ', ''),
'(', ''),
')', ''),
'#', ''),
'.', ''),
'/', ''),
'-', '')
));
UPDATE #t
SET phone = '('+STUFF(STUFF(phone,4,0,') '),9,0,'-')
WHERE
-- make sure phone has 10 numerical digits
-- you can make this more precise, e.g.
-- disallow 0 in the first position
phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
SELECT phone FROM #t ORDER BY phone;
DROP TABLE #t;
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks|||For short term, create a scalar UDF like:
CREATE FUNCTION dbo.f ( @.s VARCHAR( 20 ) )
RETURNS VARCHAR( 20 ) AS BEGIN
WHILE PATINDEX( '%[^0-9]%', @.s ) > 0
SET @.s = REPLACE( @.s, SUBSTRING( @.s, PATINDEX( '%[^0-9]%', @.s ), 1 ),
'' )
RETURN CASE WHEN LEN( @.s ) = 10
THEN '(' + STUFF( STUFF( @.s, 4, 0, ') ' ), 9, 0, '-' )
ELSE 'BAD DATA'
END
END
Now try:
SELECT phone_col, dbo.f( phone_col )
FROM tbl ;
Anith|||I am using DTS to import. Thanks for the assistance. I'll try to create a
function that I can add exceptions to in future. I'll be importng this data
on a daily basis from a legacy system which has to validation in the fronten
d
so the users practically enters anything.
"Chris" wrote:

> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks

Need Query Help

Hello Everybody,

I have the following two tables and I need some help crafting the apropriate SQL Query to retrieve the information that I need.

Table: ImageGalleries

GalleryID INT IDENTITY(1,1),
GalleryName VARCHAR(255),
DisplayOrder INT NOT NULL

Table: ImageDetails

ImageId INT IDENTITY(1,1),
GalleryID INT NOT NULL (FKey relationship to above table)
ImageName VARCHAR(255),
DisplayOrder INT NOT NULL

In the ImageGalleries table and ImageDetails table, DisplayOrder is a positive integer value that resents the order on the page in which the respective entries display

Here's my attempted query: I need to pull the ImageGalleries.GalleryID, ImageGalleries.GalleryName, and the corresponding ImageDetails.ImageID for the image with a DisplayOrder of 1 and order the full result set by ImageGalleries.DisplayOrder

I am sure there is a way to form this query but at the moment it is alluding me. I doubt that a lot of people are looking at the message boards on a holiday weekend but just in case I thought I would post this

SELECT
 g.GalleryID,
 g.GalleryName,
 i.ImageId,
 i.ImageName
FROM
 ImageGalleries g
 INNER JOIN ImageDetails i
ON g.GalleryID = i.GalleryID
ORDER BY
 g.DisplayOrder,
 i.DisplayOrder

one hour turn around time on a holiday season.. I think that's impressive. :)|||1 Hour turn around is indeed impressive on a Holiday. I shall share my condolences that we were both doing work on a Holiday. As far as the query above, thank you for the effort but it didn't quite suit what I was looking for. I just want to return 1 record per image gallery and that of course returns all records in the image gallery. I thought about doing a sub query

select G.GalleryID, G.GalleryName, Q.ImageId, Q.ImageName FROM ImageGalleries LEFT JOIN (SELECT * FROM ImageDetails WHERE DisplayOrder = 1) AS Q ON G.GalleryID = Q.GalleryID ORDER BY G.GalleryID

But instead, I chose to implement the same functionality in a user defined function cuz I never get to use them at my day job.

select *, dbo.GetFirstImage(GalleryID) As ImageName FROM ImageGalleries|||add..

WHERE
 q.DisplayOrder = 1

:) oh well.|||Bloody Hell, that was so obvious that I almost want to cry. Thank you. I will go hide my head in shame now.|||naw man. we all make the most obvious mistakes, and they're impossible to find because you never care to look in that direction.

We all hit that wall every other day. don't stress yourself out like that.

Need Query Help

I have the following queries which probably violates all sorts of rules.
I'm doing year over year changes and can't seem to get the entire calucation
done in one query. So I'm pulling data from one query (SQL2) and using it
in another query (SQL1). You'll see real numbers being used in SQL1 (ie
(totalmem - 30152)). The 30152 is coming from the other query.
I would rather have access to all the data in a single query. My real issue
is that from period to period health plans come and go in our data file
because they cease operations in a State and then reenter the market a year
or two later... Most health plans are constant in the data. A few come and
go. I might have 5 records in 2Q03 data and 6 in 2Q02 data. This causes
SQL1 to break because the rs2 data does not exisit if there is more data in
SQL1 than SQL2.
Data format:
MCO HMO MeciareMem MedicaidMem CommMem TotalMem
If I could get the data returned like this:
MCO HMO MedicareMem2Q03 MedicareMem2Q02, MedicaidMem2Q03,
MedicareMem2Q02... I would be in great shape
My data file looks like this:
Period MCO HMO MeciareMem MedicaidMem CommMem TotalMem
2Q02 Aetna Aetna Health of CT 0 0 37947 37947
2Q03 Aetna Aetna Health of CT 0 0 41110 41110
except that I have 11,000 rows of data.
I'd love some help on this one. A sample data set is below.
***
SQL2= "Select MCO, HMO, medicaremem, medicaidmem, commmem, totalmem from
sheet1$ where period = '" & varPeriod4 & "' AND " & strStateSQL1 & " order
by " & varsort & " " & varorder
renders like this:
SQL2 = Select MCO, HMO, medicaremem, medicaidmem, commmem, totalmem from
sheet1$ where period = '3Q03' AND (domicile LIKE '%ct%' OR domicile LIKE
'%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR domicile LIKE
'%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR domicile LIKE
'%nj%' OR domicile LIKE '%pa%') order by hmo asc
***
SQL1= "Select MCO, HMO, medicaremem, (medicaremem - " & rs2("medicaremem") &
") as medicarememchange, medicaidmem, commmem, totalmem, (totalmem - " &
rs2("totalmem") & ") as totalmemchange from sheet1$ where totalmem <> '' and
period = '" & varPeriod & "' AND " & strStateSQL1 & " order by " & varsort &
" " & varorder
renders like this:
SQL1 = Select MCO, HMO, medicaremem, (medicaremem - 0) as medicarememchange,
medicaidmem, commmem, totalmem, (totalmem - 30152) as totalmemchange from
sheet1$ where totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%'
OR domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
domicile LIKE '%nj%' OR domicile LIKE '%pa%') order by hmo ascHi
Posting DDL and example data is the best way to help people answer your
questions, see http://www.aspfaq.com/etiquett_e.asp?id=5006
This may give you some indication of what you require assuming primary the
key is MCO, HMO, medicaremem
Select a.MCO, a.HMO, a.medicaremem, a.medicaidmem-b.medicaidmem, a.commmem,
b.commmem, a.totalmem-b.totalmem
from sheet1$ a
JOIN sheet1$ b ON a.MCO = b.MCO AND a.HMO = b.HMO AND a.medicaremem =
b.medicaremem
where a.period = '3Q03'
and b.period = '3Q04'
If there is not always a record in '3Q04' then try:
Select a.MCO, a.HMO, a.medicaremem, a.medicaidmem-ISNULL(b.medicaidmem,0),
a.commmem, b.commmem, a.totalmem - ISNULL(b.totalmem,0)
from sheet1$ a
LEFT JOIN sheet1$ b ON a.MCO = b.MCO AND a.HMO = b.HMO AND a.medicaremem =
b.medicaremem
where a.period = '3Q03'
and b.period = '3Q04'
John
"William" wrote:

> I have the following queries which probably violates all sorts of rules.
> I'm doing year over year changes and can't seem to get the entire calucati
on
> done in one query. So I'm pulling data from one query (SQL2) and using it
> in another query (SQL1). You'll see real numbers being used in SQL1 (ie
> (totalmem - 30152)). The 30152 is coming from the other query.
> I would rather have access to all the data in a single query. My real iss
ue
> is that from period to period health plans come and go in our data file
> because they cease operations in a State and then reenter the market a yea
r
> or two later... Most health plans are constant in the data. A few come a
nd
> go. I might have 5 records in 2Q03 data and 6 in 2Q02 data. This causes
> SQL1 to break because the rs2 data does not exisit if there is more data i
n
> SQL1 than SQL2.
> Data format:
> MCO HMO MeciareMem MedicaidMem CommMem TotalMem
>
> If I could get the data returned like this:
> MCO HMO MedicareMem2Q03 MedicareMem2Q02, MedicaidMem2Q03,
> MedicareMem2Q02... I would be in great shape
> My data file looks like this:
> Period MCO HMO MeciareMem MedicaidMem CommMem TotalMem
> 2Q02 Aetna Aetna Health of CT 0 0 37947 37947
> 2Q03 Aetna Aetna Health of CT 0 0 41110 41110
> except that I have 11,000 rows of data.
>
> I'd love some help on this one. A sample data set is below.
> ***
> SQL2= "Select MCO, HMO, medicaremem, medicaidmem, commmem, totalmem from
> sheet1$ where period = '" & varPeriod4 & "' AND " & strStateSQL1 & " order
> by " & varsort & " " & varorder
> renders like this:
> SQL2 = Select MCO, HMO, medicaremem, medicaidmem, commmem, totalmem from
> sheet1$ where period = '3Q03' AND (domicile LIKE '%ct%' OR domicile LIKE
> '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR domicile LIKE
> '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR domicile LIKE
> '%nj%' OR domicile LIKE '%pa%') order by hmo asc
> ***
> SQL1= "Select MCO, HMO, medicaremem, (medicaremem - " & rs2("medicaremem")
&
> ") as medicarememchange, medicaidmem, commmem, totalmem, (totalmem - " &
> rs2("totalmem") & ") as totalmemchange from sheet1$ where totalmem <> '' a
nd
> period = '" & varPeriod & "' AND " & strStateSQL1 & " order by " & varsort
&
> " " & varorder
> renders like this:
> SQL1 = Select MCO, HMO, medicaremem, (medicaremem - 0) as medicarememchang
e,
> medicaidmem, commmem, totalmem, (totalmem - 30152) as totalmemchange from
> sheet1$ where totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%'
> OR domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
> domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
> domicile LIKE '%nj%' OR domicile LIKE '%pa%') order by hmo asc
>
>
>|||I'd build a table of the periods, then do a LEFT OUTER JOIN on it to
the membership table. This will give you NULLs for the periods that
the particular company was not in the State.
If you really must have things on one row, then you can use CASE
expressions inside aggregates.
I also wonder why you use things like "(domicile LIKE '%pa%')" instead
of scrubbing the data to assure that you have CHAR(2) valid state
codes.

Need Query for update and insert in one go.

Hi All,
Please look at the following tables. I have Two tables
Data and TmpData with the following structure.

Data ( All int columns, ID column is Primary and identity)

Id UserID PrgID RoldID
322 1 1 2
323 1 2 2
324 1 3 2
325 2 1 2
326 2 2 2
327 2 3 2
328 3 1 2
329 3 2 2
330 3 3 2

TmpData
Id UserID PrgID RoldID
82 1 1 3
83 1 2 3
84 1 3 3
85 2 1 3
86 2 2 3
87 2 3 3
91 20 1 2
92 20 2 2
93 20 3 2
94 21 1 2
95 21 2 2
96 21 3 2

Now I need to run a query so that
Part 1: It updates existing RoleId columns ( Based on Userid,PrgID) in 'Data' Table with corresponding values from 'Data' table.
Part2 : It inserts new rows in 'TmpData' to 'Data' table.

I am done with Part1 using the simple update statement.

Update Data
Set Data.programroleid=tmp.ProgramRoleID
from TmpData tmp
where Data.userid=tmp.UserId
and Data.programId=tmp.ProgramId

This works fine for existing userids,programids in 'Data' and 'TmpData' tables. But I am struggling with inserting new rows into Data from 'TmpData' ( That exists only in 'tmpData' table). Based on above table structures how do I insert new data into 'Data' table from 'TmpData'.INSERT INTO Data(ID, UserID, PrgID, RoldID)
SELECT * FROM TmpData
WHERE ID not in (SELECT ID FROM TmpData Inner Join Data on TmpData.UserID=Data.UserID and TmpData.ProgID=Data.PrgID and TmpData.RoldID=Data.RoldID)|||ID is auto-generated identity value, and thus cannot be inserted into the target table (unless you specifically disable this for the transaction...).
I wouldn't expect the identity values to match between Data and TmpData anyway, so the "NOT IN" method is probably not appropriate. It is also not efficient. NOT EXISTS is faster than NOT IN.

INSERT INTO Data
(ID,
UserID,
PrgID,
RoldID)
SELECT ID,
UserID,
PrgID,
RoldID
FROM TmpData
WHERE NOT EXISTS
(SELECT *
FROM Data
WHERE Data.UserID = TmpData.UserID
and Data.PrgID = TmpData.PrgID
and Data.RoldID = TmpData.RoldID)

need query by MAX date

Hello All,
I have the following table structure:
CREATE TABLE [dbo].[tbl_PP_PermitStatusDates] (
[Status_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Permit_ID] [int] NOT NULL ,
[Status_Type] [tinyint] NOT NULL ,
[Status_Date] [datetime] NOT NULL
) ON [PRIMARY]
My data is:
INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,2,'4/2/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,2,'4/4/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(818,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,2,'4/2/2005')
I need to make a query to get out a recordset that is the entire row for
the MAX(Status_Date) for that Permit_ID, so like this:
Status_ID Permit_ID Status_Type Status_Date
2 816 2 '4/2/2005'
4 817 2 '4/4/2005'
5 818 1 '4/1/2005'
7 819 2 '4/2/2005'
Mia J.
*** Sent via Developersdex http://www.examnotes.net ***Try,
select [Status_ID], [Permit_ID], [Status_Type], [Status_Date]
from [dbo].[tbl_PP_PermitStatusDates] as a
where [Status_Date] = (select max(b.[Status_Date]) from
[dbo].[tbl_PP_PermitStatusDates] as b where b.[Permit_ID] = a.[Permit_ID])
AMB
"Mij" wrote:

> Hello All,
> I have the following table structure:
> CREATE TABLE [dbo].[tbl_PP_PermitStatusDates] (
> [Status_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Permit_ID] [int] NOT NULL ,
> [Status_Type] [tinyint] NOT NULL ,
> [Status_Date] [datetime] NOT NULL
> ) ON [PRIMARY]
> My data is:
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,2,'4/2/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,2,'4/4/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(818,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,2,'4/2/2005')
> I need to make a query to get out a recordset that is the entire row for
> the MAX(Status_Date) for that Permit_ID, so like this:
> Status_ID Permit_ID Status_Type Status_Date
> 2 816 2 '4/2/2005'
> 4 817 2 '4/4/2005'
> 5 818 1 '4/1/2005'
> 7 819 2 '4/2/2005'
> Mia J.
> *** Sent via Developersdex http://www.examnotes.net ***
>|||On Fri, 01 Apr 2005 13:25:54 -0800, Mij wrote:
(snip)

>I need to make a query to get out a recordset that is the entire row for
>the MAX(Status_Date) for that Permit_ID, so like this:
Hi Mia,
Thanks for posting CREATE TABLE and INSERT statements. You can use:
SELECT Status_ID, Permit_ID, Status_Type, Status_Date
FROM tbl_PP_PermitStatusDates AS a
WHERE Status_Date =
(SELECT MAX(Status_Date)
FROM tbl_PP_PermitStatusDates AS b
WHERE b.Permit_ID = a.Permit_ID)
ORDER BY Status_ID
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||That does seem to work. Thanks.
Mia J.
*** Sent via Developersdex http://www.examnotes.net ***|||Hello Mij,
This one works fine :
select (select Status_ID
from tbl_PP_PermitStatusDates B
WHERE B.Status_Date = MAX(A.Status_Date)
and B.Permit_ID = A.Permit_ID) StatusID,
A.Permit_ID ,
(select Status_Type
from tbl_PP_PermitStatusDates B
WHERE B.Status_Date = MAX(A.Status_Date)
and B.Permit_ID = A.Permit_ID) StatusType,
MAX(A.Status_Date)
from tbl_PP_PermitStatusDates A
Group by A.Permit_ID
Thanks,
Gopi
"Mij" <mdsj@.infi.net> wrote in message
news:uJxBkFwNFHA.2392@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I have the following table structure:
> CREATE TABLE [dbo].[tbl_PP_PermitStatusDates] (
> [Status_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Permit_ID] [int] NOT NULL ,
> [Status_Type] [tinyint] NOT NULL ,
> [Status_Date] [datetime] NOT NULL
> ) ON [PRIMARY]
> My data is:
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,2,'4/2/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,2,'4/4/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(818,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,2,'4/2/2005')
> I need to make a query to get out a recordset that is the entire row for
> the MAX(Status_Date) for that Permit_ID, so like this:
> Status_ID Permit_ID Status_Type Status_Date
> 2 816 2 '4/2/2005'
> 4 817 2 '4/4/2005'
> 5 818 1 '4/1/2005'
> 7 819 2 '4/2/2005'
> Mia J.
> *** Sent via Developersdex http://www.examnotes.net ***

Need Opinions on Updating Large tables

I have an incomming table that is similar to the following...
locationNbr, SalesDate, SalesAmt, TranCode, SalesQty
I have to update a table that is formated as such...
locationNbr, historyType, Year, D001, D002, D003, D004... --> D365
I am running a join from the incoming table to the table I need to update to
determine what records I need to Insert.
Then I am using a VB app to create a batch of update statements (because I
couldnt figure out how to dynamically specify columns in T-sql) for those
whose key records already exist.
I would imagine this would run alot faster if I could figure out a way to do
this within an sproc.
Is there a good way that I can dynamically build a SQL Update statement
within an sproc?
And, as I am not extremely familiar with MSSQL, is there any other fast
update methods when you are updating large tables?
Thanks for any input.Jace wrote:
> I have an incomming table that is similar to the following...
> locationNbr, SalesDate, SalesAmt, TranCode, SalesQty
> I have to update a table that is formated as such...
> locationNbr, historyType, Year, D001, D002, D003, D004... --> D365
>
You can use dynamic SQL, but I'm not sure it's going to provide any
improvements over all the processing your going to have to do to move
that nicely normalized table into one that looks strangely denormalized.
What's the story behind the denormalized table? What about leap years?
They have 366 days.
Check out sp_executesql in BOL for more information about how to build
dynamic SQL statements from in T-SQL.
David Gugick
Imceda Software
www.imceda.com|||Well, actually it has 371 columns, but it only uses the last 7 col every 9
years. The reason for the denorm format is for speed in accessing the data
for a forecasting method. This system forecasts 100'000+ skus in several
thousand locations, and I am told that using this table vs a nomalized table
increases the speed a significant amount.
Thanks for your input. And um... What is BOL ? :)
"David Gugick" wrote:

> Jace wrote:
> You can use dynamic SQL, but I'm not sure it's going to provide any
> improvements over all the processing your going to have to do to move
> that nicely normalized table into one that looks strangely denormalized.
> What's the story behind the denormalized table? What about leap years?
> They have 366 days.
> Check out sp_executesql in BOL for more information about how to build
> dynamic SQL statements from in T-SQL.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||BOL is Books Online - the help documentation that comes with SQL Server.
The denormalized table design is potty and will not scale. Analysis Services
and indexed views are tools designed for this kind of work and can routinely
handle 100s of millions of rows of data.
David Portas
SQL Server MVP
--|||Jace wrote:
> Well, actually it has 371 columns, but it only uses the last 7 col
> every 9 years. The reason for the denorm format is for speed in
> accessing the data for a forecasting method. This system forecasts
> 100'000+ skus in several thousand locations, and I am told that using
> this table vs a nomalized table increases the speed a significant
> amount.
> Thanks for your input. And um... What is BOL ? :)
>
David is right. While denormailzed tables have been used for years in
read-only formats for reporting, it makes sense here to test a properly
designed, nomalized solution before commiting to this one.
David Gugick
Imceda Software
www.imceda.com|||Thanks for the input. Im going to do some testing on this in a denorm table
.
It appears the row count will be around 6.5 billion. Do you think that I
may run into trouble with that many records? Or will I have to split
different history types into different tables?
"David Portas" wrote:

> BOL is Books Online - the help documentation that comes with SQL Server.
> The denormalized table design is potty and will not scale. Analysis Servic
es
> and indexed views are tools designed for this kind of work and can routine
ly
> handle 100s of millions of rows of data.
> --
> David Portas
> SQL Server MVP
> --
>|||You'll almost certainly want to look at Analysis Services and Partitioned
Views for this.
Billions of rows are not uncommon - with the right hardware there is no
problem in principle with tables of that size. You should probably consult o
r
hire someone with AS and VLDB experience though.
David Portas
SQL Server MVP
--

Need ID after OLEDB command (insert)

Hi,

I'm stuck on the following thing:

After a slowly changing dimension task I replaced the OLE DB Destination task by an OLE DB Command and created the insert manually. This because I need to work further on the dataset. So I do a union all between the output of the two OLE DB Commands (insert and update). Untill here no problem. But than, because I need an ID further on, I do a lookup in the table in which I just inserted and updated my data for the right ID's. When I run this project I get the error message "row yielded no match during lookup".

I don't understand this beacause I just inserted the data and I've checked, it's there.

I could resolve this by splitting up in two control flows (reselect all the needed data wíth the ID-field in my selection) but I would prefer to solve it in another way

Greets,

Tom

You cannot be assured that the inserted records exist and are avaiable to you until after the data-flow completes. You will need two data-flows I'm afraid. Use a raw file to pass data between them if you require.

-Jamie

|||

Hi Jamie,

In my opinion SSIS does a sort of precaching (I mean loading the lookup table in to memory before real execution of the whole data flow task and that must be the reason why he can't perform a lookup in an 'empty' table during the execution). << Can't I force him not to cache that table? just an idea >>

But in order to continue and to satisfy my boss, I will split it up...

Thanks anyway

Greets,
-Tom

|||

Tom DC wrote:

Hi Jamie,

In my opinion SSIS does a sort of precaching (I mean loading the lookup table in to memory before real execution of the whole data flow task and that must be the reason why he can't perform a lookup in an 'empty' table during the execution). << Can't I force him not to cache that table? just an idea >>

But in order to continue and to satisfy my boss, I will split it up...

Thanks anyway

Greets,
-Tom

Yes, you can easily configure the LOOKUP component not to cache any data. Still though you cannot guarantee that a row that you think will be there will indeed be there. [This is due to the buffer architecture of the pipeline which is elaborated on at various places around the internet and in textbooks if you fancy some heavy reading.]

So, regardless of your caching options you still need two data-flows.

-Jamie

|||

Allright, I've split it up. Thanks for your help!

|||

Setting the CacheType to none (and using a lookup table instead of SQL statement to avoid weird errors about unmatched parameters) did solve our problem. The data we expect to find in the table was written in the same dataflow by an OLE DB Command - it is not part of the dataflow itself.

Also, the lookup happens in a different "Execution Tree" than the insert via the OLE DB Command.

I think we are safe and do not have to split the dataflow.

Tom (the satisfied boss)

|||

Tom VdP wrote:

Setting the CacheType to none (and using a lookup table instead of SQL statement to avoid weird errors about unmatched parameters) did solve our problem. The data we expect to find in the table was written in the same dataflow by an OLE DB Command - it is not part of the dataflow itself.

Also, the lookup happens in a different "Execution Tree" than the insert via the OLE DB Command.

I think we are safe and do not have to split the dataflow.

Tom (the satisfied boss)

Fair enough. I can only advise as strngly as I possibly can that you do not under any circumstances do this. There is no way to guarantee that a record that you expect to be in teh table will indeed be there. This is because SSIS processes data buffer-by-buffer, not row-by-row.

It is of course up to you

-Jamie

|||

Jamie,

I understand. But how could SSIS treat an OLEDBCommand differently than just directly executing it ? The data flow engine doesn't know what the SQL command does, hence there is no way it would be able to buffer the result. Every "row" in the dataflow below the OLEDBCommand must have been processed (in our case: inserted in a table). Or am I thinking too much the old "DTS-way" ?

Regards,

Tom

|||

Tom VdP wrote:

Jamie,

I understand. But how could SSIS treat an OLEDBCommand differently than just directly executing it ? The data flow engine doesn't know what the SQL command does, hence there is no way it would be able to buffer the result. Every "row" in the dataflow below the OLEDBCommand must have been processed (in our case: inserted in a table). Or am I thinking too much the old "DTS-way" ?

Regards,

Tom

Tom,

Basically, yeah. You're thinking in DTS terms. The data-flow processes rows in groups called buffers whereas DTS was more row-by-row. Hence, if a row enters the LOOKUP component and it requires a row from the same buffer to be in the lookup table - it won't (yet) be in the lookup table.

So when i talk about buffers - I don't mean that any result is being buffered. Sorry, I should have elaborated on that more. The buffer architecture is a key feature of SSIS and I highly recommend you read around it to understand what it does and why it does it.

-Jamie

|||

Slight misunderstanding :-)

I thought you were hinting at the fact that the output of the OLEDBCommand might not yet be available. But you are referring to the input of the Lookup component. But then what is the CacheType property for ? If this is set to "none", doesn't it indicate that each lookup should be done against the actual contents of the table ? This property is undocumented in MSDN...

Regards,

Tom

|||

Correct. But like I'm trying (and failing miserably ) to explain is that there is no guarantee that a row put there by an OLE_DB Command/Destination adapter/whatever... will be available to all subsequent rows in the data-flow.

-Jamie

|||

Ok... to roundup allow me to summarize:

all data below an OLEDBCommand will have had its corresponding SQL statement executed, hence those changes are visible in the database

need helps in this store procedure?

Hi Everyone,

I appreciate if you can help on the following procedure. When I ran it in my project in Visual Studio, the compiler always complained that it expected a parameter "@.stud_id" that was not provided. What was wrong with this procedure?

Thank you for your help.

a123.

ALTER Procedure registerstudent
(
@.email nvarchar(100),
@.student_password nvarchar(10),
@.first_name nvarchar(25),
@.last_name nvarchar(25),
@.address nvarchar(255),
@.city nvarchar(50),
@.province nvarchar(25),
@.country nvarchar(50),
@.phone_no_cell nvarchar(25),
@.phone_no_home nvarchar(25),
@.hour_rate int,
@.status char(1),
@.stud_id int output

)
As

INSERT INTO student
(
email,
student_password,
first_name,
last_name,
address,
city,
province,
country,
phone_no_cell,
phone_no_home,
hour_rate,
status

)
VALUES
(

@.email,
@.student_password,
@.first_name,
@.last_name,
@.address,
@.city,
@.province,
@.country,
@.phone_no_cell,
@.phone_no_home,
@.hour_rate,
@.status

)

SELECT
@.stud_id = @.@.identity

Did you add an output SqlParameter to your code?

Ryan

|||

Need to know how you execute the sp in your code BTW, usually we use SCOPE_IDENTITY() instead of @.@.identity. For the differences, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp

need help, How to change datatype in order to sort some extracted numbers from string

Hello,

I am trying to extract from some strings like the following strings the number and order them by that number:

Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)

The problem I am having is that I already extracted the number using

Substring([field],[starting position],[lenght])

but the output seems to be in a string format, so the order is not in an ascending order.

Thanks for any suggestions.

You need to cast the result as a number. It looks like your number is always an integer so the following should do it:

(DT_I4)Substring([field],[starting position],[lenght])

-Jamie

|||I tried it but I am getting an SQL Execution Error message,

I am doing this on the Query Builder using Visual Studio 2005.net

|||

If you're getting an error message its useful to post it up here!

-Jamie

|||Sorry, I am behind a very restrictive firewall at work, doesnt let me get inthere!

Why should I post it somewhere else, anyways?

|||

mendez_edd wrote:

Sorry, I am behind a very restrictive firewall at work, doesnt let me get inthere!

It doesn't let you get to where? Can you replicate the error in BIDS?

mendez_edd wrote:

Why should I post it somewhere else, anyways?

Posting the error message helps people deduce what is causing the error!

-Jamie

|||

Where are you doing this? Jamie’s solution was for a SSIS expression as you might use in the Derived Column Transform. Your did mention a SQL exception, but since this is a SSIS forum, we are kind of thinking you may want a SSIS solution.

To sort like a number, convert the value to a number, so strip off the text as Jamie suggested, or pad the numeric part with spaces ans sort as a string–
Box 1
Box 2
Box 21
Box 22

(Note the extra space for single digit numbers.)

Regardless of your firewall you can post to this forum, so could you not type the error message text you get into a post?

Need help, error with store proc

I am trying to do an insert with a SPand I get the following error: "The name 'A2LA Website' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."
When iParentID=30 and strTexte="A2LA Website"

What an I doing wrong?

Table:
ID int(identity)
ParentID int
Text nvarchar(50)
Valeur nvarchar(50) Allow Null
Ordre int Allow Nulls

 Public Sub addItemToDdSelection(ByVal strTable As String, ByVal iParentID As Int16, ByVal strTexte As String)
 Dim connect As New SqlConnection(strConnection)
 Dim cmdSelect As New SqlCommand("AddDropDownContent", connect)
 Dim paramReturnValue As SqlParameter
 Dim strError As String
 cmdSelect.CommandType = CommandType.StoredProcedure
 'PARAM
 cmdSelect.Parameters.Add("@.intParentID", iParentID)
 cmdSelect.Parameters.Add("@.strTexte", strTexte)
 Try
 connect.Open()
 cmdSelect.ExecuteNonQuery()
 connect.Close()
 Catch ex As Exception
 strError = ex.Message
 Finally
 If connect.State = ConnectionState.Open Then
 connect.Close()
 End If
 End Try
 End Sub
ALTER PROCEDURE dbo.AddDropDownContent
(
@.intParentID int,
@.strTexte varchar(50)
)
AS
EXEC ('INSERT INTO DropDownMenus (ParentID, Texte) VALUES(' + @.intParentID + ',"' + @.strTexte + '")')

(1) when you add parameters, specify their type/lengths too
xample :

cmdSelect.Parameters.Add("@.intParentID", iParentID)

should be

cmdSelect.Parameters.Add(New SqlParameter("@.intParentID",SqlDbType.int))
cmdSelect.Parameters("@.intParentID").Value = Trim(iParentID)

and
(2) you dont need dynamic sql to insert. you can modify your insert stmt as

Create Procedure ...
AS
SET NOCOUNT ON
 INSERT INTO DropDownMenus (ParentID, Texte) VALUES ( @.intParentID ,@.strTexte )
SET NOCOUNT OFF

hth|||Thanks for your reply this is really helping. A few questions,
-Trim(iParentID) is used in case the id is enter from a text box I guess?
-What is the role of the line "SET NOCOUNT ON" and "SET NOCOUNT OFF"?|||>>-Trim(iParentID) is used in case the id is enter from a text box I guess?

yes. you might also want to do a

Convert.ToInt16(Trim(iParentId))

to make sure you are sending in an integer type value. Its a good idea to validate user input.

>>What is the role of the line "SET NOCOUNT ON" and "SET NOCOUNT OFF"?

read up BOL (Books On Line)

hth

Need help writing stored procedure involving dates

I am trying to write a stored procedure which would execute following logic:

- The stored procedure takes 2 optional parameters @.StartDate and @.EndDate

@.StartDate Datetime = null
@.EndDate Datetime = null

- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @.StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @.EndDate as the ED and MIN() of the Date field as SD

I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below

CREATE PROCEDURE SearchDocumentTable

@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null


AS
IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND LName LIKE ISNULL(@.LName,'%' + NULL)
AND ID LIKE ISNULL(@.ID,'%' + NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

Any help would be appreciated
Thanks in advance :)I'm a little confused. What is your question ?

did you mean except for incoporating NULLS ?

Cheers,
-Kilka|||I am sorry wasnt thinking right when I posted that to the forum in the morning. Let me try to explain my problem with an example.
To make it simple lets say I have a simple table with 3 columns: FName, LName and Date

FName LName Date
Aab 02/05/2005
Abc Bb 02/06/2005
Aaaa Bbb
02/07/2005
Baaaa Bbb
Baaca 02/07/2005
Caa Bbbb 02/07/2005

As can be seen that FName, LName and Date columns accept NULL values.
Now I want to write a stored procedure which takes 4 parameters ( all of them are optional) @.FName, @.LName, @.StartDate and @.EndDate and do a search on this table.
I am having trouble handling these NULL fields.

When I execute the stored procedure, I should get results as follows:
- If user enters @.FName LIKE 'A%', sp should return only the rows where first name matches that format ( no null first name or null last names or null dates should be returned). Result should be first 3 rows with first names: Aab, Abc, Aaaa
- Similarly when searching for start date and end date, procedure should return only the rows which match the date criteria ignoring null first name and last name fields

I hope this example would prove helpful, since everytime I try to write a query I always end up getting rows with null fields.
Thanks again for your time :)|||Do you really have '%' + NULL in the code? I would think you just want '%'.

Are you getting any data back from this query when you have data that looks like your example?|||You guys are good sorry forgot to update the stored procedure.
No, '%' + NULL doesnot return any records. So I changed the stored procedure and use only '%' for all null parameters and adds OR FIELDNAME IS NULL at the end:

CREATE PROCEDURE SearchDocumentTable

@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null

AS

IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND (LName LIKE ISNULL(@.LName,'%') OR LName IS NULL)
AND (ID LIKE ISNULL(@.ID,'%') OR ID IS NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

This is the stored procedure and as can be seen it will return null fields when I pass last name, ID or date as the parameter.
Any ideas how can I modify the stored procedure to avoid returning null fields.

Thank you again guys, I really appreciate your help|||Do you get the correct results if you take out OR LName IS NULL and
OR ID IS NULL and OR DateInputted IS NULL?

If this is not the case then please type out using the example data you used above the exact results you would like to see if all the input fields are NULL.|||First, decide on precedence. What if the user passes all three parameters?

Assuming the precedence is LastName, FirstName, Date:Declare @.t Table(Table_Pk int identity(1,1), FirstName varchar(3) Null, LastName varchar(3) Null, EndDate datetime Null)

Insert @.t (FirstName, LastName, EndDate)
Select 'Kim', 'Cat', GetDate()
Union
Select 'Pat', 'Dog', Null
Union
Select 'Ted', Null, GetDate()
Union
Select 'Jim', Null, Null
Union
Select Null, 'Fox', GetDate()
Union
Select Null, 'Fox', Null
Union
Select Null, Null, GetDate()
Union
Select Null, Null, Null

Select * From @.t

Declare @.FirstName varchar(3)
, @.LastName varchar(3)
, @.EndDate datetime

Select @.LastName = 'F'

Select *
From @.t
Where (@.LastName Is Not Null And LastName Like @.LastName + '%')
Or
(@.LastName Is Null And @.FirstName Is Not Null And FirstName Like @.FirstName + '%')
Or
(@.LastName Is Null And @.FirstName Is Null And EndDate > = Coalesce(@.EndDate, '01/01/1950') )|||FYI, if you test for the IF statements and, inside the stored procedure, call a different function for each test to return the final result set, you will have no recompiles. This will make the overall execution of the stored proc much faster when you get larger recordsets. You also might want to consider having the developers use a checkbox for exact match. That sounds stupid, but the users will learn to love it if this table gets extremely large.

Need help writing a SQL Server Query

Could someone please help me out? I need to write a sql stored proc to query the following table.
My SQL experience is very week. If someone can help me with this, I will be happy to pay you$40 for
your help.

I need the proc to do the following:
1.) For every Superintendent in a region, country state and county; return the state name, superintendent name,
the county name and and a string which is a comma delimited list of schools they supervise. See the sample output italicised and bold.

So the big challenge here is to also return a string that is a concatenation of school names for a particular
Superintendent in a given state and county. For example:East,Kennedy,Apolo,Morrison.

So basically the stored proc should accept input parameters of the Region, Country, State, and County

Here is the data table:

REGION COUNTRY STATE SUPER_INTENDENT PHONE_NO SCHOOL County

NA USA Texas Mike Andrews 789-3614 East Lake
NA USA Texas Mike Andrews 789-3614 Kennedy Lake
NA USA Texas Mike Andrews 789-3614 Apolo Lake
NA USA Texas Mike Andrews 789-3614 Morrison Lake
NA USA Texas Amy Markson 789-2134 Anderson Maylor
NA USA Texas Amy Markson 789-2134 Molina Maylor
NA USA Texas Amy Markson 789-2134 Polima Maylor
NA USA Ohio Terry Ellis 966-8314 Kingston Keel
NA USA Ohio Terry Ellis 966-8314 Martin Keel
NA USA Ohio Terry Ellis 966-8314 Eastmore Keel
NA USA Ohio Terry Ellis 966-8314 Canondale Keel

Here is the sample output the way it will appear on a web form:

State:Texas

County:Lake

Mike Andrews East,Kennedy,Apolo,Morrison
789-3614

County:Maylor

Amy Markson
789-2134 Anderson,Molina,Polima

State:Ohio

County:Keel

Terry Ellis Kingston,Martin,Eastomore,Keel

You can mail me the check..

You could create a stored proc that would concatenate the values based on given state and county.

Declare

@.schoolvarchar(500)

select

@.school=isnull(@.school,'')+','+ school

from

yourTable

where

state= @.state and county=@.county

Given the above hint, I hope you can figure out the rest.

|||Sorry your post has not helped me at all. I need someone to write the query for me. I will be happy to pay that person. please do not respond to this post if you are not serious about helping me.|||

ndinakar:

You can mail me the check..

That was meant to be a joke. I forgot to put a "smiley face" at the end. I was trying to show you how to write the code yourself since what you are trying to do is pretty simple. Now, I do agree that if you cannot figure out how to put the rest of the puzle together, you definetely need someone to write you the code. And perhaps you will need to spend more than the $40 you are offering..

|||

I appreciate your sense of humor. No problem.

Another fellow on the forum has offered to help me. I will see take his advice and try to get it working. Thank you.

Need help writing a query

Database consists of the following 4 tables with respective
attributes:

CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]
ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]
BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]
PREFER(I#, C#), the key is [I#, C#]

I'm trying to construct the following query (in SQL)

List of customers that bought all the items that John prefers.

I can get the list of all the items that John prefers, but I'm not
sure how to check that list against customers who bought ALL those
items. I'm assuming it's either a division or some sort of subtraction
but I'm not sure how to formulate the SQL query.

Any and all help is appreciated, thanks!(tizmagik@.gmail.com) writes:

Quote:

Originally Posted by

Database consists of the following 4 tables with respective
attributes:
>
CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]
ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]
BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]
PREFER(I#, C#), the key is [I#, C#]
>
I'm trying to construct the following query (in SQL)
>
List of customers that bought all the items that John prefers.
>
I can get the list of all the items that John prefers, but I'm not
sure how to check that list against customers who bought ALL those
items. I'm assuming it's either a division or some sort of subtraction
but I'm not sure how to formulate the SQL query.

This smells of class assignment, but OK, let's go for it anyway.

If memory serves this is something they for some reason I've never
understood call relational division. In less occluded terms, a HAVING
clause can shortcut the need for a couple of EXISTS and NOT EXISTS.

SELKCT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN (SELECT B.C#
FROM BOUGHT B
GROUP BY B.C#
HAVING COUNT(DISTINCT B.I#) =
(SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER C ON P.C# = C.C#
WHERE C.CUSTOMER_NAME = 'John')) AS res
ON C.C# = res.C#

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

(tizmagik@.gmail.com) writes:

Quote:

Originally Posted by

>Database consists of the following 4 tables with respective
>attributes:
>>
>CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]
>ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]
>BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]
>PREFER(I#, C#), the key is [I#, C#]
>>
>I'm trying to construct the following query (in SQL)
>>
>List of customers that bought all the items that John prefers.
>>
>I can get the list of all the items that John prefers, but I'm not
>sure how to check that list against customers who bought ALL those
>items. I'm assuming it's either a division or some sort of subtraction
>but I'm not sure how to formulate the SQL query.

>
This smells of class assignment, but OK, let's go for it anyway.
>
If memory serves this is something they for some reason I've never
understood call relational division. In less occluded terms, a HAVING
clause can shortcut the need for a couple of EXISTS and NOT EXISTS.
>
SELKCT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN (SELECT B.C#
FROM BOUGHT B
GROUP BY B.C#
HAVING COUNT(DISTINCT B.I#) =
(SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER C ON P.C# = C.C#
WHERE C.CUSTOMER_NAME = 'John')) AS res
ON C.C# = res.C#

That will select all customers who bought the same /number/ of
items as what John prefers, but not necessarily the same items.

I think this will select all customers who bought all the items
that John prefers:

SELECT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN BOUGHT B ON C.C# = B.C#
JOIN PREFER P ON B.I# = P.I#
JOIN CUSTOMER J ON P.C# = J.C# AND J.CUSTOMER_NAME = 'John'
GROUP BY C.C#, C.CUSTOMER_NAME
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER J ON P.C# = J.C# AND J.CUSTOMER_NAME = 'John'
)|||Erland: Why would it matter if it's a class assignment or not? Is not
the purpose of a Usenet group to share and learn from each other? What
relevance is it what the knowledge will be used for? Thank you for
your attempt anyway, but Ed's answer seems more in line with what the
query is intended to do.

Thank you Ed, seems to be what I'm looking for, it's interesting, I
never even though of setting up a Count, but now that I look at it,
it's hard to imagine any other way of doing it.

Thanks again :)|||tizmagik@.gmail.com wrote:

Quote:

Originally Posted by

Thank you Ed, seems to be what I'm looking for, it's interesting, I
never even though of setting up a Count, but now that I look at it,
it's hard to imagine any other way of doing it.

I thought of doing J JOIN P LEFT JOIN B and looking for nulls, but I
can't figure out a way to do it, and even if there is one, it would
probably be less clear than the COUNT = COUNT method.|||<tizmagik@.gmail.comwrote in message
news:1176697766.757819.271590@.b75g2000hsg.googlegr oups.com...

Quote:

Originally Posted by

Erland: Why would it matter if it's a class assignment or not?

It matters if you're asking others to do your homework. Some professors
frown upon that. (and it could, in some cases, be considered a form of
cheating.)

Quote:

Originally Posted by

Is not
the purpose of a Usenet group to share and learn from each other?

Oh certainly. And I think Erland would agree, many of us here love to help
others (and certainly to learn from others). But from time to time (and I'm
not claiming you're one of them) who come here looking simply for answers to
homework problems, not necessarily understanding. That benefits no one in
the long run.

Quote:

Originally Posted by

What
relevance is it what the knowledge will be used for? Thank you for
your attempt anyway, but Ed's answer seems more in line with what the
query is intended to do.
>
Thank you Ed, seems to be what I'm looking for, it's interesting, I
never even though of setting up a Count, but now that I look at it,
it's hard to imagine any other way of doing it.
>
Thanks again :)
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>Why would it matter if it's a class assignment or not? Is not the purpose of a Usenet group to share and learn from each other? <<

In most university systems having someone else do your homework gets
you kicked out of school. It is academic fraud. I know. I have had
two kids expelled from schools in New Zealand and Australia for doing
this. An old friend of mine got a "social engineer" taken out of
Georgia Tech; etc.|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Why would it matter if it's a class assignment or not? Is not the purpose of a Usenet group to share and learn from each other? <<

>
In most university systems having someone else do your homework gets
you kicked out of school. It is academic fraud. I know. I have had
two kids expelled from schools in New Zealand and Australia for doing
this. An old friend of mine got a "social engineer" taken out of
Georgia Tech; etc.

Same rule applies here at the University of Washington.

Get caught cheating and it is a one-way trip.

Anyone that thinks instructors such as myself are not watching
these groups is in the wrong business.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||(tizmagik@.gmail.com) writes:

Quote:

Originally Posted by

Erland: Why would it matter if it's a class assignment or not? Is not
the purpose of a Usenet group to share and learn from each other?

But Usenet is not the best place to learn everything. If you have some
experience in the field of SQL programming, I can assume that you can
understand the solution I post to some extend and learn from it.

But if you are a student who is not interested in doing his homework?

I remember way back when, when I was a student myself, and also worked as
an assistant teacher in programming. Back in those days, the assignments
were made on paper, and when the student was approved for this week's
exercise I would give him a paper with the "ideal" solution. Sometimes
it happened that students arrived to the classroom with this ideal
solution, in which case I told them not do to it again. And I did not
approve them for that assignment. (It was permitted to miss one or two.)

One year I had a group in Programming 2, an optional class which taught
programming structures. I had one guy who consistently arrived with
the ideal solution, and I knew that his girlfriend was taking the same
class. I figured that at this stage, he should know better than cheating,
so I did not say anything. I approved his "solutions" without a comment
and let him go. But these assignments were not all - there was a written
exam as well. And when the results came up, his girl-friend was there.
But, not surprisingly, he wasn't. He had just copied the ideal solutions,
but he hadn't learnt anything.

Quote:

Originally Posted by

Thank you Ed, seems to be what I'm looking for, it's interesting, I
never even though of setting up a Count, but now that I look at it,
it's hard to imagine any other way of doing it.

Sorry for the incorrect solution, but there is a standard recommendation
for this type of questions, and that is that you post:

o CREATE TABLE statements for your table(s).
o INSERT statements with sample data.
o The desireed output, given the sample.

That makes it easy to copy and paste to develop a tested solution. Without
that, most people here tend to just type something up, and sometimes
there are errors.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am fairly new to SQL programming and believe me that wasn't the only
thing that the assignment asked, however this question was the one
question that I had a lot of trouble with and the lack of a book for
the class (it's strictly lecture notes) was what brought me to look
for help elsewhere.

If I would have based the SQL query on the examples given by the
professor I would have gotten a list of all customers who bought *at
least one* item that "john" prefers, as apposed to the correct list
(all customers who bought *all* the items that john prefers).
Furthermore, the professor did not go over COUNT so I really did not
see any way of doing it with what he has gone over so far. Perhaps
there is a solution without using COUNT, I will be sure to ask the
professor during next lecture.

Thanks for those that helped.|||On 16 Apr 2007 21:15:59 -0700, tizmagik@.gmail.com wrote:

Quote:

Originally Posted by

Perhaps
>there is a solution without using COUNT, I will be sure to ask the
>professor during next lecture.

Hi tizmagik,

Indeed, there is. It is called "inverse logic". If a customer has bought
every item John prefers, than clearly, there can not be any single item
that is preferred by John but that the customer didn't buy. I'm sure
that you're able to cough up the actual query for that logic. :-)

This solution is actually the solution most people produce first for
this problem. Maybe because many classes explain subqueries and NOT
EXISTS before moving on to aggregates and HAVING? Or maybe it's just
related to how our brain functions? Anyway, the version as posted by Ed
looks like (I didn't check in detail) the second standard solution to
this problem, based on the logic "if a customer buys everything John
prefers, then the number of items bought by the customers *and* prefered
by John must be equal to the number of items prefered by John. Outside
of class, you'd probably try both against the actual data on the actual
database to figure out which one gives the best performance.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||(tizmagik@.gmail.com) writes:

Quote:

Originally Posted by

I am fairly new to SQL programming and believe me that wasn't the only
thing that the assignment asked, however this question was the one
question that I had a lot of trouble with and the lack of a book for
the class (it's strictly lecture notes) was what brought me to look
for help elsewhere.
>
If I would have based the SQL query on the examples given by the
professor I would have gotten a list of all customers who bought *at
least one* item that "john" prefers, as apposed to the correct list
(all customers who bought *all* the items that john prefers).
Furthermore, the professor did not go over COUNT so I really did not
see any way of doing it with what he has gone over so far. Perhaps
there is a solution without using COUNT, I will be sure to ask the
professor during next lecture.

So that's another problem with asking for help with class assignments
on Usenet. While the COUNT may be a more elegant solution, the professor
probably wanted you to exercise in the use of EXISTS and NOT EXISTS.
Which certainly is an investment worth making, because such problem
as commonplace. (While the exercise you had, has a disctinct flavoour
of class assignment. Did I ever encounter such a problem myself? I can't
recall any.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your input guys.

I'll be sure to post here when the solution without using COUNT is
covered (assuming there is one).|||Okay, this is what I have come up with so far:

SELECT c1.Customer_Name
FROM Customer c1
WHERE c1.CustomerID IN (
SELECT B.CustomerID
FROM Bought B
WHERE B.ItemID IN (
SELECT P.ItemID
FROM Prefer P, Customer c2
WHERE c2.Customer_Name = 'John'
AND c2.CustomerID = P.CustomerID ) )

But that brings me back to the problem where it will list customers
that bought *at least one* of the items that John prefers, not ALL of
the items that John prefers, that query gives:
John
Jeremy
Michelle

The expected answer is just 'Michelle' as being the only customer that
bought ALL of the items that John prefers with the following data:

-CUSTOMER table
CREATE TABLE Customer (
CustomerID int(4) NOT NULL,
Customer_Name varchar(30) NOT NULL,
Address varchar(30) NOT NULL,
PRIMARY KEY (CustomerID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Customer VALUES (1000, 'John', '123 John St.');
INSERT INTO Customer VALUES (1001, 'Jeremy', '456 Jeremy Ave.');
INSERT INTO Customer VALUES (1002, 'Michelle', '789 Michelle Blvd.');
INSERT INTO Customer VALUES (1003, 'Laura', '1011 Laura Way');
INSERT INTO Customer VALUES (1004, 'Nicholas', '1004 Nicholas Place');
INSERT INTO Customer VALUES (1005, 'James', '1005 James Drive');

-ITEM table
CREATE TABLE Item (
ItemID int(11) NOT NULL,
Item_Name varchar(30) NOT NULL,
Manufacturer varchar(30) NOT NULL,
`Year` int(4) NOT NULL,
PRIMARY KEY (ItemID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Item VALUES (9000, 'Camera', 'Nikkon', 1997);
INSERT INTO Item VALUES (9001, 'Camera', 'Sony', 1998);
INSERT INTO Item VALUES (9002, 'Camera', 'Olympus', 2001);
INSERT INTO Item VALUES (9003, 'Camera', 'Olympus', 2001);
INSERT INTO Item VALUES (9004, 'Camera', 'Polaroid', 1991);
INSERT INTO Item VALUES (9005, 'Laptop', 'Dell', 2006);
INSERT INTO Item VALUES (9006, 'Laptop', 'HP', 2005);
INSERT INTO Item VALUES (9007, 'Desktop', 'Dell', 2002);
INSERT INTO Item VALUES (9008, 'Desktop', 'Apple', 2004);
INSERT INTO Item VALUES (9009, 'PDA', 'Palm', 2003);
INSERT INTO Item VALUES (9010, 'PDA', 'Handspring', 1998);
INSERT INTO Item VALUES (9011, 'HDTV', 'Sony', 2004);
INSERT INTO Item VALUES (9012, 'HDTV', 'Samsung', 2005);
INSERT INTO Item VALUES (9013, 'HDTV', 'Toshiba', 2003);
INSERT INTO Item VALUES (9014, 'HDTV', 'Mitsubishi', 2003);

-BOUGHT table
CREATE TABLE Bought (
CustomerID int(4) NOT NULL,
ItemID int(4) NOT NULL,
`Date` date NOT NULL,
Quantity int(5) NOT NULL,
PRIMARY KEY (CustomerID,ItemID,`Date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Bought VALUES (1002, 9000, '2007-04-01', 5);
INSERT INTO Bought VALUES (1002, 9001, '2007-04-30', 2);
INSERT INTO Bought VALUES (1002, 9008, '2007-04-09', 1);
INSERT INTO Bought VALUES (1002, 9014, '2007-04-15', 1);
INSERT INTO Bought VALUES (1001, 9001, '2007-04-16', 1);
INSERT INTO Bought VALUES (1001, 9008, '2007-04-16', 1);
INSERT INTO Bought VALUES (1000, 9008, '2007-04-16', 5);
INSERT INTO Bought VALUES (1000, 9001, '2007-04-17', 2);
INSERT INTO Bought VALUES (1005, 9003, '2007-04-16', 2);
INSERT INTO Bought VALUES (1004, 9002, '2007-04-16', 1);
INSERT INTO Bought VALUES (1001, 9011, '2007-02-16', 3);
INSERT INTO Bought VALUES (1001, 9010, '2007-02-16', 3);
INSERT INTO Bought VALUES (1003, 9012, '2007-02-16', 1);
INSERT INTO Bought VALUES (1005, 9013, '2007-02-16', 2);
INSERT INTO Bought VALUES (1004, 9006, '2007-04-01', 1);

-PREFER table
CREATE TABLE Prefer (
ItemID int(4) NOT NULL,
CustomerID int(4) NOT NULL,
PRIMARY KEY (ItemID,CustomerID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Prefer VALUES (9000, 1000);
INSERT INTO Prefer VALUES (9001, 1000);
INSERT INTO Prefer VALUES (9002, 1004);
INSERT INTO Prefer VALUES (9003, 1003);
INSERT INTO Prefer VALUES (9006, 1001);
INSERT INTO Prefer VALUES (9007, 1004);
INSERT INTO Prefer VALUES (9007, 1005);
INSERT INTO Prefer VALUES (9008, 1000);
INSERT INTO Prefer VALUES (9008, 1002);
INSERT INTO Prefer VALUES (9008, 1004);
INSERT INTO Prefer VALUES (9009, 1002);
INSERT INTO Prefer VALUES (9013, 1005);
INSERT INTO Prefer VALUES (9014, 1000);

Again, any help is appreciated. (Yes, the professor didn't go over
AutoNumber fields yet in case you're wondering :) )|||A little closer... I believe this would be the right SQL
theoretically, but this will only work in an Oracle DB or DB that
supports the MINUS operation (MySQL doesnt), so I will try to
reformulate this without using MINUS. I'm guessing it's some sort of
JOIN operation where you check for Nulls and select those that are not
Null...

SELECT B1.CustomerID
FROM Bought B1
WHERE NOT EXISTS (
( SELECT Prefer.ItemID
FROM Prefer, Customer
WHERE Customer.Customer_Name = 'John'
AND Prefer.CustomerID = Customer.CustomerID
) MINUS (
SELECT B2.ItemID
FROM Bought B2
WHERE B2.CustomerID = B1.CustomerID )
)

Any help would be appreciated.|||tizmagik@.gmail.com wrote:

Quote:

Originally Posted by

Okay, this is what I have come up with so far:
>
SELECT c1.Customer_Name
FROM Customer c1
WHERE c1.CustomerID IN (
SELECT B.CustomerID
FROM Bought B
WHERE B.ItemID IN (
SELECT P.ItemID
FROM Prefer P, Customer c2
WHERE c2.Customer_Name = 'John'
AND c2.CustomerID = P.CustomerID ) )
>
But that brings me back to the problem where it will list customers
that bought *at least one* of the items that John prefers, not ALL of
the items that John prefers, that query gives:

I think this will work:

SELECT c1.Customer_Name
FROM Customer c1
WHERE 0 = (
SELECT COUNT(*)
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Name = 'John'
AND B.CustomerID IS NULL
)

but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand.|||On Apr 17, 9:43 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

tizma...@.gmail.com wrote:

Quote:

Originally Posted by

Okay, this is what I have come up with so far:

>

Quote:

Originally Posted by

SELECT c1.Customer_Name
FROM Customer c1
WHERE c1.CustomerID IN (
SELECT B.CustomerID
FROM Bought B
WHERE B.ItemID IN (
SELECT P.ItemID
FROM Prefer P, Customer c2
WHERE c2.Customer_Name = 'John'
AND c2.CustomerID = P.CustomerID ) )

>

Quote:

Originally Posted by

But that brings me back to the problem where it will list customers
that bought *at least one* of the items that John prefers, not ALL of
the items that John prefers, that query gives:

>
I think this will work:
>
SELECT c1.Customer_Name
FROM Customer c1
WHERE 0 = (
SELECT COUNT(*)
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Name = 'John'
AND B.CustomerID IS NULL
)
>
but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand.

Thanks Ed, but I'm trying to avoid using COUNT since that was not
covered in class.|||tizmagik@.gmail.com wrote:

Quote:

Originally Posted by

On Apr 17, 9:43 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

>tizma...@.gmail.com wrote:

Quote:

Originally Posted by

>>Okay, this is what I have come up with so far:
>>SELECT c1.Customer_Name
>>FROM Customer c1
>>WHERE c1.CustomerID IN (
>> SELECT B.CustomerID
>> FROM Bought B
>> WHERE B.ItemID IN (
>> SELECT P.ItemID
>> FROM Prefer P, Customer c2
>> WHERE c2.Customer_Name = 'John'
>> AND c2.CustomerID = P.CustomerID ) )
>>But that brings me back to the problem where it will list customers
>>that bought *at least one* of the items that John prefers, not ALL of
>>the items that John prefers, that query gives:

>I think this will work:
>>
>SELECT c1.Customer_Name
>FROM Customer c1
>WHERE 0 = (
> SELECT COUNT(*)
> FROM Customer C2
> JOIN Prefer P ON C2.CustomerID = P.CustomerID
> LEFT JOIN Bought B ON P.ItemID = B.ItemID
> AND B.CustomerID = C1.CustomerID
> WHERE C2.Customer_Name = 'John'
> AND B.CustomerID IS NULL
>)
>>
>but I still think the positive approach (COUNT = COUNT) is a lot
>easier to understand.

>
Thanks Ed, but I'm trying to avoid using COUNT since that was not
covered in class.

SELECT C1.Customer_Name
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Name = 'John'
AND B.CustomerID IS NULL
)

or

SELECT C1.Customer_Name
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
WHERE C2.Customer_Name = 'John'
AND P.ItemID NOT IN (
SELECT B.ItemID
FROM Bought B
WHERE B.CustomerID = C1.CustomerID
)
)|||On Apr 17, 10:03 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

tizma...@.gmail.com wrote:

Quote:

Originally Posted by

On Apr 17, 9:43 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

tizma...@.gmail.com wrote:
>Okay, this is what I have come up with so far:
>SELECT c1.Customer_Name
>FROM Customer c1
>WHERE c1.CustomerID IN (
> SELECT B.CustomerID
> FROM Bought B
> WHERE B.ItemID IN (
> SELECT P.ItemID
> FROM Prefer P, Customer c2
> WHERE c2.Customer_Name = 'John'
> AND c2.CustomerID = P.CustomerID ) )
>But that brings me back to the problem where it will list customers
>that bought *at least one* of the items that John prefers, not ALL of
>the items that John prefers, that query gives:
I think this will work:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

SELECT c1.Customer_Name
FROM Customer c1
WHERE 0 = (
SELECT COUNT(*)
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Name = 'John'
AND B.CustomerID IS NULL
)

>

Quote:

Originally Posted by

Quote:

Originally Posted by

but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand.

>

Quote:

Originally Posted by

Thanks Ed, but I'm trying to avoid using COUNT since that was not
covered in class.

>
SELECT C1.Customer_Name
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Name = 'John'
AND B.CustomerID IS NULL
)
>
or
>
SELECT C1.Customer_Name
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
WHERE C2.Customer_Name = 'John'
AND P.ItemID NOT IN (
SELECT B.ItemID
FROM Bought B
WHERE B.CustomerID = C1.CustomerID
)
)

That first one didn't work for me, some syntax error, not sure why,
might just be a phpMyAdmin problem, but that second one worked
beautifully.

I'm trying to step through it and understand it line by line now...
this is what I understand from it:

You are selecting all the customers that are not in the following:
- You are selecting all the Items that john prefers, from the list of
items that are not in the list of items that customers have bought

haha really confusing, but I think I get it. Thanks so much for your
help.|||tizmagik@.gmail.com wrote:

Quote:

Originally Posted by

That first one didn't work for me, some syntax error, not sure why,
might just be a phpMyAdmin problem,

You do realize this isn't a MySQL group?

Quote:

Originally Posted by

but that second one worked beautifully.
>
I'm trying to step through it and understand it line by line now...
this is what I understand from it:
>
You are selecting all the customers that are not in the following:
- You are selecting all the Items that john prefers, from the list of
items that are not in the list of items that customers have bought

For each customer, you're looking for items that John prefers
but the customer didn't buy; if there is no such item, then the
customer gets selected.|||I couldn't find a suitable MySQL (or just SQL group) that was as
active as this one. Thanks though.|||(tizmagik@.gmail.com) writes:

Quote:

Originally Posted by

I couldn't find a suitable MySQL (or just SQL group) that was as
active as this one. Thanks though.

There is a comp.databases.mysql. Don't how much traffic there is though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx