[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

query problems

Posted on 2007-03-27
8
Medium Priority
?
272 Views
Last Modified: 2011-09-20
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
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 33

Expert Comment

by:knightEknight
ID: 18802293
just curious ... what happens when you reverse them?

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

Expert Comment

by:knightEknight
ID: 18802311
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 '*'

0
 

Author Comment

by:itortu
ID: 18802406
your query returns:

Line 22: Incorrect syntax near '0'.
0
Independent Software Vendors: 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 10

Assisted Solution

by:ksaul
ksaul earned 400 total points
ID: 18802425
I think you want to put the left join in the main query so that you return all rows in asodetal and only matching rows (based on the join you provided).  Rows in asodetal without matching records in bhuserial_number will have null values for all fields.

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 snm.order_id IS NULL
            THEN '*'
            ELSE 'unknown'
            END AS serial_number
FROM asodetal ord
LEFT JOIN bhuserial_number snm WITH (NOLOCK)
      ON (snm.order_id = ord.order_id AND snm.client = ord.client AND snm.line_number = ord.line_no)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18802429
sorry, I left out a paran
0
 

Author Comment

by:itortu
ID: 18802511
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  
0
 

Author Comment

by:itortu
ID: 18802532
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?


0
 
LVL 33

Accepted Solution

by:
knightEknight earned 600 total points
ID: 18802564
 ord.line_no + (CASE WHEN(

is it trying to add it as an int?

  convert(varchar,ord.line_no) + (CASE WHEN(
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

650 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