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?
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

777 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