List:General Discussion« Previous MessageNext Message »
From:Steve Orton Date:June 28 2006 7:41pm
Subject:Tough query to crack
View as plain text  
  Hello fellow listers,

  I'm currently trying to reduce our TCO by incorporating this fine DBMS and replacing the
MS SQLServer we're using.  I'm trying to re-implement the stored procedures written for
SQLServer to MySQL and have one that's tough to figure out.

  This stored procedure basically finds ID values in one table that fit criteria based on
set flags in the table and puts them in another table along with a context value that is
assigned to the 'flag' settings.  This 'master_context_list' table holds all the IDs
assigned to each context value.  So when the flags are changed or another context is
created, the table is updated with new ID values that fit the new criteria.

  The stored procedures make extensive use of MS shortcuts to create tables, assign
values, etc.  This is the MS SQL statement:

  INSERT INTO master_context_list (Context_ID, Target_ID)
    SELECT Context_ID = @ContextID, targets_list.Target_ID FROM targets_list 
      WHERE Target_ID IN ( SELECT Target_ID FROM #APPLICABLE_TARGET_IDS )

   So this is taking all the Target IDs that match in both 'targets_list' and the
temporary table APPLICABLE_TARGET_IDS and inserting them into 'master_context_list' along
with the Context_ID with the value of @ContextID.  This sort of shorthand is nice for MS
users, bad for the rest of us.

 When I try to run this in a MySQL stored procedure, I get this error:
    "Unknown column 'db_context_ID' in field list"

  I know why it's failing but there's a failure in translation.  Can anybody figure out
how to translate this into SQL that will work in MySQL?

  Thanks in advance for any help....steve---

Steven J Orton
Software Engineer
Northrop Grumman Mission Systems
Middletown, RI 02842

Thread
Tough query to crackSteve Orton28 Jun
  • Re: Tough query to crackPeter Brawley28 Jun
  • RE: Tough query to crackSteve Orton28 Jun
    • Re: Tough query to crackPeter Brawley28 Jun
  • RE: Tough query to crackSteve Orton28 Jun
    • Re: Tough query to crackPeter Brawley28 Jun