Need a query

Database
Enthusiast

Need a query

INPUT 

  Tab1                                               Tab2

ID Name                                          ID  Name

1   a                                                1   a

2  b                                                 2  b

3 c                                                  5  e

4 d                                                  6  f

OUTPUT :

 Tab3 

Id Name 

3  c

4  d

5  e

6  f

8 REPLIES
Enthusiast

Re: Need a query

union two outer joins

Enthusiast

Re: Need a query

Hi,

This is the query you are looking for :

SELECT * FROM (SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM STORE

union

SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM STORE1)B WHERE B.EMPLOYEE_ID NOT IN

( SELECT C.EMPLOYEE_ID FROM STORE AS C INNER JOIN STORE1 AS D ON C.EMPLOYEE_ID=D.EMPLOYEE_ID) ORDER BY 1,2

Enthusiast

Re: Need a query

very Thanks for you respone ...

Thanks 

harish

Enthusiast

Re: Need a query

Hi Experts,

 

 Write a query for this ?

 

custid      productname      qty

-------     ------------    ----

c100     prodcabc         3

c100     prodcabc         4

c100         prodcabc      5

 

oupt:

---

custid     product-QTY1     PRODUCT-QTY2       PRODUCT-QTY3

----------  ---------               --------------              ------------

C100L      3                                  4                         5

Enthusiast

Re: Need a query

Generate rownumber and use Max(Case when rownumber=1 then qty) as qty 1.

But do you know what is the max number of rows for a custid?

Re: Need a query

Try this!!

create volatile table store(cust_id varchar(6), productname varchar(10), qty byteint)on commit preserve rows;

insert into store('c100','prodcabc',3);

insert into store('c100','prodcabc',4);

insert into store('c100','prodcabc',5);

sel cust_id,

max(case when rnk=1 then qty end) as product_QTY1,

max(case when rnk=2 then qty end) as product_QTY2,

max(case when rnk=3 then qty end) as product_QTY3

from 

(sel cust_id,productname,qty,  row_number() over (partition by cust_id order by qty) as rnk from store) a

group by 1

Enthusiast

Re: Need a query

It is (A-B) union (B-A). You can write it many ways

Enthusiast

Re: Need a query

Hi Experts.

Need a query for this :

Input :                                                  

code           market

Na

statewide

Na

statewide

outpt

code          market

na               1

statewide     2

na               1

statewide     2

Thanks,

harish.