Hello

If you're prepared to experiment a little, then a numbers or tally table can make this kind of operation really sing. Here's something which might start you off.

DECLARE @cSearchExpression nvarchar(4000),...

That's ok Grinja, my fault, not enough coffee. This should do the trick:

-- identifies which CoreItem has SubItems with categories which do not correspond to any of the CoreItem categories

SELECT...

Hi Adam

Here's a little extra material relating to your question. It pulls in Jack's nifty solution which would appear to be ideal in your case, and an alternative method or...

No problem Grinja.

This would appear to do the trick:

DROP TABLE #CoreItem

DROP TABLE #SubItem

CREATE TABLE #CoreItem (CoreItemID INT, Category CHAR(1))

INSERT INTO #CoreItem (CoreItemID, Category)

SELECT 1, 'A' UNION ALL ...

What do you get when you run this, Ray?

SELECT COUNT(*), CardNo,Base,TxType,RspCode,RspFault,TxDate,TxCurrency,TxAmount,SettlementCurrency,SettleAmount,

Acquirer,TerminalID,TraceNr,Merchant,Accountno,Rundate

FROM Test

WHERE ISNUMERIC(Accountno) <> 1

GROUP BY CardNo,Base,TxType,RspCode,RspFault,TxDate,TxCurrency,TxAmount,SettlementCurrency,SettleAmount,

Acquirer,TerminalID,TraceNr,Merchant,Accountno,Rundate

HAVING COUNT(*) > 1

Oops sorry, that's always the danger of not having test data to...well, test.

D1.X_AREA2 as c4,

should read

D1.X_AREA as c4,

Cheers

ChrisM

Hi Pete, I'm really sorry I can't help you any further with this, I don't have the client tools you are using. I'm sure someone will hop in shortly and...

I'm not entirely sure what you mean...but you would usually apply an alias to a CASE construct in a SELECT in one of two ways:

SELECT NewColumnName = CASE WHEN [Start...

Hi Grinja

I suspect there may be a flaw in the test data. Try running this and examine the results. I'm guessing that you want to eliminate category 'D' for coreitemid=2...

pete.trudell (7/29/2008)

---

I am a novice, and I need to dupicate a formula used in Access in a Stored Procedure of our Database. The Access Column reads as : ...

rbarryyoung (7/29/2008)

---

No, that is...

I wonder if it's the peculiar join between D1 and D2? Using a proper INNER JOIN might help...

select distinct D3.c4 as c1,

D3.c4 as c2,

...

You're welcome, Usul.

Shame that, like us, you're not using 2k5. The solution is more elegant and much faster...

http://www.sqlservercentral.com/Forums/Topic537762-338-3.aspx#bm538585

Cheers

ChrisM

Is this what you're expecting to see?

DROP TABLE #Test

CREATE TABLE #Test (Rowid INT, orderdt DATETIME, orderid INT, comment VARCHAR(60))

INSERT INTO #Test (Rowid, orderdt, orderid, comment)

SELECT 1, CAST('13/05/2008 07:54:13' AS DATETIME),...

tedo (7/29/2008)

---

sorry hehe it takes 1hr and 6min to London waterloo, that is still quicker, I just checked out nationalrail.co.uk I still knew it was quicker. cost is 12:80 for...