Friday, November 8, 2013

SQL | Analysis and Query to find items that are not part of input list

I will have a few SQL series blogs and as a WCS developer, I feel it is very important to have good SQL skills.

--Exampple . Find all part numbers that are not found in orders table from the list (DSC_1,DSC_3, DSC_10, DSC_11)  in the XORDERITEMS table below.
Result: DSC_10, DSC_11

--1. Table creation:

CREATE TABLE XORDERITEMS (orders_id NUMBER,partnumber varchar2(255), status varchar2(1)) NOLOGGING;

--2. Data insertion into table
insert into /*+ APPEND */  XORDERITEMS values (1,'DSC_1','P');
insert into /*+ APPEND */  XORDERITEMS values (2,'DSC_2','Y');
insert into /*+ APPEND */  XORDERITEMS values (3,'DSC_3','c');
insert into /*+ APPEND */  XORDERITEMS values (4,'DSC_4','c');
insert into /*+ APPEND */  XORDERITEMS values (5,'DSC_5','c');
insert into /*+ APPEND */  XORDERITEMS values (6,'DSC_4','P');
insert into /*+ APPEND */  XORDERITEMS values (7,'DSC_3','y');
insert into /*+ APPEND */  XORDERITEMS values (8,'DSC_7','c');
insert into /*+ APPEND */  XORDERITEMS values (9,'DSC_8','P');
insert into /*+ APPEND */  XORDERITEMS values (10,'DSC_9','P');

--two alternates to fetch the above result.
--Query-1
select pno partnumber from
(SELECT TRIM(SUBSTR ( partnumber , INSTR (partnumber, ',', 1, level ) + 1 , INSTR (partnumber, ',', 1, level+1 ) - INSTR (partnumber, ',', 1, level) -1)) pno
FROM ( SELECT ','||'DSC_1,DSC_3,DSC_10,DSC_11'||',' AS partnumber FROM dual )
CONNECT BY level <= LENGTH(partnumber)-LENGTH(REPLACE(partnumber,',',''))-1 )
where pno not in (select partnumber from XORDERITEMS);

--Alternate Query
select pno partnumber from
(select 'DSC_1' pno from dual
union
select 'DSC_3' pno from dual
union
select 'DSC_10' pno from dual
union
select 'DSC_11' pno from dual)
where pno not in (select partnumber from XORDERITEMS);


1 comment:

  1. It turned out outrageous at that time any s / s sport activity follow this marketed for your equal cost as the check out via some other designs. The actual parallel concerning the van and watch is actually fun, additionally, the quantities tend to be effectively an identical for each. The fact is that, the actual fabulous interpretation of any observe which was blisteringly swiftly, along with seriously fantastic, met the country dying. A designer watches surely failed to! It was which means that attractive and additionally exquisite. This was a wristwatch meant to get a good iconic manifestation for the make. Even while everyone assumed a watch was gorgeous, just a small number of individuals extremely perceived the significance associated with her style and design, along with the tremendous firm powering it again. Easily ahead so that you can 2015. that pieces have style as part of your prior to when and ceramic could be the trendiest product round. The market is usually filled with different watches created from cosmetic, steel and altered all steel metal. Your designer watches for comes with tried using distinct components and additionally for example others lives in ceramic for most people applications nonetheless thoroughly ceramic wrist watches continue to be strange. Since introduction of the extremely first follow, they have got assembled upon the first composition creating quite a few varieties and comes to an end the fact that swells their particular reach to the current market whilst keeping correct therefore to their genuine vision- a luxury sports activity watch for an exceptionally discerning consumer http://www.replicabestsale.co.uk. Some form of observe has become grown so that you can contain a tourbillion, some sort of chronograph, a good perpetual date, your remarkable end result, and much more!. You can even find a observe entirely coated within diamonds any time that is definitely your own point. Whether it is in your preference or even never, any follow is obviously able to make an effort anything innovative, bold as well as bold. These are two opposites, I love that see that is located 48mm diverse, nonetheless sadly Ariel actually reviewed this, then i ca possibly not. Moreover, I am able to merely suppose the band available a break within the majority regarding the fact that was these days, with regards to present necklaces in the marketplace. Let alone, when you have found that necklace modified the right way, it must be relaxing concerning very nearly any arm.

    ReplyDelete