Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Aug 5th, 2012  |  syntax: None  |  size: 1.40 KB  |  hits: 12  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. How to select rows only with Active status
  2. CREATE TABLE USERS(
  3.         USERID INTEGER NOT NULL,
  4.         GROUPID INTEGER,
  5.         SPECIALNUMBER VARCHAR2(60 ),
  6.         USERNAME VARCHAR2(50 ),
  7.         PASSWD VARCHAR2(50 ),
  8.         DATETOCHANGEPASSWD DATE,
  9.         ADDRESS VARCHAR2(60 ),
  10.         STATEREGION VARCHAR2(50 ),
  11.         COUNTRY VARCHAR2(50 ),
  12.         USERSTATUS VARCHAR2(30 ),
  13.         TELEPHONE VARCHAR2(50 ),
  14.         DATEUSERADDED DATE,
  15.         USEREXPIREDATE DATE,
  16.         DATEUSERLOCKED CHAR(20 ),
  17.         CITY VARCHAR2(50 ),
  18.         EMAIL VARCHAR2(50 ),
  19.         DESCRIPTION CLOB
  20.         )
  21.        
  22. SqlStatementSmall = "SELECT c.*"
  23.             + " FROM (SELECT b.*, rownum rn"
  24.             + " FROM (SELECT a.*"
  25.             + " FROM USERS a"
  26.             + " ORDER BY %s %s) b"
  27.             + " WHERE rownum <= ?) c"
  28.             + " WHERE rn > ?";
  29.        
  30. SqlStatementSmall = "SELECT c.*"
  31.             + " FROM (SELECT b.*, rownum rn"
  32.             + " FROM (SELECT a.*"
  33.             + " FROM USERS a"
  34.             + " ORDER BY %s %s) b"
  35.             + " WHERE rownum <= ?) c"
  36.             + " WHERE rn > ? AND USERSTATUS = 'ACTIVE'";
  37.        
  38. SqlStatementSmall = "SELECT c.*"
  39.             + " FROM (SELECT b.*, rownum rn"
  40.             + " FROM (SELECT a.*"
  41.             + " FROM USERS a"
  42.             + " WHERE UPPER(USERSTATUS) = 'ACTIVE'"
  43.             + " ORDER BY %s %s) b"
  44.             + " WHERE rownum <= ?) c"
  45.             + " WHERE rn > ?";
create a new version of this paste RAW Paste Data