I have a column where the values are like
SQL> SELECT distinct a.legacy_product_code FROM im_polprdmap_pukka a where a.new_product_code like '%GIFT%';
LEGACY_PRODUCT_CODE
-------------------
GIFT25
GFTPF21NP
GIFT21/H
GIFT21//
[code]...
I need to calculate a field taking the numeric part of the above filed.How can i write this?
I have a datablock like this
datablock name : jou_tra1_tab
No. of record dispalyed is 5
so datablock display like this
jtt_amt jtt_amt_1 ck_amt
jtt_amt jtt_amt_1 ck_amt
jtt_amt jtt_amt_1 ck_amt
jtt_amt jtt_amt_1 ck_amt
jtt_amt jtt_amt_1 ck_amt
----------------------------
ck_amt_tot
ck_amt is checkbox..
ck_amt_tot is total of jtt_amt_1 [but total only those record whose checkbox is checked]
My task is like this When i checked checkbox whatever value in jtt_amt transfer to jtt_amt_1 field
but i can change value in jtt_amt_1 field ...i want to take addition of that changed field and show that sumation in ck_amt_tot.
I write trigger when-checkbox-changed like this
-------------------------------------------------
IF :jou_tra1_tab.ck_amt = 'Y' THEN
set_item_INSTANCE_property ('jou_tra1_tab.jtt_amt_1',CURRENT_RECORD, UPDATE_ALLOWED, PROPERTY_TRUE);
:jou_tra1_tab.jtt_amt_1 := :jou_tra1_tab.jtt_amt;
else
:jou_tra1_tab.jtt_amt_1 := 0;
END IF;
and when validation item trigger for jtt_amt_1 :
--------------------------------------------------
IF :jou_tra1_tab.ck_amt = 'Y' then
:jou_tra1_tab.ck_amt_tot := :jou_tra1_tab.ck_amt_tot + :jtt_amt_1 ;
else
:jou_tra1_tab.ck_amt_tot := :jou_tra1_tab.ck_amt_tot - :jtt_amt_1 ;
But when i changed value in jtt_amt_1 field i cant get write summation .
I want to convert the field value into numeric form using the formula:
∑[((radix)^ position * alphvalue) mod PR]
where alphvalue A= 10, B = 11, C = 12, D = 13,E = 14............. S = 28, ...........Z = 35, radix = 36, PR= 731
The value of position is marked from right to left starting with 0.
For Example:
ASIN
Let PR = 731 (large prime number)
∑[((36)^3 * 10)mod 731) + ((36)^2 * 28)mod 731)+
((36)^1 * 18)mod 731) + ((36)^0 * 22)mod 731) ]
= 182+ 469 + 648 + 22
= 1321
is it possible that it pick the field value and convert it into numeric form.
providing sql statement to strip commas from a number/numeric field in the BI repository logical column? I do not want to use cast function to convert to character because we need to keep that field as numeric. I could only find string expressions to remove commas.
View 12 Replies View Relatedhow to caluclate days between two dates of single timestamp filed and with this
query
Select * from m_activity_transaction where actn_opp_id in (
Select actn_opp_id from m_activity_transaction where ACTN_ACTV_ID = 218
Group by actn_opp_id
[code]...
and i nedd to caluclate no.of days between two dates like 27-JAN-12 11.06.20.000000 AM and 08-FEB-12 05.32.54.000000 PM where actn_id is unique AND ACTN_OPP_ID IS NOT UNIQUE.
I am using APEX 4.1 and Oracle 10g XE.
I want to restrict 3 digits after decimal in tabular form. Only three or less than three digits should be allowed after decimal and should be stopped automatically.
e.g. 34.543
I have 3 pages: - Classes: a Classic Report that shows a list of classes and summarises attendance- Class Attendance Report: An Interactive Report that shows a row for each student that attended a class- Attendance Add / Edit Form On the Classes page, the Date value is a Link which passes a filter in the URL to the Class Attendance Report, showing only students who attended the class held on this date On the Class Attendance Report, I want to have a Region Button that, when clicked, goes to the Attendance Add / Edit Form and the Class Date field on this form is automatically populated with the same date value as was being used for the filter on the Interactive Report on the Class Attendance Report Page. So, I click on the Class Date link for 2013-11-01, this takes me to the Class Attendance Report and filters for students who have been already entered as attending on 2013-11-01. If I click on the "Add Attendance" button, it goes to the Attendance Add / Edit form and the Class Date is automatically set to 2013-11-01. I assume I need to do this via a Dynamic Action that runs when the "Add Attendance" button on the Class Attendance Report page is clicked?
View 6 Replies View RelatedI want to Bold the value with highest value in Numeric field. how can i do this.
View 12 Replies View RelatedI have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.
The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:
Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;
But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?
I have to date field in detail part of master detail Form. In detail part i have field of From_date and To_date. I want when i go to next record in detail part ,,,from date is initiaze by previous to date+1....eg.like
1.record: From date=01-jan-2011, To_date=10-jan-2011
2.Record: From_date = 11-jan-2011;
I have Attached calender to two feild...and calender is the different block... In Detail block when_record_instance i handled the;; From date=previous to-date +1;
but when i double click the feild calender is open and when_new_record trigger is fired agian. calender called ...the when_new_record is not callled .....
I have an sqlldr process running loading data into my database. I have created a trigger to run before inserts on each row to start gathering summary data from the basic underlying data. The trigger compiles ok and the procedures the trigger is calling compile ok, but when the sqlldr process runs I get errors in the log files.
Here is the sqlldr control file:
LOAD data
APPEND INTO TABLE cdr.day_tables
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
RecordCode
,CdrStart DATE 'YYYY DDD SSSSS'
[code].......,
Next is my trigger
create or replace TRIGGER BNUMBER_SUMMARY_INS
BEFORE INSERT ON DAY_TABLES
FOR EACH ROW
DECLARE
[code]......
Next are the procedures that are called by the trigger:
create or replace PROCEDURE BNUMBER_SUMMARY
( BNUMBER IN VARCHAR2
, CALLDATE IN DATE
, CALLDURATION IN NUMBER
) AS
record_found NUMBER;
BEGIN
[code].......
The error messages I am getting are:
Record 1: Rejected - Error on table CDR.DAY_TABLES, column CDREND.
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDR.BNUMBER_SUMMARY_INS", line 6
ORA-04088: error during execution of trigger 'CDR.BNUMBER_SUMMARY_INS'
I need to find out what field it is complaining about, especially since I am not even using the cdrend field from the input record?
select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
WHERE ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');
Error:-ORA-01858: a non-numeric character was found where a numeric was expected
I get the error message mentioned in the subject with this SELECT-statement
....where (t.cfonte=14 and t.data_ultima_modifica between sysdate -4000/(24*60*60) and sysdate ) or (t.data_ultima_modifica > to_date('%TIMESTAMP%','ddmmyyhh24miss'))]]>
If I substitute %TIMESTAMP% with 310507143709 then it works
We are getting an error : a non numeric was found where a numeric was expected sometimes when this statement is executed:
INSERT INTO training select * from temp_training where class_id='xyz';
all columns and their datatypes are the same in both the tables
however if i replace the * with the column names as shown below it seems to work fine without giving an error
insert into training (a,b,c) select a,b,c from temp_training where class_id='xyz'
wanted to understand the subtle difference between the 2 statements
why this query works fine:
SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK
FROM zajezd Z
ORDER BY Z.JMENO
But when I try to use the alias to filter the results like this (note the WHERE part):
SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK
[code]...
Then it throws an error message ORA-00904: Unknown Identifier for the column/alias POCET_ZASTAVEK?
How can i convert the time part into second. for example
in mu table i have below rows
1753-01-01 09:18:00.000
1753-01-01 09:52:00.000
1753-01-01 09:36:00.000
I want to convert the time part of each rows to number of second in different column. so for the 1st rows it is 09:18:00.000 that means 9hour and 18 min = 9*60*60+18*60.
But that is complex column.
I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:
SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID
However, I only need the DATABASE piece selected in my FOR loop below.
CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;
[Code]...
To make SQL query to before and after specific character.
Create table test(flist not null VARCHAR2(200));
First field content with below record:
FC028CONNE_IMPORT_WRONG_COMP_LENGAPXXXXPPPP
FC024CALL_FUNCTION_OPEN_ERRORAPXXXXPP
FC025OPEN_DATASET_NO_AUTHORITYAPXXXXPPPPPPPPPPPPPP
FC015RAISE_EXCEPTIONAPAXEPPPPPPPPPPPPPPPPPPPP
to filter the above record from FLIST column thorugh sql script as below:
FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP
means remove first 5 charator and after APXXXXXXXXX.
Output of SQL query should come like below:
CONNE_IMPORT_WRONG_COMP_LENG
CALL_FUNCTION_OPEN_ERROR
OPEN_DATASET_NO_AUTHORITY
RAISE_EXCEPTION
I started one packaged procedure in plsql developer yesterday..thats being run from yesterday...I wanted to know what part of the query that is being run now from the procedure.
View 23 Replies View RelatedI need to insert Data into a table based on particular condition, So I'm using an Insert select as follows:
INSERT INTO error_table
(error_code,cust_id,customer_name,error_message )
SELECT '34', cust_id,customer_name, 'Missing xxx'
FROM customers
WHERE cust_id = cur_cust.cust.id
AND ( NOT EXISTS ( Select .....) -- control 1
OR (EXISTS ( Select .....) --control 2
OR (NOT EXISTS ( Select .....) -- control 3
I have around 10 controls. I want these controls to run conditionally..I was wondering if this can be done using a decode based on some variable outside the query.
I want the query to run like this:
if A then execute control 1: AND ( NOT EXISTS ( Select .....)
If B then execute control 2: AND ( OR EXISTS ( Select .....)
In a table, I have a column in a table with the following values:
targetcol
30 WD
180 WD
70%
0
80%
180 CD
70%
0
7 WD
2 WD
0
-
-
-
-
-
-
-
-
-
where WD is working days and CD Calendar days.
I just need to extract number from above.
I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.
View 3 Replies View RelatedFor an entered date, I want to hardcode the time part as 18:00:00, and rest of the date should be whatever is passed as input, how can i do this?
View 4 Replies View Relatedi am working on sql server developer in on of the organization. i got requirement from client that migration part from oracle 6i forms to oracle 10g and i got existing forms backup from client that is have .fmb, .mmb, .rdf and some of libraries having .pll files, but am new to oracle which form need to open from form builder first?
i tried to open .fmb file given database connection also but it is showing some libraries are missing. what should be the solution for my problem.
Select * from XXX where xxx.a in �123,456�-----> this values comes dynamic to my query.
Here xxx.a will contain values like 123 or 456 like that .
I work with Oracle Forms and Report Builder since several weeks, but now i have something that i want to do with Report Builder.
I get string from DB (It's a paragraph) But i would like to set bold weight on a part of the string
for now, i set bold text i want to and i have created a fonction that make text bold like this
l_chaine := (SUBSTR(MyStrFromDB, InSTR(UPPER(MyStrFromDB), '[B]'),InSTR(UPPER(MyStrFromDB), '[/B]') ));
if(length(l_chaine) > 0) then
SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT);
end if;
but it doesnt look to works..
how can i do that? And where i can do that?
i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER)
IS
C DIA_RL_PKG.CURSOR_TYPE ;
[Code]....
I have a table
select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002
2 rows selected
select * from testing where name like ('GHI,TCF');
now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.
Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.
View 13 Replies View RelatedI want to write a plsql program to display half part of a string .Example there is a string like gillmadden@myharbour.org.uk
I want to display only the later part of the string
myharbour.org.uk as output.
Can we write using string functions in plslql?