4D - SQL sysdate?

Joshua Fletcher (5/29/14 12:27AM)
Tim Fitzgerald (5/29/14 11:06AM)
aldupon at gmail.com (5/29/14 2:14PM)
Tim Fitzgerald (5/29/14 4:25PM)

Joshua Fletcher (5/29/14 12:27 AM)

Are you trying to execute this code against a 4D database, or are you
making an external connection to Oracle? ?SYSDATE isn't generic SQL,
it's Oracle (it's vendor-specific) so it's not surprising the syntax
doesn't work against 4D (or anywhere else besides an Oracle database).
So I think you're probably doing the former.

What specifically do you mean by "doesnít work"?

Kind regards,

Josh

--
Josh Fletcher
Technical Account Manager
4D, Inc

color><param>00000,0000,DDEE/param>
/color>

-----Original Message-----
color><param>00000,0000,DDEE/param>FFrom: 4d_tech-bounces@...
[mailto:4d_tech-bounces@...
On Behalf Of Tim Fitzgerald
Sent: Wednesday, May 28, 2014 2:42 PM

Hi -

I am trying to write SQL code into 4D and I am not having some luck.
Here
is what I am trying to do:

On my Oracle SQL Developer, I am able to retrieve data (STRM) from this
script:

SELECT DISTINCT
?=A0STRM
FROM ps_term_tbl
WHERE holiday_schedule='UMTC'
AND TERM_BEGIN_DT<<=sysdate
AND TERM_END_DT>=sysdate
ORDER BY STRM

Now if I use this script into 4D:

ARRAY TEXT(atCurrentSemester;0)

Begin SQL
?SELECT DISTINCT
?=A0?=A0STRM
?FROM ps_term_tbl
?WHERE holiday_schedule='UMTC'
?AND TERM_BEGIN_DT<<=sysdate
?AND TERM_END_DT>=sysdate
?ORDER BY STRM
?INTO :atCurrentSemester
End SQL

It doesn&iacute;t work with sysdate but if you replace 2 lines with this:

?=A0?=A0?AND TERM_BEGIN_DT<<='16-May-14'
?=A0?=A0?AND TERM_END_DT>='16-May-14&iacute;

Those will work fine. Can you tell me how can I replace sysdate to
&igrave;current date&icirc; into SQL? I have tried several ways but no luck.

I even tried this but no luck either:

C_TEXT($Date)
$Date:="16-May-14"

?=A0?=A0?AND TERM_BEGIN_DT<<=:$Date
?=A0?=A0?AND TERM_END_DT>=:$Date

Hope you can give me some clues. Thanks.
TIM

--
Timothy S. Fitzgerald
Disability Resource Center, Office of Equity and Diversity
University of Minnesota
952-388-2172
timfitz@...
/color>
Tim Fitzgerald (5/29/14 11:06 AM)

Josh -

I am using an external connection to Oracle database (PeopleSoft) here
at University from 4D. If I use "AND TERM_BEGIN_DT<<=sysdate&icirc;, it
looks like sysdate value is blank because I got all 570 records. If I
use ?"AND TERM_BEGIN_DT<<='16-May-14&iacute; &igrave; then I get 1 or 2
records. The
problem with ?'16-May-14&iacute;, I will have to manual enter the date
every
time I want to execute this script&Ouml;I am looking for a way to accept a
value as current date.

I guess the worse case, I could dump all of 570 records and then use
4D to sort it out for me.

Hope this makes sense.
Thanks.
TIM

--

Date: Thu, 29 May 2014 00:27:02 +0200
From: Joshua Fletcher <JFletcher@...

Are you trying to execute this code against a 4D database, or are you
making an external connection to Oracle? ?SYSDATE isn't generic SQL,
it's Oracle (it's vendor-specific) so it's not surprising the syntax
doesn't work against 4D (or anywhere else besides an Oracle database).
So I think you're probably doing the former.

What specifically do you mean by "doesn&iacute;t work"?

Kind regards,

Josh

--
Josh Fletcher
Technical Account Manager
4D, Inc

color><param>00000,0000,DDEE/param>
/color>

-----Original Message-----
color><param>00000,0000,DDEE/param>FFrom: 4d_tech-bounces@...
[mailto:4d_tech-bounces@...
On Behalf Of Tim Fitzgerald
Sent: Wednesday, May 28, 2014 2:42 PM

Hi -

I am trying to write SQL code into 4D and I am not having some luck.
Here
is what I am trying to do:

On my Oracle SQL Developer, I am able to retrieve data (STRM) from this
script:

SELECT DISTINCT
?STRM
FROM ps_term_tbl
WHERE holiday_schedule='UMTC'
AND TERM_BEGIN_DT<<=sysdate
AND TERM_END_DT>=sysdate
ORDER BY STRM

Now if I use this script into 4D:

ARRAY TEXT(atCurrentSemester;0)

Begin SQL
SELECT DISTINCT
?=A0?STRM
FROM ps_term_tbl
WHERE holiday_schedule='UMTC'
AND TERM_BEGIN_DT<<=sysdate
AND TERM_END_DT>=sysdate
ORDER BY STRM
INTO :atCurrentSemester
End SQL

It doesn&iacute;t work with sysdate but if you replace 2 lines with this:

?=A0?=A0AND TERM_BEGIN_DT<<='16-May-14'
?=A0?=A0AND TERM_END_DT>='16-May-14&iacute;

Those will work fine. Can you tell me how can I replace sysdate to
&igrave;current date&icirc; into SQL? I have tried several ways but no luck.

I even tried this but no luck either:

C_TEXT($Date)
$Date:="16-May-14"

?=A0?=A0AND TERM_BEGIN_DT<<=:$Date
?=A0?=A0AND TERM_END_DT>=:$Date

Hope you can give me some clues. Thanks.
TIM

--
Timothy S. Fitzgerald
Disability Resource Center, Office of Equity and Diversity
University of Minnesota
952-388-2172
timfitz@...
/color>---
Timothy S. Fitzgerald
Disability Resource Center, Office of Equity and Diversity
University of Minnesota
952-388-2172
timfitz@...
aldupon at gmail.com (5/29/14 2:14 PM)

Hi Tim,

To have something working,

could you try this:

// ---------------------------------------------
C_TEXT($T_Sysdate)
$T_Sysdate:=String(current date; ISO Date GMT)

SELECT DISTINCT
?STRM
FROM ps_term_tbl
WHERE holiday_schedule='UMTC'
AND TERM_BEGIN_DT<<= : $T_Sysdate
AND TERM_END_DT>= : $T_Sysdate
ORDER BY STRM

// ---------------------------------------------

Regards

Alain.

Le 28 mai 2014 &yacute; 23:41, Tim Fitzgerald <timfitz@... a =E9crit :

color><param>00000,0000,DDEE/param>HHi -

I am trying to write SQL code into 4D and I am not having some luck.
Here is what I am trying to do:

On my Oracle SQL Developer, I am able to retrieve data (STRM) from
this script:

SELECT DISTINCT
?STRM
FROM ps_term_tbl
WHERE holiday_schedule='UMTC'
AND TERM_BEGIN_DT<<=sysdate
AND TERM_END_DT>=sysdate
ORDER BY STRM
/color>
Tim Fitzgerald (5/29/14 4:25 PM)

Ah ha!! ?I found the problem. You CANNOT use &igrave;comment out&icirc; lines
in
SQL string in 4D as you could do in SQL Developer. If there is no
comment out, then the SQL works fine. (sighs) Thanks for your help tho.

TIM

--
Timothy S. Fitzgerald
Disability Resource Center, Office of Equity and Diversity
University of Minnesota
952-388-2172
timfitz@...

On May 29, 2014, at 3:20 PM, 4d_tech-request@... wrote:

color><param>00000,0000,DDEE/param>MMessage: 1

Date: Thu, 29 May 2014 19:08:48 +0200
From: Joshua Fletcher <JFletcher@...

Maybe it's important to explain something: 4D doesn't interpret
"sysdate". 4D sends the query you have provided to the ODBC driver
manager, which hands it off to the Oracle server via your ODBC driver,
and the Oracle server decides what to do with it. ?I know you said the
query works from the Oracle tools but it's unlikely those tools are
using ODBC, I would expect them to use the wire protocol. ?Can you get
this query to work from any other tool that's using ODBC, e.g. Excel?

If you're sure that the Oracle tools are using the ODBC connection, or
you can get it working from some other tool using ODBC, then the next
step I'd do is turn on the ODBC trace log and compare the 4D version
to the other version. But this presumes you've got some experience
with ODBC programming and/or interpreting the logs of course. It's
worth learning this if you haven't, it's pretty much required in order
to troubleshoot external connections.

The other thing I would suggest is consider creating a View for this
in the Oracle DB since you're not really parameterizing this query
(all the parameters are constants).

Kind regards,

Josh

--
Josh Fletcher
Technical Account Manager
4D, Inc
/color>
Reply to this message

Summary created 5/29/14 at 7:36PM by Intellex Corporation

Comments welcome at: feedback@intellexcorp.com