0

I'm trying to create a view of a table that I made of subqueries. I have no problems creating views with other tables but it looks like when I try the CREATE VIEW command below, I get an error. I think its something to do MySQL's capabilities creating views that have nested subqueries in them? Does anyone know how to resolve this issue? Or perhaps optimize my method so I don't have to write subqueries?

I know it is not a privilege/permission issue since I have granted all access to my user and I can create other tables and views just fine.


SELECT YEAR(order_date) AS 'year', MONTH(order_date) AS 'month', SUM(ctc_reported) AS 'ctc_reported', SUM(ctc_detected) AS 'ctc_detected',
    SUM(ctc_detected)/SUM(ctc_reported) AS 'detection_percent',
    SUM(ck_pos_detected) AS 'ck_pos_detected',
    SUM(ck_pos_detected)/SUM(ctc_detected) AS 'ck_pos_percent',
    SUM(ck_neg_detected) AS 'ck_neg_detected',
    SUM(ck_neg_detected)/SUM(ctc_detected) AS 'ck_neg_percent',
    SUM(ck_both_detected) AS 'ck_both_detected',
    SUM(ck_both_detected)/SUM(ctc_detected) AS 'ck_both_percent'

FROM
(SELECT result_management_report_id,
    accession_id,
    order_date,
    IF(ctc_enumeration_result IN ('DETECTED','NOT DETECTED'), 1, 0) AS 'ctc_reported',
    IF(ctc_enumeration_result = 'DETECTED', 1, 0) AS 'ctc_detected',
    IF(ck_pos > 0 AND ck_neg IS NULL, 1, 0) AS 'ck_pos_detected',
    IF(ck_neg > 0 AND ck_pos IS NULL, 1, 0) AS 'ck_neg_detected',
    IF(ck_pos > 0 AND ck_neg > 0, 1, 0) AS 'ck_both_detected'


FROM Biocept_DB.result_management_report
WHERE final_reported_date IS NOT NULL
) AS subquery
GROUP BY YEAR(order_date), MONTH(order_date)
);

Error Code: 1142. ANY command denied to user 'BioceptAnalyst'@'%' for table ''

This is what the table looks like: enter image description here

Heeje Cho
  • 81
  • 2
  • 8
  • Why do you want a VIEW anyway?? – Strawberry Feb 07 '20 at 21:06
  • I'd like to create this view so I can view all this information with a click of a button, isn't that what VIEWS are for? And also because I'd like to connect Power BI to this view so I can graph this information. – Heeje Cho Feb 07 '20 at 21:36
  • I would argue that that's what queries are for. I suppose I've never really seen any point in Views – Strawberry Feb 07 '20 at 22:38
  • I'd always thought views as a "pre-saved" easy access to queries. Nevertheless, I've been actually been able to get around this issue by making a view on top of a view. – Heeje Cho Feb 10 '20 at 19:04

0 Answers0