List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:June 28 2006 8:57pm
Subject:Re: Tough query to crack
View as plain text  
/>I don't think that will work because Context_ID is not found in 
'targets_list' hence the error.
 >That's why I thought the Context_ID field was being assigned with the 
value of @ContextID.
 >That's the only way we can get this value.

/Perhaps not, but no matter./
/
/>What I would like is Context_ID to be filled with the user var. 
@Context_ID and the Target_IDs
 >found in both the tables.  So if the input is 3, then each entry 
should be 3 and any target ID
 >found with the flags associated with context 3 applied.  These flags 
are processed earlier in
 >this same procedure and the IDs are put into the APPLICABLE_TARGET_IDS 
temporary table.
/
1. To reference a user variable @contextID (NOT a declared sproc 
variable) you need only:
INSERT ...
SELECT @contextID, ...

2. If all required flagged targetIDs are in applicable_target_ids, you 
do not need the join either:
INSERT INTO master_context_list (Context_ID, Target_ID)
SELECT @contextID, Target_ID FROM applicable_target_ids;

PB

-----

Orton, Steve wrote:
>   I don't think that will work because Context_ID is not found in 'targets_list'
> hence the error.  That's why I thought the Context_ID field was being assigned with the
> value of @ContextID.  That's the only way we can get this value.
>
>   What I would like is Context_ID to be filled with the user var. @Context_ID and the
> Target_IDs found in both the tables.  So if the input is 3, then each entry should be 3
> and any target ID found with the flags associated with context 3 applied.  These flags are
> processed earlier in this same procedure and the IDs are put into the
> APPLICABLE_TARGET_IDS temporary table.
>
>  steve---
>
>
>
> Steven J Orton
> Software Engineer
> Northrop Grumman Mission Systems
> Middletown, RI 02842
>
>
>
> -----Original Message-----
> From: Peter Brawley [mailto:peter.brawley@stripped]
> Sent: Wed 6/28/2006 4:01 PM
> To: Orton, Steve
> Cc: mysql@stripped
> Subject: Re: Tough query to crack
>  
> Steve,
>
>  >  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 )
>
> context_id=@contextID tells the server to return 1 when the column value 
> of context_id is equal to the value of the user variable @contextID, and 
> otherwise zero. Not likely what you intend.
>
> Apart from that, does the following, in standard SQL, represent what you 
> are trying to accomplish?
>
> INSERT INTO master_context_list (Context_ID, Target_ID)
> SELECT l.Context_ID, l.Target_ID
> FROM targets_list AS l
> INNER JOIN applicable_target_ids AS a USING (target_id);
>
> PB
>
> -----
>
> Orton, Steve wrote:
>   
>>   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
>>
>>
>>   
>>     
>
>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
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