Tag: oracle

How to calculate ratio using sql query?

I have a table like below: ID Name Department Gender 1 Crib MA MALE 2 Lucy Bsc FEMALE 3 Phil Bcom MALE 4 Ane MA FEMALE I have 1000 row of records like this. I want to find the ratio from column Gender( MALE & FEMALE) of all students. I need a query to perform […]

Can I “join” across datasources in SSRS?

I’ve got two datasources, one Oracle and one Sql Server. Due to circumstances that predate me (as in it was like this when I found it) some columns in the Oracle database contain PKs from lookup tables in the Sql Server database. I am attempting to create a Sql Server Reporting Services report that will […]

Converting a pivot table to flat table in Oracle SQL

Consider the following table: I am using the query below (in SQL Server) to convert this table to a flat table as follows: I would like to the same thing using Oracle SQL. However the query does not work in “Oracle SQL” language: cross apply, which is used below, does not work in Oracle. Any […]

What options are available for connecting to a Microsoft SQL Server database from an Oracle database?

At the moment I pull data from remote MS SQL Server databases using custom-built JDBC connectors. This works fine but doesn’t feel like the way to do it. I feel I should be able to put a JDBC connection string into tnsnames on the server and have it “just work”. I’ve looked around a little […]

SQL – performance in varchar vs. int

I have a table which has a primary key with varchar data type. And another table with foreign key as varchar datatype. I am making a join statement using this pair of varchar datatype. Though I am dealing with few number of rows (say hunderd rows), it is taking 60ms. But when the system will […]

Foreach Loop Container For Data Flow Task

I want to import nearly 12 million records from oracle DB but beacuse of the SSIS memory buffer problem i need to iterate my Data Flow Task for each year i.e 2005 to 2012 (7 times). How can i use foreach my oracle query to fetch the data year wise. Query SELECT * FROM EMP_RECORDS_DETAILS […]

When are nulls “safe” in a column?

Is there a general rule of thumb for designing databases to allow the columns as nulls vs 3nf normalization? I have a table with a column that will mostly consist of nulls (85%) but the table size is not over 10K records (not very big). It’s mostly for logging and record keeping so most transactions […]

connecting to multiple databases using different jdbc drivers

I have a requirement to write a daemon based java process (Not web based) that will connect to an Oracle 10G database, read some data from it and then connect to a SQL Server database and write the data to a table. Sounds simple enough but i have a couple of queries about this. i […]

How do I design a table which will store very large data?

I need to design a table in Oracle, which will store 2-5 TB of data in a day. It can grow to 200TB, and records will purged when it crosses 200 TB. Is it a feasible choice to keep it in OLTP, or do I need to shift it to data warehouse DB? Please advice […]

Get hash values from SQL Server and Oracle and compare them?

Is it possible to generate hash code from both database server and compare them? How to write the following pseudo SQL in SQL Server? Especially the two getHash functions which accept mutliple numeric/float columns in SQL server and oracle. select s.PK from sqltable s join openquery(oracleLinkedServer, ‘select PK, getHash(Column1, floatColumn2, …, floatColumnN) oracleHash from oracleTable’) […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.