SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query
Dec 8, 2005
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
I need few clarifications regarding oracle wallet.
db version: 11.2.0.3.2 (Enterprise Edition)
We have a requirement to run shell scripts calling stored procedures for specific activities, which are run on database server. We do not want to store passwords in shell scripts and decided to use Secure External Password Store for hiding passwords instead of os authentication method. need few clarifications on the below.
1) Currently, we are creating oracle wallet entry on db server and making modifications in sqlnet.ora file accordingly. Is it good to use like this or we should do this only on a client machine? 2) Do we need any licensing to use this option? 3) Any knows issues with using oracle wallet? 4) Can we use orapki for creating oracle wallet instead of mkstore? 5) Any knows issues we face during startup and shutdown of db activities?
I'm just trying to see if there is another way of doing this query without using a UNION. The only way I can see is using a UNION but maybe I'm missing something or a way to do it without a UNION.
Result:
Select any customer within the user_states table who lives in "MO" Select any customer within the user_cars table who lives in "MO" and has a "White" car Select any customer within the user_plates table who lives in "MO" and has a plate of "A" or "B" Join to the user_names table to display the customers name
So, the result would be any customer who lives in "MO" who owns a "White" car and any customer who lives in "MO" who has a plate of "A" or "B".
I am new to SQL Oracle programming and have a question on a union query. I am trying to get results (example) for the following:
Org # Full_date Run_date 5 2/20/2012 2/20/2012 5 2/21/2012 2/21/2012 5 2/22/2012 null (there is not a record for this) 6 2/20/2012 2/20/2012 6 2/21/2012 null 6 2/22/2012 2/22/2012 7 2/20/2012 2/20/2012 7 2/21/2012 2/21/2012 7 2/22/2012 2/22/2012
The dw_time table would have the listing of all dates, (Full_date) and the dw_capacity_daily table would have the run_date. Here is my
select * from ( select distinct a.Organization_Nbr, d.full_date from CMBHS_DW.DW_ORGANIZATION a, cmbhs_DW.DW_Organization_Identifier b, cmbhs_DW.DW_Contract c, cmbhs_dw.dw_time d where a.ORGANIZATION_NBR = b.ORGANIZATION_NBR [code]....
I have one scenario in which i want to write the sql,but not able to write correct qry,
tables 1-emp 2-emp_hist 3-dept 4-dept_hist.
i want to retrieve data from the emp and hist able based on some conditions,but if data is not present in emp and dept tables.then fetch data from emp_hist and dept_hist.I have written below qry which is working fine,but my prob is i want to provide my emp_id only one place. else i have to change my java code.
select * from emp,dept where emp.emp_id=dept.dept_id and emp_id=5 union select * from emp_hist,dept_hist where emp_hist.emp_id=dept_hist.dept_id and emp_id=5
We have very large table having data more than 1000 millions rows. We divide this table into four physical tables say A, B, C and D. The physical horizontal partition of data of this original table is done based upon their business policy.
Each partitioned table has contained data of particular business entity. Further each table has partition and sub partitions based upon business rule.
We have to retrieve data from all these tables as follows:
select a1, a2, a3, a4, a5, a6 from A where < logical filter condition> union all select b1, b2, b3, b4, b5, b6 [code].....
We observed that above each query block execute in serial one after another and individual each query block capable to process data in parallel from respective table.
How does this above query able to execute each query block in parallel?
The DBAs have built 2 Materialized Views, which aggregates data in the fact tables at column C1 level
MAT_VIEW1 :SELECT C1_ID, SUM(SALE) SALES from FACTTABLE1 join LOOKUPTABLE on C1_ID MAT_VIEW2: SELECT C1_ID, SUM(SALE) SALE from FACTTABLE2 join LOOKUPTABLE on C1_ID
We are using an old BI tool that can ONLY generate Inline Views in these formats.
CASE1: select INL_VIEW.C1_ID ,LOOKUPTABLE.C1_NAME ,sum(SALE) SALE from (select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1)INL_VIEW join LOOKUPTABLE on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
CASE2: select INL_VIEW.C1_ID ,LOOKUPTABLE.C1_NAME ,sum(SALE) SALE from (select C1_ID, null C2_ID, C3_ID, SALE from FACTTABLE2)INL_VIEW join LOOKUPTABLE on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
CASE3: select INL_VIEW.C1_ID ,LOOKUPTABLE.C1_NAME ,sum(SALE) SALE from ( select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1 union all select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE2 )INL_VIEW join LOOKUPTABLE on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
Oracle 11g rewrites Case 1 and Case 2 to use the correct materialized views. But for case 3, it goes to the base fact tables 1 and 2. Is there a way to make oracle use the MVs even if there is a UNION ALL in the inline view? There is a 1:M Foreign Key relationship between LOOKUPTABLE.C1_ID and the 2 fact tables.
select a.empno,a.ename,a.job, B.DNAME from scott.emp a,scott.dept b where ( a.ename like 'S%' and a.deptno=b.deptno) union select a.empno,a.ename,a.job, 'aaa' AS DNAME from scott.emp a,scott.dept b where ( a.ename like 'S%' and a.job not like 'SALES%');
I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.
Query 1 :- select a.col1,a.col2,b.col3,sum(b.col4) from tab a, tab b where a.key1=b.key1 and a.key2=b.key2 group by a.col1,a.col2,b.col3
Query 2 :- select a.col1,a.col2,b.col3,sum(b.col6) from tab a, tab b where a.key1=b.key1 and a.key2=b.key2 group by a.col1,a.col2,b.col3,b.col5
I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.
Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.
The select statements inside the inline view returns huge number of row (like 50 million rows).
The whole query fails with time out.
How can I optimize this query further?
Is there a way to pass Oracle Hints so that Oracle uses indexes?
I am making a report in hrd regarding gross, deduction and there difference. I hv an attribute in the table as indicator whose value is addition and deduction. i want the sum of both in two diff column in a single rep. i want the report dept wise.
but i m getting fatal error at run time. which i don't understand.
I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.
Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.
The select statements inside the inline view returns huge number of row (like 50 million rows).
The whole query fails with time out.
Is there a way to pass Oracle Hints so that Oracle uses indexes?
I need to copy records from a working table to a history table. I have the following sql statement
insert into test.history (equip_ID, state, manufacturer, install_year, capacity, group_ID, Test_status) select (equip_ID, state, manufacturer, install_year, capacity, group_ID, Test_status from test.info_AP
Table test.history has one more field in it called test_year. I need to fill this field when I do the insert. Can't use an after update trigger as the field is currently set to not allow nulls.
Once I add an extra column to link the two tables the number of rows is reduced by one, however when I try to get this extra record I find none matching the same criteria that led to reducing the records.
SQL> SELECT count(*) 2 FROM N_CONTRACT NC, N_WITHDRAWAL_REQUEST NWR 3 WHERE NC.fk_temP_withdrawal_req_serial = NWR.SERIAL_NUMBER; COUNT(*) ---------- 2243 [code]...
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
In employees table I am having employee_name column data as follows
Aaron, Mrs. Jamie (Jamie)Aaron, Mrs. Jenette (Jenette)Abbott, Ms. Rachel (Rachel) Breton, Mr. Jean Britz, Mrs. Sarie (Sarie) --> Now, I want to display the employee name like "Mrs. Jamie" (with out Surname and with out bracket included text),-->
What is the trigger which should be used to check certain field value after posting the query.
Example:
I have executed the query and the records are fetched. There is one field I want to check if it is null then it should be enabled, else, keep it disabled.
create table FIELDS ( FIELD_NAME VARCHAR2(30) not null, PRG_FIELD NUMBER not null, LENGTH NUMBER );
with
INSERT INTO FIELDS VALUES('FIELD1', 1, 3); INSERT INTO FIELDS VALUES('FIELD2', 2, 3); INSERT INTO FIELDS VALUES('FIELD3', 3, 4); INSERT INTO FIELDS VALUES('FIELD4', 4, 2); INSERT INTO FIELDS VALUES('FIELD5', 5, 1);
I need to insert in a table:
create table STUFF ( FIELD_NAME VARCHAR2(30) not null, FSTART NUMBER not null, LENGTH NUMBER );
And the output I want is:
INSERT INTO STUFF VALUES('FIELD1',0,3); INSERT INTO STUFF VALUES('FIELD2',3,3); INSERT INTO STUFF VALUES('FIELD3',6,4); INSERT INTO STUFF VALUES('FIELD4',10,2); INSERT INTO STUFF VALUES('FIELD5',12,1);
So each field starts where the previous (ordered by PRG_FIELD asc) ends.
I think the query should use both lag and connect by but I haven't had any luck writing it. The problem is that all the examples I've seen around, using connect by prior, utilize 2 fields with different names, es connect by prior emp_id = mgr_id. Instead I should do something like connect by prior prg_field = prg_field-1 but that doesn't seem to work.
PS: I don't necessarily need to do this, I have a guy manually writing the inserts, this is just an exercise I would like to figure out
If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
I have a report that comes from SQL Query (updateable report). I'm using the apex_item.text and apex_item.hidden on fields. I'm using a button to submit and after submit process to add some logic that I need.
There could be 1 - 10 records in the report. There is only 1 field that is needed to enter a value, but the value of this field determines the value of another field. I think that I can do this with a submit button and an after submit process where I loop through all the records. I think I have this handled.
This is the question
When the value of that field is changed then the value of another field in the same row changes immediately. All the examples I've seen so far are for a single record and that doesn't work for us.
I guess this is a MRU process but I haven't seen an example where a dynamic action is possible on a Multi Row Update.
I am having a problem with a popup lov. When I click on the "popup icon" I can select a supplier and it is stored in the field. But when I leave the form and return later, it shows the return value instead of the display value. For example: you select "supplier A" from the popup list with ID 12. "Supplier A" is shown in the text field, when you save the form, it stores ID 12 in it. But when you return to the form, it fetches ID 12 and shows "12" in the text field instead of "Supplier A".
I tried setting the "source" of the popup lov field as an SQL query, but that didn't work for me.
I was looking for application column name corresponding to "Draft Invoice Number" ra_interface_lines_all table.
I tried the below.
/* Get descriptive_flexfield_name for the application table name*/ SELECT * FROM FND_DESCRIPTIVE_FLEXS_VL WHERE application_id = 222 AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all');
/* Get the application column name and end user column name*/ SELECT * FROM FND_DESCR_FLEX_COLUMN_USAGES WHERE application_id = 222 AND descriptive_flexfield_name = 'RA_INTERFACE_LINES';
There are many DESCRIPTIVE_FLEX_CONTEXT_CODEs obtained. I could finally trace out that draft invoice number corresponds to INTERFACE_LINE_ATTRIBUTE2. How can I know what DESCRIPTIVE_FLEX_CONTEXT_CODE should I look for?
I want to build a single query to fetch the application column name and flex field name for a specific table .
Here i am displaying the multiplication tables by given number...the problem is the tables are displaying one after another...i need to print it by side by side like..
How do I update Table2(idno) with values of Table1(idno)? By the way there are other columns which i do not want to be touched. This is just to create a match parent-child table test data.
Example:
Table1
idno === 100 101 102 103
Table2
idno === any no any no any no any no
I want all idno values in Table2 to be identical with Table1. It can be any order as long as the value is 1 to 1. I just want it populated for test data purposes.
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal 023 | John | 6000 143 | Tom | 9000 876 | Chi | 4000 987 | Alen | 7800
