I have to display a record in column format. I have tried doing it using COUNT & DECODE as in below script.
SELECT COUNT (DECODE (a1.application_status, 21, a1.ppid, NULL)) cnt_app21, COUNT (DECODE (a1.application_status, 22, a1.ppid, NULL)) cnt_app22 FROM (SELECT a.application_status application_status, a.ppid ppid FROM vp_recon_upload_dtls a, vp_ppid_upload_info b
[Code]...
This gives output as:-
cnt_app21 cnt_app21 3 1
The second query is little different from above as it has 2 more tables joined and more conditions than first query.
SELECT SUM (DECODE (a2.application_status, 21, a2.rate_amount, NULL)) sum_app21, SUM (DECODE (a2.application_status, 22, a2.rate_amount, NULL)) sum_app22 FROM (SELECT a.application_status application_status, d.rate_amount rate_amount FROM vp_recon_upload_dtls a,
[Code]....
Second query output is :-
SUM_APP21 SUM_APP21 222 111
My requirement is to display all the four values in one row as:-
I'm trying to sort out my coloumn headers and make things a bit neater using the code....
column name format A9 heading 'Emp|Name' column birthdate format A9 heading 'Birth|Date' select name, birthdate from Employee where ....;
It seems to display all the results in double space if i use more than one 'column' statement though? One statement works fine, what've i gotta change?
I am using SQL Plus v9.2.0.1.0 and am having trouble with the column widths. By way of example:
Let's say the column is a varchar(2) and the column name is called V9ABC12345. When I see the result of my query I only get the heading name as V9 ie the maximum width of 2 characters. The table has over 100 columns and I know I can specify the column width using the format command but I am using the select * from table_name command
How do I change my formatting to include the column heading by default?
I have job, working hours, employee id, employee name in test_emp table. The job name and employees are not fixed in this table and it varies from project to project. We don't know how many employees are there and needs to be fetching on runtime.
select to_char(exp_Date,'Mon-YYYY') dt, count(*) from exp_main where exp_type like 'Income%Photo%' group by to_char(exp_Date,'Mon-YYYY') order by exp_date
When I run this I get :not a GROUP BY expression
If I remove the order by, it works fine.
Is there a way to order by since the output of the query is character-sorted.
I did search online most have suggested to use order by column_name. But does not work for me.
I have a report and require the output in xls/xlsx (Excel) format. Currently following properties under System Parameters in Report Builder 6i have been made
Desname = The path for e.g: \C:sz-serverc$ihelp_workingxls pt_sample_report.xls Desformat = Delimited Destype = File
The rest parameters has default settings I have not changed any of the rest User parameter.The report comes up in excel format but totally in distorted manner. i.e; All the column names of report are displayed first in vertical format followed by actual rows for the report.
The requirement is like this
[Select all] [Show/ hide] Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3 2 sample4 sample5 sample6
And it is displaying like this
Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3 Student ID Student Name Fathers Name Guardian Name 2 sample4 sample5 sample6
I have a field in a table that is declared in the CREATE statement as an INT datatype. However, when I query that table using vb.net, the value comes back as a decimal.
How do you declare a field in Oracle as a true integer data type?
Actually I got a data in hex values and i want to convert it to datetime format. But before i convert the hex value into datetime format, i must convert the hex values into decimal val first for each 2 digits of hex values. This is an example of the input data:-
This is input data in hex value:- STARTTIME : 080b1317021a
This is decimal value after convert from hex to decimal:- STARTTIME : 081119230226
My problem is how could i convert hex to decimal in oracle? Below are my coding:-
case when substr(LOAD_NGNSM.STARTTIME, 1, 2) not between '00' and '99' or substr(LOAD_NGNSM.STARTTIME, 3, 2) not between '01' and '12' or substr(LOAD_NGNSM.STARTTIME, 5, 2) not between '01' and '31' or substr(LOAD_NGNSM.STARTTIME, 7, 2) not between '00' and '59' or substr(LOAD_NGNSM.STARTTIME, 9, 2) not between '00' and '59' [code]....
I am using Oracle Forms 6i. In my program unit i write into a .CSV and .TXT File. Here if the amount is 100.00 then it prints as 100 only. That is if the 2nd decimal digit is 0 it doesnt print it.
For example 10.00 -->10 10.01--> 10.01 10.10-->10.1 10.12-->10.12
I used to_char(nvl(&amount,0),'9999999.99') so if query select to_char(nvl(10.10,0),'9999999.99') from dual, then i get the output as 10.10.
Do you think this is a problem with Oracle forms or SQL or the formatting of .TXT & .CSV.
Recently i have started working on PLSQL coding. I have a requirement. Either error or un-processed record count is 90% of to be processed records then the script has to fail. Currently I am having a situation where error count is 1 and total to be processed is also 1.
in the below V_ERR is error count V_UPS is un processed count V_PROCESSED_COUNT is total to be processed.
I am expecting PASS result but it is giving FAIL.
DECLARE V_ERR NUMBER:=0; V_UPS NUMBER:=0; V_PROCESSED_COUNT NUMBER:=0; NIN NUMBER; BEGIN V_PROCESSED_COUNT:=1; [Code] .......
Using Oracle SQL Developer 2.1.1.64 to run the queries & Oracle 11g.
I have two numbers in two colomns of an oracle table(colomn a & colomn b). I am trying to divide colomn a/colomn b and putting the results in colomn c & also in colomn d (all in the same table) using update commands
Eg: UPDATE MTOTABLE_PWELD pw SET pw.WELDO=(pw.pipe_length/12000);
But here is the real issue. In colomn d I only need the integer portion of the division value.
For example , when I divide colomn a/colomn b , let us assume that we are getting a value of 2.56. Then I want the value of 2 to go to colomn d.
I tried round((colomn a/colomn b),0). But it rounds off 2.56 to 3. I dont want that. I need the exact integer portion of the value to be seperated.
Insert into TEST(CREDIT_BALANCE, AWARDS_BALANCE) Values ('1678.09', '1678.9'); Insert into TEST(CREDIT_BALANCE, AWARDS_BALANCE)
[Code]....
From valid records from test table those two columns,need to be insert into test1 table. We should validate the decimal values while selecting from test table.
load data infile 'C:\Documents and Settings\xxxxx\Desktop\abc.txt' APPEND PRESERVE BLANKS INTO TABLE table1
[code]...
When I run the above control file in sqlldr, I'm getting the error as Record 1: Rejected - Error on table table1, column column3. ORA-01481: invalid number format model
In the table the column3 data type is NUMBER(6,2).: The column size in table is 6 and position of column3 in control file is only 4. Also if possible let me know how the same data (send me 2 dummy records) which exactly works for the above control file especially for column3 where decimal number comes in the flat file.
For generating the flat file, for column3, i'm using LPAD(:value,4,0) in the select query column list.
I have a small prob. I want an amount to be always 2 decimal places. I've used the Round function - Round(amount,2). the problem is that if the amount is only to 1 dp like 1.4. the above function will return 1.4. I want it to appear like 1.40
I am using an portuguese(brazil) Oracle 11g db,which should have ',' as decimal char. it seems to be working fine when i m trying to get numerical data via select query,but when i am trying to retrive the same data using out parameter of an sproc.I am getting data with '.' as decimal char.