asked on

query problems

on my query, i am adding a column with an asterix in it if the order id does not have a serial number.
my problem is that the query is adding the asterix for orders that don't have serial numbers (it never goes to unknown)
could someone tell me wher my error might be?

thank you.

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,
(CASE WHEN(
SELECT count(*)
FROM bhuserial_number snm WITH (NOLOCK)
LEFT JOIN asodetail ord ON (snm.order_id = ord.order_id AND snm.client = ord.client AND snm.line_number = ord.line_no)
) != 0 THEN '*'
ELSE 'unknown'
END
)AS serial_number

knightEknight

just curious ... what happens when you reverse them?

) = 0 THEN 'unknown' -- changed != to =
ELSE '*'

knightEknight

is it null instead of 0 ?

(CASE WHEN isNull((
SELECT count(*)
FROM bhuserial_number snm WITH (NOLOCK)
LEFT JOIN asodetail ord ON (snm.order_id = ord.order_id AND snm.client = ord.client AND snm.line_number = ord.line_no), 0
) = 0 THEN 'unknown' -- changed != to =
ELSE '*'

itortu

ASKER

your query returns:

Line 22: Incorrect syntax near '0'.

SOLUTION

ksaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

knightEknight

sorry, I left out a paran

itortu

ASKER

got it to work:

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,
ord.line_no + (CASE WHEN(
SELECT count(*) cnt
FROM bhuserial_number snm
WHERE snm.order_id = ord.order_id AND snm.client = ord.client and snm.line_number = ord.line_no
) > 0 THEN '*'
ELSE ' '
END) as concatenate
FROM asodetail ord WITH (NOLOCK)
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 )
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 = '5176209' AND
ord.client = 'HC'
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 --serial_number

itortu

ASKER

the problem i have is now here:

ord.line_no + (CASE WHEN(
SELECT count(*) cnt
FROM bhuserial_number snm
WHERE snm.order_id = ord.order_id AND snm.client = ord.client and snm.line_number = ord.line_no
) > 0 THEN '*'
ELSE ' '
END) as concatenate

Syntax error converting the varchar value '*' to a column of data type int.

is there any way i can solved this problem, converting the * to int data type or the other way around?

ASKER CERTIFIED SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial