List:General Discussion« Previous MessageNext Message »
From:Steve Orton Date:June 28 2006 9:02pm
Subject:RE: Tough query to crack
View as plain text  
  What if @ContextID is an input variable to the procedure.  What can we do then?
I'm sorry if this seems very academic, but I'm rather new to the stored procedure thing
and only a little better at SQL statements as a whole:)


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:57 PM
To: Orton, Steve; mysql@stripped
Subject: Re: Tough query to crack
 
/>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
>   

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