items in list A that dont appear in list B (was "Simple Query...I think")

Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true

In pseudo-SQL, I want to write a clause like this

Code:

IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True


Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.

I may have to achieve this with some kind of logical loop but I don't know how to do that.

Can anyone help?OK so it wasnt' so simple...at least MY solution isn't:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC sp_get_trolley_inconsistencies
@.grower CHAR(2),
@.load_id INT
AS

DECLARE @.ID int,
@.TrolleyList varchar(300),
@.Date DATETIME
--Get the relevant date from the DB Table
SELECT @.Date = (SELECT CONVERT(DATETIME, rl_eta, 102) FROM requi_load WHERE rl_id = @.load_id)

SET @.TrolleyList = ''
--Get the list of values into a comma delimited string
DECLARE crs_Trolleys CURSOR
FOR SELECT DISTINCT ldl_trolley
FROM load_detail_lines
WHERE ldl_requi_load_id = @.load_id

OPEN crs_Trolleys
FETCH NEXT FROM crs_Trolleys INTO @.ID

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.TrolleyList = @.TrolleyList+CAST(@.ID AS varchar(5))+ ', '
FETCH NEXT FROM crs_Trolleys INTO @.ID
END

SET @.TrolleyList = SUBSTRING(@.TrolleyList,1,DATALENGTH(@.TrolleyList)-2)

CLOSE crs_Trolleys
DEALLOCATE crs_Trolleys

--Parse the string and run the 'IN' statement on each of the Parsed Values

DECLARE @.parsingList VARCHAR(300)
DECLARE @.find_comma INT
DECLARE @.trolleytocheck VARCHAR(4)

SELECT @.parsingList = @.TrolleyList

WHILE @.parsingList IS NOT NULL

BEGIN
SELECT @.find_comma = PATINDEX('%,%',@.parsingList)
IF @.find_comma <> 0
BEGIN
SELECT @.trolleytocheck = SUBSTRING(@.parsingList,1,(@.find_comma-1))
SELECT @.parsingList = LTRIM(SUBSTRING(@.parsingList,(@.find_comma+1),300))
PRINT @.trolleytocheck
IF @.trolleytocheck IN(SELECT distinct ldl_trolley
FROM load_detail_lines, requi_load
WHERE ldl_requi_load_id = rl_id
AND ldl_requi_load_id NOT LIKE @.load_id
AND rl_status = 1
AND DAY(rl_eta) = DAY(@.Date)
AND MONTH(rl_eta) = MONTH(@.Date)
AND YEAR(rl_eta) = YEAR(@.Date))
BEGIN
RAISERROR 50001 'Error! You screwed up, trolley '
END
CONTINUE
END
ELSE
BEGIN
--this block finds the last value in the trolley list
SELECT @.trolleytocheck = @.parsingList
SELECT @.parsingList = null
PRINT @.trolleytocheck
BREAK
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If anyone can suggest anything more elegant...please let me know.|||Why not join the tables and count the rows that match?|||Doh!

*Commits ritual suicide*

It seems SQL editor do not support "Format" function. Is it right?

When I write SQL command in the editor, I can not find a menu to format my command.

Also the "Format Document" function in VS2005 Beta2 seems do not delete blank rows. I found a good Format tool here. I realy hope MSSQL 2005 has build-in function like that.
http://www.sqlinform.com/

Issue with OLE DB Command while writing to DB2 database

Hi,

I have created a package which uses the OLE DB Command as the target where I write the sql command to insert data into the table. The issue which I am facing is, while at the OLE DB COmmand , the package fails. I notices that it is not able to get the input columns which are mapped to the target columns.

The same package works fine when the target is on Oracle database or a SQL Server database.

For DB2, i have tried using the Microsoft OLE DB Driver for Db2, as the IBM DB2 Driver doesnt work for insert properly.

Any suggestion regarding this would be really helpful.

Thanks,

Manish

Some thoughts on this:

1. It is better to use the OLE DB Destination to insert data into a table instead of the OLE DB Command with an insert statement. You should give that a try.

2. Are you using the Microsoft OLE DB provider for DB2 from our SQL Server 2005 Feature Pack? That's the provider that's been tested with SSIS.

3. I don't understand what you mean by not able to get input columns. What are the error messages when the package fails?

|||

Yes, I am using the Microsoft DB2 drivers only. Using the OLE DB Destination, it worls only for insert. But, I wanted to simulate a scenario, where we do, both insert and update to the target table. So, we used OLE DB Command, and wrote the insert/update query.

The same works fine on Oracle and SQL Server target database. But on DB2, it doesnt work, as in the OLE DB command, we need to map the input columns to the parameters value. The drivers are not able to fetch the input data, and everytime, it insert NULL as it is not able to get the input values.

Let me know if you need any more information on the same.

Issue displaying data

I am a Crystal novice (to say the least) and I am trying to write a report that is presenting some problems. Our company sells memberships to our association and some people have multiple memberships. I need to create a list of all of these people with their memberships listed in columns next to their name. Since each membership is a different product I am having difficulty since it lists the member with all of information for each product. for instance I will group it by the ID# and ge tthe following results:

00001
Mike Smith company name address administrative
Mike Smith company name address business

I need it to display like this:

00001
Mike Smith company name address administrative business

I am not sure how to merge to the two records so that it only displays the personal information once with the two different memberships he purchased.

Does anyone have any ideas?

Thanks.My suggestion - don't use the details section to print stuff, I pratically never do.

Ensure your innermost group is the level at which you want to display data (i.e. the person level)
Have a formula in the details section to build up the string you want to display
Suppress the details section
Have another formula to return the string, and put this in the group footer
Have a formula in the group header to initialise the string

Your example currently shows each person record with some common details ('Mike Smith company name address') so I assume it doesn't matter which detail record you get this from. If so, your formula just needs to build up the 'administrative business ...' bit and you can display the common stuff directly in the footer from the last record in the person group.

Not knowing how many 'detail' sections you'll iterate through, you just need to ensure that your formula on the group footer is wide enough to display all the data, or set its 'can grow' property so that it will span multiple lines if necessary.

So, something like:

group header formula:

whileprintingrecords;
stringvar membershiplist := '';

details formula

whileprintingrecords;
stringvar membershiplist;
if membershiplist <> '' then
(
membershiplist := membershiplist + ' ';
)
membershiplist := membershiplist + <column>;

group footer formula:

whileprintingrecords;
stringvar membershiplist;
//return the variable
membershiplist

By the way, I've not put this code into Crystal, so you'll have to excuse me for any typos or missing semicolons, but it should get you on your way.