| Author |
Message
|
| kumar.shanj |
 Posted: Wed Jan 05, 2011 2:46 am Post subject: How to compare two dates in ESQL codei n Message Broker v6.0 |
 |
|
Apprentice
Joined: 18 Nov 2010
Posts: 49
|
I am working on Message broker v6.0
SET OutputRoot.XML.Data.ORBIT.ORBITTOASM[] =
(
SELECT
A.TERMINATION_DT AS TERM_DT,
CASE
WHEN A.TERMINATION_DT < to_date(SYSDATE + 1, 'dd-mon-yy') THEN
A.TERMINATION_DT
ELSE NULL
END AS EndDate
FROM
Database.PS_PWCUK_EMP_C_VW AS A );
the above mentioned statement is working fine in SQL command console,but when use the same command in ESQL code in Compute node,its throwing error while deployment saying to_date() is not supported.
Any other eay of comparing dates in ESQL code |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Jan 05, 2011 3:06 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
ESQL Select is not the same as SQL Select.
You shouldn't be using Broker 6.0. It's going out of service in April. Tell your bosses that they need to send you to training and upgrade to 7.0 - or at least 6.1. |
|
| Back to top |
|
|
| kumar.shanj |
| Posted: Wed Jan 05, 2011 3:12 am Post subject: |
|
|
Apprentice
Joined: 18 Nov 2010
Posts: 49
|
Thanks for reply.
Its working now...
CASE
WHEN
cast(A.TERMINATION_DT as date FORMAT 'dd-mon-yy')
<= cast(CURRENT_DATE as date FORMAT 'dd-mon-yy')
THEN
A.TERMINATION_DT
ELSE
NULL
END
This solved the problem. |
|
| Back to top |
|
|
| Braindrain |
| Posted: Thu Apr 21, 2011 11:51 am Post subject: Date Issue |
|
|
Newbie
Joined: 14 May 2006
Posts: 6
Location: NC
|
HI
I m Also trying to execute the below query can you please help.It is erroring out.
SET OutputRoot.XMLNSC.Data.TableData.ManagerList.JobInfo[]= SELECT M.pdu_id, M.last_nme ||' '||
M.first_nme AS full_nme FROM Database.ENINTDEV.PSO_USER.PTU_PSO_TERMNTD_USER_HIST AS M WHERE
M.dlr_cde = '422A22' AND M.brand_cde = 'AU'AND M.job_cde
IN ('INET-SL-MG', 'SL-MGR', 'A-PO-MGR', 'DLR-PRGM', 'GEN-MGR', 'SL-USD-MGR')AND M.ude_id
IS NOT NULL AND (Sys_date<5) < M.term_dte
How to format it.[/b] |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Apr 21, 2011 11:56 am Post subject: Re: Date Issue |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Braindrain wrote: |
| It is erroring out. |
Perhaps if you gave us a clue as to the error rather than making us guess?
| Braindrain wrote: |
SET OutputRoot.XMLNSC.Data.TableData.ManagerList.JobInfo[]= SELECT M.pdu_id, M.last_nme ||' '||
M.first_nme AS full_nme FROM Database.ENINTDEV.PSO_USER.PTU_PSO_TERMNTD_USER_HIST AS M WHERE
M.dlr_cde = '422A22' AND M.brand_cde = 'AU'AND M.job_cde
IN ('INET-SL-MG', 'SL-MGR', 'A-PO-MGR', 'DLR-PRGM', 'GEN-MGR', 'SL-USD-MGR')AND M.ude_id
IS NOT NULL AND (Sys_date<5) < M.term_dte |
Though if I had to guess, it doesn't like the term "Sys_date".
Where did you see that term in the Broker documentation?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Apr 21, 2011 11:57 am Post subject: Re: Date Issue |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
| Braindrain wrote: |
| It is erroring out. |
Perhaps if you gave us a clue as to the error rather than making us guess? |
The error is clear.
Braindrain doesn't know ESQL. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Apr 21, 2011 11:58 am Post subject: Re: Date Issue |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| Vitor wrote: |
| Braindrain wrote: |
| It is erroring out. |
Perhaps if you gave us a clue as to the error rather than making us guess? |
The error is clear.
Braindrain doesn't know ESQL. |
And didn't read this thread all too carefully before posting to it....
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Braindrain |
| Posted: Thu Apr 21, 2011 12:08 pm Post subject: |
|
|
Newbie
Joined: 14 May 2006
Posts: 6
Location: NC
|
This is coming when we I m deploying onto broker.
Begin running task [Deploying [EMPID.msgflow.generated.bar] to execution group [QA]]
BIP2087E: Broker EAIBRK7 was unable to process the internal configuration message.
The entire internal configuration message failed to be processed successfully.
Use the messages following this message to determine the reasons for the failure. If the problem cannot be resolved after reviewing these messages, contact your IBM Support center. Enabling service trace may help determine the cause of the failure.
BIP4041E: Execution group 'QA' received an invalid configuration message. See the following messages for details of the error.
The broker was asked to deploy a message flow which contained properties that were not recognized by the broker. This typically results from a message flow requiring a version or type of node that is not supported by the broker installation.
Check that the message flow is only using properties or nodes that are supported on the broker. Check that all necessary user-defined extensions are installed and that they are of a version that is compatible with the message flow.
BIP4001E: Syntax error in SQL statements in node 'EMPID.Retrieve Data From Database'.
The configuration failed due to errors in the SQL statement text. See the following messages for details of the error
The following error messages will give specific details of the location and nature of the error. Check and correct the syntax of the SQL statements and redeploy.
BIP2420E: (.EMPID_Compute.Main, 16.65) : Invalid or incompatible data types for '' operator.
Either the data types of the operands of the operator were not valid for the operator, or the datatypes were incompatible.
Correct the syntax of your ESQL expression in node '.EMPID_Compute.Main', around line and column '16.65', then redeploy the message flow: Ensure that the data types of the operands are valid and compatible with each other.
The task was unsuccessful: The deployment was unsuccessful. Check error messages above for explanation. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Apr 21, 2011 12:15 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Braindrain wrote: |
This is coming when we I m deploying onto broker.
|
I really can't say I'm surprised. I'm more surprised that the Toolkit didn't give you a yellow triangle.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Braindrain |
| Posted: Fri Apr 22, 2011 5:01 am Post subject: |
|
|
Newbie
Joined: 14 May 2006
Posts: 6
Location: NC
|
Vitor,MQJeff,
R you guyz only for talking nonsense rather helping I don't see anything useful from you. javascript:emoticon(' ') |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Apr 22, 2011 5:10 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Braindrain wrote: |
| R you guyz only for talking nonsense rather helping I don't see anything useful from you. |
That's because the complete solution to your problem is already in this thread. There's nothing else useful to add.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Braindrain |
| Posted: Fri Apr 22, 2011 6:09 am Post subject: |
|
|
Newbie
Joined: 14 May 2006
Posts: 6
Location: NC
|
I know there is an error You don't have to say that ,If you know how to do the process of an SQL statement into ESQL,YOu can tell If not please don't post.All I m asking and trying is how to fit in ESQL the above query I m almost through but stuck in the date process ,
My situation is I have current _date I have to minus 5 days and compare it with the database value of Term_dte.
If you have answer post me or Don't waste time of others.javascript:emoticon(' ') |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Apr 22, 2011 6:24 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Braindrain wrote: |
| My situation is I have current _date I have to minus 5 days and compare it with the database value of Term_dte. |
So you've spotted that Sys_date isn't a valid ESQL construct but CURRENT_DATE is? Well done. When you look up CURRENT_DATE in the InfoCenter, it tells you that it returns a value of type DATE, not INTEGER. That's incompatible with the '<' operator when the other operand is an INTEGER.
| Braindrain wrote: |
| If you have answer post me or Don't waste time of others.javascript:emoticon('') |
If you weren't wasting my time throwing your rattle out of the parm I'd be more inclined to help. As it is, I'll pose this question and leave you to it:
How would you express 5 days in date form?
Answers in an InfoCenter near you. Enjoy. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Braindrain |
| Posted: Fri Apr 22, 2011 6:39 am Post subject: |
|
|
Newbie
Joined: 14 May 2006
Posts: 6
Location: NC
|
Vitor,
So how will you compare?using extract function,But how will you minus 5 days of the current date?
I think this was started by you not me talking as if you know everything and putting the idiotic quotes below.
Instead of writing post like this write something which makes sense if you know how to do that in ESQL do it and keep up your respect.
Thanks In advance. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Apr 22, 2011 6:43 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
What does the documentation on ESQL say?
Your original statement mentioned does not strongly resemble an ESQL statement, but merely an SQL statement.
And the earlier issue started by someone else in this thread had the same issue...
I think you will find that there is no obligation on anyone here to actually be helpful or to otherwise respond at all to anything. The only reason anyone posts anything is because they choose to.
There is a specific thread on "how to ask questions the smart way". If you had read it, you would see that most of your comments in this thread are completely counter to that.
You need to rewrite your SELECT statement according to the rules of ESQL as documented.
If you don't know what those rules are, it is required of YOU that YOU learn them. It is not required of anyone here that we TEACH you. |
|
| Back to top |
|
|
|
|
