Thanks for posting this! :)

[b]DISCLAIMER:
As Will correctly states below: Executing SQL Statements directly to the database will nullify any support or maintenance contract. Please contact your HCC Partner! [/b]

The problem above affects Hotcakes Category Viewer when using DrillDown-View.
It will stop working with a nasty error message

If you run into this problem you can fix it using this the statement below:

1) [b]Create a DB Backup[/b]
2) Make sure DB Backup is correctly created
3) Make sure you followed step 1) and 2)
4) This is tested with HCC 3.1.0 on DNN 8.0.4

[quote]
SELECT ppv.id
FROM hcc_ProductPropertyValue ppv
LEFT JOIN hcc_ProductPropertyChoice ppc
ON ppv.PropertyValue = ppc.Id
WHERE ppv.PropertyId IN
(
SELECT ID
FROM hcc_ProductProperty
WHERE TypeCode = 2
)
AND ppc.Id IS NULL
[/quote]

if this returns one or more rows you can execute the following statement to delete the orphan references:

[quote]
DELETE FROM
hcc_ProductPropertyValue
WHERE id IN
(
SELECT ppv.id
FROM hcc_ProductPropertyValue ppv
LEFT JOIN hcc_ProductPropertyChoice ppc
ON ppv.PropertyValue = ppc.Id
WHERE ppv.PropertyId IN
(
SELECT ID
FROM hcc_ProductProperty
WHERE TypeCode = 2
)
AND ppc.Id IS NULL
)
[/quote]

Now DrillDown View should work again.
Hope that helps.
Reto

So... Should we trust your SQL snippet, or not? :D

For all: Just so you know, I realize that everyone tends to run SQL against various solutions out there. It's always worth noting, though, that it's rare that any solution supports any kind of direct SQL access and in most cases running SQL is nullify any support or maintenance contract you might have. :)

Hi Will

You are correct, I updated my post above to make aware of the problem with executing SQL statements against the db but left the statements in there.
If you think they should be removed (not sure about that) please PM