[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2007-03-27
8
Medium Priority
?
212 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
  • 4
  • 3
8 Comments
 
LVL 67

Assisted Solution

by:sirbounty
sirbounty earned 1500 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
Suggested Courses

640 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