Solved

pu a 'y' or an 'n' in column based on query results.

Posted on 2007-03-27
8
208 Views
Last Modified: 2010-04-30
i need to place a 'Y' or a 'N' in a column field after running a query to obtain a serial number based on an order number.

if the query returns results then place a 'Y' in the field, if the query doesn't return anything, place a 'N' in the field.


0
Comment
Question by:itortu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 67

Assisted Solution

by:sirbounty
sirbounty earned 500 total points
ID: 18799912
You should be able to use IIF
IIF(rs!FieldName<>Null,"Y","N")
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 18800975
You can also do it in your select command
0
 

Author Comment

by:itortu
ID: 18801354
if i want to do it in the select command, can this be done in sql server and not in the vb code?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 67

Expert Comment

by:sirbounty
ID: 18801398
Yes, either - if you need help formulating it, post the sql string you're using now...
0
 

Author Comment

by:itortu
ID: 18801599
i am pasting my query. what i need help with is to figure out how to concatenate an "*" (forget the 'y' or 'n')
to the ord.line_no column if the join

LEFT JOIN bhuserial_number snm  ON ( ord.order_id = snm.order_id AND ord.client = snm.client)

returns any results.


SELECT 2 level ,  
       ord.order_id,  
       ord.client,  
       ord.line_no,
       ord.article,  
       ord.article,  
       ord.art_descr,  
       COALESCE ( hj.rev_val , ord.rev_val ) rev_val ,  
       COALESCE ( hj.reserved_val , ord.reserved_val ) reserved_val ,  
       COALESCE ( hj.vow_val , COALESCE ( SUM ( dis.value_1 ) , 0 ) ) vow_val ,  
       hj.status ,  
       MAX ( dis.deliv_date ) dispatch_date ,  
       -1 item_owner ,  
       ' ' stock_location,  
       ' ' return_action ,  
       ' ' item_class,
       snm.serial_number AS serial_number  
FROM asodetail ord
LEFT JOIN algrelvalue fly ON ( ord.article_id = fly.article_id AND
                               ord.client = fly.client AND
                               fly.rel_attr_id = 'V7' )  
LEFT JOIN bhjpackageviewhighjump hj ON ( ord.order_id = hj.order_id AND
                                         ord.client = hj.client AND
                                         ord.line_no = hj.line_no AND
                                         hj.user_id = ' ' AND
                                         hj.job_id = 84303)
LEFT JOIN algudispatch dis ON ( ord.order_id = dis.order_id AND
                                ord.client = dis.client AND
                                ord.line_no = dis.line_no AND
                                ord.article_id = dis.article_id AND
                                dis.status = 4 )
LEFT JOIN bhuserial_number snm  ON ( ord.order_id = snm.order_id AND
                                     ord.client = snm.client)
WHERE ((COALESCE(CASE WHEN fly.rel_value = ' ' THEN 'NA' ELSE fly.rel_value END , 'NA' ) = 'NA' AND
      ord.kit_type = '2') OR (
      ord.kit_type = '0' ) ) AND
      ord.order_id = @order_id AND
      ord.client = @client          
GROUP BY ord.order_id , ord.client , ord.line_no ,  ord.article, ord.art_descr , hj.rev_val , ord.rev_val , hj.reserved_val , ord.reserved_val , hj.vow_val , hj.status , ord.kit_type, snm.serial_number  

0
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
ID: 18801666
Should be simply:
SELECT 2 level ,  
       ord.order_id,  
       ord.client,  
       Iif(ord.line_no=Null,'N','Y') As [Ord Results]
....
0
 

Author Comment

by:itortu
ID: 18801841
what i need to check for 0 not null is for the srial number
some order will have serial numbers, some order won't
0
 

Author Comment

by:itortu
ID: 18801992
in the query below, i  am getting asterisks for orders that don't have a serial number when i should be getting 'unknown'

SELECT 1 level ,  
       ord.order_id ,  
       ord.client ,  
       ord.line_no ,  
       ord.article ,  
       ord.article physical_article ,  
       ord.art_descr ,  
       ord.rev_val ,  
       0 reserved_val ,  
       ord.vow_val vow_val ,  
       ' ' status ,  
       ' ' dispatch_date ,  
       -1 item_owner ,  
       ' ' stock_location ,  
       ' ' return_action ,  
       ' ' item_class,
       (CASE WHEN(
                  SELECT count(*)
                  FROM bhuserial_number snm
                  LEFT JOIN asodetail ord ON (snm.order_id = ord.order_id AND snm.client = ord.client)
                  WHERE ord.order_id = snm.order_id AND ord.client = snm.client) <> 0 THEN '*'
             ELSE 'unknown'
             END
       )AS serial_number
FROM asodetail ord  
WHERE ord.order_id = 897354  
AND ord.client = 'HC'
AND ord.order_id NOT IN ( SELECT order_id FROM bretheader WHERE client = 'HC' )
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question