Hello Everyone, I finally came up with a mixture of everyone's suggestions so that I now have good output:
select node_name as "NODE_NAME ",filespace_name as "FILESPACE_NAME ",substr(char(backup_start),1,16) as backup_start,substr(char(backup_end),1,16) as backup_end from filespaces where node_name like 'NAS%' or node_name like 'SERVER%' Which gave me: NODE_NAME FILESPACE_NAME BACKUP_START BACKUP_END --------------------- ------------------------------- ------------------ ------------------ NAS_SERVER_2 / 2006-10-04 19:00 2006-10-04 19:01 NAS_SERVER_2 /.etc_common 2006-10-04 19:00 2006-10-04 21:19 NAS_SERVER_2 /apache 2006-10-04 19:00 2006-10-04 21:08 NAS_SERVER_2 /clearcase_prod 2006-10-04 19:00 2006-10-04 20:16 NAS_SERVER_2 /clearcase_test 2006-10-04 19:00 2006-10-04 20:03 NAS_SERVER_2 /edi_test 2006-10-04 19:00 2006-10-04 20:24 NAS_SERVER_2 /home2 2006-10-04 19:00 2006-10-04 20:28 NAS_SERVER_2 /home2/home2dir1 2006-10-04 19:00 2006-10-04 21:02 NAS_SERVER_2 /home2/home2dir2 2006-10-04 19:00 2006-10-04 21:00 NAS_SERVER_2 /home2/home2dir3 2006-10-04 19:00 2006-10-04 21:17 NAS_SERVER_2 /home2/home2dir4 2006-10-04 19:00 2006-10-04 21:17 NAS_SERVER_2 /nas_mp2 2006-10-04 19:00 2006-10-04 20:23 My next challenge is to figure out how to find any filespaces where the backup_start date/time is > the backup_end date/time. It is hard for me to find failed NDMP backups and this is one of the ways in which I can see which filespaces failed/missed. I have tried the following statement: select node_name as "NODE_NAME ",filespace_name as "FILESPACE_NAME ",substr(char(backup_start),1,16) as backup_start,substr(char(backup_end),1,16) as backup_end from filespaces where node_name like 'NAS%' or node_name like 'SERVER%' and date(BACKUP_START)>=date(BACKUP_END) and time(BACKUP_START)>=time(BACKUP_END) But this gives me incorrect output. Does anyone know what I am doing wrong? I've even just tried the "and backup_start > backup_end and I still get incorrect data. Any ideas? Thanks again! ******************************** Joni Moyer Highmark Storage Systems, Senior Systems Programmer Phone Number: (717)302-9966 Fax: (717) 302-9826 [EMAIL PROTECTED] ******************************** "Andrew Carlson" <[EMAIL PROTECTED]> Sent by: "ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.EDU> 10/05/2006 10:45 AM Please respond to "ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.EDU> To ADSM-L@VM.MARIST.EDU cc Subject Re: Select Statement Syntax I went through this same problem. Never got a satisfactory answer, totally. It behaves differently depending on if you do it from a terminal, or disconnected, like from cron. Also it depends on the setting of sqldisplaymode. Try the command `set sqldisplaymode wide` and see if that makes it look like you want. On 10/5/06, Joni Moyer <[EMAIL PROTECTED]> wrote: > > Hello Everyone, > > I have been trying to figure out how to have the syntax of the below > output so that it will all go on 1 line, but so far I have been very > unsuccessful. > > Here is the statement I am issuing: > > select node_name,filespace_name,backup_start,backup_end from filespaces > where node_name like 'NAS%' or node_name like 'SERVER%' > > And here is the output: > > NODE_NAME FILESPACE_NAME BACKUP_START > BACKUP_END > ------------------ ------------------ ------------------ > ------------------ > NAS_SERVER_2_OFFS- / 2006-10-04 > 2006-10-04 > ITE 21:20:32.000000 > 21:23:03.000000 > NAS_SERVER_2_OFFS- /.etc_common 2006-10-04 > 2006-10-05 > ITE 21:20:32.000000 > 02:34:44.000000 > NAS_SERVER_2_OFFS- /apache 2006-10-04 > 2006-10-05 > ITE 21:20:32.000000 > 02:21:08.000000 > NAS_SERVER_2_OFFS- /clearcase_prod 2006-10-04 > 2006-10-04 > ITE 21:20:32.000000 > 22:36:12.000000 > > As you can see the node_name column wraps. I tried to do a left justify > and I also tried to make it 20 characters wide, but I guess I am doing > something wrong because I continue to get the same output. > > I was also wondering if there is a way to change the backup_start to go on > 1 line as well and to make it include the date and the time to be just > hh:mm:ss? > > Thank you in advance for any pointers you might have! > > ******************************** > Joni Moyer > Highmark > Storage Systems, Senior Systems Programmer > Phone Number: (717)302-9966 > Fax: (717) 302-9826 > [EMAIL PROTECTED] > ******************************** > -- Andy Carlson --------------------------------------------------------------------------- Gamecube:$150,PSO:$50,Broadband Adapter: $35, Hunters License: $8.95/month, The feeling of seeing the red box with the item you want in it:Priceless.