Solved

query problems

Posted on 2007-03-27
8
236 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
  • 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
 
LVL 10

Assisted Solution

by:ksaul
ksaul earned 100 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 150 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now