I am doing a Gross Profit percentage. I want to display zero if the GP% is have negative value.
for example
If GP% shows -75 I want to display as 0.
trunc((sum(total)-(sum(DECODE(po_pur,'',0,PO_PUR))+sum(DECODE(pl_pur,'',0,PL_PUR))+SUM(parts))) / SUM(DECODE(TOTAL,0,1,TOTAL))*100,2) "GP%"
I have one column table with negative and positive values and want to display positive and negative values in different columns thru a single query.
Column
1
-4
3
-6
output should be like
A B
1 -4
3 -6
I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
I found the data in DB with negative time as below
Quote:
1 SELECT
2 c.time
3 FROM partition c
4 WHERE
5* c.time < to_date('0001-01-01','YYYY-MM-DD')
SQL> /
DEACTIVATION_TIME
------------------------------------------------------------------------
17-JUN-08 04.06.22.893 PM
The data type for c.time is Timestamp.
When I use JDBC to retrieve the data and convert it to mill seconds, and it also shows as negative time. My question is the retrieved time doesn't show the negative date, but obvious it is a BC date and earlier than 0001-01-01.
1. How can we detect/show the date to show correctly for the BC date without checking c.time < to_date('0001-01-01','YYYY-MM-DD')? (I am wondering if there is format configuration to show the time like 17-JUN-08 04.06.22.893 PM BC, or something to show the time is negative )
2.how could we fix it negative time to positive ?
I am working on a script that is reading from a numeric column converting it to a character string where the decimal is dropped (assumed) and the value is padded with leading zeros to fill the specified lengh. Using to_char(payment_amt,'FM0000000V00') is working fine for non-negative values. When the value of the field is a negative number the output includes a leading - (i.e., -000002458).
For the negative numbers I need to convert them to a 'Signed EBCDIC' value, where the last digit is converted to a specified character (in the above example it would be changing the 8 to a Q) and also replace the - with a zero, resulting in 00000245Q.
What I am doing is generating a fixed-length record with values in a specific location. What I'm getting now looks like this:
AAAA 000002358 BBBBBBB
CCCC -000002458 DDDDDDD
EEEE 000024555 FFFFFFF
And what I need it to look like is:
AAAA 000002358 BBBBBBB
CCCC 00000245Q DDDDDDD
EEEE 000024555 FFFFFFF
can you to display a field with negative value
S -- format mask display both sing + and -
MI -- format mask display negative sign at end
PR --Returns negative value in <angle brackets>. and Returns positive value with a leading and trailing blank.
I want the output should display only leading negative if the value is negative. I have used S99990.99 as format Mask. but need output should display only leading negative if the value is negative.
i am doing a data transformation using the following function
FLOOR(ONSET_TPQ-VAX_DATE)
Where ONSET_TPQ and VAX_DATE are both dates. Now in a situation where the ONSET_TPQ precedes VAX_DATE I want the result to be null for those records. As an example if ONSET_TPQ= 2000 and VAX_DATE=2010
I want the result to be NULL as I dont want negative values. Any exact SQl syntax on how to do that. DO i write a case statement?
We are getting Negative values of Library cache hit ratio in AWR Report of 11g(11.2.0.3) with Solaris[tm] OE (64-bit). Why it shows negative value.
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.87 Redo NoWait %: 99.99
Buffer Hit %: 92.17 In-memory Sort %: 100.00
Library Hit %: -3,321.23 Soft Parse %: 81.95
Execute to Parse %: 92.88 Latch Hit %: 95.11
Parse CPU to Parse Elapsd %: 87.25 % Non-Parse CPU: 81.39
This query
SELECT
SCRATTR_ATTR_CODE
from scrattr_TEST,scbcrse_TEST
where
SUBSTR(scbcrse_subj_code,1,3) = SUBSTR(scrattr_subj_code,1,3)
and SUBSTR(scbcrse_crse_numb,1,4) = SUBSTR(scrattr_crse_numb,1,4)Returns this
SCRATTR_ATTR_CODE
A
INS
MCSR
How I can make to return someting like A INS MCSR in one row there is a row for every code
hERE is some code to create the tables and insert the data
CREATE TABLE SCRATTR_test
(
SCRATTR_SUBJ_CODE VARCHAR2(4 CHAR) NOT NULL,
SCRATTR_CRSE_NUMB VARCHAR2(5 CHAR) NOT NULL,
SCRATTR_EFF_TERM VARCHAR2(6 CHAR) NOT NULL,
SCRATTR_ATTR_CODE VARCHAR2(4 CHAR)
[Code]....
a sample query to display second MAX value from the oracle database
View 1 Replies View RelatedBelow is returning rows in single column.I need to display in two different columns
SELECT * FROM TABLE(PLUSER.SPLIT('a,b,c'))
union all
SELECT * FROM TABLE(PLUSER.SPLIT('1,2,3'))
this returning result like
a
b
c
1
2
3
but need abc in one column and 1,2 3 in one column
a 1
b 2
c 3
i was tried like
select * from TABLE(PLUSER.SPLIT('1,2,3')),(select * from TABLE(PLUSER.SPLIT('a,b,c')));
but it's giving cartesion result like below
1a
1b
1c
2a
2b
2c
3a
3b
3c
I have a requirement where I want to display amount what I have billed to my client and then how I received my amounts from that Bill. For eg.lets say there is bill no 001 amounting 10000 and after some days I received a part payment cheque/cash from client worth 2000 then he gave second part 2000 and then finally I received 2000. Still 4000 is remaining to be received.So in tabular format I want to display data which will have 3 records of 2000 and one record of balance 4000.
In attached file i have given few examples, where highlighted area is what i want to derive. There are formula's in Collection date, delay by and Int calc. Basically highlighted lines are the ones which should be shown in query result. And data shown above that is what i have right now.
I have a procedure in this i want add a column to display my procedure is
set serveroutput on;
create or replace PROCEDURE IdentifySparesInDSIGNALTables
AS
V_SQL VARCHAR2(1024);
cnt1 number;
[code]...
i wnat to add dlstation in this procedure to display output...
I have a requirement where i need to display like
Consider EMPLOYEES table and If an employee 'A' joined in Jan month then he should come under JAN, if employee B and C joined in MARCH month than both has to come under MAR and so on..
OUTPUT:
JAN FEB MAR APR MAY JUNE JULY AUG SEP OCT NOV DEC
A B D E
C
is this possible.????
I want to display '18-MAR-13' as 20130318.
View 9 Replies View RelatedI need to return the stored result in a record by ref cursor. In the below code I am displaying the result through DBMS_OUTPUT.PUT_LINE but i need to do it through ref cursor to display the result.
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
[code].....
How to display row value of each class as a column value
IDNAMECLASSHOURSSCHOOLNAME
1AllenMaths12school1
2SamMaths12school1
4PamMaths10school1
3SamComp10school1
9SmithComp15school1
7SmithPhy20school1
5PamPhy12school1
[code]....
I wanted output like below for School1
NamemathsCompPhyAccountsBusiness
Allen120000
Sam1210000
Pam10012150
Smith01520120
Adams000030
I wanted output like below for School2
NameCompAccountsBusiness
Anthony2000
Chris102020
I wanted output like below for School3
NamemathsAccounts
Angelo2015
Robert1515
how to write a single query, Also i may add /delete school/class anytime. Query should support mesans everything dynamically should get the data.Even stored procedure will work for me.
i have to display the 10 most costly missions. but if the 11th record has the same cost then it needs to display 11 records instead of 10. how can i do this?
View 5 Replies View Relatedi have a table displaying location, cost, inner_num and rownum ordered by the descending order of the cost.
the question is: display locations of the highest 10 costs. if the cost of 11th row is the same as 11th, then display 11 rows.
query in order to display a hierarchy list. Example:
Database has three tables
Heading --> id, status, order, title
Sub-heading --> id, status, order, title, heading_id
Item --> id, status, order, title, heading_id, sub-heading_id, actual_content
order = the sequence in which they are to be displayed a sub-heading's heading_id refers to the parent heading, likewise an item's sub-heading_id refers to the parent sub-heading.
I assumed there was no need to track child content.
Now, the output format should be something like this, where the sub-headings are not repeated, nor are the items. Sort of like chapters in a book, things in a catalogue, or even topics in a forum.
Heading 1
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Heading 2
Sub-heading 1
Item 1
Item 2
Item 3
Heading 3
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Sub-heading 3
Item 1
Item 2
Item 3
Where I'm getting hung up is how to query the data so it's quick, efficient, clean and easy to read and maintain. I've only gotten to listing the sub-headings and already the query looks convoluted. So, how should I approach this? Should it be done all in one query, and always checking against the results to know when to print a new heading or sub heading. Or should I run multiple queries and save the results to a variable, and then run sub-queries off that? Is there a better way to do this, am I going about it the hard way or am I in the right direction and just haven't gone far enough with it yet?
I've tried using Select * from ALL_TABLES but this does not return me the column info for each table.
How do I get an output of all tables in a database along with column (e.g datatypes etc)?
I'm using TOAD v8.6.1.
I have two columns which I need to add together then devide by 60 in order to display that time in minutes. the problem I am facing is that at times I get numbers above 60 and my client doesn't want to see numbers above 60.
i.e (col1 + col 2)/60 = 34.87
what I need to do is make sure that when the number reaches 60 it moves on to 35.
I had a database at microsoft access but i had move it to the oracle database.
When i check the datatype at oracle, date column is at DATE datatype. but it only display date. like in oracle. it display:
oracle_date.PNG ( 74.61K )
Number of downloads: 4
But when i link it to microsft access. like i will control the oracle from access. the result is at below:
access_date.PNG ( 136.11K )
Number of downloads: 3
So, my problem is how to display the date and time fully in oracle?
I have a following table,
create table test ( col1 date,col2 number);
Data:
col1 Col2
----------
NULL NULL
select * from test where col2=123 will give me null because It doesnt have any values, But can we display some harcoded value when I do not have anything ?
how to write a select query to display a number column value in between single quotes. Ex:i have a emp table in that sal column is a number type.
Ex:
select sal from emp;
sal
----
1234
231
3456
3211
23445I want to display the same above result in b/w single quotes.
ex:
sal
----
'1234'
'231'
'3456'
'3211'
'23445'
for this i need to write a query,
1.Requirement....
deptno ename
++++++++++++++++++++++++++++++
10 a,d,e,g
20 b,c,j
30 k,l,o
2.How to swap the column values ? ex. col2 into col1 and col1 into col2
3.What is the major Diff. b/w Right outer join & Left outer join ?
I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).
What do I need to do to display 'data' if 'data2' contains rows?
WITH data
AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation,
b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,
[Code]....
I want to display a specified image if not form_success!
declare
image_path varchar2(80) := 'C:R_ORACLEPHOTOS';
photo_name varchar2(80);
begin
:system.message_level := '25';
[code].......
My form failed to display photo (data type long row).
scenario a)
If photo in black and white with
width : 472 pixels
height: 590 pixels
horizontal resolution : 300dpi
vertical resolution : 300dpi
bit depth : 8
size file : 28KB
scenario b)
If photo in color with
width : 124 pixels
height: 155 pixels
horizontal resolution : 75dpi
vertical resolution : 75dpi
bit depth : 24
size file : 14KB
Above 2 scenario:
My form 6 program can successful display the photo for scenario a , while for scenario b, my form 6 program failed to display the photo.
I have a form in which the fields are not database items i want to display the next record, i know i can use the next_record for the same but it does not work since it is not the database item, is there any way when the item is not the database item and i can see the next record. I use the cursor for loop but since it fecthes all the records in one go it cannot display one record at the time in the form..
View 3 Replies View Related