CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: Help convert this query from oracle to mysql

  1. #1
    Join Date
    Jan 2009
    Posts
    1,689

    Help convert this query from oracle to mysql

    Below is a valid oracle query, which is used to insert things into my database. I need to know the equivalent mysql query.

    Code:
    insert into SWAP_TABLE 
    	select '123' prefix, ptgid, stgid from 
    		(select GROUP_ID ptgid from ALL_GROUPS where label = 'foo'), 
    		(select GROUP_ID stgid from ALL_GROUPS where label = 'bar')
    
    	union all
    
    	select '456' prefix, ptgid, stgid from 
    		(select GROUP_ID ptgid from ALL_GROUPS where label = 'hello'), 
    		(select GROUP_ID stgid from ALL_GROUPS where label = 'world')
    I'm a software engineer so I don't really know much about DB queries. I had an oracle expert help me create this one, but he doesn't know mysql all that well.

    The query is actually generated by code, the labels and prefixs can be anything and there might theoretically be hundreds of thousands of union alls in there.

  2. #2
    Join Date
    Feb 2005
    Location
    Denmark
    Posts
    742

    Re: Help convert this query from oracle to mysql

    The query looks like simple generic SQL syntax, so have you tried if it works in MySQL? And if not, which error do you receive?

  3. #3
    Join Date
    Jan 2009
    Posts
    1,689

    Re: Help convert this query from oracle to mysql

    Of course I tried it :P

    Error #1248 - Every derived table must have it's own alias.

    I don't know what that means, each one does have it's own alias.

  4. #4
    Join Date
    Feb 2005
    Location
    Denmark
    Posts
    742

    Re: Help convert this query from oracle to mysql

    The reason I asked was because I wanted the error message

    Based on the error message I'd suggest that the (SELECT .... ) subqueries must have their own alias, something like

    Code:
    insert into SWAP_TABLE 
    	select '123' prefix, T1.ptgid, T2.stgid from 
    		(select GROUP_ID ptgid from ALL_GROUPS where label = 'foo') T1, 
    		(select GROUP_ID stgid from ALL_GROUPS where label = 'bar') T2
    
    	union all
    
    	select '456' prefix, T3.ptgid, T4.stgid from 
    		(select GROUP_ID ptgid from ALL_GROUPS where label = 'hello') T3, 
    		(select GROUP_ID stgid from ALL_GROUPS where label = 'world') T4
    Or something similar.
    I don't know where the "prefix" comes from, so I don't know if that needs to be qualified.
    Note this is just "guessing" right now, as I've not tested it in a MySQL but the error message indicates this.

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)