pu a 'y' or an 'n' in column based on query results.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
EDDYKT
You can also do it in your select command
ASKER
if i want to do it in the select command, can this be done in sql server and not in the vb code?
Yes, either - if you need help formulating it, post the sql string you're using now...
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what i need to check for 0 not null is for the srial number
some order will have serial numbers, some order won't
some order will have serial numbers, some order won't
ASKER
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' )
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' )