> 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:)
Give the param a name which cannot conflict with an existing column
name, eg pcontextid, then write
INSERT INTO master_context_list (Context_id, Target_ID)
SELECT pcontextid, Target_ID FROM applicable_target_ids;
>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:)
Nothing academic about, and being a recovering M$SQL user ain't easy :-) .
PB
-----
Orton, Steve wrote:
> 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
>>
>>
>
>
> ------------------------------------------------------------------------
>
> 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
