Invalid column name 'Expr1'. 2

[Keyth]

Can anybody please tell me why I get the error Invalid column name 'Expr1' in the query text below?

SELECT     DeliveryNoteID, DelDate,
                          (SELECT     COUNT(AutoID) AS Expr1
                            FROM          Transport.dbo.DESPATCH_ITEM AS DI
                            WHERE      (NOTE_NUMBER = DeliveryNotes.DeliveryNoteID) AND (Expr1 = 0)) AS cpc
FROM         DeliveryNotes
WHERE     (DelDate BETWEEN '20070101' AND '20080101')
ORDER BY DeliveryNoteID

Thanks very much,
Keyth

 

[ca8msm]

Because Expr1 isn't a valid column name. You would need to use:

SELECT     DeliveryNoteID, DelDate,
                          (SELECT     COUNT(AutoID) AS Expr1
                            FROM          Transport.dbo.DESPATCH_ITEM AS DI
                            WHERE      (NOTE_NUMBER = DeliveryNotes.DeliveryNoteID) AND ([!]COUNT(AutoID)[/!] = 0)) AS cpc
FROM         DeliveryNotes
WHERE     (DelDate BETWEEN '20070101' AND '20080101')
ORDER BY DeliveryNoteID

Although I fail to see why you would want to find all records which have a count of zero! Did you actually mean to type a different field in that clause?



-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[TysonLPrice]

I'm not an expert by anymeans but since no one has replied yet I suggest trying

DeliveryNotes.DeliveryNoteID) AND (COUNT(AutoID) = 0)) AS cpc

 

[Keyth]

I want to find DeliveryNotes that have been created but not despatched/logged in Despatch_Item.

I tried your query but got this error:
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

 

[hmckillop]

Alternatively I think what you are asking for is how many deliverynotes are there with no dispatches, so alternatively try

SELECT     DeliveryNoteID, DelDate, 0 AS cpc --(is always going to be 0 as you have Expr1=0)
FROM         DeliveryNotes DN
LEFT JOIN Transport.dbo.DESPATCH_ITEM DI on DN.DeliveryNoteID = DI.NOTE_NUMBER
WHERE     (DelDate BETWEEN '20070101' AND '20080101')
AND Di.AutoID is NULL
ORDER BY DeliveryNoteID

"I'm living so far beyond my income that we may almost be said to be living apart

 

[ca8msm]

Just left join to the Despatch_Item table and put a clause in to say where the id is null e.g.

SELECT     DeliveryNoteID, DelDate
FROM         DeliveryNotes
LEFT JOIN Transport.dbo.DESPATCH_ITEM ON DeliveryNotes.DeliveryNoteID = NOTE_NUMBER
WHERE     (DelDate BETWEEN '20070101' AND '20080101')
AND NOTE_NUMBER IS NULL
ORDER BY DeliveryNoteID

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Keyth]

These queries do run but do not return the data I need.

Document/Record Process:
1. A delivery note is raised in the DELIVERYNOTES Table.
2. At a later date, a record should be created in the DESPATCH_ITEM table and the DeliveryNotes.DeliveryNoteID is logged in the DESPATCH_ITEM.NOTE_NUMBER column. If the delivery note is not used it will not get logged in the DESPATCH_ITEM table.

Currently there are no DESPATCH_ITEM records where 'NOTE_NUMBER IS NULL'

I want to find all DeliveryNote numbers that cannot be found in DESPATCH_ITEM.NOTE_NUMBER

Examples of DESPATCH_ITEM.NOTE_NUMBER;
57254
57254/57255
57254/57255/57259/57282

The queries above return records where the Delivery Notes have been entered in DESPATCH_ITEM.

 

[ca8msm]

Examples of DESPATCH_ITEM.NOTE_NUMBER;
57254
57254/57255
57254/57255/57259/57282

What do you mean? Are you storing "/" separated values of id's in one field in the item table?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Keyth]

Yes. There are not a lot of "/" separated value records stored so I only need to concentrate on the single value records.

 

[ca8msm]

That's a very bad method of storing data as it makes it almost impossible to join to the table. I'd suggest you look at storing a record for each id instead before attempting to retrieve any records.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Keyth]

Each delivery note is stored individually with its own ID in the DeliveryNotes table.
When despatching goods, multiple packages/despatches are grouped onto one despatch/pallet which is why they are stored in the Despatch_Item table under one record. I can perform this check by including a function in a program but was wondering if the operation can be performed in an sql query. I know it would lengthen the execution time by converting the DeliveryNotes.DeliveryNoteID (int) to a string before matching it to the Despatch_Item.NOTE_NUMBER (string) field but this query used to work and I cannot fathom out why it has stopped.

If I can take * from the delivery note table and for each of these records, see if there is an exact match in the despatch_item table then I have won half the battle.

It may be that I need to refer back to my program instead of trying to tackle this in SQL.

 

[ca8msm]

When despatching goods, multiple packages/despatches are grouped onto one despatch/pallet which is why they are stored in the Despatch_Item table under one record.

That's fine but what you should do is create another column for the despatch/pallet id, and then insert a record for each item e.g
[tt]
NOTE_NUMBER PALLET_NUMBER
57254 1
57255 1
[/tt]
When you do this, it will mean that the queries above (which are the simplest and recommended way to get records from one table that do not exist in another) will return the correct results.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[chrissie1]

If you follow Mark's advice it will scale much better too.

Christiaan Baes
Belgium

My Blog

 

[Keyth]

I am working with two existing systems which were designed at different times, years ago! I know what your saying, that would be the better way of approaching it but the systems are already in place.

Thanks all for your help, its back to the drawing board with this one!

 

[hmckillop]

I understand the limitations of existing systems and original designs. You have to balance up the amount of effort changing GUIs other code etc and underlying data structure for effectively no business benefit and alot of cost against ongoing development effort taking longer with poor design. So with that in mind here is an alternative

You could do what you want to do by writing an inline function which will split the values in the NOTE_NUMBER. I am just writing a version now to see if I can help.
I have worked with the fields shown above so excuse the incorrectness of the tables, fieldtypes/sizes etc.

create table hmk_DeliveryNotes
(
DeliveryNoteID varchar(10),
DelDate datetime,
OtherFields varchar(10)
)

Create table hmk_despatch_Item
(
AutoID int identity(1,1) ,
NOTE_NUMBER varchar(255)
)

insert into hmk_DeliveryNotes values('57111', '20070112', '20080101')
insert into hmk_DeliveryNotes values('57254', '20070112', '')
insert into hmk_DeliveryNotes values('57259', '20070112', '')

insert into hmk_despatch_item(NOTE_NUMBER) values('57254')
insert into hmk_despatch_item(NOTE_NUMBER) values('57254/57255')
insert into hmk_despatch_item(NOTE_NUMBER) values('57254/57255/57259/57282')


CREATE function hmk_SplitNOTE_NUMBER_And_Check_AgainstSingleValue(@notenum varchar(255), @ComparisonField varchar(10) )
--returns @tempTable table (notenumbers varchar(255))
returns int  -- if item found return 1 else 0
BEGIN
	--variable i is for current and j for previous locations
	DECLARE @myword varchar(255), @iReturnValue int
	SELECT @iReturnValue = 0
	DECLARE @tempTable TABLE (NoteNumbers varchar(10))
		
	IF CHARINDEX('/', @notenum )>0  --more than one value
	BEGIN
		while CHARINDEX('/', @notenum)>0 
		begin
			select @myword = LEFT(@notenum, CHARINDEX('/', @notenum)-1)
			select @notenum = rtrim(substring(@notenum, CHARINDEX('/', @notenum)+1,len(@notenum)-(CHARINDEX('/', @notenum))))
			INSERT INTO @tempTable values(@myWord)
		end
	END
	INSERT INTO @tempTable values(@notenum)
	IF EXISTS(Select 'X' FROM @tempTable WHERE @ComparisonField = NoteNumbers)
		SELECT @iReturnValue = 1
	ELSE
		SELECT @iReturnValue = 0

	RETURN @iReturnValue
END

e.g. example of it working
select dbo.hmk_SplitNOTE_NUMBER_And_Check_AgainstSingleValue('57254/57255/57259/57282', '57255')
select dbo.hmk_SplitNOTE_NUMBER_And_Check_AgainstSingleValue('57254/57255/57259', '57255')
select dbo.hmk_SplitNOTE_NUMBER_And_Check_AgainstSingleValue('57254/', '57255')

I want to find all DeliveryNote numbers that cannot be found in DESPATCH_ITEM.NOTE_NUMBER

Examples of DESPATCH_ITEM.NOTE_NUMBER;
57254
57254/57255
57254/57255/57259/57282

Now the problem is you could try and use this function to join but you would end up with some funny results because its effectively a cross product on the two fields and thus wouldnt return the correct results.

In order to alleviate that you need to go through each field one by one and count the amount of times it is referenced, and if greater than 1 it has a link
e.g.

--declare a table to hold all items in delivery note as we need to step through one by one
declare @v_tempTable table (deliverynoteid varchar(10), totalrowsfoundin int)
DECLARE  @DelNoteID varchar(10), @iCounter int

-- Iknow i should do this without cursors, but i just dont have that much time
declare Cur_delNotes cursor FOR
SELECT DeliveryNoteID FROM hmk_DeliveryNotes
OPEN cur_delNOtes
FETCH NEXT FROM cur_delNOtes
INTO @DelNoteID
WHILE @@FETCH_STATUS = 0
BEGIN
	select @iCounter = 0 --reset value

	
	SELECT @iCounter = SUM(dbo.hmk_SplitNOTE_NUMBER_And_Check_AgainstSingleValue( NOTE_NUMBER,@DelNoteID))
		FROM hmk_despatch_Item

	INSERT into @v_tempTable values(@DelNoteID, @iCounter)
	FETCH NEXT FROM cur_delNOtes
	INTO @DelNoteID
END
select * from @v_tempTable
CLOSE Cur_delNotes
DEALLOCATE Cur_delNotes

This returns a table containing all your delivery notes and how many despatch_items they are contained within
Now I know there must be many better ways of doing this (asides from changing the structure of your database), but its the one I have come to in the quickest time available.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[Keyth]

Thanks very much hmckillop!!!! It looks like you have put a lot of effort into finding a solution on this. If I could give you a bucket full of stars for this I would!

I am going to give it a try - Thanks again, Keyth

 

[hmckillop]

No worries, thats what these forums are all about, only hope it works.
Now i am waiting for someone like gmmastros or SQLSister to tell me the proper way of doing it (asides the recommendations of changing data design)


"I'm living so far beyond my income that we may almost be said to be living apart