Count Number Of Combinations Of Country / State Invalid For Driving Licenses
May 21, 2012
I'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licenses.
select COUNTRY_CODE, STATE_CODE, count(*) from
(select COUNTRY_CODE, STATE_CODE from CUSTOMER_TABLE
where DRIVING_LICENCE is not null
and not (COUNTRY_CODE in ('AUST') AND STATE_CODE in ('VIC', 'NSW', 'SA', 'QLD', 'NT', 'TAS', 'WA', 'ACT')))
group by COUNTRY_CODE, STATE_CODE
The output is okay...for example I get these results:
INTINT
NZSI
NZINT
AUSTINT
NZNSW
NZ <null>
However, what I am missing is the combination of "AUST" & <null> for country/state respectively. Am I writing the code correctly?
View 1 Replies
ADVERTISEMENT
Feb 15, 2012
The function is supposed to tell whether numbers stored as a string value evaluates to a date.I copied into an SQL windown in PL/SQL Developer and typed "commit;" after and then tried to view the results as I posted below to make sure the function worked.
However, I get an ora-06575 Package or Function GETDATE is in an invalid state.So would like to know first, if the code is correct? And if it is, do I need to do something else to make Developer take the code? Here is the code that gave me the error I used to try to test the function:
select GetDate ('010312', 'dd-mm-yyyy')
from dual
Here is the code I used to create the function:
create or replace function GetDate (vDate varchar2, vMask varchar2)
return varchar2
is
vReturnDate date;
begin
vReturnDate = to_date(vDate, vMask);
return vReturnDate;
exception
when value_error then
return 0;
end GetDate;
commit;
View 5 Replies
View Related
Feb 2, 2012
Find the date difference. I need to find that how many days the task is pending, if ACT_NAME field switching from 'SET PENDING%' to 'RESUME PENDING%' by using ACTIONTAKENDATETEXT field in the History table.
Example as needed:
NoPendingDays = 23 (8+15)
I have attached Create table and Insert table values sample as SQL file.
View 10 Replies
View Related
May 21, 2010
how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1
View 5 Replies
View Related
Sep 18, 2012
So usually joining a local table and a remote table (much larger table), the best practice is using a /*+ DRIVING_SITE(remote table) */ hint.
*CASE1:
INSERT INTO another_local_table
SELECT /*+ DRIVING_SITE(remote_table) */
FROM local_table
,remote_table@remotedb
WHERE join condition
*CASE2:
However I saw this particular
FOR x IN Select id From local_table l
LOOP
INSERT INTO another_local_table
[code]...
So far I haven't seen the explain plan of both cases since most of the tables are Global temporary tables. But in terms of the logic of the two cases and the common best practice, logically doesn't CASE1 should have a better performance?
For me, its like taking a trip to a grocery. CASE1 buys all that you need, one time, it will take you a half-day trip perhaps. However CASE2 is like quickly buying a grocery item, one at a time, for several short trips. You'll save on gas on CASE1 right.
View 1 Replies
View Related
Apr 30, 2013
I have come across a requirement to generate a report of all the users in Hyperion shared services console along with their previlages and also the different licences consumed by the users like Essbase, HFM, Hyperion Planning, Workforce planning etc.... The provisioning report in Shared services is not giving these details.
View 0 Replies
View Related
Dec 3, 2012
select to_char(to_date(mcih_date, 'dd-mm-yyyy')),
mcih_terms from I_MEMO_CONF_H;
TO_CHAR(TO_DATE(MCIH_DATE,'DD-MCIH_TERMS
118-OCT-1245
206-NOV-1222
[Code]...
why i am getting this error?
View 3 Replies
View Related
May 3, 2011
I'm having some troubles while concatenating fields.
If I make a simple join it works right:
select t1.nombre_archivo, t2.periodo
from proc_lotes t1, proc_procesos t2
where t1.lote_id = t2.lote_id;
NOMBRE_ARCHIVO PERIODO
------------------------------ ------------------------------
OSDE 201101
OSDE 201102
IOMA 201101
IOMA 201102
PAMI 201101
PAMI 201102
But... when I try to concatenate both fields it doesn't work. Nombre_archivo and periodo data type is varchar(30)
select t1.nombre_archivo + '_' + t2.periodo
from proc_lotes t1, proc_procesos t2
where t1.lote_id = t2.lote_id;
Error starting at line 1 in command:
select t1.nombre_archivo + '_' + t2.periodo
from proc_lotes t1, proc_procesos t2
where t1.lote_id = t2.lote_id
Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
PS. I'm using SQLDeveloper 1.5.3
View 4 Replies
View Related
Mar 26, 2012
my table having doj 30-jan-2003
SQL> select to_char(date_of_joining,'yyyy') from t1;
select to_char(date_of_joining,'yyyy') from t1
*
ERROR at line 1:
ORA-01722: invalid number
View 1 Replies
View Related
Nov 26, 2012
I have simple table and one column type raw I have simple insert query but not working
INSERT INTO xDESCRIPTION (errorcode,XDESCRIPTION) VALUES ( '0x0002','Test'); and I see this error ORA-01465: invalid hex number
View 17 Replies
View Related
Apr 3, 2013
I keep getting this error when I run my update statement. Here is what the coding looks like. I'm running Oracle 11g.
CREATE TABLE A (
SUPP_CD_EIM VARCHAR2 (20),
SUPP_CD VARCHAR2(20),
DSN_FAC_CD VARCHAR2(5));
[code]......
I want the output to look like
A1_1234
A2_2345
A3_3456
View 2 Replies
View Related
May 9, 2011
I'm getting the following error in my DB, what would be the reason.
select * from Table_Name where rownum<10
*
ERROR at line 1: ORA-01722: invalid number
and I'm getting this error for every query.
View 4 Replies
View Related
Mar 4, 2010
using the sql statement in SQL server. I noticed some of the sql syntax are different. My sql statement is as below;
---Select fieldA, Sum(fieldB+fieldC) as fieldD, fieldE, fieldF from TableA group by fieldA, fieldE, fieldF ---
the output will be as below;
fieldA fieldD fieldE fieldF
------ ------------- ------ ------
fieldA fieldB+fieldC fieldE fieldF
However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.
View 2 Replies
View Related
Mar 13, 2013
I need to count number of elements in the same catagory of an array.. For example, an array consists of {'a','b','c','c','a','d','c'} means, i need to display like a=2, b=2, c=3, d=1.
I have written the below code.
declare
type array_val is varray(10) of varchar2(15000);
counter number:=0;
SMQ_NAME ARRAY_VAL:=ARRAY_VAL();
begin
[code]....
But its not showing exact output as my requirement..
View 6 Replies
View Related
Jun 21, 2010
To count the number of rows to the below table entries with certain conditions
my table
col1(varchar2(4000)) col2(varchar2(10))
3-1808116101 3-Standard
3-1808312141 3-Standard
3-1808312061 3-Standard
3-1807714441 2-Significant
3-1808284201 2-Significant
3-1808278361 2-Significant
3-1808284111 3-Standard
3-1807714261 3-Standard
3-1807128371 3-Standard
3-1807128351 3-Standard
3-1808301721 2-Significant
3-1808301701 3-Standard
3-1808322501 2-Significant
where conditions:
1)need to take the count of rows(col1).where col2 value is 3 or 2 .
2)value 3 or 2 is extracted and conditioned in the query
View 13 Replies
View Related
Nov 1, 2010
Earlier, we had a discussion how to generate the permutations. generating the code for combinations
For eg: if I have input as [1,2,3,4,5] then 5c2 = 20 combinations should be generated as pairs
1,2
1,3
1,4
1,5
2,3
2,4
2,5
3,4
3,5
4,5
Total of 10 combinations
View 8 Replies
View Related
Sep 12, 2011
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options
How i can get all possible combinations of a given value using SQL or PL/SQL
for example:
1234 - input value
4321
2314
2143
3412
and so on
View 2 Replies
View Related
Mar 9, 2007
INSERT INTO t_category m(
m.service_id,
m.customer_id)
SELECT
u.service_id,
(SELECT p.add_data
FROM t_add p
WHERE
p.service_id=u.service_id AND p.add_type='CUSTOMER_ID')
FROM t_iservice u
I got this error: ORA-01722: invalid number. The problem is m.customer_id has data type NUMBER, p.add_data is VARCHAR.
View 1 Replies
View Related
Jul 26, 2011
I have a problem when executing the statement below querying a specific ID# in view
select * from VW_MML_LTR_MSTR where LTR_ID = 26 order by CLNT_CDE ,LTR_GRP, ltr_purp_id, LTR_CDE
I checked the data type acquired by the view and the table involved and confirmed that it was of number type.Weird thing for me is that I changed the where statement as
where LTR_ID LIKE 26
where LTR_ID != 25
Is this on the database settings? What should be done here?
View 9 Replies
View Related
Jul 6, 2012
Am getting invalid number error for the query ..
select ROUND( ( SUBSTR(' m1058,1672|1090,1672|1090,1716|1058,1716 x e', 2, instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e',',',1,1)- 2)
+ SUBSTR(' m1058,1672|1090,1672|1090,1716|1058,1716 x e', instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e','|',1,2)+1, instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e',',',1,3) - (instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e','|',1,2)+1))
)/ 2) AS x from dual;
View 3 Replies
View Related
Jul 27, 2011
can I install/use Oracle 10g Enterprise Manager Grid Control if my organization has only Oracle 10g Standard Edition Licences.
View 2 Replies
View Related
Jun 28, 2013
i have two tables one as test_master and other one limit master.
test master table structure
ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure
limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --
ct_id and limit_id is match column used for mapping.
now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.
how to achieve the same using sql/oracle query.
View 7 Replies
View Related
Jun 13, 2011
I want to know that whether we can write a query to add total number of count of each table.
for example:
select count(*) from emp;
count(*)
14
select count(*) from emp_1;
count(*)
15
select count(*) from emp_2;
count(*)
16
My question is that is it possible to add this all the three results and get the value as 45.
View 3 Replies
View Related
Oct 30, 2013
We are having this problem of entering duplicated inventory parts in the system which physically refers to the same part. Need to create a select query to search through the list of inventory parts to find out existing parts with similar names.
Requirement is to have a search on a specific character set and it should list even records with 50% of the characters they have entered. Characters can be anywhere in the part name with any case.
For example: input of 'abcd1234' should return the parts with the following name... A nut 234 R49SE B12 A22Hub 156 65 a2 But not... abc 89998AK 47Xbox 360Mauser K98b
View 11 Replies
View Related
Jun 10, 2013
I have a pkg with a procedure that uses dbms_sql to process a varchar2_table. Each record in the table is a delimited string, such as "Priority^2", or "Destination^7". The goal is to split that string on the hat and update a record in the panelfield_users table. It works perfectly if I replace the first substr/instr on :pColumn with a hardcoded number, so I know that the binding is working to that point. It is only when I try to get the latter half of the string that it chokes. I keep getting a "invalid number" error. The displayorder field is an integer field and all values will be 3 digits or less. If I pull the sql string out into an editor, it runs just lovely.
The pertinent code is:
C := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(C,'update panelfield_users set displayorder = to_number(substr(:pColumn, instr(:pColumn, ''^'') + 1)) where userid = :pUID and fieldid = (select fieldid from panel_fields where upper(panelname) = upper(:pPanel) and upper(modulename) = upper(:pModule) and upper(field_displayname) = upper(substr(:pColumn, 1, instr(:pColumn, ''^'')-1)) )
[code]...
View 4 Replies
View Related
Oct 8, 2012
I have to ask a pretty basic question (my pl/sql skills need serious work). I need to validate that a string can be converted to a number, and if it can't be, substitute something else. This works:orcl> create or replace function jw_to_number(str varchar2) return number is
2 n number;
3 begin
4 n := to_number(str);
5 return n;
6 exception when others then return 0;
7 end;
8 /
Function created.
orcl> select jw_to_number('a') from dual;
JW_TO_NUMBER('A')
-----------------
0
orcl>
but of course I don't want to use WHEN OTHERS. So I tried this:orcl> create or replace function jw_to_number(str varchar2) return number is
2 n number;
3 begin
4 n := to_number(str);
5 return n;
6 exception when INVALID_NUMBER then return 0;
7 end;
8 /
Function created.
orcl> select jw_to_number('a') from dual;
select jw_to_number('a') from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SCOTT.JW_TO_NUMBER", line 4
orcl>I
thought this would replace the standard error handler with my own, but it doesn't. I tried redeclaring the pre-defined exception, same result.
View 4 Replies
View Related
Oct 15, 2012
REVNUMBER is a VARCHAR2. I've set it to increment by letter in alphabet to note the revision of a document, i.e. IR*, A, B, C... . This works beautifully.
VERNUMBER is a VARCHAR2. I've set it to increment by number but starts from null to 'IR' to 1, 2, 3...
Until now, I've been incrementing VERNUMBER without problem if, instead of IR as the first incremented value I leave the field null. The user has asked that IR display. I've tried using IR as the default but I still need to increment from there so I don't see it making a difference between that or dumping it as the first condition of the following. The problem is I get an invalid number error either way.
*'IR' = 'Initial Release'
SELECT CASE
WHEN VERNUMBER IS NULL THEN 'IR'
WHEN VERNUMBER = 'IR' THEN '1'
ELSE REGEXP_REPLACE(VERNUMBER, '[0-9]+$', REGEXP_SUBSTR(UPPER(VERNUMBER), '[0-9]+$') + 1)
END NEW_VERSION
FROM DOC_INFO
WHERE DOC_INFO_ID = :P3_DOC_INFO_ID
View 4 Replies
View Related
May 10, 2007
I have been asked to show the names and room number according to students who have 4 classes in a room.
select s.name, r.room_no
from s, r
where r.room_no = u.room_no
and e.unit_code = u.unit_code
GROUP BY s.name, r.room_no
HAVING COUNT(DISTINCT s.stu_no) > 4;
However I get the error 'ERROR at line 4:
ORA-00904: "U"."UNIT_CODE": invalid identifier'
View 2 Replies
View Related
Aug 2, 2007
I have a string like this:
s_list varchar2(234) :=
'asdasd
asfsdf
dsfsdfs
dfsdfs';
How can I find the number of lines in this string? I tried using
INSTR('s_list', '
', 1, 1)
but it gives 0.
Is there any inbuilt function/proc SQL or PL/SQL which can do this?
View 2 Replies
View Related
Aug 31, 2011
I need writing sql which can return the Count of Comma's in a string. Here is my table and data
CREATE TABLE TEST1(SNO NUMBER,STR1 VARCHAR2(30));
INSERT INTO TEST1 VALUES(1234,'ABCD,LL LT,MP');
INSERT INTO TEST1 VALUES(1456,'PP MR');
INSERT INTO TEST1 VALUES(1589,NULL);
INSERT INTO TEST1 VALUES(1897,'PP MR,FTR CLR ON');
Here is the output I am expecting
SNO STR1 STR1_COUNT
1234 ABCD,LL LT,MP 3
1456 PP MR 1
1589 0
1897 PP MR,FTR CLR ON 2
Basically I need to the count of Words separated by comma
View 9 Replies
View Related
