SQL & PL/SQL :: Writing A Query To Multiply Columns
Nov 26, 2012
I am writing a query and I am trying to multiply some of the columns with *1.50 and 0.75.
The columns I am trying to multiply are coke_rebate.volumecsd and coke_rebate.volumencb. I am getting an error in oracle sql command from web "ORA-00937: not a single-group group function"
I have the task that I have to determine the number of parts that need to be produced based on the number of products sold for the day (each product consists of many parts).
I am using SQL 11g Express.
The report would look something like this:
{OrderDate PartID PartDesc NumOfParts(Total for that day) 10-24-2011 2001 12" X 12" Solid Shelf 108 10-24-2011 2003 12" X 24" Solid Shelf 32 10-24-2011 3001 96" Side Panel 50
[code].......
My issue is, I can't get the equation right to produce the total number of parts. I think I need to multiply ProductPart.NumOfParts by SUM(CustOrder.Qty) Group by CustOrder.SKU.
Below I have what the calculations should look like
Have 2 small tasks for regexp..where we need to extract numbers from the text & multiply & get the result.
Input field varchar2 : 5x a day x 10 days Output : 5 * 10 = 50 Select regexp_replace('5x a day x 10 days', '[^[:digit:]]' from dual;
The code extracts numbers ..but doesn't multiply & give the result.Have one more scenario as well.
Input field varchar2 : take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days Output : 2 * 800 * 4 * 6 * 10 = 384000 Select regexp_replace('take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days', '[^[:digit:]]' from dual;
For the above code, if it's hours ..we need to convert into day by multiplying with the required factor to make it a day.
Difference in hours is supposed to be 35 but it comes as 34.When i manually find the difference in days and multiply by 24 it comes as 34.999999999 and floor converts it as 34.. But when i execute the difference between dates alone and convert to 24 it shows as 35. So when i apply floor to this, it should be 35 instead of 34 right?
For the following query, difference of the dates in minutes is 2100.. so mod(2100,60) should give 0 but it's giving 60.. Separately if if put mod(2100,60) it is giving as 0 only.
I'm a beginner in PL/SQL ! --> " get_sql_metadata(p_query IN Varchar2) RETURN VARCHAR2;" I have to display the names and columns of the query by using the package dbms_sql and how can I know how many columns will have my query.
I have one issue with the converting columns into rows.
Employee table has manager , additional manager and some other columns. If the employee has additional manager, then i need those employee record with 2 rows : 1st row - manager with other data , 2nd row - additonal manager with other data .
Orginal data is like this:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ADDTIONAL_MGR ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7698
[code]....
I have tried this , using UNION ALL method, But it is taking more time.
SQL> ED Wrote file afiedt.buf 1 SELECT DECODE (DUMMYTAB.NUM,'1',MGR, '2',addtional_mgr) MANAGER, empno, ename, job, hiredate, sal, comm, deptno 2 FROM emp E, (SELECT '1' NUM FROM DUAL UNION ALL SELECT '2' NUM FROM DUAL) DUMMYTAB 3* WHERE addtional_mgr IS NOT NULL SQL> /
CREATE TABLE group_device ( group_id NUMBER (8) NOT NULL , member_id NUMBER (4) NOT NULL , device_id NUMBER (10) NOT NULL , install_date DATE NOT NULL , remove_date DATE );
ID Status description Tracking ID 1 Strat Frog 1 2 Start Dog 2 3 Process Frog 1 4 Completed Dog 2 5 Start Rabbit 3 6 Error Frog 1 7 Stop Rabbit 3 8 Start Elephant 4 9 process Elephant 4 10 Start Human 5 11 Stop Human 5 12 Start Butterfly 6 13 completed Butterfly 6 14 start lion 7 15 error lion 8 16 complted lion 8 17 start tiger 9 18 error tiger 9
select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss') And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));
Result should be " Frog and Elephant and tiger"
Start of every record(descrpition --status is Start) End of every record ( status is stop or done or completed) status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )
I have seen lots of examples of using PIVOT to return an sql query where the rows have been converted to columns. All the examples I can find require you to have two columns with multiple rows, and also require you to know the data which is in one of columns to make titles for the new columns.
I need something a little different...I have a query using UNION which goes something along the lines of...
SELECT [get data from one place] UNION SELECT [get data from another place] UNION SELECT [get data from another place]
this returns the following... a_val ----- 100 200 300
query to return something like this... a_val1 a_val2 a_val3 ------ ------ ------ 100 200 300
The names of the columns don't really matter. And I dont want to create a new table in the database just for the result.
I am new to the Toad World � ---- SELECT TABLE1.PRODUCTID, TABLE1.TYPE , TABLE1.PRODUCT, TABLE2.#SOLD FROM TR.TABLE1 INNER JOIN PR.TABLE2 ON (TR. PRODUCTID = PRODUCTID )
When we merge the above two table..The query result will look like this:Product IDProductType#Sold 1FruitBanana2 2FoodRice2 3FoodSugar6 4FruitOrange8
[code]...
Now I want manipulate the query further and create three new columns from �#Sold�
Is it Possible to twist the below code to produce the above SELECT TABLE1.PRODUCTID, TABLE1.TYPE , TABLE1.PRODUCT, TABLE2.#SOLD FROM TR.TABLE1 INNER JOIN PR.TABLE2 ON (TR. PRODUCTID = PRODUCTID )
SELECT LOC_CD, TO_CHAR(DT,'fmMon RRRR'), SUM(QTY) , GROUPING_ID(LOC_CD) FROM WIP WHERE LOC_CD IN (1,2,3,4) GROUP BY ROLLUP(LOC_CD), TO_CHAR(DT,'fmMon RRRR'),TRUNC(DT,'MM') ORDER BY TRUNC(DT,'MM'),LOC_CD
This query result attached
The red coln is the total I want to place it in row-wise
Date loc_1 loc_2 loc_3 loc_4 Total May 2012 4,554 6,644 11,198 June 2012 4,986 5,838 777 11,601 22,799
I have limited permissions and am unable to create temp tables.So I would like to use a cursor to "create" a table of sorts then access/query it. But this "table"/cursor would have no column names so how do I refer to the columns? Is there a way to refer to a column by column number rather than column name in a query:
select column1 from tablename where column2 = 'abc'?
Is there a way in a query/update/insert to refer to a column by column number rather than column name?
declare cursor c1 is select 'abc', '8-Apr-2013', pk_id from EMPLOYEE where pk_id = '153' UNION select '1xyz', '4-10-2013', pk_id from EMPLOYEE where pk_id = '154' c1_val number;
Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.
I want to pass Number of columns dynamically to a query. I got success in SQL.
SQL> select &column_list from emp; Enter value for column_list: empno,ename,sal
EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300
14 rows selected.
But the same i need to achieve in pl/sql. I try with the Ref cursor, but not succeeded.
When I create a data block based on a table using Data Block Wizard, the block's Query Data Source Columns property is automatically populated with column definition entries corresponding to the columns of the base table.
I tried making changes to these entries, for example by changing the data types to wrong data types or even deleting them, and I found that those changes had no effect on the block at all. The form was still working as I wanted.
What is exactly the role of the block's Query Data Source Columns property.
The F1 key help says "The Query Data Source Columns property is valid only when the Query Data Source Type property is set to Table, Sub-query, or Procedure". So, explain in each context of Query Data Source Type.
INSERT INTO CFL VALUES (11, 1, 'JAN-10', 10); INSERT INTO CFL VALUES (21, 1, 'FEB-10', 20); INSERT INTO CFL VALUES (31, 1, 'MAR-10', 10); and so on (12 records for a year with same quota_id, e.g. here it is 1) [code].......
Also this column values period is dynamic.. it can be for any year.
The select will return values like
Select per_id, PERIOD, amount from cfl where quota_id = 1
Basically 12 rows will be the output: per_id period amount 1 JAN-10 10 1 FEB-10 20 1 MAR-10 10 ..............and so on
MISSING_DATES EMPNO ---------------------- ---------- 09-SEP-12 TO 11-SEP-12 7499 23-SEP-12 TO 26-SEP-12 7499 01-sep-12 TO 30-SEP-12 7521 01-sep-12 TO 30-SEP-12 7788
I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?
So far I have tried this:
SELECT (CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1, (CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1, (CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2, (CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2, (CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3, (CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3 FROM <TABLE_NAME>
I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:
SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS
If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....
DECLARE stmt_str varchar2(200); cur_hdl int; rows_processed int; FIRST_NAME varchar2(200); LAST_NAME varchar2(200); BEGIN cur_hdl := dbms_sql.open_cursor; -- open cursor stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS'; [code]....
However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.
I am trying to search a word which starts with 'FRA' in any columns and any tables.
I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.
I ahve looked into
Re: How to search in all rows and all columns? Re: SQL Search Query?
but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view
select distinct substr (table_name, 1, 14) "Table", substr (t.column_value.getstringval (), 1, 50) "Column/Value" from all_cons_columns, table (xmlsequence (dbms_xmlgen.getxmltype ('select ' || column_name || ' from ' || table_name || ' where upper(' || column_name || ') like upper(''%' || 'fra' || '%'')' ).extract ('ROWSET/ROW/*') ) ) t order by "Table";
running teh above query got me thsi error:
ORA-19202: Error occurred in XML processing ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_XMLGEN", line 288 ORA-06512: at line 1 19202. 00000 - "Error occurred in XML processing%s" *Cause: An error occurred when processing the XML function *Action: Check the given error message and fix the appropriate problem
I had never done this before, basically I need to get the result of the SQL query which had three columns and pass into thesend _mail proc, should I build a variable to hold the 3 value and send mail from it?
