Solved

query problems

Posted on 2007-03-27
8
224 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

11 Experts available now in Live!

Get 1:1 Help Now