create table abc(
id number,
num varchar2(10),
name varchar2(10),
ref varchar2(10)
);
insert into abc values(1,'sss','cd','111');
insert into abc values(1,'sba','other','111');
insert into abc values(1,'svss','cd','111');
[Code]...
Quote:
The output:
ID CD OTHER
1svss,ssh,jhs,ssssba
2hvg,sss ggsss,hvgsss
I want the output not in coma seperated but in '||' seperated so I used translate function to replace coma by '||', but the problem I am facing that for column where values are enterd allready in coma seperated for eg:hvg,sss for ID 2, then its difficult to identify.
so I want the output in this format:
for eg if the values are entered: 'aaa', 'bbb,ccc'
I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.
I have copied the code used in
select stud_id, 2006, 2007, 2008, 2009 from ( select stud_id, avg(case when year=2006 then ((present/poss)*100) else null end) 2006, avg(case when year=2007 then ((present/poss)*100) else null end) 2007, avg(case when year=2008 then ((present/poss)*100) else null end) 2008, avg(case when year=2009 then ((present/poss)*100) else null end) 2009 from attendance.vw_all_attendance_perc group by stud_id );
I'm having truble by querying a column in Oracle 10g. This is my situation I wrote a standard Oracle Query to show only the domain name of user's registration. The column's alias is called MAILS so far so good, the main problem is when I tried to shortener the result to show only Gmail, Hotmail and Yahoo.
Also I had the same issue when I tried to use GROUP BY the field "MAILS", but when I use the order by clause with that field it run perfectly. I think a test case is not necesarry because there are common mails address.
SELECT SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1, INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) "MAILS", COUNT(SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1, INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))) "TOTAL" FROM USER_REGISTRATION WHERE TRUNC(FECHAALTA) BETWEEN TRUNC(TO_DATE('01/01/2012','DD/MM/YYYY')) AND TRUNC(TO_DATE('31/12/2012','DD/MM/YYYY'))
GROUP BY SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1, INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) ORDER BY MAILS DESC;
I have a table which has two partitions (by range): first_half and second_half based on a column "INSERT_DAY".
I need to add sub partitions "SUCCESS" and "NONSUCCESS" based on the values of another column "STATUS" (subpartition by list) i.e. I need to transform my range partition to composite (range-list) partition. I do not wish to drop existing tables or partitions. What is the ALTER query for this?
As far as SQL is concerned, I am using oracle 9i and oracle sql *plus.
1. This is a table 'spj' SID PID JID QTY ---------- ---------- ---------- ---------- 1 3 2 5 1 2 2 12 1 7 1 10 2 5 3 5
[code]...
I wish to count all the qty for each pid.This is what I have: SQL> select pid,sum(qty) from spj group by pid; PID SUM(QTY) ---------- ---------- 1 5 2 12 3 29 4 10 5 5 6 6 7 10
and it seems to work fine. But I now I want to display another column in the table called 'pname' which is a primary key in another table 'p'. 'spj.pid' is the foreign key.
2. Another query I can't perform needs the table s SID SNAME STATUS CITY ---------- -------------------- -------------------- ------------------- 1 s1 y asansol 2 s2 y durgapur 3 s3 y durgapur 4 s4 y asansol 5 s5 y kolkata 6 s6 y asansol 7 s7 y tarakeshwar
the question says:
Quote: List supplier names(snames) who supply at least all parts supplied by supplier(sname) S2.
I am running a query in Oracle that uses multiple joins. I want to return as a part of the select statement a column that contains the row number of the result.
Here is an example of my query;
SELECT * FROM A_TABLE INNER JOIN THEADM.A_TABLE_EXTENSION ON THEADM.A_TABLE_EXTENSION.OBJID = A_TABLE.OBJID INNER JOIN THEADM.A_TABLE_ENV ON THEADM.A_TABLE_ENV.A_TABLE_ENV2A_TABLE = A_TABLE.OBJID INNER JOIN THEADM.A_TABLE_LOG ON THEADM.A_TABLE_LOG.A_TABLE_LOG2A_TABLE = A_TABLE.OBJID WHERE CREATION_TIME > '01AUG10'
I want the resulting query (after the final inner join) to contain a column named 'Column' (or similar) that contains the number of the row.
I cannot re-sort the result set - there is no criteria by which to do this. (which is why I want the row number so badly... so that I can sort the result set however I want later and return to the original using this row number) The way that it is returned from the system is the way that I need it so using OVER ORDER BY won't work for me here. I also can only read the database, I cannot INSERT or add any tables.
I would like to know how can i add a column in existing report. I try to change SQL by doing this it does add that new column into group but when i try to run report i can not see that changes . then i try to do some changes in paper layout but i am not able to bring the changes in out put. i am new to this FORMS Reports . do i have to create a group again i am how can i change the layout in that report.
I have a table A with a column B timestamp(6). The tables contains around 300000 rows..I have created index 'idx' on the column B.When i compare column 'B' with systimestamp, it does not use the index, whereas if i compare 'B' with sysdate it uses the index.
Eg : select count(*) from a where b<=sysdate;
The above used the index 'idx' and executed in 1 second
select count(*) from a where b<=systimestamp;
The above does not use the index and executed in 19 seconds.
I have a table called cust_file, his table consists of a lot of columns (one of these columns called cus_tax) and have a lot of data,I use oracle 11g, I want to change the default value of the column cus_tax to be equal 1, I wrote
ALTER TABLE cust_file MODIFY(cus_tax DEFAULT 1); table alteredbut
after I inserted new data to test the operation, I found that the new record has a value
= null for the column cus_taxthen
I tested using the following query select
data_default from all_tab_columns where table_name='CUST_FILE' and column_name='CUS_TAX'; no rows selected...
I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.
I want to imopert excel data by coloumn mapping to oracle table using forms 6i". for this I've created a form which is having following details.
1. path, file name text field and button called browse. when the form load. click on browse botton, and user will be asked to go to specific drive and select the excel file. The file path is stored in path text field and file name is displayed on file name text field.
2. After doing this I've porvide a button called column. after selecting the file click on column button. Once the button is pressed all column heading of the selected excel file get displayed on the text fields which i've provide on the form.
3. In frot of each column headig field I've provide list box which contains database table column heading. Now user have to select table column according to his excel column. (i.e I am asking user to map his excel column with oracle table column.)
4. Once the maping from user is done, I've provide button called process. once the button is pressed all the data from excel is to get insert in to database colmum.
what i've achive - - User is able to select file path and file name get displayed. - Columns heding of excel get displayed on the form. problem area -In the process button code column heading get change dynamically but records i.e actual data does not. -I am able to insert records into table, but only when excel column heading sequence is same as database table column.
excel column = table column ----------------------------------------------------------------- route = route date = del_date time = del_time id = tran_id
In above situation record get insert into table. But when only excel column sequence change that time data is not inserted into table. If required i can upload my form and sample excel sheet also provide table script.
When we try to query a column from a table the output is shown as "?????". Seems the data in that column is stored in a different character set, which is not recognised while displaying the output. The column size is 35byte.
The nls parameter table shows the character set as "we8mswin1252".
I want to provide excel sheet with column header only to user, with the push button of oracle forms.(i.e user suppose to get the excel sheet when he press the button on the form).
how should i populate table column heading in list items of forms? I've create lov to select the column, then i have 10 separate list items. once i select the table from lov then list item should get populated with selected table column.
I want to export the oracle data into an excel sheet. I have written the code by using UTL_FILE package. but i am getting the output as shown in the screen shot(without formatting the column size as the width of the data it has). But I want the output column width to be set according to the size of the data automatically.
previously i set null constraint to the column and creating some rows and need to change new entering values as not null constraint to the column in oracle without disturbing the old records. how can I do that.
I am having issue with IMPDP on ORACLE VIRTUAL COLUMNS.I am having following table with Virtual column defined with Not null. Expdp is fine without any issue.
DDL : ------ CREATE TABLE alert_hist ( alertky INTEGER NOT NULL, alertcreatedttm TIMESTAMP(6) DEFAULT systimestamp NOT NULL, alertcreatedt DATE GENERATED ALWAYS AS (To_date(Trunc("alertcreatedttm"))) VIRTUAL NOT NULL
When I do the import (IMPDP) it got failed with the following error.
. . imported "TESTSCHEMA"."VALART" 359.1 KB 4536 rows ORA-31693: Table data object "TESTSCHEMA"."ALERT_HIST" failed to load/unload and is being skipped due to error: ORA-39097: Data Pump job encountered unexpected error -1
After that I dropped the Virtual Not null column and recreated that column with Nullable.
DDL : ----- alter table alert_hist drop column alertcreatedt; alter table alert_hist add alertcreatedt DATE GENERATED ALWAYS AS (To_date(Trunc("alertcreatedttm"))) VIRTUAL;
After that I took the expdp and impdp , it went fine with out any issue.
I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.
HERE
select a.* from DOC a , FOLDER B where a.NUMBER= B.INCIDENT_ID and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
<Column name> value 1 value 2 value 3 . . . value n
Since the column is small it can fit in a page more then one time. I know how to make it print more then once: i switch the repeating frame to print down and across and i modify the frame that contains it so the horizontal elasticity is variable.
After these changes my report looks like this: <Column name> value 1-----------------------value n+1-----------------------value m+1 value 2-----------------------value n+2-----------------------value m+2 value 3-----------------------value n+3-----------------------... .------------------------------- . .------------------------------- . .------------------------------- . value n-----------------------value m
What i want is my report to look like this:
<Column name>-------<Column name>------------<Column name> value 1-----------------------value n+1-----------------------value m+1 value 2-----------------------value n+2-----------------------value m+2 value 3-----------------------value n+3-----------------------... .------------------------------- . .------------------------------- . .------------------------------- . value n-----------------------value m
I have written the code for sql loader, I have given termination of file is | , i have given five columns name in sequence...My Requirement is that i want to mapped 100th column from Csv file after 5th column how to do it.
OPTIONS (ERRORS=100000000) LOAD DATA CHARACTERSET UTF8 INTO TABLE "TARGET_STG_AMC" TRUNCATE [code]....
We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report
<code>select * from( select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site, case when count(*) < 31 then '*' else round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b where Overall_Satisfaction is not null and year_dt = 2012
[Code]....
The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.
Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)
ie Overall Satisfaction JAN FEB MAR APR MAY ... Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240... Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240... etc.
So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
