TSQL Stored Proceedure

[Paul31Hants]

Hello,

I am fairly new to this forum and to TSQL although have done alot of SQL scripting.

I wondered if someone could help me with a stored proceedure I am putting together.

I am trying to count the number of invoice number that are duplicated within the database across the company. However an Invoice number is generated using a statement from two different tables.

I have read up on TSQL and can run using a count from one table only.

Can anyone point me in the right direction? or direct me to some useful websites?

Thanks

SQL Script
-----------
SELECT IIf([dbo_oas_docline]![doccode]="Pmigrate",[dbo_oas_dochead]![descr],[ref1]) AS A, Count(IIf([dbo_oas_docline]![doccode]="Pmigrate",[dbo_oas_dochead]![descr],[ref1])) AS B
FROM dbo_oas_docline INNER JOIN dbo_oas_dochead ON (dbo_oas_docline.docnum = dbo_oas_dochead.docnum) AND (dbo_oas_docline.doccode = dbo_oas_dochead.doccode) AND (dbo_oas_docline.cmpcode = dbo_oas_dochead.cmpcode)
WHERE ((([A]=[B])>1))
GROUP BY IIf([dbo_oas_docline]![doccode]="Pmigrate",[dbo_oas_dochead]![descr],[ref1]);

[rmiao]

Do you use sql server?

[Paul31Hants]

Many thanks for your reply.

I do use SQL Server yes as a back end to MS Access.

[mikr0s]

SELECT t1.fld1+t2.fld2 AS invoice_no, COUNT(t1.fld1+t2.fld2) AS counter
FROM table1 t1, table2 t2
WHERE .....--my conditions--
GROUP BY t1.fld1+t2.fld2
--HAVING COUNT(t1.fld1+t2.fld2) > 1 --for displaying invoices that appear more than once

--HTH--

[rmiao]

And IIf is not valid t-sql statement.

[Paul31Hants]

many thanks for your reply

So instead of IIF I would set my conditions as:

Where tbl1.fld1 x=y or tbl2.fld2 a=b

for example?

Paul

[Paul31Hants]

Apologies i have 2 further questions:

1) Is + valid SQL
2) In my SQL script I am stating that:

If doccode is equal to PMIGRATE and use descr where complete or else use ref1.

IIf([dbo_oas_docline]![doccode]="Pmigrate",[dbo_oas_dochead]![descr],[ref1])

Where in the T-SQL would I place else ref1?

[rmiao]

You can try with case statement, there are sample code in sql books online. Like:

USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END
FROM titles