Santosh 的个人资料Santosh Kumar照片日志列表更多  |  工具 帮助 |
|
|
Oracle, How to select records from record no N1 to N2SQL> SELECT ROWNUM, E.* FROM EMP E /*This is the table*/; ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1 7369 SMITH CLERK 7902 17-DEC-80 -800 20 2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 3 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 4 7566 JONES MANAGER 7839 02-APR-81 2975 20 5 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 6 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 8 7788 SCOTT ANALYST 7566 09-DEC-82 3000 5000.5 20 9 7839 KING PRESIDENT 17-NOV-81 5000 10 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 11 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 12 7900 JAMES CLERK 7698 03-DEC-81 950 30 13 7902 FORD ANALYST 7566 03-DEC-81 3000 20 14 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL> SELECT * FROM( /*This will select 5th to 10th records* 2 SELECT ROWNUM RN, E.* FROM EMP E WHERE ROWNUM <= 10 3 ) WHERE RN >= 5 4 / RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 5 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 6 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 8 7788 SCOTT ANALYST 7566 09-DEC-82 3000 5000.5 20 9 7839 KING PRESIDENT 17-NOV-81 5000 10 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 6 rows selected. »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com
Oracle, How to select last N recordsSQL> SELECT ROWNUM, E.* FROM EMP E /*This is the table*/;
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1 7369 SMITH CLERK 7902 17-DEC-80 -800 20 2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 3 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 4 7566 JONES MANAGER 7839 02-APR-81 2975 20 5 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 6 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 8 7788 SCOTT ANALYST 7566 09-DEC-82 3000 5000.5 20 9 7839 KING PRESIDENT 17-NOV-81 5000 10 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 11 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 12 7900 JAMES CLERK 7698 03-DEC-81 950 30 13 7902 FORD ANALYST 7566 03-DEC-81 3000 20 14 7934 MILLER CLERK 7782 23-JAN-82 1300 10 2 SELECT * FROM( 3 SELECT ROWNUM RN, E.* FROM EMP E 4 ) ORDER BY RN DESC 5 ) WHERE ROWNUM <= 5 6 / ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 14 7934 MILLER CLERK 7782 23-JAN-82 1300 10 13 7902 FORD ANALYST 7566 03-DEC-81 3000 20 12 7900 JAMES CLERK 7698 03-DEC-81 950 30 11 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com
Oracle, How to select random recordsSQL> SELECT ROWNUM, E.* FROM EMP E /*This is the table*/;
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1 7369 SMITH CLERK 7902 17-DEC-80 -800 20 2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 3 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 4 7566 JONES MANAGER 7839 02-APR-81 2975 20 5 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 6 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 8 7788 SCOTT ANALYST 7566 09-DEC-82 3000 5000.5 20 9 7839 KING PRESIDENT 17-NOV-81 5000 10 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 11 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 12 7900 JAMES CLERK 7698 03-DEC-81 950 30 13 7902 FORD ANALYST 7566 03-DEC-81 3000 20 14 7934 MILLER CLERK 7782 23-JAN-82 1300 10 ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 3 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 12 7900 JAMES CLERK 7698 03-DEC-81 950 30 4 7566 JONES MANAGER 7839 02-APR-81 2975 20 1 7369 SMITH CLERK 7902 17-DEC-80 -800 20 6 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 13 7902 FORD ANALYST 7566 03-DEC-81 3000 20 5 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 14 7934 MILLER CLERK 7782 23-JAN-82 1300 10 8 7788 SCOTT ANALYST 7566 09-DEC-82 3000 5000.5 20 2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 11 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 9 7839 KING PRESIDENT 17-NOV-81 5000 10 2 SELECT * FROM EMP ORDER BY DBMS_RANDOM.VALUE 3 ) E WHERE ROWNUM <= 5 4 / ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 2 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 3 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 4 7788 SCOTT ANALYST 7566 09-DEC-82 3000 5000.5 20 5 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, Concatenating two/multiple columns may not come/be uniqueUnique key integrity ensures prevention from duplicacy. But some time it requires composite columns to form it unique. And the general practice is to concatenate two or more columns. But all the time, especially if it a bulk insert statement to a table from one another table, may raise errors. Lets connect to oracle using string scott/tiger@ora9i and do the following exercise. SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 1 10:18:22 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> SET LINES 1000 SQL> SET PAGES 1000 SQL> CREATE TABLE RECEIPT_T( 2 CUST_ID NUMBER, 3 BOOK_NO NUMBER, 4 REC_SNO NUMBER, 5 REC_AMT NUMBER 6 ); TABLE CREATED. SQL> INSERT INTO RECEIPT_T VALUES(1,10,1,1010); 1 ROW CREATED. SQL> INSERT INTO RECEIPT_T VALUES(2,20,1,1020); 1 ROW CREATED. SQL> INSERT INTO RECEIPT_T VALUES(3,10,2,1000); 1 ROW CREATED. SQL> INSERT INTO RECEIPT_T VALUES(4,11,1,1005); 1 ROW CREATED. SQL> INSERT INTO RECEIPT_T VALUES(5,1,11,1020); 1 ROW CREATED. SQL> SELECT * FROM RECEIPT_T ; CUST_ID BOOK_NO REC_SNO REC_AMT ---------- ---------- ---------- ---------- 1 10 1 1010 2 20 1 1020 3 10 2 1000 4 11 1 1005 5 1 11 1020 Here as the data above states no rec_sno is repeated for any book_no. Well, that’s an illusion. If you try to concatenate book_no and rec_sno it will tell you a completely different story. Let’s try the following query. SQL> SELECT CUST_ID, BOOK_NO||REC_SNO UNIQ_NO, REC_AMT FROM RECEIPT_T; CUST_ID UNIQ_NO REC_AMT ---------- ------- ---------- 1 101 1010 2 201 1020 3 102 1000 4 111 1005 5 111 1020 Did you see the record no 4 and 5. Are they unique at uniq_no, No na? All right what is the solution then? They are many. Various techniques can be used. One of them can be padding the value with lpad or rpad. Just see the data below. SQL> SELECT CUST_ID, LPAD(BOOK_NO,4,0)||LPAD(REC_SNO,3,0) UNIQ_NO, REC_AMT FROM RECEIPT_T; CUST_ID UNIQ_NO REC_AMT ---------- ------- ---------- 1 0010001 1010 2 0020001 1020 3 0010002 1000 4 0011001 1005 5 0001011 1020 »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com SQL Server, Using OSQL command line for command line query and exitSometimes working with command line makes many things easy. For example, if you want a text file that will list all employees with latest data, the following command line can be a better method. And the better suggestion is to create a batch file so that every time typing the same thing will not be required. osql.exe ^ -S myHost ^ -U myUser -P myPwd ^ -Q "select * from pubs..employee" ^ -o "d:\PUBS_EMP__%date:~-4,4%%date:~-7,2%%date:~-10,2%_%time::=%.txt" ^ -w 1024 I have used symbol ^ so that I can write a single command into multiple lines. Otherwise it’s a single command and must be given in a single line. The switch –Q is for command line query and then exit. All the all the switch used above are case sensitive and other switches can be seen by issuing osql.exe /?. It will create a file called PUBS_EMP__20090501_ 95401.57.TXT. The switch –o is for output filename and the value d:\PUBS_EMP__%date:~-4,4%%date:~-7,2%%date:~-10,2%_%time::=%.txt will form this filename. So you will get the following thing in that file EMP_ID FNAME MINIT LNAME JOB_ID JOB_LVL PUB_ID HIRE_DATE --------- ------------ ----- ---------- ------ ------- ------ ----------------------- PMA42628M Paolo M Accorti 13 35 0877 1992-08-27 00:00:00.000 PSA89086M Pedro S Afonso 14 89 1389 1990-12-24 00:00:00.000 VPA30890F Victoria P Ashworth 6 140 0877 1990-09-13 00:00:00.000 H-B39728F Helen Bennett 12 35 0877 1989-09-21 00:00:00.000 L-B31947F Lesley Brown 7 120 0877 1991-02-13 00:00:00.000 F-C16315M Francisco Chang 4 227 9952 1990-11-03 00:00:00.000 PTC11962M Philip T Cramer 2 215 9952 1989-11-11 00:00:00.000 A-C71970F Aria Cruz 10 87 1389 1991-10-26 00:00:00.000 AMD15433F Ann M Devon 3 200 9952 1991-07-16 00:00:00.000 ARD36773F Anabela R Domingues 8 100 0877 1993-01-27 00:00:00.000 PHF38899M Peter H Franken 10 75 0877 1992-05-17 00:00:00.000 PXH22250M Paul X Henriot 5 159 0877 1993-08-19 00:00:00.000 CFH28514M Carlos F Hernadez 5 211 9999 1989-04-21 00:00:00.000 PDI47470M Palle D Ibsen 7 195 0736 1993-05-09 00:00:00.000 KJJ92907F Karla J Jablonski 9 170 9999 1994-03-11 00:00:00.000 KFJ64308F Karin F Josephs 14 100 0736 1992-10-17 00:00:00.000 MGK44605M Matti G Karttunen 6 220 0736 1994-05-01 00:00:00.000 POK93028M Pirkko O Koskitalo 10 80 9999 1993-11-29 00:00:00.000 JYL26161F Janine Y Labrune 5 172 9901 1991-05-26 00:00:00.000 M-L67958F Maria Larsson 7 135 1389 1992-03-27 00:00:00.000 Y-L77953M Yoshi Latimer 12 32 1389 1989-06-11 00:00:00.000 LAL21447M Laurence A Lebihan 5 175 0736 1990-06-03 00:00:00.000 ENL44273F Elizabeth N Lincoln 14 35 0877 1990-07-24 00:00:00.000 PCM98509F Patricia C McKenna 11 150 9999 1989-08-01 00:00:00.000 R-M53550M Roland Mendel 11 150 0736 1991-09-05 00:00:00.000 RBM23061F Rita B Muller 5 198 1622 1993-10-09 00:00:00.000 HAN90777M Helvetius A Nagy 7 120 9999 1993-03-19 00:00:00.000 TPO55093M Timothy P O'Rourke 13 100 0736 1988-06-19 00:00:00.000 SKO22412M Sven K Ottlieb 5 150 1389 1991-04-05 00:00:00.000 MAP77183M Miguel A Paolino 11 112 1389 1992-12-07 00:00:00.000 PSP68661F Paula S Parente 8 125 1389 1994-01-19 00:00:00.000 M-P91209M Manuel Pereira 8 101 9999 1989-01-09 00:00:00.000 MJP25939M Maria J Pontes 5 246 1756 1989-03-01 00:00:00.000 M-R38834F Martine Rance 9 75 0877 1992-02-05 00:00:00.000 DWR65030M Diego W Roel 6 192 1389 1991-12-16 00:00:00.000 A-R89858F Annette Roulet 6 152 9999 1990-02-21 00:00:00.000 MMS49649F Mary M Saveley 8 175 0736 1993-06-29 00:00:00.000 CGS88322F Carine G Schmitt 13 64 1389 1992-07-07 00:00:00.000 MAS70474F Margaret A Smith 9 78 1389 1988-09-29 00:00:00.000 HAS54740M Howard A Snyder 12 100 0736 1988-11-19 00:00:00.000 MFS52347M Martin F Sommer 10 165 0736 1990-04-13 00:00:00.000 GHT50241M Gary H Thomas 9 170 0736 1988-08-09 00:00:00.000 DBT39435M Daniel B Tonini 11 75 0877 1990-01-01 00:00:00.000 (43 rows affected) »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, Error ORA-01033: ORACLE initialization or shutdown in progressThere are many reasons for this error. One of these reasons is file corruption, which needs media recovery. The following steps can help ...1. Go Start Menu -> Run -> Cmd -> OK ...2. Give the following commands for shutting down oracle C:\> SQLPLUS /NOLOG SQL> CONNECT SYS/PWD AS SYSDBA SQL> SHUTDOWN ABORT SQL> EXIT ...3. Now check and fix the drive in which oracle is installed C:\> CHKDSK <DRIVE_NAME>: /F ...4. Go again to oracle and recover database C:\> SQLPLUS /NOLOG SQL> CONNECT SYS/PWD AS SYSDBA SQL> RECOVER DATABASE SQL> STARTUP SQL> EXIT ...5. All the best »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, The beauty while using UNIONEvery time just de-normalizing data cannot satisfy the understanding of information. Many times it requires proper heading and branching for putting the information on a report. That’s why people use to hard code each and every report. But I feel, if brain is applied properly query alone can give all kind of presentation of the data.
SQL> SELECT * FROM EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 -800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 5000.5 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 ROWS SELECTED. SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 3 ROWS SELECTED. SQL> SELECT NAME, JOB, SAL, EMPNO FROM( 2 SELECT 3 ORD, DEPTNO, ENAME NAME, JOB, SAL, EMPNO FROM EMP 3 UNION 4 SELECT 1 ORD, DEPTNO, DNAME NAME, NULL, NULL, NULL FROM DEPT 5 UNION 6 SELECT 2 ORD, DEPTNO, SUBSTR(LPAD('-',20,'-'),1,20) NAME, NULL, NULL, NULL FROM DEPT 7 UNION 8 SELECT 4 ORD, DEPTNO, NULL NAME, NULL, NULL, NULL FROM DEPT 9 ORDER BY DEPTNO, ORD 10 / NAME JOB SAL EMPNO -------------------- --------- ---------- ---------- ACCOUNTING -------------------- CLARK MANAGER 2450 7782 MILLER CLERK 1300 7934 KING PRESIDENT 5000 7839 RESEARCH -------------------- ADAMS CLERK 1100 7876 SMITH CLERK -800 7369 SCOTT ANALYST 3000 7788 FORD ANALYST 3000 7902 JONES MANAGER 2975 7566 SALES -------------------- ALLEN SALESMAN 1600 7499 WARD SALESMAN 1250 7521 TURNER SALESMAN 1500 7844 BLAKE MANAGER 2850 7698 JAMES CLERK 950 7900 MARTIN SALESMAN 1250 7654 »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, Date & Time ArithmeticWith very few functions oracle has provided every thing needed for doing all kind of arithmetic with date and time. Most of the thing are possible just with to_date() and to_char(). Have a look on the following statements. Oracle, PL/SQL execution feedbacksExecution in PL/SQL script must give certain feedback so that it can reduce 100s of checks on the executed output. The variables like SQL%ROWCOUNT, SQL%FOUND and SQL%NOTFOUND can give much more interactive feedbacks. Find the following codes DECLARE RCNT NUMBER; UCNT NUMBER; FCNT NUMBER; BEGIN RCNT:=0; UCNT:=0; FCNT:=0; FOR RS IN (SELECT ROWNUM RN, E.* FROM EMP E) LOOP UPDATE EMP SET EMPNO=EMPNO WHERE TO_CHAR(HIREDATE,'MM')=TRIM(TO_CHAR(RS.RN,'00')); RCNT:=RCNT+1; IF SQL%ROWCOUNT>0 THEN FCNT:=FCNT+1; UCNT:=UCNT+SQL%ROWCOUNT; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('In '||RCNT||' attempts, a total of '||UCNT||' records are updated by ' ||FCNT||' successful executions.'); END; »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, Keeping last few records in a tableMany times some fixed number of transaction is required to maintain for some trailing purpose but it does not require keeping history of the same. In such a situation deleting unwanted data is a good idea. Following set of statements is describing how to keep last 5 records in the table. The delete statement given below can be used to trigger at each insertion of record. SQL> SELECT RID, DETAIL, AMT, TO_CHAR(RDT,'DD/MON/YYYY HH:MI:SS') RDT 2 FROM ANYTABLE ORDER BY RDT DESC; RID DETAIL AMT RDT ---------- ---------- ---------- -------------------- 5036 FUEL 600 01/DEC/2008 04:52:49 5035 RENT 300 01/DEC/2008 04:52:26 5034 STATIONARY 50 01/DEC/2008 04:51:54 5033 SALARY 5000 01/DEC/2008 04:51:36 5032 DEBTOR 350 01/DEC/2008 04:51:13 5031 INT 600 01/DEC/2008 04:50:03 5030 DUE 200 01/DEC/2008 04:50:01 7 rows selected. SQL> DELETE FROM ANYTABLE WHERE RID IN( 2 SELECT RID FROM( 3 SELECT ROWNUM SNO, RID FROM( 4 SELECT RID FROM ANYTABLE ORDER BY RDT DESC 5 ) 6 ) 7 WHERE SNO>5 8 ) 9 / 2 rows deleted. SQL> SELECT RID, DETAIL, AMT, TO_CHAR(RDT,'DD/MON/YYYY HH:MI:SS') RDT 2 FROM ANYTABLE ORDER BY RDT DESC; RID DETAIL AMT RDT ---------- ---------- ---------- -------------------- 5036 FUEL 600 01/DEC/2008 04:52:49 5035 RENT 300 01/DEC/2008 04:52:26 5034 STATIONARY 50 01/DEC/2008 04:51:54 5033 SALARY 5000 01/DEC/2008 04:51:36 5032 DEBTOR 350 01/DEC/2008 04:51:13 SQL> »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com SQL Server, Ensuring functionalities on web based password dependant live applicationsWhen a web application is going live and a new version of the application is deployed on it, there exists variety of cases where one needs to check the functionalities of the screens, which are role based. But encrypted password in the database restricts for many thing. Specially, when you change the password of a live user for login and verifying his screen and, then you miss the original password to revert back. I would suggest the following procedure, consider the following table SELECT * FROM MYTAB RID USR PWD ---- ---------- --------- 1 JOHN wrdt== 2 LILLY tdo== 3 MARIA rtg45== 4 MONA wecv== 5 JESSIKA er54t== 6 DAVID wgb5lp== 7 SMITH ert== (7 row(s) affected) At the very first step you have to use the following command at command prompt C:\> OSQL.EXE -S LOCALHOST -U SA -P SA -Q "SELECT 'UPDATE MYTAB SET PWD=''' + PWD + ''' WHERE RID=''' + CONVERT(VARCHAR, RID) + '''' SQL FROM MYWEB..MYTAB" -o "D:\MYPWD.TXT" -w 1024 You will get a text file called MYPWD.TXT with the following data SQL -------------------------------------------------------------------------------------- UPDATE MYTAB SET PWD='wrdt==' WHERE RID='1' UPDATE MYTAB SET PWD='tdo==' WHERE RID='2' UPDATE MYTAB SET PWD='rtg45==' WHERE RID='3' UPDATE MYTAB SET PWD='wecv==' WHERE RID='4' UPDATE MYTAB SET PWD='er54t==' WHERE RID='5' UPDATE MYTAB SET PWD='wgb5lp==' WHERE RID='6' UPDATE MYTAB SET PWD='ert==' WHERE RID='7' (7 rows affected) Now you give a command as follows, at your query analyzer. UPDATE MYTAB SET PWD='rtg1==' –let’s think ‘rtg1==’ is an encryption of ‘abc’. Now my friend you are free to get into anybodies live login using a password ‘abc’. So, just get inside, have a look, whether everything is working fine and then.. then.. then.. yes then my friend, you execute all the update statement of MYPWD.TXT file with your query analyzer. But with the these procedure don’t rotate you mind in an anti clockwise direction »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, Building a tree from an un-normalized databaseWhere the table is not normalized and the output is required in a tree structure, the ‘union’ operator can do a lot for you. Here in the example, table ‘tree’ contains three fields viz. main-group, sub-group, and element. And details below are everything else required. SQL> select * from tree; MGRP SGRP ELE ---------- ---------- ---------- MAINGRP1 SUBGRP11 ELEMENT111 MAINGRP1 SUBGRP11 ELEMENT112 MAINGRP1 SUBGRP12 ELEMENT121 MAINGRP1 SUBGRP12 ELEMENT122 MAINGRP2 SUBGRP21 ELEMENT211 MAINGRP2 SUBGRP21 ELEMENT212 MAINGRP2 SUBGRP22 ELEMENT221 MAINGRP2 SUBGRP22 ELEMENT222 MAINGRP3 SUBGRP31 ELEMENT311 MAINGRP3 SUBGRP31 ELEMENT312 MAINGRP3 SUBGRP32 ELEMENT321 MAINGRP3 SUBGRP32 ELEMENT322 12 rows selected. SQL> select datatree from( 2 select distinct ' |-- ' || mgrp datatree, mgrp ord from tree 3 union all 4 select distinct ' | |-- ' || sgrp datatree, mgrp||sgrp ord from tree 5 union all 6 select distinct ' | | |-- ' || ele datatree, mgrp||sgrp||ele ord from tree 7 ) order by ord 8 / DATATREE ------------------------- |-- MAINGRP1 | |-- SUBGRP11 | | |-- ELEMENT111 | | |-- ELEMENT112 | |-- SUBGRP12 | | |-- ELEMENT121 | | |-- ELEMENT122 |-- MAINGRP2 | |-- SUBGRP21 | | |-- ELEMENT211 | | |-- ELEMENT212 | |-- SUBGRP22 | | |-- ELEMENT221 | | |-- ELEMENT222 |-- MAINGRP3 | |-- SUBGRP31 | | |-- ELEMENT311 | | |-- ELEMENT312 | |-- SUBGRP32 | | |-- ELEMENT321 | | |-- ELEMENT322 21 rows selected. SQL> »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com SQL Server, Summing with time
Consider the from output
SELECT * FROM TIME_TEST
STIME ETIME ----- ----- 00:15 01:02 01:05 03:05 10:06 05:05 11:50 01:06
Now we want sum of the two fields, which are not already a datetime field, rather is storing the data in character datatype using time format. Following is the solution.
SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME, SUM(CONVERT(NUMERIC(30, 15), CONVERT(DATETIME, STIME)))),108) SUM_STIME, CONVERT(VARCHAR(20),CONVERT(DATETIME, SUM(CONVERT(NUMERIC(30, 15), CONVERT(DATETIME, ETIME)))),108) SUM_ETIME FROM TIME_TEST
SUM_STIME SUM_ETIME --------- --------- 23:16:00 10:18:00 SQL Server, Shrinking Data Filesbegin
declare @thisdb varchar(50) declare @thisLog varchar(50) declare @thisData varchar(50) set @thisdb='db_name' --set db name set @thisData=@thisdb + '_Data' set @thisLog=@thisdb + '_Log' DBCC SHRINKFILE (@thisLog, 1) DBCC SHRINKFILE (@thisData, 1) end go SQL Server, Building a Tree Structure with HierarchyAs we know the aim of database designing must be making it normalized to its greater extent but some time the data retrieval becomes pathetic. Consider the following table, which is well designed to maintain data hierarchy in a tree structure. But to build the tree may not be that much easy. The Store Procedure Written will enable all sub level IID of a selected one and also display the data in a tree structure. Its parameters will make it more useful Input Table ITEM_HIERARCHY IID PIID INAME 3 0 Europe 5 0 Asia 2 0 South America 1 0 North America 4 0 Africa 6 0 Austrailia 7 0 Antarctica 8 5 India 9 5 China 10 5 Pak 11 5 Afgan 12 8 Andhra 13 8 Orissa 14 8 Chhattisgarh 15 13 Sambalpur 16 13 Barahmpur 17 13 Rourkela 18 13 Bhubaneshwar 19 15 Khetrajpur 20 15 Sasan 21 15 Rengali Output Data INAME HIR HID IID Europe 1 101 3 Asia 1 102 5 India 2 102101 8 Andhra 3 102101101 12 Orissa 3 102101102 13 Sambalpur 4 102101102101 15 Khetrajpur 5 102101102101101 19 Sasan 5 102101102101102 20 Rengali 5 102101102101103 21 Barahmpur 4 102101102102 16 Rourkela 4 102101102103 17 Bhubaneshwar 4 102101102104 18 Chhattisgarh 3 102101103 14 China 2 102102 9 Pak 2 102103 10 Afgan 2 102104 11 South America 1 103 2 North America 1 104 1 Africa 1 105 4 Austrailia 1 106 6 Antarctica 1 107 7 And .......... The Store Procedure /*'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ' Store Proc : SK_DoTree ' Author : Santosh Kumar ' Date : 12-Jul-2008 ' Purpose : To Build tree structure ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''*/ alter procedure SK_DoTree ( @amIID int = 0, -- whose sub levels are required, default all @amHIR int = 0, -- how many sub levels are required, default all @amGAP int = 3 -- no of space before items, default 3 ) as set nocount on declare @HIR decimal declare @HID decimal declare @FIID decimal declare @FPIID decimal declare @CIID decimal begin set @HIR=1 set @HID=0 set @CIID=0 create table #AllTable ( IID decimal, HID varchar(300), HIR decimal) create table #PrvTable ( IID decimal, HID varchar(300), HIR decimal) create table #CurTable ( IID decimal, HID varchar(300), HIR decimal) select IID as IID, identity(int,1,1) as HID, 1 as HIR into #IIDtmp from ITEM_HIERARCHY where PIID = @amIID insert into #PrvTable select IID, convert(varchar(3),100+HID), HIR from #IIDtmp insert into #AllTable select * from #PrvTable drop table #IIDtmp while (@HIR < @amHIR or @amHIR = 0) and (select count(IID) from ITEM_HIERARCHY where PIID in (select IID from #PrvTable))>0 begin set @HIR=@HIR + 1 truncate table #CurTable declare curcer cursor local for select IID, PIID from ITEM_HIERARCHY where PIID in (select IID from #PrvTable) open curcer fetch curcer into @FIID, @FPIID while @@fetch_status=0 begin if @FPIID <> @CIID begin set @CIID = @FPIID set @HID = 0 end set @HID = @HID + 1 insert into #CurTable select @FIID, HID + convert(varchar,100+@HID), @HIR from #PrvTable where IID = @FPIID fetch curcer into @FIID, @FPIID end close curcer deallocate curcer truncate table #PrvTable insert into #PrvTable select * from #CurTable insert into #AllTable select * from #CurTable end select replicate(' ',@amGAP*a.HIR) + b.INAME as INAME, HIR, a.HID, a.IID from #AllTable a, ITEM_HIERARCHY b where a.IID = b.IID order by HID drop table #AllTable drop table #PrvTable drop table #CurTable end set nocount off go SK_DoTree »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, The beauty in normalized database
And the Query is... Oracle, Generating horizontal bar graphSimple in build function can result much desired output. Knowledge of these functions in any DBMS can solve most of the text based reporting problem within the query itself. INPUT TABLE YEAR SALE ---------- ---------- 2000 35 2001 12 2002 25 2003 45 2004 30 2005 42 2006 22 2007 32 OUTPUT FORMAT -------------------------------------------------------- 2000 ææææææææææææææææææææææææææææææææææ 2001 æææææææææææ 2002 ææææææææææææææææææææææææ 2003 ææææææææææææææææææææææææææææææææææææææææææææ 2004 æææææææææææææææææææææææææææææ 2005 æææææææææææææææææææææææææææææææææææææææææ 2006 æææææææææææææææææææææ 2007 æææææææææææææææææææææææææææææææ -------------------------------------------------------- AND THE QUERY IS... SELECT GRAPH " " FROM( SELECT SUBSTR(YEAR||' '||LPAD(' ',SALE,CHR(230)),1,56) GRAPH, YEAR, 1 POS FROM ANYTABLE UNION SELECT SUBSTR(LPAD('-',56,'-'),1,56) GRAPH, 0 YEAR, 2 POS FROM DUAL ) ORDER BY POS, YEAR »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, Generating tree structure outputMost of the time software engineering includes sophisticated id system for records and using this, a single query can do a much better results. Consider the following case that will give the output in a tree structure. INPUT TABLE ID TRAN AMT ---------- -------------------- ---------- 111 John 220 112 David 200 11 Salary 2 Interest 122 Pencil 13 3 Rent 20.75 12 Stationary 1 Expenses 121 Pen 4 21 UTI 30 22 SBI 300.5 OUTPUT FORMAT TRANSACTION AMOUNT -------------------- ------- Expenses Salary John 220.00 David 200.00 Stationary Pen 4.00 Pencil 13.00 Interest UTI 30.00 SBI 300.50 Rent 20.75 AND THE QUERY IS... SELECT SUBSTR(LPAD(' ',2*(LENGTH(ID)),' ')||TRAN,1,20) TRANSACTION, TO_CHAR(AMT,'99999.99') AMOUNT FROM ANYTABLE ORDER BY SUBSTR(ID||'00000',1,5); »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com SQL Server, Splitting rows into columnsThink of a situation where you want your records to display in three columns so that it will get fit into one third number of rows. In oracle rownum keyword can be useful, but in SQL Server it requires additional logic. Think of the following data where you don’t have ID column INPUT TABLE ID NAME AGE SEX 1 N1 20 M 2 N2 21 M 3 N3 21 M 4 N4 21 M 5 N5 21 M 6 N6 21 M 7 N7 36 M 8 N8 26 M 9 N9 25 M 10 N10 22 M 11 N11 23 M 12 N12 22 M 13 N13 33 M 14 N14 23 M OUTPUT FORMAT ID NAME AGE SEX ID NAME AGE SEX ID NAME AGE SEX 1 N1 20 M 2 N2 21 M 3 N3 21 M 4 N4 21 M 5 N5 21 M 6 N6 21 M 7 N7 36 M 8 N8 26 M 9 N9 25 M 10 N10 22 M 11 N11 23 M 12 N12 22 M 13 N13 33 M 14 N14 23 M AND THE QUERY IS... select identity(int, 1,1) rn, a.* into #tmp from anytable a select * from (select * from #tmp a where rn%3=1) t1, (select * from #tmp b where rn%3=2) t2, (select * from #tmp c where rn%3=0) t3 where (t1.rn+1)=*t2.rn and (t2.rn+1)*=t3.rn drop table #tmp In Oracle it would look like below query...
select * from
(select * from anytable where mod(id,3)=1) t1, (select * from anytable where mod(id,3)=2) t2, (select * from anytable where mod(id,3)=0) t3 where (t1.id+1)=t2.id(+) and (t2.id+1)=t3.id(+) or it would be...
select * from (select * from anytable where mod(id-1,3)=0) t1, (select * from anytable where mod(id-2,3)=0) t2, (select * from anytable where mod(id-3,3)=0) t3 where (t1.id+1)=t2.id(+) and (t2.id+1)=t3.id(+) »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, Building a Dynamic SQL StatementThink of a situation where one crash dump of an entire user UsrY is imported into another important user UsrX and you need to delete these unwanted objects now. The problem here is, if the no of objects are many and are not only tables but also views, there will be difficulty to list out the objects manually and generate the ‘drop’ statement. The following statement should be executed in UsrY after importing it separately, and then it can generate dynamic SQL statement that can be used to execute in UsrX.
SQL> CONN SYSTEM/MANAGER; |
|
|
