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

Posted on 2007-03-27
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.

Question by:itortu
  • 4
  • 3
LVL 67

Assisted Solution

sirbounty earned 500 total points
ID: 18799912
You should be able to use IIF
LVL 26

Expert Comment

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

Author Comment

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?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 67

Expert Comment

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

Author Comment

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 ,  
       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  

LVL 67

Accepted Solution

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

Author Comment

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

Author Comment

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'
       )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' )

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

828 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