Oracle Query Question - Urgent Please Help!

Unanswered Question
May 3rd, 2007

Hello All,

IPCC Express 4.0.4 HA

We have some situation going here when we fire query with Oracle.

I will explain in Details.

Customer has three types of Employees

1. Contractors - starting with x followed by six numberic digits e.g x0124567

2. Suppliers - Starting with s than followed by six digits e.g s0124567

3. Regular Employees- just six digit numeric number. e.g 123456

Now while doing the testing part we created Test Database in SQL and created a table with employee details.

And in script we are asking emoployee press 1 if you are regular employee, press 2 if you are contractor, press 3 if you are supplier.

If someone press 2

And in DB read node we have defined this query.

Select Employee_ID from ABC where Employee_ID like 'x%' + '%' + $Emp_Num(Emp_Num is local variable in which we are storing employee input)

This all works well and fine with SQL and we dont have any issues with supplier or Regular employee as well.

But now we are trying to integrate our IPCC with Oracle database(Actual Database).

We are running this Query.

Select Employee_ID from ABC where Employee_ID like 'x%' and Employee_ID like '%0124567'

This query runs fine. But the moment we put Emp_Num variable instead of static value we see in debug that while reading database call is going to No Data node of DB read instead of Success.

We are really not sure whats happening we have tried lots of permutations and combinations with the query but the query seems to be not working for us at all.

Guys we need this really badly. If someone can help in this we will really appreciate.

Thanks,

Daljeet

I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
daljeetsingh1_2 Mon, 11/17/2008 - 02:33

Yes, we got this working, and its working fine for more than a yr. Did you get the solution? or u want me to help you out?

Explain me in details what is you are looking for.

shewettcobb Fri, 01/16/2009 - 04:33

I am having a similar issue. Can you tell me how you corrected this problem? Any advise would be appreciated.

Clifford McGlamry Wed, 11/05/2008 - 17:21

I recently struggled with a similar situation, but I was connecting to MS SQL Server. My method of resolution may work for you.

1. Create a stored procedure in Oracle that you can call with the variable as input.

2. Create a dummy table that has a single field in it called Employee_ID. This field should be of the same data type and length as the Employee_ID field in the table ABC mentioned in your original query. You're not going to put data here, but you do need it for the schema.

3. Execute the stored procedure with a DB Read.

4. Then execute the DB Get step. Point it at the dummy table that you created. You have to do this because IPCC can't handle cursors from stored procedures. What we are doing is giving it the table to use as a schema for the results.

This same thing happened to me a week ago, and this is how I fixed it.

Actions

This Discussion