question

ShreyasKale-8444 avatar image
0 Votes"
ShreyasKale-8444 asked ErlandSommarskog answered

Query executed successfully but 0 rows

Whenever I am executing SQL query it says "Query executed successfully" but rows count is "0"
I have checked in Query options and "SET PARSEONLY" is not selected.

If I right click on same table and click on "Select Top 1000 rows" then I am able to see Top 1000 rows results but only this query is not returning any rows or results.

Can you please suggest solution on this issue.228793-sql-query.png


sql-server-generalsql-server-transact-sql
sql-query.png (194.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Why do you convert DATETIME into string for the compare?!?

By doing so you (assuming you have index) you probably lose the option to use index seek and you reduce the performance of the query. If this column is datetime or any type of date/time then you probably better compare it directly

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ShreyasKale-8444 commented

What happens if you comment out that WHERE condition? I don't know anything about your data or tables, but the most likely explanation to me is that there are no rows that fulfil the condition in the WHERE clause.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog Thanks for the hint.
I am using WHERE clause to get unique records for current date, after removing WHERE clause I am getting output result from query but its giving multiple records for each UserID. I want to get only unique record for each user for current date.

Not sure what stopped my WHERE clause to work because 1 week back it was working fine.

WHERE CONVERT(VARCHAR(10), [Eventdate], 102) = CONVERT(VARCHAR(10), GETDATE(), 102)

0 Votes 0 ·
llaxmikant avatar image
0 Votes"
llaxmikant answered ErlandSommarskog converted comment to answer

remove the where clause from Source Table CTE and try.

or

can you query select top 100 EDDateTime, * from PowerBI_Data and paste screenshot of result here.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@llaxmikant I am using WHERE clause to get unique records for current date, after removing WHERE clause I am getting output result from query but its giving multiple records for each UserID. I want to get only unique record for each user for current date.

Also I tried select top 100 EDateTime, * from PowerBI_Data and its giving below output result as expected.

Not sure what is wrong with my WHERE clause because 1 week back it was working fine.

228776-screenshot-2022-08-07-183131.png





0 Votes 0 ·
ErlandSommarskog avatar image
2 Votes"
ErlandSommarskog answered

Not sure what is wrong with my WHERE clause because 1 week back it was working fine.

Presumably, one week ago you had data for the date that was the current date then. But if the data set has not been refreshed since then, you don't have any data for today.

Also, that filtering only filters out old rows, but you can still get multiple rows for the same user. Maybe you are looking for the most recent row for each user, no matter the date?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog Thanks Erland...!!! yes, your right...!!! Dataset is not refreshed and hence there are no records in dataset for current date hence query is not returning result.
I will check on dataset refresh issue and revert back on this topic.

0 Votes 0 ·