Select in Access to Oracle

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
Nadia Teles
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Mon, 19 Jun 2006 11:57 am
Location: São Caetano do Sul

Good morning guys,

Someone could help me with this SELECT. I have a select that works perfectly for Access. How to convert to Oracle?

See below:

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 and type INT field (I store the date as yyyymmdd)
] The Time and Type Type field (I store the time as HHMMSS)
The Minute and type-type field

Transform the bank and time of the bank for DD / mm / YYYY HHMMSS and subtract with the current date / time. This difference should be larger than the field in minutes.

Thanks

Nadia
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

The problem is that you are using specific access functions.
Example:
Mid, DateDiff, Format, [Fields] with [] between them ... This is not compatible with Oracle. I believe that Mid is equivalent to the Oracle substr. To concatenar is || And no +. And so on.

Try the following:

Select all

Select * 
from cadastro  
where 
  SYSDATE -  to_date(data||hora, 'yyyymmddhhmiss') > minuto * 60 
order by data, hora 
Nadia Teles
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Mon, 19 Jun 2006 11:57 am
Location: São Caetano do Sul

When I run the SELECT below, it is not concatenating the two fields (date and time) and nor formatting for yyyymmddhhmiss. It shows only the date:

Select all

Select to_date(Data || Hora, 'yyyymmddhhmiss')  From Cadastro
Return:

19/06/06

99] What can be wrong?

Thanks

Nádia
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Are you using SQL * plus to test?
If yes, remember that SQL * Plus does not show the dates in the format of HH: MM as you want.
See the example below:

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 specified the format I wanted.
Nadia Teles
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Mon, 19 Jun 2006 11:57 am
Location: São Caetano do Sul

Good morning,

I still can not solve this select.

Using the date formatting for DD / mm / rrrr HH24: MI: SS, it generates an error when the date is less than 12:00. He asks to enter an integral value from 1 to 23.

Thanks

Nadia
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Send us a sample of the data you have.
Example:
makes a select time, minute from your_tabela ... and wide the result so we can see how your information is.
Nadia Teles
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Mon, 19 Jun 2006 11:57 am
Location: São Caetano do Sul

Guys, thank you for the help. Anyway I managed to do the Select.

Nadia
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 16 guests