How to include or create NULL values to a query

• team.bernard

  Old Hand

  Points: 300

  More actions

  May 21, 2017 at 4:06 am

  #330608

  Hello,

  This capture is a part of my database :
  

  orders and orders_done have the same structure, same for orders_result and  orders_done_result.

  This is an extract of some values :
  

  And this is my current query ;
  select ISNULL(orders.order_id, orders_done.order_id) AS order_id,
ISNULL(orders_result.reference_res,orders_done_result.reference_res) AS reference_res,
ans.answer
from orders_result
full outer join orders_done_result on orders_done.order_id = orders.order_id
LEFT OUTER JOIN ans_list_lines ans ON cast(ans.unique_id as varchar(255)) = ISNULL(orders_result.result_value,orders_done_result.result_value)
where
(orders.order_id in ('SUG21','SUG22') and (orders_result.reference_res LIKE '%_ship%' or orders_result.reference_res LIKE '%_send_email%' or orders_result.reference_res LIKE '%_send_invoice%'))
or
(orders_done.order_id in ('SUG21','SUG22') and (orders_done_result.reference_res LIKE '%_ship%' or orders_done_result.reference_res LIKE '%_send_email%' or orders_done_result.reference_res LIKE '%_send_invoice%'))

  I want to include the red part, to have any details about both orders.

  How can I create NULL values ?

  Regards

• Thom A

  SSC Guru

  Points: 99133

  More actions

  May 22, 2017 at 4:16 am

  #1943511

  Considering that you didn't provide any DDL or DLM, this entire thing is guesswork. Personally, however, I'd strongly consider your database design. For example, why are there two tables for your orders, one for done and one not; this could easily be shown using an additional column. (f your columns (reference_res, result_value) in orders_result are always going to be NULL, then they don't need to exist in the table either.

  Your data doesn't even seem to match, as iun your result set you have details of orders SOG21 and SOG22, however, the data in your tables is SUG21 and SUG22. Not sure if these were meant to be replaced in your final dataset, but not something I have done.

  As i said, this is total guesswork based on the limited data I have, but perhaps something like this:
  USE DevDB;
GO

  CREATE TABLE orders (order_id char(5));
GO

  CREATE TABLE orders_result
  (order_id char(5),
  reference_res varchar(20) DEFAULT NULL,
  result_value bit DEFAULT NULL);
GO

  CREATE TABLE ans_list_lines
  (unique_id int,
  answer varchar(3));
GO

  CREATE TABLE orders_done_result
  (order_id char(5),
  reference_res varchar(20),
  result_value bit);
GO

  CREATE TABLE orders_done (order_id char(5));
GO

  INSERT INTO orders VALUES ('SUG21');
GO
INSERT INTO orders_result (order_id)
VALUES ('SUG21'),('SUG21'),('SUG21');
GO
INSERT INTO ans_list_lines
VALUES (0,'YES'),(1,'NO');
GO
INSERT INTO orders_done_result
VALUES
  ('SUG22','A_shipped',0),
  ('SUG22','B_send_email',0),
  ('SUG22','C_send_invoice',1);
GO
INSERT INTO orders_done VALUES ('SUG22');
GO

  WITH orderslist as (
  SELECT *
  FROM orders_result
  WHERE order_id IN ('SUG21','SUG22')
  UNION ALL
  SELECT *
  FROM orders_done_result
  WHERE order_id IN ('SUG21','SUG22')
  AND (reference_res LIKE '%shipped' OR reference_res LIKE '%send_email' OR reference_res LIKE '%send_invoice')
)
SELECT ol.order_id, ol.reference_res, al.answer
FROM orderslist ol
    LEFT JOIN ans_list_lines al on ol.result_value = al.unique_id;
GO
--cleanup
DROP TABLE orders;
DROP TABLE orders_result;
DROP TABLE ans_list_lines;
DROP TABLE orders_done_result;
DROP TABLE orders_done;
GO

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 22, 2017 at 4:49 am

  #1943515

  In addition to Thom's points:

   

  Your query references table [orders] which doesn't existin the FROM list.

   

  Full outer join between [orders_result] and[orders_done_result] is probably nonsense. It would make more sense to leftjoin these two tables to an [orders] table.

   

  Your WHERE clause invalidates the outer joins - they areconverted into inner joins.

   

  This - cast(ans.unique_id as varchar(255)) - will destroyperformance.

   

  Build your query gradually, ensuring results are correct foreach incremental change before moving on to the next.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden