| Author |
Message
|
| Raj2000 |
 Posted: Wed Sep 16, 2009 9:13 am Post subject: esql statement issue |
 |
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
I have an issue regarding using esql statement to query the database.
issue:
Actually the database table has 3 coloumns they are:
C1 is c_ts which records current timestamp
C2 is source which records the source(CHARACTER TYPE)
C3 is status which records the status(CHARACTER)
I want to query the table such that it retrieves the row which contains source modified very recently and this should be done for different Source(C2)
the above is what i have to achieve
what i have tried:(SOURCESTATUS is the table)
SET Environment.X[]= SELECT R FROM Database.SOURCESTATUS AS R WHERE R.SOURCE= 'S_ID' AND R.C_TS=(SELECT MAX(R.CURRENT_TS)FROM Database.SOURCESTATUS AS R);
but it doen't work.
Do you think is there anything wrong with the above code???
even i can't use group function.So any suggestions??? 
note: the above task i think can be achieved in java using the group keyword. |
|
| Back to top |
|
 |
| Raj2000 |
| Posted: Wed Sep 16, 2009 9:46 pm Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
| Can anyone suggest me the workaround to come across this issue as we cannot use the group functionality in esql and without using the group is ther anyother way we can achieve the above task? |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Sep 16, 2009 9:57 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Raj2000,
First, your SQL statement.
1. Do you really need another select....wouldn't a direct MAX on the field work ? i.e MAX(R.C_TS)
2. Should it be SELECT R FROM or SELECT * FROM or SELECT R.* FROM...i could be wrong, been some time since i've coded 
Second, on options.
1. If nothing works, you could try using PASSTHRU.
2. If the SQL is complex, I always prefer using a stored procedure.
Regards. |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Thu Sep 17, 2009 9:15 am Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
Yes elvis, i have tried the following statement as you have said but it says syntax error
statement:
SET Environment.X[]=SELECT MAX(R.CURRENT_TS), R.SOURCE_ID, R.STATUS FROM Database.SOURCESTATUS AS R WHERE R.SOURCE_ID = 'S_ID';
and it point at ',' saying as syntax error.
elvis as i pretty new to this can you give me an example of usage of passthru in this context (i have researched on this statment but it helps to someextent from where i don't get any idea  ) sorryy |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Sep 17, 2009 9:35 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| but it doen't work. |
What doesn't work? Is there an error message? If so post it...
| Quote: |
| Do you think is there anything wrong with the above code??? |
Well both your SELECTS use the same name "R" which will confuse things... try renaming the nested SELECT to be "AS X" or some other name...
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Thu Sep 17, 2009 10:06 am Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
i don't get any error the onlything is environment variable is blank
hello MGK i have tried what you have said this time it executes the query but it doesn't yield any result as query searches for the whole max timestamp and for that row S_ID is not the sourceid so it gives no result.
But what is wrong with the following statement asit says syntax error:
SET Environment.X[]=SELECT MAX(R.CURRENT_TS), R.SOURCE_ID, R.STATUS FROM Database.SOURCESTATUS AS R WHERE R.SOURCE_ID = 'S_ID';
i am still not able to solve the issue that i have said initially
is ther any workaround so that i can solve the issue without using the group statement (as this is not supoorted in ESQL)??? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Sep 17, 2009 12:16 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
You really should review your standard SQL.
| Quote: |
SET Environment.X[]=SELECT MAX(R.CURRENT_TS), R.SOURCE_ID, R.STATUS FROM Database.SOURCESTATUS AS R WHERE R.SOURCE_ID = 'S_ID';
|
is missing the mandatory group by r.source_id, r.status for this type of select...
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Thu Sep 17, 2009 1:35 pm Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
| Quote: |
| the mandatory group by r.source_id, r.status for this type of select... |
Yes i do undersatnd that saper but Esql doesnot support "group" statement. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Sep 17, 2009 7:29 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| Raj2000 wrote: |
| Quote: |
| the mandatory group by r.source_id, r.status for this type of select... |
Yes i do undersatnd that saper but Esql doesnot support "group" statement. |
So you need a pass through. ESQL never pretended it could do all a SQL statement could... when talking about databases...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Fri Sep 18, 2009 9:41 am Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
| I am really sorry Saper i shoudn't have asked this but can you give me a example of pass thru which can be used in this context?? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Sep 18, 2009 9:58 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Try something like this
| Code: |
declare SQLCommand character;
set SQLCommand = 'SELECT MAX(R.CURRENT_TS), R.SOURCE_ID, R.STATUS FROM Database.SOURCESTATUS AS R WHERE R.SOURCE_ID = 'S_ID';
PASSTHRU (SQLCommand);
IF SQLERROR <> 0 Then
...
end if;
|
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Luke |
| Posted: Fri Sep 18, 2009 10:00 am Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
Hi Raj
Did you take a look at the example in the infocenter? ...
| Code: |
SET OutputRoot.XML.Data.SelectResult.Row[] =
PASSTHRU('SELECT R.* FROM Schema1.Table1 AS R WHERE R.Name = ? OR R.Name =
? ORDER BY Name'
TO Database.DSN1
VALUES ('Name1', 'Name4')); |
Shouldn't be too difficult to adapt this to your needs? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Sep 18, 2009 10:03 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Steven - that's ESQL SELECT syntax in an SQL statement.
Raj2000 - If you modify your original nested select to use different Database qualifiers "AS A" "AS B", you may have better luck.
I think you want something like
SELECT A.* Database.SOURCESTATUS AS A WHERE A.SOURCE= 'S_ID' AND A.C_TS=(SELECT MAX(B.CURRENT_TS) FROM Database.SOURCESTATUS AS B WHERE B.SOURCE='S_ID') ; |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Sep 18, 2009 12:14 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Thanks mqjeff for pointing out my elementary mistake.
Luckily, I'm off on holiday tomorrow. I guess I need it 
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Fri Sep 18, 2009 6:46 pm Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
| Quote: |
| you may have better luck. |
Jeff I had LUCK!!! you did it!! It works!!!!
Thank you much!!
Thanks alot guys you all made my day i had waited almost 2 days for this result and i had skipped this part of work and proceeded for the other work which is linked to this problem Hoping that someone would help me out here . this forum ROCKZZZ and you people too!!!
Saper,Luke,Davies thanks alot for your valuable input.. 
NOW i can have nice weekend!!! |
|
| Back to top |
|
|
|
|
