You can’t reference the column alias in the WHERE clause – your options are: replicate the CASE statement in the WHERE clause use a subquery: PROCEDURE P_LOAD_EXPIRED_ACCOUNT(pDayDiff NUMBER, ExpiredCur OUT MEGAGREEN_CUR) IS BEGIN OPEN ExpiredCur FOR SELECT x.account_name, x.service_type, x.expired_date FROM (SELECT s.account_name, s.service_type, CASE WHEN s.service_type = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3) WHEN s.service_type = 2 … Read more
You can, using LIKE. You don’t want to match for partial values, so you’ll have to include the commas in your search. That also means that you’ll have to provide an extra comma to search for values at the beginning or end of your text: select * from YourTable where ‘,’ || CommaSeparatedValueColumn || ‘,’ … Read more
Use single quotes. select fname,lname from reg1 where uname=”bbb”;
You can definitely get Boolean value from a SELECT query, you just can’t use a Boolean data-type. You can represent a Boolean with 1/0. CASE WHEN (10 > 0) THEN 1 ELSE 0 END (It can be used in SELECT QUERY) SELECT CASE WHEN (10 > 0) THEN 1 ELSE 0 END AS MY_BOOLEAN_COLUMN FROM … Read more
The reason for this error is that SQL SELECT statements are logically * processed in the following order: FROM: selection of one table or many JOINed ones and all rows combinations that match the ON conditions. WHERE: conditions are evaluated and rows that do not match are removed. GROUP BY: rows are grouped (and every … Read more
This is not possible directly, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain. You can do a sub-select and filter on it: SELECT * FROM ( SELECT A.identifier , A.name , TO_NUMBER(DECODE( A.month_no , 1, 200803 , 2, 200804 , 3, 200805 , 4, 200806 , 5, … Read more
Your problem is those pernicious double quotes. SQL> CREATE TABLE “APC”.”PS_TBL_DEPARTMENT_DETAILS” 2 ( 3 “Company Code” VARCHAR2(255), 4 “Company Name” VARCHAR2(255), 5 “Sector_Code” VARCHAR2(255), 6 “Sector_Name” VARCHAR2(255), 7 “Business_Unit_Code” VARCHAR2(255), 8 “Business_Unit_Name” VARCHAR2(255), 9 “Department_Code” VARCHAR2(255), 10 “Department_Name” VARCHAR2(255), 11 “HR_ORG_ID” VARCHAR2(255), 12 “HR_ORG_Name” VARCHAR2(255), 13 “Cost_Center_Number” VARCHAR2(255), 14 ” ” VARCHAR2(255) 15 ) 16 … Read more