SQL Formatting To Char Function

Dec 23, 2012

i have a problem with my SQL programming which i am learning at the moment. Currently here is my Show how many orders were placed in each month of 2012. (group by question, to char function)

select count(orderdate)
from csorder
group by orderdate;

where the to char function would occur?

tables for reference
CSPRODUCT contains (PRODCODE, PRODNAME, CATCODE, PRICE, PARTOF_PRODCODE)
CSCATEGORY contains (CATECODE, CATNAME)
CSORDER contains (ORDERID, ORDERDATE, CUSTID, PAID)
CSORDERLINE contains (ORDERID, PRODCODE, QUANTITY)

View 3 Replies


ADVERTISEMENT

Oracle 9i To 10g To Char Function?

Feb 11, 2009

Well the company i work for has just recently upgraded from Oracle 9i to 10g. We are having various problems with the migration, and with certain code breaking. My question regards this piece of code;

[code]

to_number(to_char('a_date','YYYY'))

[code]

There are various statements like the one above scattered throughout a query i am trying to fix. When run, the query returns an "invalid number ora-01722" error, which i know is caused by the above code.

if this method of converting from date, to character was discontinued from 9i to 10g?it just seems strange as there are a lot of statements like the one above and they must have worked at some point, but now i cant even get one to work on its own.

View 3 Replies View Related

SQL & PL/SQL :: USING Listagg Function For More Than 8000 Char

Oct 28, 2011

using listagg function for more than 8000 char.I did the below sample SQL and in "e_orig" and "d_orig" for upto 4000 char it is working fine but I have to use it for more than 8000 char. and it is giving error,I checked the listagg function is having limitation of 4000 char.I tried but I am unable to achieve this.

select d.dname,d.loc,e.hiredate
,listagg(e.ename,',' ) within group (order by e.deptno) over (partition by e.deptno) as e_orig
,listagg(e.ename, ',') within group (order by e.sal) over (partition by e.deptno) as d_orig
from emp e, dept d
where e.deptno=d.deptno;
[code]....

View 17 Replies View Related

Reports & Discoverer :: Clob In 6i - Error Function Return Must Char

Jun 23, 2011

I having issue when i try to use CLOB as varchar2 is not enough in my case. I'm developing function column in oracle report. I'm using developer 6i. I get error function return must char?

How I can use CLOB in oracle report?

function CF_RnoFormula return Char is
--v_release_num CLOB;
v_release_num varchar2(32767);
begin
FOR rec IN
[code]........

View 3 Replies View Related

SQL & PL/SQL :: Search Swedish / Danish / German Char Using English Char

Oct 27, 2010

I want to pass english character as a parameter and search a string that having swedish character, this needs to be done for all the swedish characters.

test _t table having the below 3 values

päiväp
metervara
flerfärgad

1. If user searches based on english char like below then they should get all the 3 values bcz fist & last have swedish ä and second one having english a.

Query : select name from test_t where name like '%a%'

Regsult:
päiväp
metervara
fräg

2. If user searches based on swedish char like below then they should get only 2 values bcz fist & last have swedish ä and second one having english a.

select name from test_t where name like '%ä%'

päiväp
fräg

Is any in-built function available, for Text alternative string search. If not how to search the string based on the query.

View 6 Replies View Related

Selection Criteria Char Between Char

Jul 22, 2009

I have a table with column val1 having data's starting with 0920 and 4 digit char values.

so we have the query to find

select * from table
where val1 between '0920' and 'ZZZZ'

will it work fine?

View 1 Replies View Related

PL / SQL Data Formatting?

Sep 10, 2003

I am trying to develop a PL/SQL function to perform some data cleansing. One of the data cleansing requirements is If there are instances of more than 1 space together, the extra spaces should be removed.Say for example, if my data comes in as "123 456 789", then my function should clean it as "123 456 789" replacing multiple spaces with just 1 space.

What PL/SQL functions could I use to accomplish this?

View 7 Replies View Related

Date Formatting In Oracle

Oct 24, 2006

I have the age of a property stored in my DB. (Lets say 3 yrs old). I would like to extract from that information, the year the property was built. I know I can minus the sysdate by days (-1) and months (add_months), but how can I do it using the YEAR information I have stored?

So if I was doing it against sysdate, I would want the result to be 10/24/2003.

View 2 Replies View Related

SQL & PL/SQL :: Formatting In Utl_mail Output

May 26, 2012

Formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is like very distorted and i have to have it as readable in single line .Please find below the code i have written along with test case, you may substitute your emailid and test, especially my main focus is on the lable aff.mark number where all the mark numbers are distorted and want to be arranged in line.I have attached the email format that i am getting at present also.


CREATE TABLE FAB_LOG ( MRK VARCHAR2(30))
INSERT ALL
INTO FAB_LOG VALUES ('1018017M-6001')
INTO FAB_LOG VALUES ('1018017M-6002')
INTO FAB_LOG VALUES ('1018017M-6003')
INTO FAB_LOG VALUES ('1018017M-6004')
[code]......

View 7 Replies View Related

SQL & PL/SQL :: Formatting A Variable In Report?

Jun 1, 2011

I'm creating a report and wrapping the output line.

These are the variables I'm using

lv_actn_line_1 VARCHAR2(60);
lv_actn_line_2 VARCHAR2(60);
lv_actn_line_3 VARCHAR2(60);
lv_actn_line VARCHAR2 (192);
lv_rpt_ln_19 VARCHAR2(102);

I assign variable

lv_actn_line:=lv_actn_line_1||' '||lv_actn_line_2||' '||lv_actn_line_3;

And want to use it in output as below.

lv_rpt_ln_19 := edge_blank ||lv_actn_line;
osfile.put_line(v_fileid,lv_rpt_ln_19);
line_cnt := line_cnt+1;

How can I format the output so that lv_actn_line will wrap at character 102?

View 10 Replies View Related

SQL & PL/SQL :: Formatting Date And Time

Jul 21, 2011

I have the following piece of

SELECT decode(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'00000000000000',
NULL,
(to_date(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'YYYY/MM/DD HH24:MI:SS')))
FROM dual;

When I run the above code I get an incorrect display ie. "18-JUL-11". when I remove the decode I get the correct display ie."2011/07/18 11:21:09 AM"

View 2 Replies View Related

SQL & PL/SQL :: Formatting Output File

Sep 6, 2012

CREATE OR REPLACE PROCEDURE A516907.proc_data_T_DTIA_TS_ADDR
is cursor CSR1 IS SELECT * FROM T_DTIA_TS_ADDR ;
v_file UTL_FILE.FILE_TYPE ;
BEGIN
v_file := UTL_FILE.FOPEN( 'DATA_PUMP_DIR' , 'T_DTIA_TS_ADDR.txt','w',32767);
FOR CS1 IN CSR1

[Code]....

The procedure above is dynamically generated. It's simple enough in that I am calling "the parent procedure" by passing the table name in. After this the procedure for creating the output data file for that table is generated and that's what we have above. I am doing this to load data into DB2.

What I need is to be able to generate the output such that if the column is of type varchar, char (anything non numeric) then the data should have " around it. If the data is of type number it should not have ".

I suppose I could use something as follows in lieu of the procedure above

Declare
cursor c1 is select column_name, data_type from user_tab_Cols where table_name = 'Table Name here';

-- I would like to know how to use the utl_file.put_line
-- where if the column data type is varchar 2
-- I could get ' ||"|| CS1.TS_ADDR_TY_CD||" || '
-- and if it's a number -- || CS1.CB_TS_DTIS_ID ||

View 33 Replies View Related

PL/SQL :: Losing Date Formatting Somewhere?

Jun 26, 2013

So the data is coming from a collection where the first column is like '1-May' 

declare
no_year EXCEPTION;
  holddate VARCHAR2(20);
  totaldate VARCHAR2(20);
  cursor emp_cursor is

[code]...

I can't see what is in the temp table because even when I don't truncate it global temp table and can't get to the information in it. 

View 1 Replies View Related

SQL & PL/SQL :: Formatting Output Into Multiple Columns?

Apr 16, 2013

as part of an exercise we are to demonstrate a FOR LOOP from 1 - 100.

We must show the output as evidence of doing the work.

Is it possible to display the results in two columns, instead of all in one row? Because 100 rows will not fit it one screenshot!

BEGIN
FOR i in 1..100
LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/

View 5 Replies View Related

SQL & PL/SQL :: Formatting The TEXT In Exported File?

Nov 15, 2011

How to make the multiple lines of character in single line. Below is the example where i am getting the value when we export the data from the database table.

----------------------
Welcome Aboard customer contact based on Account activation.

The current selection criteria for Welcome Aboard is based at the service level. Therefore, each time a customer connects a service, Welcome Aboard material is mailed to the customer. This is causing the following problems;

Customer complaints. If a service or a number of services are connected on a particular day, it will result in (ie except for some minor exclusions) a single Welcome Aboard pack being sent to each individual customer. As you can expect for corporate accounts, they receive a high volume of this material. Other problems occur when, service/s are deactivated and reactivated, each time a Welcome aboard pack will be distributed.

High costs to Telstra. Each MNET service connected may produce a Welcome aboard pack. This volume of material currently costs Telstra over $500,000 per quarter for Welcome Aboard alone.

------------------------------

here is what i want to the above data to export:

----------------------
Welcome Aboard customer contact based on Account activation.The current selection criteria for Welcome Aboard is based at the service level. Therefore, each time a customer connects a service, Welcome Aboard material is mailed to the customer. This is causing the following problems;Customer complaints. If a service or a number of services are connected on a particular day, it will result in (ie except for some minor exclusions) a single Welcome Aboard pack being sent to each individual customer. As you can expect for corporate accounts, they receive a high volume of this material. Other problems occur when, service/s are deactivated and reactivated, each time a Welcome aboard pack will be distributed. High costs to Telstra. Each MNET service connected may produce a Welcome aboard pack. This volume of material currently costs Telstra over $500,000 per quarter for Welcome Aboard alone.

View 3 Replies View Related

Formatting Number With Comma Separation / TO_CHAR

Mar 1, 2012

how a number can be displayed using comma separation without using TO_CHAR function.

NUMBER 3455678.05 should be displayed as 3,455,678.05 NUMBER 3455678 should be displayed as 3,455,678

View 1 Replies View Related

Query On Formatting Data In A Specific Way In Oracle

Apr 25, 2010

I am currently doing a project where i need to write a stored procedure which will be doing the following-

i)it will retrieve multiple columns from multiple tables in a single database(through join) based on certain conditions
II)then it will store the entire data in a certain field(File_data) of staging table

inside file_data a header and a trailer will be present with the records.also the field values will be pipe separated and a new record will start in a new line.

So,the data inside the file_data of staging table will look like this-

H|v1000
transdate|ordnmb|deposite_amt|order_status....
12-nov-09|123456|23.8|C...
4-dec-07|234567|67.7|R...
..........
7-jan-04|567890|54.7|x.....
T|234(record count)

i did this formatting using java, but my project leader wants me to do the formatting using SP,and wants me to use staging table.

View 7 Replies View Related

SQL & PL/SQL :: Formatting Output - Only Email Column Should Be Printed

May 4, 2010

i have column names as empno,ename,email...

i want output as

asb@asd.com,sad@sad.com,..... only email column should be printed

how can we do that?

View 3 Replies View Related

PL/SQL :: Aligning / Formatting Records In Text File

Feb 26, 2013

I have a query then I write it to a text file using utl_file. However, I'm having some trouble formatting the data itself on the text file. =(
How would I align these records against its header considering the length of the Name/subscriber no. column which is not constant. Would LPAD work?

ID       Name                       Subscriber No

1     James Smith               12454654     
123     Scott Kevin Joseph           4564564
21     Dana Marice Curtis          455
2     Stephanie Hans Walter      23134

View 1 Replies View Related

Application Express :: Formatting (dollar Value) Within ITEM?

Mar 21, 2013

So I'm trying to display a monetary value in an item.

SELECT to_char(SUM(TXN_AMT),'$9,999') from CMS_COST_TRACKING where UPPER(TXN_NODE)='VMAX'this results in ######### in the item

If I remove the to_char formatting then the SUM displays correctly.Is this a restriction of some kind. The item is a display only.

APEX Application Express 4.1.1.00.23
ORacle 11G.

View 1 Replies View Related

Application Express :: Formatting A Table Without Headings

Apr 3, 2013

I want to make a report which is formated (rebuilding an older tool in Access).

So on the one hand I need some formattings which I implemented with the search bar in the interavtive report. (content of the first column is the heading for a table with the rest of the content of all rows which have the sam content in the first column) On the other hand I don't want to show the headings of the columns, what I solved with the classic report(report attributes/ column attributes: none).
Is the a solution to match these two requirements in one report?

View 4 Replies View Related

SQL & PL/SQL :: Number Datatype Formatting To Show $ Sign And Commas

Mar 8, 2011

I have a Number(18,0) datatype.It could have millions/billions stored in that field.How can I show that amount in the form of $7,123,787 ($ sign and commas)?

By using To_char function like below, I am not getting the desired format.

Select to_char(123000000.25, '$9,9999999999.00') FROM dual;

Result is (no commas):

$123000000.25

View 2 Replies View Related

Reports & Discoverer :: Workbook - Export With Formatting To Excel

Aug 5, 2011

I have a workbook in Discoverer which is not formatted correctly when I export it to excel. I have to re-size the columns and also adjust the height of the rows to see the data clearly.

Is there a way I can export the report without having to make these changes all the time?

View 1 Replies View Related

Application Express :: Date Formatting - Not A Valid Month

Apr 29, 2013

I am using the cloud version of APEX for a college course. The database script I am trying to load has dates formatted in "DD-MON-RR" and I receive 'not a valid month' and 'a non-numeric character was found where a numeric was expected'. Is there a workaround in APEX for this? I'm trying to avoid changing thousands of lines of data.

View 4 Replies View Related

Reports & Discoverer :: Builder Get Closed - Formatting Page 1000

Dec 23, 2011

I've created report to generate letters,my query contains records more than 1000 while generating report it shows report progress

"Formatting Page 1000" after that report builder gets closed without any error message,I've tried for 10 records it is working fine,

View 5 Replies View Related

Reports & Discoverer :: Formatting - Display Value Of Field In Single Line

Oct 20, 2010

I have developed one report but need one formatting suggestion. There is one field called "DESCRIPTION", I want the value of this field to be displayed in a single line. Now the big values are wrapped into multiple line.

Now,

DISPLAY
----------------
This is a Oracle
Report.

I want,
DISPLAY
------------------------
This is a Oracle Report.

Maximum size of "DISPLAY" field is VARCHAR2(240).

View 3 Replies View Related

Application Express :: Interactive Report - Break Formatting Style?

Nov 14, 2013

,Is there a way to make Interactive reports break format work/look like classic reports break format? i.e.1st,2nd,3rd column option. The way IR breaks is that is reserves a whole row for the column used in the break, which is not what I want, I want the report to look like the following:

 city               emp           sal 
Chicago     John Miller  1500                
Mark Horton 2000
Denver       Rob Martino 1200 

I tried to use rollup queries which work fine except that it does not do exactly what I want when I sort columns through the front end , I am basically displaying a check box for grouped records ( and I want it to display at the first record of each group), so the order is important. for example: 

checkbox   City               emp           sal[ ]              
Chicago     John Miller    1500         
Mark Horton  2000[ ]             
Denver       Rob Martino  1200 

View 2 Replies View Related

SQL & PL/SQL :: Date To Char / Char To Date

Dec 13, 2012

oracle 11 g xe

1) I want to pass date in '12 Dec 2012'(CHARECTER) while calling SP and in side SP i have declared parameter as DATE so I think it should not create any problem in any condition whether it is passed from oracle client or any other front end?

2) I have date in (date type variable) , how to convert it into char but it should be in iso format (i want iso format , so that it should not create any problem in any condition).

3) How to convert (date in char- in iso or any good format) into (date type variable) (so that it should not create problem and this is why i wanted char date in iso format).

4) If there is any good format and function to convert (date to char) and (char to date)?

View 5 Replies View Related

SQL & PL/SQL :: Char To Date Conversion?

Feb 7, 2013

converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.

View 6 Replies View Related

SQL & PL/SQL :: Convert Char To Number

Mar 16, 2011

I've got a table with varchar records and I need to separate numerical value and convert into the number datatype.

create table tmp_mape

(remark varchar2(100) )

insert into tmp_mape values ('Dobitie zdarma 3,32EUR 0910105067 02/02')
insert into tmp_mape values ( 'Dobitie pravidelné 9,00EUR' )

I just need to get values 3.32 and 9 from that example into the new column.

The select below returns ORA-01722: invalid number

how to solve it?

select remark,
to_number( to_number(REGEXP_SUBSTR (remark, '[[:digit:]]+', 1) ) ||','|| to_number(substr(REGEXP_SUBSTR (remark, ',[^EUR]+', 1) ,2) ) )
from tmp_mape

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved