Posted by Wes Groleau on 02/03/07 20:08

Erland Sommarskog wrote:
> But you could try:
>
> UPDATE Output
> SET xyz = R.maxseg
> FROM Output o
> JOIN (SELECT Raw_segs, maxseg = Max(Seg_Nbr)
> FROM Raw_Segs
> WHERE Seg_Nbr BETWEEN abc AND pqr) R
> ON Output.FID = R.Raw_Segs
>
> While this syntax is proprietary and not portable, it often yields better
> results than a correlated subquery.

Or maybe

UPDATE Output
SET xyz = R.maxseg
FROM Output o
JOIN (SELECT FID, maxseg = Max(Seg_Nbr)
FROM Raw_Segs
WHERE Seg_Nbr BETWEEN abc AND pqr) R
ON Output.FID = R.FID

But unfortunately, Ent. Mgr/SQL Svr 2000 rejected this, saying that the
optional FROM syntax is not supported. By some experimentation, I got
THAT message to go away (even though both FROMs were still there).

But none of the ten variations I tried were accepted.
(By the way, page 72 and following of SQL Cookbook offers both this
approach and the one that my first approach was based on. But the
syntax it says will work for the first approach was also rejected
by my system--though I managed to alter it enough to work in the one case.

Apparently, the "optimizer" is not very smart. I eventually got the job
done as follows:

Load Raw_Segments

Create and insert Raw_BPR, Raw_CLP, Raw_SVC, Raw_CAS, Patient_Names, etc.

Index the above

Create Inv_Data with indexes

Crate view or table Selected_Adjustments from Raw_CAS

INSERT INTO Inv_Data
(FID, CAS_Seg, Adj_Group, Adj_Reason, Adj_Amount)
SELECT FID, Seg_Nbr, Type, Code, CAST(Amount AS money)
AS Expr1
FROM Selected_Adjustments

UPDATE Inv_Data
SET SVC_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_SVC AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg > Seg_Nbr)

UPDATE Inv_Data
SET Service = (SELECT Elem_01
FROM Raw_SVC AS Raw
WHERE Inv_Data.FID = Raw.FID
AND SVC_Seg > Seg_Nbr)

UPDATE Inv_Data
SET CLP_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_CLP AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg > Seg_Nbr)

UPDATE Inv_Data
SET BPR_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_BPR AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg > Seg_Nbr)

Each update takes about ten seconds this way.

No doubt there's a simpler way, but I'm new at this.

--
Wes Groleau

He that complies against his will is of the same opinion still.
-- Samuel Butler, 1612-1680

[Back to original message]