May 9, 2011 at 10:38 am
mikeincape (5/9/2011)
I just looked and I am in 90 compatibility mode.
Then with the comma added, what error are you getting (if any)?
May 9, 2011 at 10:39 am
I can run the query, and get results (which I am checking now for correctness) but I still get this message everytime I run it in the SQL management studio...
The OVER SQL construct or statement is not supported.
May 9, 2011 at 10:43 am
The dates being returned are not the correct dates. They are completely off... I have double checked using my old method, and looking directly in the database one record at a time.
You are definitely pointing me in the right direction... Just not quite pulling the right records on those flags tables
May 9, 2011 at 10:43 am
If the query runs then youre problem is elsewhere!!!
Just for the fun of it, run my script and see if that works. That'll confirm if the server's able to run that type of queries!
May 9, 2011 at 10:44 am
mikeincape (5/9/2011)
The dates being returned are not the correct dates. They are completely off... I have double checked using my old method, and looking directly in the database one record at a time.You are definitely pointing me in the right direction... Just not quite pulling the right records on those flags tables
IN THE order by use Date DESC. That'll put the latest on top and return it.
May 9, 2011 at 10:46 am
Your test query executed successfully.
May 9, 2011 at 10:47 am
mikeincape (5/9/2011)
Your test query executed successfully.
Ok then post the full code you are running, maybe we're missing something obvious... or you're running it on the wrong server :w00t:.
May 9, 2011 at 10:54 am
with the addition of the FlagDate DESC that Ninja noticed to the row number's Order by, does this get you closer to what you wanted?
SELECT
cl.RecordStatus,
cl.ActiveClient,
cl.SystemEffective,
cl.Firstname,
cl.Lastname,
cl.MiddleI,
cl.SSN,
cl.ClientID,
cl.City,
cl.State,
cl.Zip,
cl.HomePhone,
cl.Address1,
bg.FlagDate as EnteredDate,
fl.FlagDate as MoneyReceived
FROM Clients cl
LEFT OUTER JOIN
(SELECT
ClientID,
FlagDate
FROM Flags
WHERE (FlagCode=1001)
) bg
ON cl.ClientID = bg.ClientID
LEFT OUTER JOIN
(SELECT
ClientID,
FlagDate
FROM (SELECT
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY ClientID,FlagCode desc,FlagDate DESC) AS RW,
ClientID,
FlagDate
FROM Flags
WHERE (FlagCode=3001 OR FlagCode=3002)
)myAlias
WHERE RW = 1
) fl
ON cl.ClientID = fl.ClientID
WHERE cl.AgentID=20
ORDER BY
cl.Lastname,
cl.Firstname,
cl.MiddleI
Lowell
May 9, 2011 at 10:57 am
I posted Lowell's code straight to SQL Management Studio... Added the comma
SELECT
cl.RecordStatus,
cl.ActiveClient,
cl.SystemEffective,
cl.Firstname,
cl.Lastname,
cl.MiddleI,
cl.SSN,
cl.ClientID,
cl.City,
cl.State,
cl.Zip,
cl.HomePhone,
cl.Address1,
bg.FlagDate as EnteredDate,
fl.FlagDate as MoneyReceived
FROM Clients cl
LEFT OUTER JOIN
(SELECT
ClientID,
FlagDate
FROM Flags
WHERE (FlagCode=1001)
) bg
ON cl.ClientID = bg.ClientID
LEFT OUTER JOIN
(SELECT
ClientID,
FlagDate
FROM (SELECT
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY ClientID,FlagCode DESC) AS RW,
ClientID,
FlagDate
FROM Flags
WHERE (FlagCode=3001 OR FlagCode=3002)
)myAlias
WHERE RW = 1
) fl
ON cl.ClientID = fl.ClientID
WHERE cl.AgentID=20
ORDER BY
cl.Lastname,
cl.Firstname,
cl.MiddleI
SQL Management Studio then gives me the error:
The OVER SQL construct or statement is not supported.
The it pulls the results, and reformats the query to this:
SELECT cl.RecordStatus, cl.ActiveClient, cl.SystemEffective, cl.Firstname, cl.Lastname, cl.MiddleI, cl.SSN, cl.ClientID, cl.City, cl.State, cl.Zip, cl.HomePhone, cl.Address1,
bg.FlagDate AS EnteredDate, fl.FlagDate AS MoneyReceived
FROM Clients cl LEFT OUTER JOIN
(SELECT ClientID, FlagDate
FROM Flags
WHERE (FlagCode = 1001)) bg ON cl.ClientID = bg.ClientID LEFT OUTER JOIN
(SELECT ClientID, FlagDate
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ClientID
ORDER BY ClientID, FlagCode DESC) AS RW, ClientID, FlagDate
FROM Flags
WHERE (FlagCode = 3001 OR
FlagCode = 3002)) myAlias
WHERE RW = 1) fl ON cl.ClientID = fl.ClientID
WHERE cl.AgentID = 20
ORDER BY cl.Lastname, cl.Firstname, cl.MiddleI
I can execute again, and it still gives me the same SQL error, then generates the results...
The dates being returned are still incorrect. seems to not be the most recent... I tried to add an order by as suggested but am having trouble.
May 9, 2011 at 10:59 am
Yes... This gives me the dataset I need. Thanks!... Still getting that weird error though... Any Ideas?
May 9, 2011 at 11:01 am
here's a potential version that doesn't use row_number;
hopefully that will work as welll:
SELECT
cl.RecordStatus,
cl.ActiveClient,
cl.SystemEffective,
cl.Firstname,
cl.Lastname,
cl.MiddleI,
cl.SSN,
cl.ClientID,
cl.City,
cl.State,
cl.Zip,
cl.HomePhone,
cl.Address1,
bg.FlagDate as EnteredDate,
fl.FlagDate as MoneyReceived
FROM Clients cl
LEFT OUTER JOIN
(SELECT
ClientID,
FlagDate
FROM Flags
WHERE (FlagCode=1001)
) bg
ON cl.ClientID = bg.ClientID
LEFT OUTER JOIN
(SELECT
ClientID,
COALESCE(FlagDate3001,FlagDate3002) as FlagDate
FROM (SELECT
FlagCode,
ClientID,
CASE
WHEN FlagCode = 3001
THEN MAX(FlagDate)
ELSE NULL
END AS FlagDate3001,
CASE
WHEN FlagCode = 3002
THEN MAX(FlagDate)
ELSE NULL
END AS FlagDate3002
FROM Flags
WHERE (FlagCode=3001 OR FlagCode=3002)
GROUP BY
ClientID,
FlagCode
)myAlias
) fl
ON cl.ClientID = fl.ClientID
WHERE cl.AgentID=20
ORDER BY
cl.Lastname,
cl.Firstname,
cl.MiddleI
Lowell
May 9, 2011 at 11:03 am
Are you using any 3rd party application that refactors sql?
BTW, the 1st left join will return ALL ROWS instead of 1, I'm not sure this is what you want. I think you need to copy the other query with the ROW_NUMBER and just change the where / order by a little bit.
Also, we've done it by date but you're doing it by id in the original code. My gut tells me that the date is actually the correct sorting, then maybe flagid as tie breaker.
May 9, 2011 at 11:03 am
Why not just do a Union of the two queries
May 9, 2011 at 11:04 am
Invalid Column Name 'RW' on the most recent sql query from Lowell
May 9, 2011 at 11:04 am
Union <all> add rows, in this case he wants columns.
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply