Remember your operator precedence: items are ANDed first, then ORed, so without strategically placed parentheses around the ORed portion of your SELECT statement, you will get the wrong results.
Also, why do you need to break down BACKUP_START and BACKUP_END into comparison of the individual date and time components? Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] IBM Tivoli Storage Manager support web page: http://www.ibm.com/software/sysmgmt/products/support/IBMTivoliStorageManager.html The only dumb question is the one that goes unasked. The command line is your friend. "Good enough" is the enemy of excellence. "ADSM: Dist Stor Manager" <[email protected]> wrote on 10/05/2006 09:48:21 AM: > 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" <[email protected]> > 10/05/2006 10:45 AM > Please respond to > "ADSM: Dist Stor Manager" <[email protected]> > > > To > [email protected] > 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.
