How to know oracle client bit version in linux whether 32 bit or 64 bit

How to know oracle client bit version in linux?

*For this you need oracle user

$ cd $ORACLE_HOME/bin

$ file sqlplus

sqlplus: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

Rewrite correlated subqueries in cognos compatible way. Decorrelating subqueries

Correlated subquery scenario 1

SELECT c.LastName, c.FirstName,
(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName
FROM Customer c

Rewrite to

SELECT c.LastName, c.FirstName, cm.CompanyName
FROM Customer c LEFT OUTER JOIN Company cm
ON c.CompanyID = cm.CompanyID



Correlated subquery scenario 2

select *

from T1

where T1.a > (select max(T2.a) from T2 where T2.b = T1.b)

Rewrite to

select T1.*

from T1, (select T2.b, max(T2.a) max_a from T2 group by T2.b) S

where T1.b = S.b and T1.a > S.max_a

xming can't connect to x11 window server using ---- xming error putty

Problem :

xming can't connect to x11 window server using


In run command, enter command in following format:

"C:\Program Files (x86)\Xming\Xming.exe" :0 -clipboard -multiwindow -ac

Providing option -ac has resolved my error.
Note: Installation path might be different in your machine.

Sub query subquery in Cognos Framework Manager

The only way to implement subquery in Cognos Framework Manager can be done by directly writing in Query Subject Definition or writin in Parameter map and then use it in Query Subject Definition (for reusability)

How to deal with queries with EXISTS clause in Cognos?

• How to deal with queries with EXISTS clause in Cognos?
  Here describes how to rewrite SQL queries containing EXISTS clause to Cognos compatible way using MINUS & INTERSECT.

  
  

  
  

  Original query:

  SELECT tc_lpn_id

    FROM lpn LP

    WHERE lpn_facility_status IN (45, 50, 55, 64)

    AND NOT EXISTS

      (SELECT 1

      FROM task_dtl TD

      WHERE TD.cntr_nbr = lp.tc_lpn_id

      AND TD.stat_code  < 90

      )

    AND EXISTS

      (SELECT 1

      FROM alloc_invn_dtl AD

      WHERE AD.cntr_nbr = lp.tc_lpn_id

      AND AD.stat_code  < 90

      )

  
  

  Modified for Cognos:

  SELECT tc_lpn_id FROM lpn LP WHERE lpn_facility_status IN (45, 50, 55, 64)

  minus

  select tc_lpn_id from lpn l, task_dtl t where t.cntr_nbr = l.tc_lpn_id and t.stat_code < 90

  intersect

  select tc_lpn_id from lpn l, alloc_invn_dtl a where a.cntr_nbr = l.tc_lpn_id and a.stat_code < 90

  
  

  
  

  Note:

  While rewriting queries, Intersect might eliminates duplicates. To avoid that ensure that correlated join condition column of parent is selected in select clause. This will overcome the problem.

  
  

  
  

  
  

  
  

Over (partition by ) query in Cognos Report Studio

If we need cognos to generate analytical function over(partiontion by):

Type	Expression	Cognos query generated
Over() – without partition	total([Planning & Execution].[Shipment].[Shipment ID] for  report)	Fig1
Over (partition by column1,column2,..)	total([Planning & Execution].[Shipment].[Shipment ID] for [Shipment Distance UOM], [Shipment Created Source],[Shipment Last Updated Source])	Fig2

Fig1

Fig2

Note: Set query Auto Group & Summarize option to No

Cognos split crosstab corner cell Report Studio

Below steps will show you how to split Crosstab corner cell (to add title).

1. Drag a text item from toolbox to the columns list, place it as left most column - Type required name

2. Same way drag text items as many as required

3. Select crosstab corner cell and change box type property as "None".

4. Select Column Body of all newly added text items and then change box type property as "None"

Finally you got the titles as you wanted!!!