I am trying to create a SQL query which will check that various postcode formats are valid, but I am having trouble getting oracle sql to check for values within ranges - for example the following returns no rows, even though most of the postcodes I am dealing with start with 'P'.
select postcode from mytable.addresses
where postcode like '[N-R]%'
;
Is it possible within a CASE statement to put conditions on the date range that I want to pull? IE: am versus pm. The query has to pull specific time ranges for an AM run versus a PM run. ..... FROM table WHERE CASE WHEN TO_CHAR(SYSDATE,'AM') = 'AM' THEN table.date BETWEEN TRUNC(SYSDATE) AND SYSDATE ELSE table.date BETWEEN TRUNC(SYSDATE+12/24) AND SYSDATE
find the below table. The entire data is within DOCSTART and DOCEND. The data is further enclosed within BACCSTART and BACCEND. This type of block is repeatable. I have to pick up any of ABCD which is also repeatable and TOTAL(occurring once per block) and ACCNAME (occurring once per block) for each block within BACCSTART and BACCEND and form an xml like
for each such block. Presently I am using a for loop, but the performance is not up to the mark. It will have around 200 such blocks for which I have to form the xmls within 15 seconds. Presently the for loop is taking around 53 secs.
ROWNUM NAME VALUE 1 DOCSTART null 2 BACCSTART null 3 ABCD abcd 4 ABCD abcd2 5 PQRS pqrs 6 PQRS pqrs2 7 TOTAL 100 8 ACCNAME name 9 BACCEND null 10 BACCSTART null 11 ABCD abcd 12 ABCD abcd2 13 PQRS pqrs3 14 PQRS pqrs4 15 TOTAL 150 16 ACCNAME name 17 BACCEND null 18 DOCEND null
I have a table which has 4 varchar columns( col1, col2,col3, col4). There is data in the table for col1,col2,col3 and col4 is a newly added column. I want to concatenate values from col1,col2 and col3( comma separated) and put it in col4( as col1,col2,col3). Note that col1 or col2 or col3 can have null values and the concatenation should ignore null.
Is it possible to do it in a single SQL statement or should I use a procedure to do this?
The conditional statements should not only be executed in sequence, but also if any of them are true they should not be overridden by any subsequent conditional statements being true.
When actual effort Accepted or Rejected for AST proposals and calculate a flag for "enhance to AST guideline" = Y/N as follows for each employee and display at the employee level
1)If AST eligibility = N AND proposed AST % >0, then "N" 2)Else If AST eligibility = N AND proposed AST % = 0 then "n/a" 3)Else If AST eligibility = Y AND Act Rank = 3 AND proposed AST = 0 then "Y" 4)Else If AST eligibility = Y AND Act Rank = 3 AND proposed AST >0 then "N" 5)Else If AST eligibility = Y AND Act Rank = 2 AND proposed AST = 0 then "Y" 6)Else If AST eligibility = Y AND AST % is greater than or equal to the AST guideline minimum AND less than or equal to the AST guideline maximum, then "Y" 7)Else If AST eligibility = Y AND AST % is less than the minimum guideline OR greater than the maximum guideline, then "N"
I tried the following code but I am not getting the expected result .
if (upper(P_stat)='ACCEPTED' or upper(P_stat) like 'REJECTED%') then else if NVL(P_elgi,'N') <> 'Y' AND P_prop > '0' then P_flag := 'N'; else if(NVL(P_elgi,'N') <> 'Y' AND P_prop = '0') then [code]........
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE EXCEPTIONS --STRSQL := ''; --STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i); -- EXECUTE IMMEDIATE STRSQL; INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement commit; --dbms_output.put_line(unrecondata(2).TRANSID); EXCEPTION
I have Oracle 10g. In a table with just one record there is a clob with the following xml. In the following clob, there could be any number of Emp elements.
I am creating the following two tables...no issues here: CODECREATE TABLE COURSE_SECTION ( Csecid NUMBER(8) CONSTRAINT COURSE_SELECTION_NUMBER_pk PRIMARY Key, Cid NUMBER(6) NOT NULL CONSTRAINTS COURSE_SELECTION_Cid_fk REFERENCES COURSE, Termid NUMBER(5) NOT NULL CONSTRAINTS COURSE_SELECTION_Termid_fk REFERENCES TERM,
[code]...
The issue I am having is actually inserting data into the table: CODEINSERT INTO ENROLLMENT VALUES (100, 1000, 'A' ); INSERT INTO ENROLLMENT VALUES (100, 1003, 'A' );
[code]...
But I get an ORACLE error of
ORA-02291- integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not foundHow can the parent key not be found when I have it declared/created in the above statement?
i want display a string like this using a select statement in oracle 10g.i have tried but not yet done.
example: ----------- from 'ABCDEFGH' to 'ACEG' removing 'BDFH' from the source string 'ABCDEFGH'
i giving here the example you can take any valid string i want the result like the above example and also in a dynamic manner means we can give string to a select statement in run time.can it is possible in a select statement only.
I am trying to search a way to get the SQL statement that caused an exception withing an oracle function. I tried:
SELECT sql_text from v$session ses, v$sql sql where sql.sql_id = ses.prev_sql_id and ses.sid = sys_context('userenv','SID') AND ROWNUM = 1;
but this doesn't always return the last statement that the function has executed. if needed i can send the complete script for the function and its tables and stored procedures for testing.
My company use a sybase database that runs business jobs. Currently we run SQL queries from Perl to gather time information on the jobs. Now we have an application that is using Oracle. The server it is on, doesn't have perl, so I am using a shell script to login to sqlplus and run a query for a job and it's end time. I have accomplished this. However, here is the 2 problems I am having.
1. The query reults are returned in Scientific time, I'm able to convert that to EPOCH time in the SQL syntax, however, it comes back with a 13 digit time, instead of 10. The last 3 digits are zero. How can you remove the last 3 digits in the query or convert the 13 digits to Human Time. Right now when you see the select statement, I am doing a to_char to get it to EPOCH time.
2. How to only show the latest time in the query and not show ALL job end times from it's past runs.
Here is my shell script, and I do realize this maybe a select statement syntax solution to one or both, but the UNIX time stamp is puzzling.
#!/usr/bin/sh sqlplus -S username/password@JAWSPROD <<eof> myfile set heading off feedback off verify off select JAWS_APP.JAWSJOB.JOBNAME, to_char(JAWS_APP.JOBRUN.ENDTIME) from JAWS_APP.JAWSJOB, JAWS_APP.JOBRUN where JAWS_APP.JAWSJOB.JOBID = JAWS_APP.JOBRUN.JOBID and JAWS_APP.JAWSJOB.JOBNAME in ('pa_box_settle'); exit
I have been asked to rewrite the following update statement without using the hint BYPASS_UJVC.
l_new_CFT_ID CASHFLOW_TYPE.CFT_ID%TYPE;
if (l_Record > 0) then -- since at least 1 loan was found with the old type, process the actual update update /*+BYPASS_UJVC*/ ( select cfa.CFA_CFT_ID [code]......
I think I am supposed to be using the Merge statement but I am not sure on how to go about it.
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
How do I write this MSSQL statement so it works in Oracle?
update b1 set b1.b1_app_status = r3.application_status from conv_app_status_update a, statyp r3, b1perm b1 where a.spc = r3.serv_code and a.task_des = r3.r3_act_type_des and a.task_status =r3.r3_act_stat_des and a.process_code = r3.r3_process_code and r3.application_status is not null and a.spc = b1.serv_code and a.id1 = b1.id1 and a.id2 = b1.id2 and a.id3 = b1.id3
I have a procedure as below. To sum up the procedure in one line it dynamically forms a string to get the values of the type which is passed as an input to the procedure.
I call the procedure as
exec exec_imm( exec_imm_t(1,'asd','1/2-34'));
ERROR: Error starting at line 9 in command: exec exec_imm( exec_imm_t(1,'asd','1/2-34')) Error report: ORA-00904: "P_TYPE_DATA"."ADDRESS": invalid identifier ORA-06512: at "PTK_ADM.EXEC_IMM", line 26 ORA-06512: at line 1 00904. 00000 - "%s: invalid identifier"
NOTE: When I try to execute the procedure with the execute immediate statement I get the above error. But when I execute the select statement which is nothing but the value in v_type_data directly (as seen in the comments in the code below) there is no error. But when the same v_type_data is used in execute immediate, I get an error.
CREATE OR REPLACE procedure exec_imm(p_type_data exec_imm_t) AS v_type_str CLOB := NULL; v_type_data CLOB := NULL; v_type_name VARCHAR2(25) := NULL; BEGIN [code]......
i'm working in an Oracle 10g database on an IBM AIX server.
I have 3 tables (tables A, B and C).
Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
I also have a Master table which is used to store the data from Tables A, B and C via the insert statement.
I'm trying to create a dynamic SQL insert statement using a shell script to insert data from the columns in Tables A, B and C into my Master table. Master table does contains all columns from Tables A, B and C, although a column name could be spelled differently. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".
I build the dynamic query using a for loop in my shell script (see below).
The problem is that i can't get the correct columns in the Master table in the dynamic SQL for the insert because depending on the table i'm selection from, the columns are different. So how do i get the correct columns in the SQL for the Master table?
Example Shell Script
--Archive_Rates.txt contains: Table A, Table B, Table C (but the next time my process runs, Archive_Rates might contain Table D, Table E and Table F -- each which have different column...but all columns are still in the Master table)
for tbl in `more Archive_Rates.txt` do echo 'BEGIN WORK; ' > rc1.sql echo ' ' >> rc1.sql echo 'insert into Master' >> rc1.sql echo '(prod, rate, rate_exp) ' >> rc1.sql
I have been trying to use case statements with oracle table type by really not sure how to go about it. I know it might be simple but it been giving me hard time.
Here is my Cursor:
CURSOR c_chk_style IS SELECT DISTINCT 1 FROM TABLE(CAST(I_message.ExtOfXOrderDesc_TBL(1).ExtOfXOrderSkuDesc_TBL AS "RIB_ExtOfXOrderSkuDesc_TBL")) item_diff,
[code]...
Now i know that the table type "RIB_ExtOfXOrderSkuDesc_TBL" will be always populated but the table type "RIB_ExtOfXOrderPackDesc_TBL" may not be populate and can be null. So i want to run the exists against the "RIB_ExtOfXOrderPackDesc_TBL" aliased pack_diff only if it is populated. If its null i dont want to run the exists clause.
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') 2 || ' User '||s1.username || '@' || s1.machine 3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text ||' is blocking the SQL statement on '|| s2.username || '@' 4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> ' 6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 , 7 v$session s2,v$sql sqlt1, v$sql sqlt2 8 WHERE s1.sid =l1.sid 9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1 10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2; [code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
DECLARE @MainTable TABLE (UniqueID INTEGER, Category VARCHAR(200), WeekDate DATETIME, VALUE INTEGER) INSERT INTO @MainTable VALUES(123, 'Shirts', '10/07/2011', 5000) INSERT INTO @MainTable VALUES(123, 'Shirts', '10/14/2011', 8000) INSERT INTO @MainTable VALUES(124, 'Pants', '10/07/2011', 4000) INSERT INTO @MainTable VALUES(125, 'Shorts', '10/14/2011', 8000) INSERT INTO @MainTable VALUES(126, 'Shoes', '10/21/2011', 9000); --select * from @MainTable; [code]...
The query works with all the CTEs up to the last select statement. Oracle does not support the OUTER APPLY statement, how should the last piece be written to make it work in Oracle?
How to call a function with a row type return in an Oracle select statement.
For e.g. :
If I had this function with a rowtype return: ------------------------------ create function abc return xyz%rowtype is rec xyz%rowtype; begin select * into rec from xyz where col1 = n; return rec; end; -------------------------------- How could I use this in a select clause, as there is a multi column return by the function ?
Yesterday only I have Installed Oracle 11G & created DataBase - JafferDB And from Oracle SQL Developer, I have created a connection called - JafferCon and SID also given..and the role is - SYSDBA And I excute the below statement
insert into MyTable1 Values ('AAA1', 'BBB1', 'CCC1') insert into MyTable1 Values ('AAA2', 'BBB2', 'CCC2')
Then I checked by Select statement, it has shown the values....No Problem.... But, as a test, I deleted the connection and created a new connection tio the same DataBase with different name and when I checked by Select Statement....., it has not shown the values....?