"case" in SQL

UPDATE inventory SET price = price *
 CASE
   WHEN quantity > 20 THEN 0.75
   WHEN quantity BETWEEN 10 AND 20 THEN 0.90
   ELSE 0.95
   END;

SELECT employee_name
 CASE admin
   WHEN 1 THEN 'yes'
   ELSE 'no'
   END 'admin'
FROM employees;

VC++ project configuration tips

1. use "local setting" files (*.vsprops) to store the directory configurations. It can be reused for more than one projects.

ProjectType="Visual C++"
Version="8.00"
Name="lokale Einstellungen"
>
Name="OcciDir"
Value="D:\apps\oracle10g\OCI"
PerformEnvironmentSet="true"
/>
Name="BoostDir"
Value="D:\boost\Boost\include\boost_1_35_0"
PerformEnvironmentSet="true"
/>
Name="OcciLib"
Value="lib\MSVC\vc8"
PerformEnvironmentSet="true"
/>


2. The includes files' path should be in "C/C++ => Allgemein => Zusaetzliche Includeverzeichnisse".

3. The libs which are needed should be in "Linker => Allgemein => Zusaetzliche Bibliotheksverzeichnisse". If the libs can not be found, there will be errors like "fatal error LNK1104: Datei libboost_program_options-vc80-mt-1_35.lib kann nicht geoeffnet werden".

Debug database application in visual studio 2005

After upgrading from visual studio 2003 to visual studio 2005, some configurations are needed for debugging database applications.

1. download occi for vc8. Place it at $oracle_home\OCI\lib\MSVC\vc8.

2. add it to Path. Becareful, it must stay before $oracle_home\bin in path.

3. Go to project properties page. Linker -> Eingabe -> Zusaetzliche Abhaengigkeiten, use oraocci10d.lib for debug mode, oraocci10.lib for release mode.

Script to create user

# user auf Ziel-Rechner anlegen, zB script in CYGWIN

-- user anlegen
sqlplus 'sys/lbsys as sysdba' <
-- drop user $1 cascade;

-- vielleicht datafile ' ' noetig?
CREATE TABLESPACE $1
LOGGING
DATAFILE 'd:\ORADATA\\DAT_$1_1.dbf' SIZE 500M AUTOEXTEND
ON NEXT 200M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER $1 PROFILE DEFAULT
IDENTIFIED BY $1 DEFAULT TABLESPACE $1
QUOTA UNLIMITED
ON $1
ACCOUNT UNLOCK;
GRANT ALTER SESSION TO $1;
GRANT CREATE PROCEDURE TO $1;
GRANT CREATE SEQUENCE TO $1;
GRANT CREATE TRIGGER TO $1;
GRANT CREATE TYPE TO $1;
GRANT CONNECT TO $1;

GRANT CREATE TABLE TO $1;
GRANT CREATE view TO $1;
GRANT CREATE synonym TO $1;

EOF