Tag: oracle

Joining oracle with mssql table

When i execute following line i get the result: select * from my_table@link; But when i try to join linked table with other tables like in select i get error that says “z.id – invalid identifier” SELECT e.USER_NAME,e.FULL_NAME,r.RFC_NUMBER,r.TYPE, TO_CHAR(TRUNC(a.TOTAL_TIME/3600),’FM9900′) || ‘:’ || TO_CHAR(TRUNC(MOD(a.TOTAL_TIME,3600)/60),’FM00′) || ‘:’ || TO_CHAR(MOD(a.TOTAL_TIME,60),’FM00′) as TOTAL_TIME, a.ASSIGN_DATE,a.TIME_START,a.TIME_STOP, r.SUBMITTED_BY,r.REGISTER_DATE,r.DESCRIPTION FROM table_a a JOIN […]

SSIS and sending query with date to Oracle

I am trying to create a flow to pull data from an Oracle table into a SQL Server table. I am sending the following query to Oracle to get the data: select distinct CHLD.id, nvl(chld_c_spl, ‘N’), to_char(chld_d_start, ‘YYYY-MM-DD’) chld_d_start, to_char(chld_d_end, ‘YYYY-MM-DD’) chld_d_end from child chld, picture ptct where CHLD.id = PTCT.chld_id and nvl(chld_d_end, sysdate) >= […]

Update from aggregate in same table if aggregate value wrong – SQL Server/Oracle/Firebird

I have a table with grouped tasks: tt_plan_task_id is the id records with tt_plantype=1 represent ‘groups’ tasks in/under a group have a tt_group_id pointing to the tt_plan_task_id there are tasks that don’t belong to a group (tt_group_id is null) groups nest multiple levels I need to fix (update) the tt_fromdate field values for the group […]

Oracle to SQL Server Migration issue – On delete cascade

Folks, Iam working on Oracle to MS Sql Server migraiton. One of the issue I came across is that “ON DELETE CASCADE” works fine in Oracle, but not in Sql Server. Is there any alternative to the syntax/command or is it something we need to manually remove/fix it. Could not get a valid link/reference on […]

Is there any common function for formatting date in SQL Server and Oracle?

I am building an application and need to format the datetime field to dd/mm. I was using TO_CHAR in Oracle and which is not supported in SQL Server and got error when it is deployed with SQL Server database. Is the any common function which can be used across different databases for formatting the datetime […]

Heterogeneous Oracle to SQL Server: Using “IS NULL” in Where clause for Update statement

I am having trouble with using the IS NULL condition in an Update statement executed on an SQL Server 2000 database via an Oracle Database ( using an ODBC heterogeneous connection. Example of update statement that will not work. UPDATE TABLENAME@RemoteSQLServer2000 SET “ColumnName” = ‘SomeValue’ WHERE “AnotherColumnName” IS NULL; Result: ORA-02070: database RemoteSQLServer2000 does not […]

Assign unique ID's to three tables in SELECT query, ID's should not overlap

I am working on SQL Sever and I want to assign unique Id’s to rows being pulled from those three tables, but the id’s should not overlap. Let’s say, Table one contains cars data, table two contains house data, table three contains city data. I want to pull all this data into a single table […]

SQL Server statement throws ErrorCode 208

I am developing an SQL query, which is compatible for Oracle SQL and SQL Server. I have the following working Oracle SQL code, which generates the top 15 consumers which bought products by Visa: SELECT ddo.code as “Data Owner”, ROUND((count(ffp.id) / (select count(ffp2.id) from Payments_Table ffp2 join PAY_CHARACTERISTICS dmc2 on ffp2.characteristics_id = dmc2.id where dmc2.visa_flag = […]

How to fetch query execution statistics using Oracle DB?

I am new to database. I try to run a simple query on SQL Server 2014 and Oracle 12c. This is the execution plan I get using SQL Server. It contains information about I/O cost and CPU cost in seconds. However I can’t find the same information using Oracle. The CPU cost shown in the […]

Oracle and SQL Server – different results when comparing date to null

I have following query in JPQL: select * from Record rec where rec.validFrom <= :param and (rec.validTo is null or rec.validTo >= :param) I know, that there exists only one record in database. If I run it with param = null, on Oracle I get the record back. However if I run it on a […]

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