Formating SQL query
I am not very cool in SQL and I need help. I have query like select distict a,b from c group by a,b Response on this SQL query in TSM Server is: A1 B1 A1 B2 A2 B3 A2 B4 A2 B5 I would like to have: A1 B1 B2 A2 B3 B4 B5 I know exactly it is possible in Oracle SQL*Plus. Is it possible in TSM Server 5.5.3? Is it possible in TSM Server 6.1.2 (DB2)? What is the way, if possible? Grigori G. Solonovitch Senior Technical Architect Information Technology Bank of Kuwait and Middle East http://www.bkme.com Phone: (+965) 2231-2274 Mobile: (+965) 99798073 E-Mail: g.solonovi...@bkme.commailto:g.solonovi...@bkme.com Please consider the environment before printing this Email Please consider the environment before printing this Email. This email message and any attachments transmitted with it may contain confidential and proprietary information, intended only for the named recipient(s). If you have received this message in error, or if you are not the named recipient(s), please delete this email after notifying the sender immediately. BKME cannot guarantee the integrity of this communication and accepts no liability for any damage caused by this email or its attachments due to viruses, any other defects, interception or unauthorized modification. The information, views, opinions and comments of this message are those of the individual and not necessarily endorsed by BKME.
Re: Formating SQL query
A year ago I attended an introductory 5-day SQL*Plus course where we were told that usage of 'distinct' is discouraged, use 'group' by instead. My 5¢. -- Warm regards, Michael Green On Thu, Oct 15, 2009 at 2:49 PM, Grigori Solonovitch g.solonovi...@bkme.com wrote: I am not very cool in SQL and I need help. I have query like select distict a,b from c group by a,b Response on this SQL query in TSM Server is: A1 B1 A1 B2 A2 B3 A2 B4 A2 B5 I would like to have: A1 B1 B2 A2 B3 B4 B5 I know exactly it is possible in Oracle SQL*Plus. Is it possible in TSM Server 5.5.3? Is it possible in TSM Server 6.1.2 (DB2)? What is the way, if possible?
Re: Formating SQL query
Thank you very much, but this an answer on my question? Is it enough to remove distinct? Grigori G. Solonovitch Senior Technical Architect Information Technology Bank of Kuwait and Middle East http://www.bkme.com Phone: (+965) 2231-2274 Mobile: (+965) 99798073 E-Mail: g.solonovi...@bkme.com Please consider the environment before printing this Email -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Michael Green Sent: Thursday, October 15, 2009 3:58 PM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] Formating SQL query A year ago I attended an introductory 5-day SQL*Plus course where we were told that usage of 'distinct' is discouraged, use 'group' by instead. My 5¢. -- Warm regards, Michael Green On Thu, Oct 15, 2009 at 2:49 PM, Grigori Solonovitch g.solonovi...@bkme.com wrote: I am not very cool in SQL and I need help. I have query like select distict a,b from c group by a,b Response on this SQL query in TSM Server is: A1 B1 A1 B2 A2 B3 A2 B4 A2 B5 I would like to have: A1 B1 B2 A2 B3 B4 B5 I know exactly it is possible in Oracle SQL*Plus. Is it possible in TSM Server 5.5.3? Is it possible in TSM Server 6.1.2 (DB2)? What is the way, if possible? Please consider the environment before printing this Email. This email message and any attachments transmitted with it may contain confidential and proprietary information, intended only for the named recipient(s). If you have received this message in error, or if you are not the named recipient(s), please delete this email after notifying the sender immediately. BKME cannot guarantee the integrity of this communication and accepts no liability for any damage caused by this email or its attachments due to viruses, any other defects, interception or unauthorized modification. The information, views, opinions and comments of this message are those of the individual and not necessarily endorsed by BKME.
Re: Formating SQL query
Grigori, I assume the sql*plus feature you use is the break statement which by default does outlines on break columns. Besides submitting sql and retrieving results sets, Sql*plus includes a lot of report writer functions which are not strictly SQL. So I don't know any way to do outlining with just sql. In version 6 you can make the db2 databases visible to other tools using jdbc or odbc. See the wiki for directions. I use a free tool - DB visualizer (http://www.minq.se/products/dbvis/download/index.jsp) to examine tables to attempt to understand what is going on. All the views on the tsm db are there. I haven't looked for a tool which does outlining but I am sure there is one. Bill Colwell Draper Lab. -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Grigori Solonovitch Sent: Thursday, October 15, 2009 8:49 AM To: ADSM-L@VM.MARIST.EDU Subject: Formating SQL query I am not very cool in SQL and I need help. I have query like select distict a,b from c group by a,b Response on this SQL query in TSM Server is: A1 B1 A1 B2 A2 B3 A2 B4 A2 B5 I would like to have: A1 B1 B2 A2 B3 B4 B5 I know exactly it is possible in Oracle SQL*Plus. Is it possible in TSM Server 5.5.3? Is it possible in TSM Server 6.1.2 (DB2)? What is the way, if possible? Grigori G. Solonovitch Senior Technical Architect Information Technology Bank of Kuwait and Middle East http://www.bkme.com Phone: (+965) 2231-2274 Mobile: (+965) 99798073 E-Mail: g.solonovi...@bkme.commailto:g.solonovi...@bkme.com Please consider the environment before printing this Email Please consider the environment before printing this Email. This email message and any attachments transmitted with it may contain confidential and proprietary information, intended only for the named recipient(s). If you have received this message in error, or if you are not the named recipient(s), please delete this email after notifying the sender immediately. BKME cannot guarantee the integrity of this communication and accepts no liability for any damage caused by this email or its attachments due to viruses, any other defects, interception or unauthorized modification. The information, views, opinions and comments of this message are those of the individual and not necessarily endorsed by BKME.
Re: Formating SQL query
Bill, Thank you very much. I do not think it is a good idea to use direct interface to DB2. I do not want to be DB2 database administrator. I had some not very good expirience with DB2-based IBM MERVA application in the past (similar to SWIFT). As far as I understatnd, IBM does not advise to work with DB2 directly in TSM as well becaose of very high risk. In my opinion, DB2 in TSM 6.1 is one of the main problems. We were planning to implement 6.1.2 in production mode, but project was posponed after testing (at least up to more stable release). I have tried to find something more simple to format SQL query. No result is a result as well. Grigori From: ADSM: Dist Stor Manager [ads...@vm.marist.edu] On Behalf Of Colwell, William F. [bcolw...@draper.com] Sent: Thursday, October 15, 2009 10:01 PM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] Formating SQL query Grigori, I assume the sql*plus feature you use is the break statement which by default does outlines on break columns. Besides submitting sql and retrieving results sets, Sql*plus includes a lot of report writer functions which are not strictly SQL. So I don't know any way to do outlining with just sql. In version 6 you can make the db2 databases visible to other tools using jdbc or odbc. See the wiki for directions. I use a free tool - DB visualizer (http://www.minq.se/products/dbvis/download/index.jsp) to examine tables to attempt to understand what is going on. All the views on the tsm db are there. I haven't looked for a tool which does outlining but I am sure there is one. Bill Colwell Draper Lab. -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Grigori Solonovitch Sent: Thursday, October 15, 2009 8:49 AM To: ADSM-L@VM.MARIST.EDU Subject: Formating SQL query I am not very cool in SQL and I need help. I have query like select distict a,b from c group by a,b Response on this SQL query in TSM Server is: A1 B1 A1 B2 A2 B3 A2 B4 A2 B5 I would like to have: A1 B1 B2 A2 B3 B4 B5 I know exactly it is possible in Oracle SQL*Plus. Is it possible in TSM Server 5.5.3? Is it possible in TSM Server 6.1.2 (DB2)? What is the way, if possible? Grigori G. Solonovitch Senior Technical Architect Information Technology Bank of Kuwait and Middle East http://www.bkme.com Phone: (+965) 2231-2274 Mobile: (+965) 99798073 E-Mail: g.solonovi...@bkme.commailto:g.solonovi...@bkme.com Please consider the environment before printing this Email Please consider the environment before printing this Email. This email message and any attachments transmitted with it may contain confidential and proprietary information, intended only for the named recipient(s). If you have received this message in error, or if you are not the named recipient(s), please delete this email after notifying the sender immediately. BKME cannot guarantee the integrity of this communication and accepts no liability for any damage caused by this email or its attachments due to viruses, any other defects, interception or unauthorized modification. The information, views, opinions and comments of this message are those of the individual and not necessarily endorsed by BKME. This email message and any attachments transmitted with it may contain confidential and proprietary information, intended only for the named recipient(s). If you have received this message in error, or if you are not the named recipient(s), please delete this email after notifying the sender immediately. BKME cannot guarantee the integrity of this communication and accepts no liability for any damage caused by this email or its attachments due to viruses, any other defects, interception or unauthorized modification. The information, views, opinions and comments of this message are those of the individual and not necessarily endorsed by BKME.
