I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.
select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a
given error as:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
I have a table in SQL , I am creating a column Of name in it , i want to restrict user to enter name in Capital only ,and i want to create this at table level . I tried Check Operator but failed .
1) Can we set a different symbol other than '' for escape operator. 2) If yes, how to see the current escape operator symbol. 3) How to find out the below name with escape operator?
I have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this
select a,b,rownum as 'field1' from table1 union select c,d,1 as 'field1' from table2
Query - SELECT * FROM sysadm.ps_tmtl_post_vw a WHERE a.month_prepared_for = 'JUNE,2012' AND a.ca_status = 'P5 CUST GO AHEAD'
[code]...
When I try for the SQL-Tuning sets its throws error that
ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - The optimizer could not merge the view at line ID 2 of the execution plan. The optimizer cannot merge a view that contains a set operator.
I read earlier forum where it says that optimizer unable to interpret the conditions like order by etc etc.Now there is one view which is getting used in the query when I did select * from vw it took more than 16 hrs to complete. (bad view).
Attached File(s)
exec_plan.txt ( 2.06MB ) Number of downloads: 1 view_def.txt ( 14.12K ) Number of downloads: 2
Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.
Select 'H '||' S' From Dual; --Output H S
Select Concat('H ',' S') A From Dual; --Output H S
Select Length('H '||' S') A From Dual; --Output 6
Select Length(Concat('H ',' S')) A From Dual; --Output 6
I want to link to blocks using description as there is no relation , for example i have two tables with one field in common called description, and i want to link this field in two tables using like operator.
create table item ( item_code varchar2(12),item_name varchar2(30)); insert into item VALUES('A','HEA160'); insert into item VALUES('B','HEA180'); create table stk (sl_item varchar2(12),sl_desc varchar2(30),sl_qty number); insert into stk VALUES ('X','HEA160X1000',12); insert into stk VALUES ('X','HEA160X2000',4); insert into stk VALUES ('Y','HEA180X3000',10);
Suppose i click on item block item_desc with value on HEA160 all the items similar to that should appear in stk block like 'HEA160X1000' ,'HEA160X2000' , if i click on 'HEA180' on item then 'HEA180X3000' it should come.
I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%'.
the first table name has not started with 'T_', why is it appearing.
********************************************************************* SELECT 'Truncate table epic500.'||table_name FROM user_tables where table_name like 'T_%' order by table_name; ********************************************************************* output: Truncate table epic500.TEMP_ENC_DEL Truncate table epic500.T_ACCOMMODATION_CODE
Whey I try to run this query in TOAD I get an ORA-00920: invalid relational operator error. It's part of a 10g stored procedure. When I highlight it and run it it prompts me for the missing values and then the error pops up. The AND in line 4 is highlighted.
select CRIME_CLASSIFICATION_ID, crime_type, nvl(count(CRIME_CLASSIFICATION_ID),0) as CRIMECNTFrom vaps.vw_offenses where regexp_instr(valoc,to_char(location_id)) AND ( fromdate is null or offense_date between to_date(fromdate, 'mm/dd/yyyy') AND to_date(todate,'mm/dd/yyyy'))group by crime_classification_id, crime_type
I am using the below query to show the difference of data between two tables using minus operator.
teh result is correct, but is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.
I'm creating Tool Tip definitions for the Operators in the Filter on the Interactive Reports. I was looking for a definition for the 'Contains' operator and from what I've found this operator is used to do a text search and it returns a relevance score for every row selected.
I've also read that in order for that score to be determined that the column(s) need to be indexed with a CONTEXT index. Non of the columns in the tables are indexed with a CONTEXT index, however, when I put a value in the Expression box for a column I get a record returned.
If I run the same query in PL/SQL Developer like: SELECT <column> FROM <table> WHERE contains(<column>,<search text>,1) > 0;
I get an error that the column is not indexed, so how does it work in APEX?
if on an interactive report, you try and filter on a date column, then the "=" operator disappears.You get other (not so useful) operators like "in the next", "not in the next" ">", ">=" etc. - but no "=" to be found.
Which warehouses have pending orders for products, which are not in stock at the warehouse at the moment? Provide warehouse number, id of the product that is not in stock, number of orders issued for this product and total quantity ordered.
The tables I am using are
Warehouses: Name Type ---------------------------------------- W_ID NUMBER(38) CITY VARCHAR2(20) W_SIZE NUMBER(38)
Inventories: Name Type ----------------------------------------- P_ID NUMBER(38) W_ID NUMBER(38) QUANTITY NUMBER(38)
Orders: Name Type ----------------------------------------- ORD_ID NUMBER(38) SUPPLIER_ID NUMBER(38) ISSUING_EMP_ID NUMBER(38) ORDER_DATE DATE ORDER_STATUS CHAR(1)
This is my code so far:
select w.w_id, i.p_id, sum(decode(o.ord_id, ' ', i.p_id, 0)) Orders_issued, select sum(i.quantity) from inventories i orders o,
[code]...
but I get this error:
select sum(i.quantity) * ERROR at line 3: ORA-00936: missing expression
Just working with Unix for the first time and trying to understand this decode statement?
cursor l_cursor is select decode(type||'-'||to_char(line,'fm99999'), 'PACKAGE BODY-1','/'||chr(10), null) || decode(line,1,'create or replace ', '') || decode(type||'-'||to_char(line,'fm99999'), 'JAVA SOURCE-1','and compile ' || type || ' named "' || name || '"' || chr(10) || 'AS' || chr(10), '') || text text from user_source where upper(name) = upper(p_name) order by type, line;
im used to using PL SQL via oracle. but lately ive been doing a lot of mysql and PHP and i really need to use something similar to DECODE in my queries. or am i forced to do the checks via PHP.
