The Specified Store provider cannot be found in the configuration or is not valid - entity framework

Check your machine.config to see if any providers mentioned in the <DbProviderFactories> section. If not, add the required provider to this section.

This solved mine.

Extracting values from an XML string using SQL query in oracle database

There are two methods you can use for extracting values from XML string (a column in a table). The column type will be XMLType.

These methods are EXTRACT(columnName, xpath). This returns list of nodes, also a record if extracting with a unique filter value.
You can also use ExtractValue(columnName, xpath) - This will work only for a single node, not for a collection of nodes. This way you can retrieve value from a unique record.


See examples below:

<Person>
    <Name>Test</Name>
    <Address>
        <Street Number=1345>Monroe St</Street>
        <City>TestCity</City>
        <Zip>12345</Zip>
    </Address>
</Person>

Assume this xml data in column
You can extract the street number attribute value using the query as below:

This can return multiple records:
select extract(columnName,'/Person/Address/Street/@Number') as StreetNumber from Table1;

For returning single record:
select extract(columnName,'/Person/Address/Street/@Number') as StreetNumber from Table1 where ID = 200;

Also For returning single record only:
select extractvalue(columnName, '/Person/Address/Street/@Number') as StreetNumber from Table1 where ID = 200;

I have used this and works like a charm.
Hope this finds useful to you also.
Njoyy...

Query to Find second highest/Nth highest salary in SQL Server and Oracle

SQL Sever
========
select top 1 a.* from (
select top 4 salary from table1 order by salary DESC ) a order by a.salary ASC;

Now in generic terms... Lets say for Nth salary.

select top 1 a.* from (
select top N salary from table1 order by salary DESC ) a order by a.salary ASC;

Just replace N with the number you want to find the salary. (N = 1 for first, N =2 for second, N= 3 for third.. so on..).

Oracle
======
select * from (
select tb1.*, rownum Rnum from (
select * from table1 order by salary DESC) tb1 where rownum <= 2) where Rnum >= 2;

Now to make it generic.. Lets say for Nth salary.

select * from (
select tb1.*, rownum Rnum from (
select * from table1 order by salary DESC) tb1 where rownum <= N) where Rnum >= N;

Just replace N with the number you want to find the salary. (N = 1 for first, N =2 for second, N= 3 for third.. so on..).

How to write a left outer join in LINQ?

This can be accomplished by using DefaultIfEmpty(). See the example LINQ query below:

var provQuery = (from a in entities.Table1
                join c in entities.Table2 on a.ID equals c.ID into temppvcon
                from t2 in temppvcon.DefaultIfEmpty()
                where a.ID == Id
                select new { a.ID, a.NAME, t2.ID, t2.NAME });

Check out the SQL generated from the above LINQ query:

SELECT
1 AS "C1",
"Extent1"."ID" AS "ID",
"Extent1"."NAME" AS "NAME",
"Extent2"."ID" AS "ID",
"Extent2"."NAME" AS "NAME"
FROM  (SELECT
      "TABLE1"."ID" AS "ID",
      "TABLE1"."CON_ID" AS "CON_ID",
      "TABLE1"."NAME" AS "NAME",
      "TABLE1"."ADDED_DT" AS "ADDED_DT"
      FROM "DB"."TABLE1" "TABLE1") "Extent1"
LEFT OUTER JOIN (SELECT
      "TABEL2"."NAME" AS "NAME",
      "TABEL2"."PART" AS "PART",
      "TABEL2"."ID" AS "ID",
      "TABEL2"."CLUSTER_ID" AS "CLUSTER_ID"
      FROM "CDCDATA"."TABEL2" "TABEL2") "Extent2" ON "Extent1"."ID" = "Extent2"."ID"
WHERE ("Extent1"."ID" = :p__linq__0)

Hope this is clear. Enjoy...

Column Names in upper case for Oracle subquery

Oracle is case-insensitive for table and column names in queries but the data dictionary records names in uppercase.

check out this example below:

select User_Number, User_Description from (
select user_id as "User_Number", User_name as "User_Description" from users_tbl);

The above query returns error as below:

ORA-00904: "USER_DESCRIPTION": invalid identifier
00904. 00000 -  "%s: invalid identifier"

Corrected query:

select USER_NUMBER, USER_DECSRIPTION from (
select user_id as "USER_NUMBER", User_name as "USER_DECSRIPTION" from users_tbl);

Same parameters used multiples times in Oracle Query

If we use same parameters multiple times in a query in oracle as shown in example, it errors out. We need to create another set of variables with same values.


Check the usage of parameters in the below example:
:fromDate
:toDate
:uname


:fromDate1
:toDate1
:uname1



:fromDate2
:toDate2
:uname2


All the above paramaters are having the same values.

For example:-

select user_name,call_reason from call_tbl, users_tbl where call_placed_dt between :fromdate and :toDate

and user_name = :uname order by user_description

FULL JOIN

select user_name,call_reason from call_mrs_tbl, users_tbl where call_placed_dt between :fromdate1 and :toDate1

and user_name = :uname1 order by user_description

FULL JOIN

select user_name,call_reason from call_tbl, users_tbl where call_placed_dt between :fromdate2 and :toDate2

and user_name = :uname2 order by user_description;

We cannot use the above query as below. It errors out saying invalid datatype.

select user_name,call_reason from call_tbl, users_tbl where call_placed_dt between :fromdate and :toDate

and user_name = :uname order by user_description

FULL JOIN

select user_name,call_reason from call_mrs_tbl, users_tbl where call_placed_dt between :fromdate and :toDate
and user_name = :uname order by user_description

FULL JOIN

select user_name,call_reason from call_tbl, users_tbl where call_placed_dt between :fromdate and :toDate
and user_name = :uname order by user_description;

Order of parameters matters in oracle query

Faced lots of time to figure out why my oracle query was erroring out. It was the order of the dynamic parameters in the query should match the order we pass the parameter with values.

For example:-
select user_name,call_reason from call_tbl, users_tbl where call_placed_dt between :fromdate and :toDate
and user_name = :uname order by user_description;

Here you can see three parameters :fromDate, :toDate & :uname. These parameters needs to be passed from C# using command object in the following order in which it is used in the query, which is as below:
1. :fromDate
2. :toDate
3. :uname

I was passing the parameters in the following order:


1. :uname
2. :fromDate
3. :toDate


Took me a while to figure out. These are some tips which can be used if you stumble across such error again, you need not waste much time to figure out.

Hope this helps others.