?
Solved

SQL Query help

Posted on 2012-08-19
47
Medium Priority
?
474 Views
Last Modified: 2012-09-07
hi Everyone,

I have a stored Procedure which works as coded but I need to add some functionality to it.  Basically, what the stored proc does is, you specify via a parameter a final assembly item #.  The stored proc looks at that, and returns all sub components of that end item.  Essentially, a bill of material is what is returned.  

What I need to do is add code to the stored procedure.  For each item reported as part of the BOM, I want to look at all open/oustanding sales orders in the system and it's sub components to see if the same sub component exists in that given sales order and keep tally of the oustanding required quantities for said sub component and report in a additional column that is returned.  

See attached the data that is returned via the current stored procedure.  the highlighted column is the column I want to add.

Below is the stored procedure as it currently stands.

;with id as (select x.*
                  ,ROW_NUMBER() over (order by [Name],[LineItemName]) as rn
               from vwItems as x)
   ,cte as
 (select [Name],[LineItemName],0 as lvl,0 as depth
    ,convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord, LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
   from id
   where [Name]=@ParentItem
  union  all
  select b.[Name],b.[LineItemName],lvl+1 As LVL, depth+1 As Depth
        ,a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':', LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
   from cte as a
   inner join id as b
     on b.[Name]=a.[LineItemName]
   )  

select Items, depth, lvl, sortord, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand --* --items
  from (
select replicate('-',depth+1)+[LineItemName] as Items,sortord,'B' as type, depth as depth, lvl as lvl, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
 from cte
 union all
 select [Name],sortord,'A', lvl, depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
   from (
 select  [Name],sortord
       ,ROW_NUMBER() over (PARTITION by [Name]
               order by sortord) as rn, lvl as lvl, depth As Depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
 from cte
 where depth=0
 ) as y
 where rn=1
 ) as x
 
order by [type],sortord
DataExample.xlsx
0
Comment
Question by:jwebster77
  • 25
  • 16
  • 6
47 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38310629
Will need to know where to get the information to "look at all open/oustanding sales orders in the system" and how it is related to the data in the current query.
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38311415
Few questions before I could help you with your query:
1.> What is the correlation between [name] and [lineitemname] in vwItems view? Does it mean that [name] is the name item and [lineitemname] is subcomponent of that item and so on?
2.> Your excel have all junk values for first column Items so unable to understand it. Does same lineitemname could be there for multiple items in view vwItems?
3.> How all open/oustanding sales orders are stored in dB. Could you please send definition of all those tables that hold all open/oustanding sales orders?
4.> Could you please provide definition of view vwItems?
0
 

Author Comment

by:jwebster77
ID: 38312267
Table structure for sales order (outstanding sales orders)

ItemName
QtyOnOrder
Oustanding (Boolean, True False.  If True, then need to consider in query)

1.  Yes, LIneItemName is the subcomponent of Name

2.  Yes, lineitemname can appear multiple times in a BOM

3.  See above.

4.  Sure, I will post vwitems in a seperate post
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:jwebster77
ID: 38312322
vwitems has additional data in respects to lineitemname:

SELECT     dbo.Items.Description, dbo.Items.QuantityOnHand, dbo.Items.QuantityOnOrder, dbo.Items.QuantityOnSalesOrder, dbo.ItemLineItems.Name,
                      dbo.ItemLineItems.LineItemName, dbo.ItemLineItems.LineItemQuantity, dbo.Items.UnitOfMeasure
FROM         dbo.ItemLineItems INNER JOIN
                      dbo.Items ON dbo.ItemLineItems.LineItemName = dbo.Items.Name
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38314607
In the view 'vwItems' ... there is the column 'dbo.Items.QuantityOnSalesOrder' ... does this contain the value you want or is it something unrelated?  
or...
Does a query like:
    SELECT ItemName, SUM(QtyOnOrder) as totalQtyOnOrder
    FROM sales_orders
    WHERE Oustanding = 'True'
need to be joined into the main query?
and if so...
What does ItemName from sales_order (if that is the table name) join to in the main query ... is it 'LineItemName'?
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38314789
In your query, could you fetch LineItemName more and make its join with SalesOrder table like this?

select Items, depth, lvl, sortord, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand, y.QtyOnOrder
from (
      select [LineItemName],replicate('-',depth+1)+[LineItemName] as Items,sortord,'B' as type, depth as depth, lvl as lvl, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
      from cte
      union all
      select [LineItemName],[Name],sortord,'A', lvl, depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
      from (
            select [LineItemName],[Name],sortord
            ,ROW_NUMBER() over (PARTITION by [Name]      order by sortord) as rn, lvl as lvl, depth As Depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from cte
            where depth=0
      ) as y
      where rn=1
) as x
      left join (select ItemName, QtyOnOrder from SalesOrder where Outstanding = 'True') y
      on x.[LineItemName] = y.ItemName
order by [type],sortord
0
 

Author Comment

by:jwebster77
ID: 38316582
Hi, I am not sure what you mean.  Could you show me the code imbedded in tihe Stored Proc adn I will try it?
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38319080
I just tried to make join of your query with SalesOrder table on LineItemName column. If you could send me code of your stored procedure and view vwItems, I think I should be able to help you better.
0
 

Author Comment

by:jwebster77
ID: 38320658
For a given sales order and it's referenced item, I need to see if LIneItemName is a subcomponent of the item referenced and if it is, reflect that in the outstanding quantity column which does not exist yet.

Here is my stored proc:

;with id as (select x.*
                  ,ROW_NUMBER() over (order by [Name],[LineItemName]) as rn
               from vwItems as x)
   ,cte as
 (select [Name],[LineItemName],0 as lvl,0 as depth
    ,convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord, LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
   from id
   where [Name]=@ParentItem
  union  all
  select b.[Name],b.[LineItemName],lvl+1 As LVL, depth+1 As Depth
        ,a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':', LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
   from cte as a
   inner join id as b
     on b.[Name]=a.[LineItemName]
   )  

select Items, depth, lvl, sortord, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand --* --items
  from (
select replicate('-',depth+1)+[LineItemName] as Items,sortord,'B' as type, depth as depth, lvl as lvl, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
 from cte
 union all
 select [Name],sortord,'A', lvl, depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
   from (
 select  [Name],sortord
       ,ROW_NUMBER() over (PARTITION by [Name]
               order by sortord) as rn, lvl as lvl, depth As Depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
 from cte
 where depth=0
 ) as y
 where rn=1
 ) as x
 
order by [type],sortord

Here is my view:


SELECT     dbo.Items.Description, dbo.Items.QuantityOnHand, dbo.Items.QuantityOnOrder, dbo.Items.QuantityOnSalesOrder, dbo.ItemLineItems.Name,
                      dbo.ItemLineItems.LineItemName, dbo.ItemLineItems.LineItemQuantity, dbo.Items.UnitOfMeasure
FROM         dbo.ItemLineItems INNER JOIN
                      dbo.Items ON dbo.ItemLineItems.LineItemName = dbo.Items.Name
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38320718
Did you see my questions in #a38314607 above?
0
 

Author Comment

by:jwebster77
ID: 38322482
Just saw it, no it does not.  And the reason is because it only contains something greater than a zero if it is a end assembly item.  Basically, it has to be specifically spelled out on the Sales Order...  I am trying to get quantities and need of the sub components that go into said end assemblies.
0
 

Author Comment

by:jwebster77
ID: 38323382
Can anyone help me!  I woudl appreciate it!
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38323447
I am trying to help.  But I am reading over and over this and still have trouble understanding the relationship between the 'sales order' table and the sub components.
In my questions above (which you only answered the first one) I asked how a query like
    SELECT ItemName, SUM(QtyOnOrder) as totalQtyOnOrder
    FROM sales_orders
    WHERE Oustanding = 'True'
could be joined into the larger SQL you provided.  
The SQL just above will sum the QtyOnOrder for each ItemName.
Your larger SQL has LineItemName and Name as columns and I do not know which, if either, the ItemName in sales order is related to.  It may need to be joined by some other field.
I do not even know the actual name of the sales order table.
If this is information that you have already provided - I am sorry to be asking again - but I just cannot find it.
0
 

Author Comment

by:jwebster77
ID: 38324742
Let me try to best explain this again.  

1.  There are sales orders loaded in the system, but the sales orders only depect the top level assembly part #.  The sub components of a top level assembly are not referenced on the sales order.  

2.  For any given item defined in the system, it could potentially be a sub component of many different top level assemblies.

3.  Name and LineItemName relation - Basically, Name is the main assembly and lineitemname is the subassembly.  I will illustrate via an example.

This is what the indented bom would look like

US9000
  ABC123
  CC2323
  773777
     87363
     87386
  73763
  83773
     73736

The Data in the database would look like this

NAME                  LINEITEM
US9000                US9000
US9000                ABC123
US9000                CC2323
US9000                773777
773777                87363
773777                87386
US9000               73763
US9000               83773
83773                 73736

My End Goal:  The current stored procedure I have spits out all of the subcomponents when you specify a item #.  For every item # listed as a sub-component, I want to look at all of the open sales orders and determine if the sub component exists on any open sales orders and identify as such by adding the required qty to a column which does not exist yet.  If the item # exists as a subcomponent on multiple sales orders, then I want the qtyonsalesorder to add all together for one total on the line for said subcomponent.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38324806
So join on LineItemName = sales_order.ItemName
Try:
;with id as 
(select x.*
      , ROW_NUMBER() over (order by [Name],[LineItemName]) as rn
   from vwItems as x)
,cte as 
(select [Name]
      , [LineItemName]
      , 0 as lvl
      , 0 as depth
      , convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
   from id 
  where [Name]=@ParentItem
 union  all
 select b.[Name]
      , b.[LineItemName]
      , lvl+1 As LVL
      , depth+1 As Depth
      , a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':'
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
   from cte as a
  inner join id as b
     on b.[Name]=a.[LineItemName]
)  
select Items
     , depth
     , lvl
     , sortord
     , LineItemQty As LineItemQty
     , QtyOnHand As QtyOnHand --* --items 
     , totalQtyOnOrder
  from (select replicate('-',depth+1)+[LineItemName] as Items
             , sortord
             , 'B' as type
             , depth as depth
             , lvl as lvl
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , [LineItemName]
          from cte
        union all
        select [Name]
             , sortord
             , 'A'
             , lvl
             , depth
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , [LineItemName]
          from (select [Name]
                     , sortord
                     , ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn
                     , lvl as lvl
                     , depth As Depth
                     , LineItemQty As LineItemQty
                     , QtyOnHand As QtyOnHand
                     , [LineItemName]
                  from cte 
                 where depth=0 ) as y
         where rn=1 ) as x
  left join (SELECT ItemName, SUM(QtyOnOrder) as totalQtyOnOrder
               FROM sales_orders
              WHERE Oustanding = 'True') as z
         on x.[LineItemName] = z.ItemName
order by [type],sortord

Open in new window

0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38324848
You could take 2 assumptions
1.> Say there is only one entry for each ItemName in SALESORDER table

With this assumption, to check whether LineItemName is a subcomponent of the item referenced in the SalesOrder table, following is the query

select q.[LineItemName], p.[QtyOnOrder], p.[Outstanding]
from dbo.SALESORDERS p inner join dbo.LineItems q on p.ItemName = q.[Name]

With this assumption, following could your query in stored procedure

declare @ParentItem varchar(100)
;with id as (select x.*, ROW_NUMBER() over (order by [Name],[LineItemName]) as rn from vwItems as x)
,cte as (select [Name],[LineItemName],0 as lvl,0 as depth,convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord, LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from id
                  where [Name]=@ParentItem
                  union  all
                  select b.[Name],b.[LineItemName],lvl+1 As LVL, depth+1 As Depth,a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':', LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from cte as a
                        inner join id as b
                        on b.[Name]=a.[LineItemName]
            )  

select Items, depth, lvl, sortord, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand,
            (select p.[QtyOnOrder]
                  from dbo.SALESORDERS p inner join dbo.LineItems q on p.ItemName = q.[Name]
                  where p.[Outstanding] = 'true' and q.[LineItemName] = x.[LineItemName] and x.[type] = 'B')
from (select [LineItemName], replicate('-',depth+1)+[LineItemName] as Items,sortord,'B' as type, depth as depth, lvl as lvl, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from cte
            union all
            select [Name] 'LineItemName', [Name],sortord,'A', lvl, depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from (
                        select  [Name],sortord,ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn, lvl as lvl, depth As Depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
                        from cte
                        where depth=0
                  ) as y
            where rn=1
) as x
order by [type],sortord
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38324907
Another assumption
Say there are multiple entries for each ItemName in SALESORDER table

With this assumption, to check whether LineItemName is a subcomponent of the item referenced in the SalesOrder table, following is the query
select q.[LineItemName], p.[TotQtyOnOrder]
from (select sum([QtyOnOrder]) TotQtyOnOrder, [ItemName]
            from dbo.SALESORDERS
            where [Outstanding] = 'true'
            group by [ItemName]) p inner join dbo.LineItems q on p.ItemName = q.[Name]


In that case the overall query should look like
declare @ParentItem varchar(100)
;with id as (select x.*, ROW_NUMBER() over (order by [Name],[LineItemName]) as rn from vwItems as x)
,cte as (select [Name],[LineItemName],0 as lvl,0 as depth,convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord, LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from id
                  where [Name]=@ParentItem
                  union  all
                  select b.[Name],b.[LineItemName],lvl+1 As LVL, depth+1 As Depth,a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':', LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from cte as a
                        inner join id as b
                        on b.[Name]=a.[LineItemName]
            )  

select Items, depth, lvl, sortord, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand,
            (select p.[TotQtyOnOrder]
            from (select sum([QtyOnOrder]) TotQtyOnOrder, [ItemName]
                        from dbo.SALESORDERS
                        where [Outstanding] = 'true'
                        group by [ItemName]) p inner join dbo.LineItems q on p.ItemName = q.[Name] and q.[LineItemName] = x.[LineItemName] and x.[type] = 'B')
from (select [LineItemName], replicate('-',depth+1)+[LineItemName] as Items,sortord,'B' as type, depth as depth, lvl as lvl, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from cte
            union all
            select [Name] 'LineItemName', [Name],sortord,'A', lvl, depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from (
                        select  [Name],sortord,ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn, lvl as lvl, depth As Depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
                        from cte
                        where depth=0
                  ) as y
            where rn=1
) as x
order by [type],sortord

Does it help?
0
 

Author Comment

by:jwebster77
ID: 38335051
Hi Satchitjain, I tried to run it and I got:  Msg 512, Level 16, State 1, Procedure BOMTEST, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)

Here is the stored procedure I have right now...

;with id as (select x.*, ROW_NUMBER() over (order by [Name],[LineItemName]) as rn from vwItems as x)
,cte as (select [Name],[LineItemName],0 as lvl,0 as depth,convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord, LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from id
                  where [Name]=@ParentItem
                  union  all
                  select b.[Name],b.[LineItemName],lvl+1 As LVL, depth+1 As Depth,a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':', LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from cte as a
                        inner join id as b
                        on b.[Name]=a.[LineItemName]
            )  

select Items, depth, lvl, sortord, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand,
            (select p.[TotQtyOnOrder]
            from (select sum([ItemQuantity]) TotQtyOnOrder, [ItemName]
                        from dbo.tblSalesOrders
                        group by [ItemName]) p inner join dbo.ItemLineItems q on p.ItemName = q.[Name] and q.[LineItemName] = x.[LineItemName] and x.[type] = 'B')
from (select [LineItemName], replicate('-',depth+1)+[LineItemName] as Items,sortord,'B' as type, depth as depth, lvl as lvl, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from cte
            union all
            select [Name] 'LineItemName', [Name],sortord,'A', lvl, depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from (
                        select  [Name],sortord,ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn, lvl as lvl, depth As Depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
                        from cte
                        where depth=0
                  ) as y
            where rn=1
) as x
order by [type],sortord
END
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38335418
did you try my attempt?  Although from your last post I was finally able to discover the same of the sales order table as being tblSalesOrders ... amended SQL below.
;with id as 
(select x.*
      , ROW_NUMBER() over (order by [Name],[LineItemName]) as rn
   from vwItems as x)
,cte as 
(select [Name]
      , [LineItemName]
      , 0 as lvl
      , 0 as depth
      , convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
   from id 
  where [Name]=@ParentItem
 union  all
 select b.[Name]
      , b.[LineItemName]
      , lvl+1 As LVL
      , depth+1 As Depth
      , a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':'
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
   from cte as a
  inner join id as b
     on b.[Name]=a.[LineItemName]
)  
select Items
     , depth
     , lvl
     , sortord
     , LineItemQty As LineItemQty
     , QtyOnHand As QtyOnHand --* --items 
     , totalQtyOnOrder
  from (select replicate('-',depth+1)+[LineItemName] as Items
             , sortord
             , 'B' as type
             , depth as depth
             , lvl as lvl
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , [LineItemName]
          from cte
        union all
        select [Name]
             , sortord
             , 'A'
             , lvl
             , depth
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , [LineItemName]
          from (select [Name]
                     , sortord
                     , ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn
                     , lvl as lvl
                     , depth As Depth
                     , LineItemQty As LineItemQty
                     , QtyOnHand As QtyOnHand
                     , [Name] [LineItemName]
                  from cte 
                 where depth=0 ) as y
         where rn=1 ) as x
  left join (SELECT ItemName, SUM(QtyOnOrder) as totalQtyOnOrder
               FROM tblSalesOrders
              WHERE Oustanding = 'True') as z
         on x.[LineItemName] = z.ItemName
order by [type],sortord

Open in new window

0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38335706
Bit of correction, try this now

;with id as (select x.*, ROW_NUMBER() over (order by [Name],[LineItemName]) as rn from vwItems as x)
,cte as (select [Name],[LineItemName],0 as lvl,0 as depth,convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord, LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from id
                  where [Name]=@ParentItem
                  union  all
                  select b.[Name],b.[LineItemName],lvl+1 As LVL, depth+1 As Depth,a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':', LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from cte as a
                        inner join id as b
                        on b.[Name]=a.[LineItemName]
            )  

select Items, depth, lvl, sortord, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand,
            (select p.[TotQtyOnOrder]
            from (select sum([ItemQuantity]) TotQtyOnOrder, [ItemName]
                        from dbo.tblSalesOrders
                        group by [ItemName]) p inner join
                                          (select distinct [name], [lineitemname] from dbo.ItemLineItems) q on p.ItemName = q.[Name] and q.[LineItemName] = x.[LineItemName] and x.[type] = 'B')
from (select [LineItemName], replicate('-',depth+1)+[LineItemName] as Items,sortord,'B' as type, depth as depth, lvl as lvl, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from cte
            union all
            select [Name] 'LineItemName', [Name],sortord,'A', lvl, depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from (
                        select  [Name],sortord,ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn, lvl as lvl, depth As Depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
                        from cte
                        where depth=0
                  ) as y
            where rn=1
) as x
order by [type],sortord
END
0
 

Author Comment

by:jwebster77
ID: 38342658
sachitjain,

Still same message... Here is SP:

;with id as (select x.*, ROW_NUMBER() over (order by [Name],[LineItemName]) as rn from vwItems as x)
,cte as (select [Name],[LineItemName],0 as lvl,0 as depth,convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord, LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from id
                  where [Name]=@ParentItem
                  union  all
                  select b.[Name],b.[LineItemName],lvl+1 As LVL, depth+1 As Depth,a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':', LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
                  from cte as a
                        inner join id as b
                        on b.[Name]=a.[LineItemName]
            )  

select Items, depth, lvl, sortord, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand,
            (select p.[TotQtyOnOrder]
            from (select sum([ItemQuantity]) TotQtyOnOrder, [ItemName]
                        from dbo.tblSalesOrders
                        group by [ItemName]) p inner join
                                          (select distinct [name], [lineitemname] from dbo.ItemLineItems) q on p.ItemName = q.[Name] and q.[LineItemName] = x.[LineItemName] and x.[type] = 'B')
from (select [LineItemName], replicate('-',depth+1)+[LineItemName] as Items,sortord,'B' as type, depth as depth, lvl as lvl, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from cte
            union all
            select [Name] 'LineItemName', [Name],sortord,'A', lvl, depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
            from (
                        select  [Name],sortord,ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn, lvl as lvl, depth As Depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
                        from cte
                        where depth=0
                  ) as y
            where rn=1
) as x
order by [type],sortord
END
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38343557
Can anyone help me!  I woudl appreciate it!
I'm trying ... but I am getting no feedback.
0
 

Author Comment

by:jwebster77
ID: 38343958
Hey ladwell,  I tried to use your code and got this message:  Column 'tblSalesOrders.ItemName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I got this message when creating the stored procedure in sql server.  The Code is as follows:  

(Just took out the where clause on the tblsalesorder query and changed the qty field name.)

-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
CREATE PROCEDURE BOMTEST1
      -- Add the parameters for the stored procedure here
      @ParentItem varchar(200)
AS
BEGIN
      ;with id as
(select x.*
      , ROW_NUMBER() over (order by [Name],[LineItemName]) as rn
   from vwItems as x)
,cte as
(select [Name]
      , [LineItemName]
      , 0 as lvl
      , 0 as depth
      , convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
   from id
  where [Name]=@ParentItem
 union  all
 select b.[Name]
      , b.[LineItemName]
      , lvl+1 As LVL
      , depth+1 As Depth
      , a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':'
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
   from cte as a
  inner join id as b
     on b.[Name]=a.[LineItemName]
)  
select Items
     , depth
     , lvl
     , sortord
     , LineItemQty As LineItemQty
     , QtyOnHand As QtyOnHand --* --items
     , totalQtyOnOrder
  from (select replicate('-',depth+1)+[LineItemName] as Items
             , sortord
             , 'B' as type
             , depth as depth
             , lvl as lvl
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , [LineItemName]
          from cte
        union all
        select [Name]
             , sortord
             , 'A'
             , lvl
             , depth
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , [LineItemName]
          from (select [Name]
                     , sortord
                     , ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn
                     , lvl as lvl
                     , depth As Depth
                     , LineItemQty As LineItemQty
                     , QtyOnHand As QtyOnHand
                     , [Name] [LineItemName]
                  from cte
                 where depth=0 ) as y
         where rn=1 ) as x
  left join (SELECT ItemName, SUM(ItemQuantity) as totalQtyOnOrder
               FROM tblSalesOrders) as z
         on x.[LineItemName] = z.ItemName
order by [type],sortord
END
GO
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38343970
added the GROUP BY
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
CREATE PROCEDURE BOMTEST1
      -- Add the parameters for the stored procedure here
      @ParentItem varchar(200)
AS
BEGIN
      ;with id as 
(select x.*
      , ROW_NUMBER() over (order by [Name],[LineItemName]) as rn
   from vwItems as x)
,cte as 
(select [Name]
      , [LineItemName]
      , 0 as lvl
      , 0 as depth
      , convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
   from id 
  where [Name]=@ParentItem
 union  all
 select b.[Name]
      , b.[LineItemName]
      , lvl+1 As LVL
      , depth+1 As Depth
      , a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':'
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
   from cte as a
  inner join id as b
     on b.[Name]=a.[LineItemName]
)  
select Items
     , depth
     , lvl
     , sortord
     , LineItemQty As LineItemQty
     , QtyOnHand As QtyOnHand --* --items 
     , totalQtyOnOrder
  from (select replicate('-',depth+1)+[LineItemName] as Items
             , sortord
             , 'B' as type
             , depth as depth
             , lvl as lvl
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , [LineItemName]
          from cte
        union all
        select [Name]
             , sortord
             , 'A'
             , lvl
             , depth
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , [LineItemName]
          from (select [Name]
                     , sortord
                     , ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn
                     , lvl as lvl
                     , depth As Depth
                     , LineItemQty As LineItemQty
                     , QtyOnHand As QtyOnHand
                     , [Name] [LineItemName]
                  from cte 
                 where depth=0 ) as y
         where rn=1 ) as x
  left join (SELECT ItemName, SUM(ItemQuantity) as totalQtyOnOrder
               FROM tblSalesOrders
              GROUP BY ItemName) as z
         on x.[LineItemName] = z.ItemName
order by [type],sortord
END
GO

Open in new window

0
 

Author Comment

by:jwebster77
ID: 38343991
The query shows me final assembly totals.  Meaning, it shows me anythign that is specifically spelled out on the sales order, but I need to dive in to the final assembly on any open sales order and look at all of the sub components as well.  I need to get total requirements for sub assemblies.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38344006
I thought that (and correct me if I have this wrong) that the original SQL pulled out the sub components and that their identifier is [LineItemName].  I used that name to to join to the [ItemName] in the tblSalesOrders table.  If this is not how to get the quantities for the sub components - then I need to understand how they are related.
0
 

Author Comment

by:jwebster77
ID: 38344075
Ok, see attached.  You will notice, AS9000 and all of the sub components for AS9000.  (AS9000 is referenced in the line item column and the part #'s of teh subcomponents that go into AS9000 are listed in the lineitemname column.

At the bottom of the spreadsheet, I took one of the subcomponents of AS9000 and showed it adn it's subcomponents.
0
 

Author Comment

by:jwebster77
ID: 38344076
File attached here.  example
example.xlsx
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38344081
OK ... I understand that AS9000 has sub-components CE0002, CE0008, ST0009, ST0010 ... etc.  How could I get the quantity from the sales order table for CE0002 say ... would it be:
    SELECT SUM(ItemQuantity) as totalQtyOnOrder
    FROM tblSalesOrders
    WHERE ItemName = 'CE0002'

It is the relationship from the sub-component to the sales order that I am struggling with.
0
 

Author Comment

by:jwebster77
ID: 38346069
I think this will definately help.  I am going to attach a document I scribbled showing the relationship.

 I am going to attach all of the data (minus the non-major columns) for both the ITEM table and ItemLineItems table.  It will show basically the relationship.  With this, you could even go as far as to import into some mock tables in SQL and create a mock sales order table and drive some content against SP.
ItemLineItem-TABLE.xlsx
ITEMS-TABLE.xlsx
SKMBT-C552D12082911190.pdf
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38349254
I have loaded the data provided into my database.  
I can run the original SQL and produce the results that were in your original Excel file.
This gives me the Items table and the ItemLineItem tables only though.

You state I could "create a mock sales order table" ... you have previously provided the definition:
    ItemName
    QtyOnOrder
    Oustanding (Boolean, True False.  If True, then need to consider in query)

But I do not understand how to populate it.  What do I populate ItemName with?  Please provide an example for the AS9000 Item and its sub-components.
0
 

Author Comment

by:jwebster77
ID: 38349734
Don't worry about Outstanding.  We will consider any sales order in the table.  ItemName is populated with the top level sell item.  An example would be:  US9000

So, we iterate through the items for which we performed the search on.  For each item existing under the main assembly we iterate through each open sales order and look at it's item and it's subcomponents underneath.  

By doing this, we can get the required usage of said subcomponents for all open sales orders.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38349772
OK ... lets see if I understand.  
The sales order table (tblSalesOrders) contains rows for the top level item only.  So you want to use the number 'on order' for the top item and multiply by the quantity for the sub-component to determine the 'total quantity' for each sub-component. e.g. If the number on order for US9000 was 5 ... you want something like:
sample reportDo I have this right?
0
 

Author Comment

by:jwebster77
ID: 38349823
Yep!
0
 

Author Comment

by:jwebster77
ID: 38349827
But, I want it to evaluate all sales orders, in the event a sub assembly/component exists on a different top level assembly number
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38350212
To determine the total for sub components on for all sales orders and main items ... it needs to be recursively joined down from the Item through each layer of the sub components ItemLineItems and then SUM() back into to create a new ItemLineItems "view".  I was working on that and while checking the output back to the original vwItems view ... there was a discrepancy in the number of rows returned.  I traced this to the source data you provided ... for CP9007 Name (for example there were others) in the ItemLineItems table there were two rows for CD0001, CP0003, CP0500, HW0015, HW0016, HW0029 and ST0014 LineItemName's.

As I am needing to do a GROUP BY and SUM() ... these multiple rows get rolled into one.  I need to understand if this is an issue or not.

This is SQL so far ... it would become a replacement for vwItems in the orginal SQL.
with recursive_LineItems as (
SELECT ItemLineItems.Name, ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity, ISNULL(QtyOnOrder,0) QtyOnOrder
  FROM Items 
 INNER JOIN ItemLineItems 
         ON ItemLineItems.Name = Items.Name
  LEFT JOIN (SELECT ItemName, SUM(QtyOnOrder) QtyOnOrder FROM tblSalesOrders GROUP BY ItemName)so
         ON Items.Name = so.ItemName
-- WHERE Items.Name in ('US9000','US9001')
UNION ALL
SELECT ItemLineItems.Name, ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity, r.QtyOnOrder
  FROM recursive_LineItems r
 INNER JOIN ItemLineItems 
         ON r.LineItemName = ItemLineItems.Name
), LineItem_w_OrderQty as (
SELECT Name, LineItemName, LineItemQuantity, SUM(QtyOnOrder) as TotQtyOnOrder
  FROM recursive_LineItems
 GROUP BY Name, LineItemName, LineItemQuantity
)
SELECT I.Description, I.QuantityOnHand, I.QuantityOnOrder, I.QuantityOnSalesOrder, I.Name, 
       LI.LineItemName, LI.LineItemQuantity, LI.TotQtyOnOrder
  FROM Items I
 INNER JOIN LineItem_w_OrderQty LI
         ON I.Name = LI.Name

Open in new window

0
 

Author Comment

by:jwebster77
ID: 38351654
I am looking at it now.  I see what you mean.  Looking at where there are two.
0
 

Author Comment

by:jwebster77
ID: 38352097
K, yep.  They would be summed.  Additional requirements were established later and instead of just adding to the pre-existing record they just created an additional entry.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38353023
This is what I managed to come up with.  Quick ... it is not.
with recursive_LineItems as (
SELECT ItemLineItems.Name, ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity, ISNULL(QtyOnOrder,0) QtyOnOrder
  FROM Items 
 INNER JOIN ItemLineItems 
         ON ItemLineItems.Name = Items.Name
  LEFT JOIN (SELECT ItemName, SUM(QtyOnOrder) QtyOnOrder FROM tblSalesOrders GROUP BY ItemName)so
         ON Items.Name = so.ItemName
UNION ALL
SELECT ItemLineItems.Name, ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity, r.QtyOnOrder
  FROM recursive_LineItems r
 INNER JOIN ItemLineItems 
         ON r.LineItemName = ItemLineItems.Name
), LineItem_w_OrderQty as (
SELECT Name, LineItemName, LineItemQuantity, SUM(QtyOnOrder) as TotQtyOnOrder
  FROM recursive_LineItems
 GROUP BY Name, LineItemName, LineItemQuantity
), id as (
SELECT I.Description, I.QuantityOnHand, I.QuantityOnOrder, I.QuantityOnSalesOrder, I.Name, 
       LI.LineItemName, LI.LineItemQuantity, LI.TotQtyOnOrder
     , ROW_NUMBER() over (order by I.Name, LI.LineItemName) as rn
  FROM Items I
 INNER JOIN LineItem_w_OrderQty LI
         ON I.Name = LI.Name
), cte as (
select [Name]
      , [LineItemName]
      , 0 as lvl
      , 0 as depth
      , convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
      , TotQtyOnOrder
   from id 
  where [Name]='US9000'
 union  all
 select b.[Name]
      , b.[LineItemName]
      , lvl+1 As LVL
      , depth+1 As Depth
      , a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':'
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
      , b.TotQtyOnOrder
   from cte as a
  inner join id as b
     on b.[Name]=a.[LineItemName]
)  
select Items
     , depth
     , lvl
     , sortord
     , LineItemQty As LineItemQty
     , QtyOnHand As QtyOnHand --* --items 
     , TotQtyOnOrder
     , LineItemQty * TotQtyOnOrder as FullQtyOnOrder
  from (select replicate('-',depth+1)+[LineItemName] as Items
             , sortord
             , 'B' as type
             , depth as depth
             , lvl as lvl
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , TotQtyOnOrder
          from cte
        union all
        select [Name]
             , sortord
             , 'A'
             , lvl
             , depth
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , TotQtyOnOrder
          from (select [Name]
                     , sortord
                     , ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn
                     , lvl as lvl
                     , depth As Depth
                     , LineItemQty As LineItemQty
                     , QtyOnHand As QtyOnHand
                     , TotQtyOnOrder
                  from cte 
                 where depth=0 ) as y
         where rn=1 ) as x
order by [type],sortord;

Open in new window

0
 

Author Comment

by:jwebster77
ID: 38370475
thanks ladwell.  Can you help me make one minor change, the query outputs something like:

-itemnumber
--itemnumber
---itemnumber

For the purpose of adding the total demand, can we remove the - tags.  still want the query to out put the - tags for now.  But when looking at the overall demand want it to look at the item # without "-", so some type of trim function I am guessing that removes the - tags.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38370524
In the query there is (on one line ... #56 above):
    select replicate('-',depth+1)+[LineItemName] as Items
it is the:
    replicate('-',depth+1)+
bit that adds them ... delete that bit so it just is:
    select [LineItemName] as Items
0
 

Author Comment

by:jwebster77
ID: 38378028
Lwadwell, that removes it from the output.  I don't want to remove it from the output.  When it is tallying the total required components on sales orders (which you did for me), I don't want it ot evaluate based on teh hashes (--)  Just want it to look at the straight item #.s
0
 

Author Comment

by:jwebster77
ID: 38378095
Scratch that.  I don't think that will help...

For example. on the attached spreadsheet, I ran the stored proc for US9000.

Labor - Chop Saw is a item #.  On line 31 the full qty On the Order is:  0  But on line 34 the qty is is 8.

Labor - Chop Saw is the same item # regardless of what it's master item # is.  The totals should be the same.

The query has to be structured in the sense that it looks at all of the sales orders and gives me a total for each subcomponent that will be required if we were to have to fill all of the orders at once.  So, wherever Labor - Chop Saw is, that needs to be considered.
0
 

Author Comment

by:jwebster77
ID: 38378097
Here is the attachment.
BOM.xlsx
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38378440
try this
with recursive_LineItems as (
SELECT ItemLineItems.Name, ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity, ISNULL(QtyOnOrder,0) QtyOnOrder
  FROM Items 
 INNER JOIN ItemLineItems 
         ON ItemLineItems.Name = Items.Name
  LEFT JOIN (SELECT ItemName, SUM(QtyOnOrder) QtyOnOrder FROM tblSalesOrders GROUP BY ItemName)so
         ON Items.Name = so.ItemName
UNION ALL
SELECT ItemLineItems.Name, ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity, r.QtyOnOrder
  FROM recursive_LineItems r
 INNER JOIN ItemLineItems 
         ON r.LineItemName = ItemLineItems.Name
), LineItem_w_OrderQty as (
SELECT LineItemName, SUM(LineItemQuantity*QtyOnOrder) as TotQtyOnOrder
  FROM recursive_LineItems
 GROUP BY LineItemName
), id as (
SELECT Items.Description, Items.QuantityOnHand, Items.QuantityOnOrder, Items.QuantityOnSalesOrder, ItemLineItems.Name, 
       ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity, 
       isnull(wQ.TotQtyOnOrder,0) TotQtyOnOrder
     , ROW_NUMBER() over (order by Items.Name, ItemLineItems.LineItemName) as rn
  FROM ItemLineItems 
 INNER JOIN Items ON ItemLineItems.LineItemName = Items.Name
  LEFT JOIN LineItem_w_OrderQty wQ ON ItemLineItems.LineItemName = wQ.LineItemName
), cte as (
select [Name]
      , [LineItemName]
      , 0 as lvl
      , 0 as depth
      , convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
      , TotQtyOnOrder
   from id 
  where [Name]='US9000'
 union  all
 select b.[Name]
      , b.[LineItemName]
      , lvl+1 As LVL
      , depth+1 As Depth
      , a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':'
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
      , b.TotQtyOnOrder
   from cte as a
  inner join id as b
     on b.[Name]=a.[LineItemName]
)  
select Items
     , depth
     , lvl
     , sortord
     , LineItemQty As LineItemQty
     , QtyOnHand As QtyOnHand --* --items 
     , TotQtyOnOrder
  from (select replicate('-',depth+1)+[LineItemName] as Items
             , sortord
             , 'B' as type
             , depth as depth
             , lvl as lvl
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , TotQtyOnOrder
          from cte
        union all
        select [Name]
             , sortord
             , 'A'
             , lvl
             , depth
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , TotQtyOnOrder
          from (select [Name]
                     , sortord
                     , ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn
                     , lvl as lvl
                     , depth As Depth
                     , LineItemQty As LineItemQty
                     , QtyOnHand As QtyOnHand
                     , TotQtyOnOrder
                  from cte 
                 where depth=0 ) as y
         where rn=1 ) as x
order by [type],sortord;

Open in new window

0
 

Author Comment

by:jwebster77
ID: 38378691
THanks lwadwell. I think this answers this post!   You are great!  I will be posting another question branching this query just a little deeper as I have to figure out how to do something else. If you are able to help me with it much appreciated.
0
 

Author Closing Comment

by:jwebster77
ID: 38378695
lwadwell helped me formulate this query and it works great!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

616 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