Access Select p/Oracle

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 19 Jun 2006 12:00 pm

Good morning guys, could someone help me with this select. I have a select that works perfectly for access. How to convert to oracle?

See below:
Code: Select all
Select * From Cadastro WHERE dateDiff('s',
(Mid(Format([Data],'00000000'),7,2)+'/'+Mid(Format([Data],'00000000'),5,2)+'/'+Mid(Format([Data],'00000000'),1,4)+'
'+Mid( Format([Hora],'000000'),1,2)+':'+Mid(
Format([Hora],'000000'),3,2)+':'+Mid( Format([Hora],'000000'),5,2)),now()) > (Minuto * 60) ORDER BY Data, Hora
the date field and type int (I store the date like yyyymmdd) the hour and field of type int (store time as hhmmss) the minute field of type int Turn the date and time of the Bank to dd/mm/yyyy hhmmss and subtract with the current date/time. This difference must be greater than the field in minutes.

Thanks Nadia
Nadia Teles
Location: São Caetano do Sul

Poston Mon, 19 Jun 2006 12:26 pm

The problem is that you're using Access-specific functions.
Example: MID, DateDiff, Format, [fields] with [] among them. It is not compatible with Oracle. I think MID is equivalent to Oracle SUBSTR. To concatenate is || and no +. And so on and so forth.

Try this:
Code: Select all
Select *
from cadastro
where
  SYSDATE -  to_date(data||hora, 'yyyymmddhhmiss') > minuto * 60
order by data, hora
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Mon, 19 Jun 2006 1:19 pm

When I run the select below, he's not concatenating two fields (date and time) and not formatting to yyyymmddhhmiss. It presents only the date: Select to_date (date || Time, ' yyyymmddhhmiss ') From a record return: 6/19/06 what can be wrong?

Thanks Nadia
Nadia Teles
Location: São Caetano do Sul

Poston Mon, 19 Jun 2006 1:45 pm

Are you using SQL * Plus to test?
If so, remember that SQL * Plus does not show the dates in the format of hh: mm as you want.
See example below:
Code: Select all
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
19-JUN-06

SQL> SELECT TO_CHAR(SYSDATE, 'DD/MM/RRRR-HH24:MI:SS') A FROM DUAL;

A
-------------------
19/06/2006-13:40:54

SQL>
to see, I put a TO_CHAR and specify the format I wanted.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 20 Jun 2006 9:32 am

Good morning everyone, I haven't been able to solve this select.

Using the date format to DD/MM/RRRR HH24: MI: SS, it generates an error when the date is less than 12:00. He asks to enter with an integer value from 1 to 23.

Thanks Nadia
Nadia Teles
Location: São Caetano do Sul

Poston Tue, 20 Jun 2006 9:57 am

Send us a sample of the data that you have.
Example: Does a select hour, minute from sua_tabela ... And put the result for us to see how it's your information.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 21 Jun 2006 2:07 pm

Guys, thanks for your help. Anyway I managed to make the select.

Nadia
Nadia Teles
Location: São Caetano do Sul



Return to SQL

Who is online

Users browsing this forum: No registered users and 6 guests