Get Information from a Table For the Current Day Only

In the snippet below the Extract function does not work but I think it should.  Is there a better way to to this.  Also, I cannot get the Column headings to print. The tabledate has the format: 08/01/2013 14:21.
 

Thanks

TextFilePath="Schedules.txt"
sqlplus user/PW@Server << % > $TextFilePath
set feedback off
set verify off
set echo off
set pagesize 10
set linesize 100
set newpage none
set feed off
set head off
set tab off
set trimout on
alter session set nls_date_format='MM/DD/YYYY HH24:MI';
COLUMN TIME        HEADING 'Time'
COLUMN POINT_NAME  HEADING 'Message'
COLUMN VALUE       HEADING 'Status'
SELECT TIME, POINT_NAME, VALUE
FROM   TheDataBase_2013
WHERE (Weight='110') AND (VALUE='100')
AND EXTRACT(DAY FROM TIME) = EXTRACT(DAY FROM tabledate);
%
lostinspace9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Is tabledate an actual date column?

By default in Oracle, dates don't have a format.

If it is a date data type try this:

...
WHERE (Weight='110') AND (VALUE='100')
AND tabledate >= trunc(sysdate) and tabledate < trunc(sysdate)+1;


sysdate is an Oracle funtion that returns the system date.  the TRUNC call strips off the time portion.
0
lostinspace9Author Commented:
I made a mistake on my question. Where

AND EXTRACT(DAY FROM TIME) = EXTRACT(DAY FROM tabledate);

should be

AND EXTRACT(DAY FROM TIME) = EXTRACT(DAY FROM sysdate);

Sorry.
0
slightwv (䄆 Netminder) Commented:
>>I made a mistake on my question.

So, is it working now?

If not, did you try my approach?

Did it work?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lostinspace9Author Commented:
With this:

SELECT TIME, POINT_NAME, VALUE
FROM   ALARMS_8_2013
WHERE (STATION_ID='110')
AND (VALUE=' Alarm')
AND TIME >= trunc(sysdate) and TIME < trunc(sysdate)+1;

I received this:

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 6 15:49:42 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5  
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Thanks
0
lostinspace9Author Commented:
That last post showed only a snippet of the code.

Thanks
0
slightwv (䄆 Netminder) Commented:
>>That last post showed only a snippet of the code.

I don't believe the problem is in the snippet you posted.

I suggest you do not copy/paste into sqlplus.  Save the commands in a script file and execute it.

For example, create a file c:\q.sql

In it place all your commands.  

For example:
--show today
select sysdate from dual;
-- say hello to the world
select 'Hello World' from dual;
-- say goodbye
select 'Goodbye World from dual;

Open in new window


The from the sqlplus prompt:
SQL> @C:\q

The .sql extension is the default so it isn't necessary to provide it.
0
lostinspace9Author Commented:
slightwv,

The original code I sent was in the form of a script file but I still get:

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 6 15:49:42 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5  
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Thanks
0
slightwv (䄆 Netminder) Commented:
>>SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5  

Whenever I see this, it looks like a copy/paste.  By script file, do you mean shell script?  There might be an issue in there.

Maybe missing a closing semi-colon on the SQL?

If you can post the script we can take a look at it.  Make sure it doesn't contain any sensitive or proprietary information.
0
lostinspace9Author Commented:
Here is the complete script:

#!/bin/bash
TextFilePath="Schedules.txt"
sqlplus user/PW@Server << % > $TextFilePath
set feedback off
set verify off
set echo off
set pagesize 10
set linesize 100
set newpage none
set feed off
set head off
set tab off
set trimout on
alter session set nls_date_format='MM/DD/YYYY HH24:MI';
COLUMN TIME        HEADING 'Time'
COLUMN POINT_NAME  HEADING 'Message'
COLUMN VALUE       HEADING 'Status'
SELECT TIME, POINT_NAME, VALUE
FROM   TheDataBase_2013
WHERE (Weight='110') AND (VALUE='100')
AND EXTRACT(DAY FROM TIME) = EXTRACT(DAY FROM tabledate);
%

Thanks
0
slightwv (䄆 Netminder) Commented:
I suspect the issue is with your select just not returning any rows.

Log into sqlplus and just run the select:
SELECT TIME, POINT_NAME, VALUE
FROM   TheDataBase_2013
WHERE (Weight='110') AND (VALUE='100')
AND EXTRACT(DAY FROM TIME) = EXTRACT(DAY FROM tabledate);

I think you'll get: no rows found.

Then you need to figure out why now rows are selected.  Either there just aren't any or the where clause isn't quite correct.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lostinspace9Author Commented:
slightwv,

Thanks for all of your help and with it I figured out my problem.  Before I asked for EE help I was doing a lot of trial and error coding and messed things up.  The Select statement was working fine before but I could not get the column headings to work.  Come to find out that I had this:  "set head off" causing the problem.

Also,

AND EXTRACT(DAY FROM TIME) = EXTRACT(DAY FROM tabledate);

should have shown this:

AND EXTRACT(DAY FROM TIME) = EXTRACT(DAY FROM sysdate);

Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.