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:

SELECT field1, field2 
FROM myTable 
WHERE
field1 = CALL mySP("SP001234560000");

or any variation there of.

Right now my SP outputs that single integer ID.

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

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

@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:

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.

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

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:

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

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

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]

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.

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