May 4, 2011 at 3:22 am
Newly modified
Hi,
Table-1
create table vijay_mas (slno int,item varchar(10))
insert into vijay_mas values (1,'pen')
Table-2
create table vijay (slno int, item varchar(10),lang_id int)
insert into vijay values (1,'pen',1)
Creating a view
create view vijay_viw as
select
m.slno,
m.item,
s.lang_id
from vijay_mas m right outer join vijay s
on(m.slno=s.slno
and m.item=s.item)
these are the tables and the views
Query outputs:
For this query:
select * from vijay_viw where lang_id=1
It gives--
slno item lang_id
1 pen 1
But for this :
select * from vijay_viw where lang_id=2
it gives no rows
Now what i expects is :
select * from vijay_viw where lang_id=2
The above query has to return :
slno item lang_id
1 pen NULL
How can i join this?
But in one of the sql2000 environment the above query
(select * from vijay_viw where lang_id=2) fetches
slno item lang_id
1 pen NULL
But the view in sql2000 looks like below:
create view vijay_viw as
select
m.slno,
m.item,
s.lang_id
from vijay_mas m , vijay s
where (m.slno *= s.slno
and m.item *= s.item)
Even it also fails in another sql2000 environment. i think there is some databse settings to solve this.
So how can i solve this ?
is there any database related settings ?
Regards
Vijayakumar N
----------------------------------------------------------------------
i have a view (view_lan) which relates two tables. I have an entry for item (s005) in the table-1 (but this table does not have a language_id column ). And have an entry for the same item1 in the table-2 also, but with an laguage_id column with a value of 1.
Now i am trying to fetch the item from the view:
select * from view_lan where language_id=2 (actually table has language_id as 1)
Now i want the view query to return one row with the language_id column NULL
Is there any environmental settings related to this situation?
it really looks challenging for me
Regards
Vijayakumar N
May 4, 2011 at 3:53 am
Take the count first and then return null value if it does not have any value else return the resultset of the query
May 4, 2011 at 3:58 am
i am meaning to say that the query has to fetch the values for the all column in the column list with the language_id as NULL
for ex:
slno item language_id date
1 s005 null 01-01-2011
May 4, 2011 at 4:15 am
Is the below you are looking for?
create Table T1(Col1 int ,Col2 int)
Insert Into T1 Select 1,2
Insert Into T1 Select 2,2
Insert Into T1 Select 2,1
Select Col1,
(Case When (Select COUNT(1) From T1 Where T1.Col1 = A.Col1 and T1.Col2=A.Col2 and Col2=1) = 0 Then Null Else 1 End) From T1 A
However, just wanna understand the purpose....
May 4, 2011 at 4:18 am
select * from view_lan where language_id != 1
try this
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 4, 2011 at 4:37 am
Kindly look at the newly modified post
May 4, 2011 at 4:48 am
Vijy, You have to try the things to understand, we have some limitations always as not dealing with the data...
Anyway, just look at the below and let us know
create table vijay_mas (slno int,item varchar(10))
insert into vijay_mas values (1,'pen')
create table vijay (slno int, item varchar(10),lang_id int)
insert into vijay values (1,'pen',1)
create view vijay_viw as
select
m.slno,
m.item,
s.lang_id
from vijay_mas m right outer join vijay s
on(m.slno=s.slno
and m.item=s.item)
select * from vijay_viw where lang_id=1
select * from vijay_viw where lang_id=2
Select slno,item,
(Case When (Select COUNT(1) From vijay_viw B Where B.slno = A.slno and B.lang_id=A.lang_id and lang_id=2) = 0 Then Null Else 1 End) From vijay_viw A
May 4, 2011 at 5:00 am
As my table has 1000 above entries with 3 various languages, i expects a generic solution to join this, not only for this example table
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply