Unable to filter Data

Question

Unable to filter Data

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The first problem is that your table mytable doesn't appear in the query. The second is that your subquery isn't correlated to the outer query. Therefore, all rows in the outer query will be returned if and only if the subquery returns any rows. Please post some DDL and sample data for AnalysisCodeAttachment, AnalysisCode and Product.

John

gazy007 Default port Points: 1491 More actions · Answer 2

I hope it helps.
+---------------------------------------+
| Results |
+---------------------------------------+
| 12/03/2018 | 25 | Carriage | | | |
+------------+------+----------+--+--+--+
| 12/03/2018 | 25 | Carriage | | | |
+------------+------+----------+--+--+--+
| 12/03/2018 | 48.8 | Tool1 | | | |
+------------+------+----------+--+--+--+
| 12/03/2018 | 8 | Flooring | | | |
+------------+------+----------+--+--+--+

CREATE TABLE yourtable(
ProductName VARCHAR(10)
,TransactionDate DATE NOT NULL
,Value INTEGER NOT NULL
);
INSERT INTO yourtable(ProductName,TransactionDate,Value) VALUES ('Carriage','12/03/2018',25);
INSERT INTO yourtable(ProductName,TransactionDate,Value) VALUES ('Carriage','12/03/2018',25);
INSERT INTO yourtable(ProductName,TransactionDate,Value) VALUES ('Wipes','12/03/2018',8);
INSERT INTO yourtable(ProductName,TransactionDate,Value) VALUES ('Flooring','12/03/2018',10);
INSERT INTO yourtable(ProductName,TransactionDate,Value) VALUES ('Blade','12/03/2018',1);
INSERT INTO yourtable(ProductName,TransactionDate,Value) VALUES ('Flooring','12/03/2018',10);
INSERT INTO yourtable(ProductName,TransactionDate,Value) VALUES ('Wall Paper','12/03/2018',12);
My Query is Here:

SELECT TransactionDate
,SUM(FCPrice * Qty / ExchangeRate) - SUM(FCPrice * LineDiscount / 100 * Qty) AS Value
,view_SalesStatistics.ProductName
FROM view_SalesStatistics
WHERE EXISTS (
SELECT *
FROM AnalysisCodeAttachment
INNER JOIN AnalysisCode
ON AnalysisCode.AnalysisCodeID = AnalysisCodeAttachment.AnalysisCodeID
INNER JOIN Product
ON AnalysisCodeAttachment.UniqueID = Product.ProductID
WHERE (AnalysisCodeAttachment.Value != 'Flooring')
)
AND (TransactionDate > '2017-12-31')
GROUP BY TransactionDate
,view_SalesStatistics.ProductName
ORDER BY TransactionDate DESC

MMartin1 One Orange Chip Points: 27862 More actions · Answer 3

You are referencing columns in your query that are not in your initial table definition. I dont know even what your business requirement is. Have a look at the link in my signature below on posting questions on to the forum, so that we may offer better assistance.

----------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

MyTable, YourTable, still don't see the table in either query. So what is the problem?

And posting in a bigger font doesn't help.