March 12, 2018 at 7:05 am
Hi,
I have been working for quite sometimes on a report but I could not filter the data as I wanted.
CREATE TABLE mytable(
12032018 DATE NOT NULL PRIMARY KEY
,25 NUMERIC(4,1) NOT NULL
,Carriage VARCHAR(8) NOT NULL
);
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',25,'Carriage');
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',48.8,'Tool1');
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',3,'Tool1');
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',3,'Plier');
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',3,'Plier');
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',5,'Cement');
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',5,'Cement');
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',8,'Flooring');
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',8,'Flooring');
INSERT INTO mytable(12032018,25,Carriage) VALUES ('12/03/2018',8,'Flooring');
My Query is Here:
SELECT TransactionDate
FROM view_SalesStatistics
WHERE EXISTS
GROUP BY
ORDER BY
March 12, 2018 at 7:33 am
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
March 12, 2018 at 10:04 am
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
March 13, 2018 at 12:11 pm
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.
----------------------------------------------------
March 13, 2018 at 12:17 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply