| Author |
Message
|
| tanishka |
 Posted: Wed Jan 19, 2011 5:46 am Post subject: Sql Select Query |
 |
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
Hi,
My requirement is there is multiple items in details of input message. in database table there is multiple items for previous version of same order number as well. my requirement is i need to compare both items and get item number which is there in database and there in input message. Please help me here? |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Jan 19, 2011 5:53 am Post subject: Re: Sql Select Query |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| tanishka wrote: |
| Please help me here? |
Use the items in the input message as keys to read the previous order from the database? In a Select statement as your reference in the title?
I don't understand your problem or your question.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| tanishka |
| Posted: Wed Jan 19, 2011 6:04 am Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
| I need to get item number from database table whereIF that item number not exist in input message. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jan 19, 2011 6:42 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| tanishka wrote: |
| I need to get item number from database table whereIF that item number not exist in input message. |
Then you need to key on some other part of the input message. You can't possibly select something from a database without knowing what you're looking for.
And this is so obvious I'm clearly misunderstanding your question still.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| optimist |
| Posted: Thu Jan 20, 2011 5:21 am Post subject: |
|
|
Apprentice
Joined: 18 Nov 2010
Posts: 33
|
I kind of understand that:
1. Your input has an order number with many items
2. The DB has the same order number with many items, some of which may not be in (1) above
and you need to retrieve the items from the DB that are not in the input, matching against the order number.
Use the SQL IN Clause and specify the items in the source:
http://www.w3schools.com/sql/sql_in.asp
more like NOT IN for your case. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Jan 20, 2011 5:44 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
It might be easier for you to use a 2 step.
Retrieve all entries in the DB that match the order
Delete from the DB Results the ones that match the in memory image.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| optimist |
| Posted: Thu Jan 20, 2011 7:39 am Post subject: |
|
|
Apprentice
Joined: 18 Nov 2010
Posts: 33
|
| fjb_saper wrote: |
It might be easier for you to use a 2 step.
Retrieve all entries in the DB that match the order
Delete from the DB Results the ones that match the in memory image.
Have fun |
Except that, if you are accessing your DB over a network, you will have additional network traffic to bring in all the details and then filter in WMB versus filtering at the DB.
This may not matter if you have few messages to process, but will do if you have a lot. |
|
| Back to top |
|
|
| tanishka |
| Posted: Thu Jan 20, 2011 11:27 am Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
Thanks for all replies.
below is the sql query which i need to write in esql.
SELECT T2.QTY, T2.SKU, T2.UNIT_COST FROM INTB_APP.PO_DSHBRD_PO_HISTORY T1, INTB_APP.PO_DSHBRD_PO_DETAILS_HISTORY T2
WHERE T2.PO_ID = T1.ID and T1.ORDER_NO = '436977' and T1.VERSION = 1 and T2.SKU NOT IN(08110018, 08110016, 08110010);
here ORDER_NO and VERSION and 08110018, 08110016, 08110010 numbers i need to take from input message.
i have passthru like this and not working
SET Environment.SKU[] = PASSTHRU('SELECT T2.QTY, T2.SKU, T2.UNIT_COST FROM INTB_APP.PO_DSHBRD_PO_HISTORY T1, INTB_APP.PO_DSHBRD_PO_DETAILS_HISTORY T2
WHERE T2.PO_ID = T1.ID and T1.ORDER_NO = ? and T1.VERSION = ? and T2.SKU NOT IN(?)'
VALUES (InmsgData.order_no, 1, ITMLIST));
itemlist is char data looks like '08110018, 08110016, 08110010' |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jan 20, 2011 11:32 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| tanishka wrote: |
| i have passthru like this and not working |
Be specific. Not working because the broker throws an error? If so, what error? Not working because you get an incorrect result? If so, what result do you get and how does it differ from the required one? Not working because every time you try it CPU reaches 100% and you need to reboot? Not working because broker crashes? The database crashes? Your car crashes? What?
Better information, better advice.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| tanishka |
| Posted: Thu Jan 20, 2011 11:48 am Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
| I meant is sql working fine returing all the items . NOT IN clause not working. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jan 20, 2011 12:13 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Instead of passing in the ITMLIST as a parameter, you might need to concatenate it to the passthru statement..
something like
| Code: |
SET Environment.SKU[] = PASSTHRU('SELECT T2.QTY, T2.SKU, T2.UNIT_COST FROM INTB_APP.PO_DSHBRD_PO_HISTORY T1, INTB_APP.PO_DSHBRD_PO_DETAILS_HISTORY T2
WHERE T2.PO_ID = T1.ID and T1.ORDER_NO = ? and T1.VERSION = ? and T2.SKU NOT IN('||ITMLIST||')'
VALUES (InmsgData.order_no, 1)); |
I suspect that the database is not interpreting your string as a list, but as a single value and trying to match against it that way.
Edit: this will remove some of the performance optimization from being able to cache the query, because the query string will be different each time the ITMLIST is different. However, unless you can do something to quote the commas in your list such that the DB sees them as list separators or otherwise indicate to the DB that the value is a list, you may have to live with this. |
|
| Back to top |
|
|
| tanishka |
| Posted: Thu Jan 20, 2011 12:30 pm Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
This query returning error
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2011-01-20 15:26:37.375167 3872 DatabaseException BIP2322E: Database error: SQL State ''HY000''; Native Error Code '936'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-00936: missing expression''.
The error has the following diagnostic information: SQL State ''HY000'' SQL Native Error Code '936' SQL Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-00936: missing expression''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jan 20, 2011 12:35 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Well, I didn't say it *would* work. I said it *might* work. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jan 20, 2011 12:38 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
And the SELECT works if executed against the database outside of WMB?
I'm just checking.
That 936 error (as I'm sure you know) is the generic Oracle badly formed select error so it's presumably still getting confused about that list.
Some experimentation may be needed to find a syntax both WMB & Oracle will accept.
Or as my most worthy associate has suggested, it might be quicker just to return everything from the database and then eliminate anything with those SKU codes using ESQL.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| tanishka |
| Posted: Thu Jan 20, 2011 12:44 pm Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
SELECT works fine if executed against the database outside of WMB..
SELECT T2.QTY, T2.SKU, T2.UNIT_COST FROM INTB_APP.PO_DSHBRD_PO_HISTORY T1, INTB_APP.PO_DSHBRD_PO_DETAILS_HISTORY T2
WHERE T2.PO_ID = T1.ID and T1.ORDER_NO = '436977' and T1.VERSION = 1 and T2.SKU NOT IN(08110018, 08110016, 08110010); |
|
| Back to top |
|
|
|
|
