Puzzling SQL
Aug 20, 2007
quote:SELECT @SQL = 'SELECT EpisodeID, MAX(Convert(Datetime, EventDate,103)) MaxDate, ID, EventCareModel INTO #TmpCareModel
FROM ObAnteEvent WHERE EventType = ''CareModel'' AND EventCareModel = EventCareModel AND EventDate <> '''' GROUP BY EpisodeID, ID, EventCareModel '
EXEC (@SQL)
SELECT * FROM #TmpCareModel
When I wrote this in a store procedure and EXEC the sp in QM, I get no results (although it shd return a record) but when I ran the SQL in Query Manager (SQL 2000) there were records returned.
I then changed #TmpCareModel to TmpCareModel and it worked. Am I not allowed to use this statement for temporary tables? It's a bit puzzling.
View 2 Replies
Jul 20, 2005
Does anyone have possible explanations for the following results?select count(*) from tablename[color=blue][color=green][color=darkred]>>> 2,500,000[/color][/color][/color]select count(*) from tablename where fieldname is null[color=blue][color=green][color=darkred]>>> 2,400,000[/color][/color][/color]select count(*) from tablename where fieldname is not null[color=blue][color=green][color=darkred]>>> 900,000[/color][/color][/color]Just my luck, I was showing something to the CFO when I got theseresults...I'd appreciate any hints. Thanks.fn
View 5 Replies
View Related
Aug 10, 2005
CASE CLOSED: removing the FROM clause noted below handles the problem ....I've used "NOT EXISTS" in inserts before and thought I understood how they work, but I'm puzzled why I get three rows from the following sub-query, which I want to use to prevent errors from duplicate keys. Since it gives me three rows, it will actually try to insert three duplicate records and cause a primary-key fault, which is a twin to the very thing I'm trying to avoid. (of course, there are three records already in the table, none having the key of 20050810)The sub-query is acting like it's using "WHERE DemoDate <> 20050810" instead of a "NOT EXISTS".Attempts to use this statement causes a termination with no records inserted.DemoDate is the primary key and an INT field in SQL Server. Four other int columns for this table have default values.<code>INSERT INTO DemoStats (DemoDate)SELECT 20050810 AS Expr1FROM DemoStats <-- remove this line so the effective table has only one row, when the NOT EXISTS is TRUEWHERE (NOT EXISTS (SELECT * FROM DemoStats WHERE DemoDate = 20050810))</code>
View 5 Replies
View Related
Mar 22, 2004
Hi,
I wonder if anybody encountered before with tables which has records with some of the primary keys as null values?
Currently I've found a number of records which isn't suppose to be inserted in the first place from a table.
My table size is over a few millions and currently using SQL Server Standard version
Seems like the primary key got corrupted or something...
Anybody encountered this before??
-deb-
View 6 Replies
View Related
