Problem with the view
-
I have a problem with a view, my view is used to rerieve data from another db.
when i run this query:
SELECT a,b, c FROM myView
IT will get the data successfully, third column is nullable but still i have populated it.
But i run this query:
SELECT a,b, c FROM myView
WHERE c LIKE 'm%'
instead of eliminating those values which don't begin with 'm' it retrieves a null for the third coulmn, so the number of rows remain same except that null is returned in 'column 3' for those which do not meet the like criteria.
What could be the reason?
nomi
-
Can you please post the definition for the view?
(my guess is that there is an outer join in there..?)
/Kenneth
-
You are right there is an outer join, Here is the definition of the view:
SELECT
1 'company_code',
RM2.CUSTNMBR 'customer_code',
RM2.ADRSCODE 'ship_to_code',
RM1.CUSTNAME 'address_name',
RM1.SHRTNAME 'short_name',
RM2.ADDRESS1 'addr1' ,
RM2.ADDRESS2 'addr2' ,
RM2.ADDRESS3 'addr3' ,
RM2.COUNTRY 'addr4' ,
RM2.CITY 'addr5' ,
RM2.STATE 'addr6' ,
'' 'addr_sort1' ,
'' 'addr_sort2' ,
'' 'addr_sort3' ,
0 'address_type',
0 'status_type',
RM2.CNTCPRSN 'attention_name' ,
RM2.PHONE1 'attention_phone' ,
RM2.CNTCPRSN 'contact_name' ,
RM2.PHONE1 'contact_phone' ,
'' 'tlx_twx' ,
RM2.PHONE2 'phone_1' ,
RM2.PHONE3 'phone_2' ,
RM2.TAXSCHID 'tax_code' ,
RM1.PYMTRMID 'terms_code' ,
'' 'fob_code' ,
'' 'freight_code' ,
'' 'posting_code' ,
'' 'location_code' ,
'' 'alt_location_code' ,
RM2.ZIP 'dest_zone_code' ,
RM2.ZIP 'territory_code' ,
RM2.SLPRSNID 'salesperson_code' ,
FNCHATYP 'fin_chg_code' ,
'' 'price_code' ,
'' 'payment_code' ,
'' 'vendor_code' ,
'' 'affiliated_cust_code' ,
0 'print_stmt_flag' ,
'' 'stmt_cycle_code' ,
'' 'inv_comment_code' ,
'' 'stmt_comment_code' ,
'' 'dunn_message_code' ,
COMMENT1 'comment',
CUSTDISC 'trade_disc_percent' ,
0 'invoice_copies' ,
0 'iv_substitution' ,
0 'ship_to_history' ,
0 'check_credit_limit' ,
MXWROFAM 'credit_limit' ,
0 'check_aging_limit' ,
0 'aging_limit_bracket' ,
0 'bal_fwd_flag' ,
0 'ship_complete_flag' ,
'' 'resale_num' ,
'' 'db_num' ,
0 'db_date' ,
'' 'db_credit_rating' ,
0 'late_chg_type' ,
0 'valid_payer_flag' ,
0 'valid_soldto_flag' ,
0 'valid_shipto_flag' ,
'' 'payer_soldto_rel_code' ,
0 'across_na_flag' ,
0 'date_opened' ,
RM2.ADRSCODE 'site_id',
'' 'rate_type_home' ,
'' 'rate_type_oper' ,
0 'limit_by_home' ,
CURNCYID 'nat_cur_code' ,
0 'one_cur_cust'
FROM two..RM00101 RM1 ,two..RM00102 RM2
where RM1.CUSTNMBR =* RM2.CUSTNMBR
-
i am having the problem with this column: 'address_name'
-
Ok... First, I recommend to rewrite this legacy syntax as ANSI instead.
Change
FROM two..RM00101 RM1 ,two..RM00102 RM2
where RM1.CUSTNMBR =* RM2.CUSTNMBR
to:
FROM two..RM00101 RM1
RIGHT JOIN two..RM00102 RM2
ON RM1.CUSTNMBR = RM2.CUSTNMBR
The old leagcy syntax is not suitable for outer joins, and should really be avoided totally. Writing in ANSI syntax is clearer and you don't risk getting false results (which indeed may happing in the 'old' way)
Anyway, regarding your problem, thinking about it, I belive that this ild syntax may also be the cause of that. Try rewriting it and see what happens. When dealing with outer joins and filtering it becomes very important where the filter is applied - in the ON clause or the WHERE clause - it depends on the desired output AND if the filter is applied to the inner or the outer table.
/Kenneth
-
kenneth
u have hit the nail on the head, it works, thanks a lot for ur help
nomi
-
Another thing you could do is :
WHERE ISNULL(address_name, '') LIKE 'M%'.
This would also eliminate NULL values
Good Hunting!AJ Ahrens
webmaster@kritter.net -
AJ ,
your soultion doesn't work.
nomi
-
Hi
When you use right join ou left join , it´s possible that you have resust set with null values.
To prevent this, youn have to way :
Use isnull function in the name of field on select clause.
Use isnull in where clause. ( i.e. : compare the field ( field is null ) ) not use isnull function
Hildevan O Bezerra
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply