Nest a Stored Procedure in a Where Clause

dlite922

Hi DB People,

I have a stored procedure that converts a specific id into another format (some conditions and bit-wise operations). Now I need to put this in a SELECT statement.

I'm not sure how to do it other than make an INOUT parameter and then use that variable in the SELECT.

The SP converts this: EP001234561234 to something like 1234567

I searched google and wasn't able to do this:

Expand|Select|Wrap|Line Numbers

  
SELECT field1, field2 

FROM myTable 

WHERE

field1 = CALL mySP("SP001234560000");

or any variation there of.

Right now my SP outputs that single integer ID.

Expand|Select|Wrap|Line Numbers

 
CALL mySP('EP003876510246');

+-----------+

| SomeID    |

+-----------+

| 134605379 |

+-----------+

1 row in set (0.00 sec)

Let me know what my options are! Thanks a whole bunch guys,

Dan

code green

I think a function in a WHERE clause is OK

Expand|Select|Wrap|Line Numbers

 SELECT field1, field2  

FROM myTable  

WHERE 

field1 = myFunction("SP001234560000");

Then write a function that CALLs the stored procedure and returns the converted value.

The only other thing I can think of is using variables

Expand|Select|Wrap|Line Numbers

 @myvariable = CALL mySP("SP001234560000");

SELECT field1, field2  

FROM myTable  

WHERE 

field1 = @myVariable;

Not sure if any of these will work but may help

dlite922

Thank you, the keyword I needed to put into Google was "MySQL stored procedure vs stored functions"

Duh! They're different things. Helpful Link:

The second method you mentioned wouldn't work for my setup because for each row in my table I'd have to assign that variable, then run a select for that row only. (something I already do with code).

Mucho Gracias!

Dan

dlite922

I have trouble getting the function to work as intended and observed some weird behavior.

I don't have that first initial number to insert into my query like my OP initially implied, this id is actually a field in another table.

So basically 2 tables, one has one format for the ID, the other has the integer format.

This works:

Expand|Select|Wrap|Line Numbers

  
SELECT 

  t1.initial_id,

  t2.integer_id

  t2.description

FROM 

  firstTable AS t1,

  secondTable AS t2

WHERE 

  t1.name = "Discovery Channel" AND

  myConvertFunction(t1.initial_id) = '12345' AND

  t2.integer_id = '12345';

The above works when I enter a static event ID. Let's say show ID number 12345 is Modern Marvel. This query will give me the show description.

I'd like to do this and get all the show's descriptions on Discovery channel for example.

Expand|Select|Wrap|Line Numbers

  
SELECT 

  t1.initial_id,

  t2.integer_id

  t2.description

FROM 

  firstTable AS t1,

  secondTable AS t2

WHERE 

  t1.name = "Discovery Channel" AND

  myConvertFunction(t1.initial_id) = t2.integer_id;

The convertFunction and t2.integer_id both produce the same result, but I can't JOIN the tables with them.

What gives? What's going on here?

Thanks,

Dan

dlite922

By the way, when i say doesn't work, It means I get an Empty set. there is no error.

The first example that works should explain that a show exists with ID 12345 for instance.

code green

if this is the problem line

Expand|Select|Wrap|Line Numbers

myConvertFunction(t1.initial_id) = t2.integer_id;

then it may be due to DATA type mismatch.
That is if the function 'does some maths' then it may return a FLOAT.
Or is one of the values a string or any non-integer. If so use CAST

dlite922

Thanks CG,

The field is integer and my function returns int. But I did try the casting anyway, both in the function (before return line) and in my query as:

Expand|Select|Wrap|Line Numbers

 
CAST(myConvertFunction(t1.initial_id) AS UNSIGNED) = CAST(t2.integer_id AS UNSIGNED);

I also tried casting to DECIMAL just to avoid the engine thinking it's already an int, and skip the cast. I don't know what else to do.

Dan

code green

I can't see a problem, but obviously there is.
Try a LEFT JOIN statement rather than the cartesian join (much better anyway)
It may help to see what is happening

Expand|Select|Wrap|Line Numbers

 FROM  

  firstTable AS t1

  LEFT JOIN 

  secondTable AS t2 USING('common_id_field`)

And try reversing the order (same as a RIGHT JOIN I suppose).
I don't think you will see any difference.
Put this into the SELECT fields to see what is actually there

Expand|Select|Wrap|Line Numbers

 SELECT ....... t1.name ,  

myConvertFunction(t1.initial_id), t2.integer_id

And play about with the WHERE clause to pull out obvious results.

dlite922

hmmm that sort of worked I think. I can't do USING because they don't have a shared column (hence I'm using the function).

Now because it's a left join I get records that are NULL in the second table. When I try to add a IS NOT NULL condition, I get the Empty Set again.

But it's weird when I say IS NULL, I do get all the NULL records.

lol

It's frustration but comical at the same time.

Dan

code green

Quote:

Now because it's a left join I get records that are NULL in the second

That is OK It proves the query is selecting from both tables. We just need to get the filter correct Did you place the additional fields in the SELECT line? Are the values as expected?

dlite922

Sorry, gone awhile, I'm back now and working on this again.

To recap. The LEFT JOIN worked but it displays records that don't exist in the second table. for those record it shows null.

What i'm selecting is:
1. The original ID that is not converted
2. The function that converts this original ID into the second format
3. The second ID that is in the other table.

I've confirmed that for those records that do match in the second table, Number 2 and 3 above are exact (that means my function is converting correctly)

Problem remaining is getting rid of records that (when converted to the second ID) don't have a match in the second table. I don't want to see these.

IS NULL gives me the records that are null, but IS NOT NULL gives me empty results.

I don't know how else to filter them out.

Dan

code green

I have lost the plot a little but you can JOIN on more than one condition.

Expand|Select|Wrap|Line Numbers

 LEFT JOIN table ON

 (fieldone = fieldtwo AND fieldtwo IS NOT NULL)

If you need to post back please post query latest version

dlite922

No worries, CG, At least I learned something, but alas in the end it didn't work out because of performance.

the inner / outter table query will work for now.

Dan