Can't create a view in Navicat for MySQL

HunterDX77M · #1 (**permalink**) 02-09-12, 17:12

I am trying to create a view using Navicat for MySQL. My query is fine and works perfectly when I run it as simply a query. When I try to create a view out of this query, I get an error message telling me that I have a "select" statement in my "from" clause. This is true, but I get no errors when I run the query alone. The error message also neglects to tell me why it's so bad that I have a select statement in my from clause. Is anyone knowledgeable with this that can help me, please?

r937 · #2 (**permalink**) 02-09-12, 17:47

please show the CREATE VIEW statement, and the exact error message

HunterDX77M · #3 (**permalink**) 02-10-12, 16:13

Quote:

Originally Posted by r937

please show the CREATE VIEW statement, and the exact error message

The Error Code was exactly:1349 - View's SELECT contains a subquery in the FROM clause.

I didn't use a CREATE VIEW statement, but rather used the GUI.

r937 · #4 (**permalink**) 02-10-12, 16:17

oh, okay

at this point i'd be inclined to blame navicat

:wink:

HunterDX77M · #5 (**permalink**) 02-10-12, 16:29

Quote:

Originally Posted by r937

oh, okay

at this point i'd be inclined to blame navicat

:wink:

I just tried on the mysql console and received the same error message. Here is the code that was run:

Code:

CREATE VIEW Prod_Code_Status AS
(
select MDProdCode, ARName, RAAMSID, ARID, PropService, Location_ID, Address, City, State, ZIP, Service, Snapshot_Month, Team_Status
	, CASE WHEN Team_Status = 'down'
            THEN ( SELECT CAST(max(Going_Down) AS CHAR) 
                     FROM MarketDataInventoryReport
                    WHERE MDProdCode = this.MDProdCode  and Snapshot_Month = this.Snapshot_Month  and ARName = this.ARName and PropService = this.PropService and Location_ID = this.Location_ID and Service = this.Service limit 1)
            ELSE '' END AS The_Date
From (select MDProdCode, ARName,  RAAMSID, ARID, PropService, Location_ID,Address, City, State, ZIP,Service, Snapshot_Month
	, case when max(Current_Month) = 'up'
		THEN 'up'
                     ELSE 'down' END AS Team_Status
	
from MarketDataInventoryReport 
where MDProdCode<>'' and MDProdCode<>'N/A' and MDProdCode is not null 
group by MDProdCode, ARName, PropService, Location_ID, Snapshot_Month order by Snapshot_Month, MDProdCode, ARName, PropService, Location_ID)  as this;
)

HunterDX77M · #6 (**permalink**) 02-10-12, 16:36

I've heard a way around this is to create a view of the of the forbidden "FROM" clause and use that. Does that make any sense?

r937 · #7 (**permalink**) 02-10-12, 22:28

yep, makes sense

Code:

CREATE VIEW my_forbidden_FROM_clause AS
SELECT MDProdCode
     , ARName
     , RAAMSID
     , ARID
     , PropService
     , Location_ID
     , Address
     , City
     , State
     , ZIP
     , Service
     , Snapshot_Month
     , CASE WHEN MAX(Current_Month) = 'up'
          THEN 'up'
          ELSE 'down' END AS Team_Status
  FROM MarketDataInventoryReport 
 WHERE MDProdCode <> '' 
   AND MDProdCode <> 'N/A' 
   AND MDProdCode IS NOT NULL 
GROUP
    BY MDProdCode
     , ARName
     , PropService
     , Location_ID
     , Snapshot_Month

and then

Code:

CREATE VIEW Prod_Code_Status AS
SELECT MDProdCode
     , ARName
     , RAAMSID
     , ARID
     , PropService
     , Location_ID
     , Address
     , City
     , State
     , ZIP
     , Service
     , Snapshot_Month
     , Team_Status
     , CASE WHEN Team_Status = 'down'
            THEN ( SELECT CAST(MAX(Going_Down) AS CHAR) 
                     FROM MarketDataInventoryReport
                    WHERE MDProdCode     = this.MDProdCode  
                      and Snapshot_Month = this.Snapshot_Month  
                      and ARName         = this.ARName 
                      and PropService    = this.PropService 
                      and Location_ID    = this.Location_ID 
                      and Service        = this.Service )
            ELSE '' END AS The_Date
  FROM my_forbidden_FROM_clause AS this

HunterDX77M · #8 (**permalink**) 02-14-12, 14:00

Once again, you've saved my life.

Internet Services

Web Development

Digital Marketing

Consumer Tech