Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-03-27
8
Medium Priority
?
211 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 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
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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

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 …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

730 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