edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
 Posted: Wed Dec 23, 2009 5:13 am Post subject: Help needed to write Query |
 |
|
We have a table which has columns
1) Account_number
2) MQMessage_Number
3) Sequence_Number
4) MQ_message
and some other columns.
The purpose of this table is to backup the MQ message which the program processed for replay purposes.
The account number is the account for which this message was obtained.
The MQ message number is a unique number which is generated for each message.
The length of the MQ_message column is only 2000 bytes but we could get MQ messages which is 16000 bytes long. So a message
of more than 2000 bytes will be truncated. Suppose if a message is 9000 bytes long then it would be stored in this table as 5 rows which have the same account number and MQ message number and there would be Sequence_Number 1,2,3,4 and 5.
So at any point in order to replay a message which has 2 segments i use a query like this
| Code: | SELECT A.MQ_Message
CONCAT B.MQ_Message
FROM MQ_TABLE
(SELECT Account_Number, MQMessage_Number, MQ_Message
FROM MQ_TABLE
WHERE Account_Number = 1234 AND MQMessage_Number = 'XYZ'
AND Sequence_Number = 1) AS A
LEFT OUTER JOIN
(SELECT Account_Number, MQMessage_Number, MQ_Message
FROM MQ_TABLE
WHERE Account_Number = 1234 AND MQMessage_Number = 'XYZ'
AND Sequence_Number = 2) AS A
ON A.Account_Number = B.Account_Number and A.MQMessage_Number = B.MQMessage_Number |
Now the problem is that everyday i'll have to replay around 10 messages from production to test and it becomes a tedious process to first go and check the number of segments (Sequence Numbers) each message has and then write a query like the one above repeating the LEFT OUTER JOIN and the CONCAT clauses depending on the number of segments.
The MQMessage_Number is defined as SMALLINT and a bigger MQMessage_Number means the MQ message was the latest.
All i want to do is just to have a query where i could give this 10 MQMessage_Numbers (Something like in a IN Clause) and i would get the equivalent 10 complete messages.
Is it possible to write a query for this?
P.S. i am not able to write a program for this because i dont have access to bind to production tables.
Did i make my question clear?
Thanks in advance
_________________
Thanks |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
