Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    14

    Red face Unanswered: Can't create a view in Navicat for MySQL

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show the CREATE VIEW statement, and the exact error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    14
    Quote Originally Posted by r937 View Post
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, okay

    at this point i'd be inclined to blame navicat

    :wink:
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2012
    Posts
    14
    Quote Originally Posted by r937 View Post
    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;
    )

  6. #6
    Join Date
    Jan 2012
    Posts
    14
    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?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2012
    Posts
    14

    Thumbs up Thanks

    Once again, you've saved my life.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •