?
Solved

query problems

Posted on 2007-03-27
8
Medium Priority
?
268 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

762 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