SQLTeam.com | Weblogs | Forums

Embedding a SELECT statement


#1

I have a report that's 99% complete, it does everything I need it to, but I need to add one more feature to it. I have a separate snippet of code that is the missing piece to completing this report, but I am not sure how to incorporate it into my current code, the snippet if a full query with JOINS, a WHERE statement etc.

Any help is greatly appreciated.

Thanks


#2

From a philosophical perspective, we can discuss your needs in general but I am sure you would like more concrete advice and it sure helps if you can post table DDL and the SQL for what you are proposing.


#3

Please provide:

  • table descriptions as create statements
  • sample data as insert statements
  • your queries
  • expected output (from your provided sample data)

#4

Thanks for the replies.

Here's the query that is compete and working. This is basically returning lines that have a ship date past their promised ship date, then filters and case statements, but at it core a ship date past their promised ship date report.

select
soitem.fac as [Fac],
somast.fcompany as [Customer],
shmast.fccompany as [End Customer],
soitem.fsono as [SO No],
somast.fsorev as [SO Rev],
soitem.fenumber as [Line Item],
soitem.fquantity as [SO Qty],
shitem.fshipqty as [Ship Qty],
sorels.fnetprice as [Sales Price],
soitem.fpartno as [Part No],
soitem.fpartrev as [Rev],
soitem.fdesc as [Desc],
soitem.fprodcl as [Prod Cls],
shmast.fshipdate as [Ship Dt],
SHITEM.FITEMNO AS [Ship Ln Item],
soitem_ext.field3 as [Prom Dt],
soitem_ext.field9 as [Reason],
--jomast.fstatus as [Status],

CASE
WHEN soitem.fprodcl = '10' then 'SUB'
WHEN soitem.fprodcl = '11' then 'PDU'
WHEN soitem.fprodcl = '12' then 'PDU'
WHEN soitem.fprodcl = '13' then 'PDU'
WHEN soitem.fprodcl = '14' then 'PDU'
WHEN soitem.fprodcl = '15' then 'X'
WHEN soitem.fprodcl = '16' then 'X'
WHEN soitem.fprodcl = '17' then 'X'
WHEN soitem.fprodcl = '18' then 'RPP'
WHEN soitem.fprodcl = '19' then 'X'
WHEN soitem.fprodcl = '1a' then 'RPP'
WHEN soitem.fprodcl = '1b' then 'RPP'
WHEN soitem.fprodcl = '1c' then 'PDU'
WHEN soitem.fprodcl = '1d' then 'X'
WHEN soitem.fprodcl = '1e' then 'RPP'
WHEN soitem.fprodcl = '1f' then 'BCMS'
WHEN soitem.fprodcl = '1g' then 'X'
WHEN soitem.fprodcl = '1h' then 'X'
WHEN soitem.fprodcl = '1J' then 'PDU'
WHEN soitem.fprodcl = '1K' then 'TRANS'
WHEN soitem.fprodcl = '20' then 'X'
WHEN soitem.fprodcl = '21' then 'PDU'
WHEN soitem.fprodcl = '22' then 'X'
WHEN soitem.fprodcl = '23' then 'PDU'
WHEN soitem.fprodcl = '24' then 'X'
WHEN soitem.fprodcl = '25' then 'HCS'
WHEN soitem.fprodcl = '26' then 'HCS'
WHEN soitem.fprodcl = '27' then 'HCS'
WHEN soitem.fprodcl = '30' then 'RPS'
WHEN soitem.fprodcl = '31' then 'LC'
WHEN soitem.fprodcl = '32' then 'X'
WHEN soitem.fprodcl = '35' then 'RPS'
WHEN soitem.fprodcl = '36' then 'RPS'
WHEN soitem.fprodcl = '37' then 'RPS'
WHEN soitem.fprodcl = '3a' then 'X'
WHEN soitem.fprodcl = '3b' then 'X'
WHEN soitem.fprodcl = '40' then 'STS - Sub'
WHEN soitem.fprodcl = '41' then 'X'
WHEN soitem.fprodcl = '42' then 'X'
WHEN soitem.fprodcl = '43' then 'X'
WHEN soitem.fprodcl = '44' then 'STS'
WHEN soitem.fprodcl = '45' then 'STS'
WHEN soitem.fprodcl = '46' then 'STS'
WHEN soitem.fprodcl = '47' then 'STS'
WHEN soitem.fprodcl = '4a' then 'STS'
WHEN soitem.fprodcl = '4b' then 'STS?'
WHEN soitem.fprodcl = '4c' then 'X'
WHEN soitem.fprodcl = '4z' then 'X'
WHEN soitem.fprodcl = '50' then 'X'
WHEN soitem.fprodcl = '51' then 'X'
WHEN soitem.fprodcl = '52' then 'X'
WHEN soitem.fprodcl = '53' then 'X'
WHEN soitem.fprodcl = '56' then 'BCMS'
WHEN soitem.fprodcl = '57' then 'X'
WHEN soitem.fprodcl = '58' then 'X'
WHEN soitem.fprodcl = '59' then 'X'
WHEN soitem.fprodcl = '60' then 'X'
WHEN soitem.fprodcl = '61' then 'X'
WHEN soitem.fprodcl = '62' then 'X'
WHEN soitem.fprodcl = '63' then 'X'
WHEN soitem.fprodcl = '64' then 'X'
WHEN soitem.fprodcl = '65' then 'X'
WHEN soitem.fprodcl = '66' then 'X'
WHEN soitem.fprodcl = '67' then 'X'
WHEN soitem.fprodcl = '68' then 'X'
WHEN soitem.fprodcl = '69' then 'X'
WHEN soitem.fprodcl = '6a' then 'SERV'
WHEN soitem.fprodcl = '6b' then 'X'
WHEN soitem.fprodcl = '6c' then 'X'
WHEN soitem.fprodcl = '6d' then 'SERV'
WHEN soitem.fprodcl = '6E' then 'SERV'
WHEN soitem.fprodcl = '6F' then 'SERV'
WHEN soitem.fprodcl = '6I' then 'X'
WHEN soitem.fprodcl = '6J' then 'X'
WHEN soitem.fprodcl = '6K' then 'X'
WHEN soitem.fprodcl = '6L' then 'X'
WHEN soitem.fprodcl = '6M' then 'X'
WHEN soitem.fprodcl = '6N' then 'X'
WHEN soitem.fprodcl = '6R' then 'X'
WHEN soitem.fprodcl = '74' then 'SERV'
WHEN soitem.fprodcl = '75' then 'SERV'
WHEN soitem.fprodcl = '80' then 'TRANS'
WHEN soitem.fprodcl = '81' then 'TRANS'
WHEN soitem.fprodcl = '82' then 'TRANS'
WHEN soitem.fprodcl = '83' then 'TRANS'
WHEN soitem.fprodcl = '84' then 'TRANS'
WHEN soitem.fprodcl = '85' then 'TRANS'
WHEN soitem.fprodcl = '8A' then 'TRANS'
WHEN soitem.fprodcl = '8B' then 'TRANS'
WHEN soitem.fprodcl = '90' then 'X'
WHEN soitem.fprodcl = '91' then 'X'
WHEN soitem.fprodcl = '93' then 'X'
WHEN soitem.fprodcl = '98' then 'X'
WHEN soitem.fprodcl = '99' then 'X'
WHEN soitem.fprodcl = '9a' then 'X'
WHEN soitem.fprodcl = '9b' then 'X'
WHEN soitem.fprodcl = '9C' then 'X'
WHEN soitem.fprodcl = 'A1' then 'SHT'
WHEN soitem.fprodcl = 'A2' then 'SHT'
WHEN soitem.fprodcl = 'A3' then 'SHT'
WHEN soitem.fprodcl = 'A4' then 'SHT'
WHEN soitem.fprodcl = 'A5' then 'BUS'
WHEN soitem.fprodcl = 'B1' then 'X'
WHEN soitem.fprodcl = 'B3' then 'X'
WHEN soitem.fprodcl = 'B4' then 'X'
WHEN soitem.fprodcl = 'CN' then 'X'
WHEN soitem.fprodcl = 'ON' then 'X'
WHEN soitem.fprodcl = 'PI' then 'X'

END AS Prod_Cls1,

CASE
** when shmast.fshipdate > SOITEM_EXT.FIELD3 then '0' --1 equals on time, 0 equals late**
** else '1' **
** end as [On_Time],**

**CASE **
** WHEN shmast.fshipdate >= '20150731'**
** AND shmast.fshipdate < '20150829' THEN '1'**
** **
** WHEN shmast.fshipdate >= '20150830'**
** AND shmast.fshipdate < '20150927' THEN '2'**
** **
** WHEN shmast.fshipdate >= '20150927'**
** AND shmast.fshipdate < '20151031' THEN '3'**
** **
** WHEN shmast.fshipdate >= '20151101'**
** AND shmast.fshipdate < '20151128' THEN '4'**
** **
** WHEN shmast.fshipdate >= '20151129'**
** AND shmast.fshipdate < '20151226' THEN '5'**
** **
** WHEN shmast.fshipdate >= '20151227'**
** AND shmast.fshipdate < '20160130' THEN '6'**
** **
** WHEN shmast.fshipdate >= '20160201'**
** AND shmast.fshipdate < '20160227' THEN '7'**
** **
** WHEN shmast.fshipdate >= '20160228'**
** AND shmast.fshipdate < '20160326' THEN '8'**
** **
** WHEN shmast.fshipdate >= '20160327'**
** AND shmast.fshipdate < '20160430' THEN '9'**
** **
** WHEN shmast.fshipdate >= '20160501'**
** AND shmast.fshipdate < '20160528' THEN '10'**
** **
** WHEN shmast.fshipdate >= '20160529'**
** AND shmast.fshipdate < '20160625' THEN '11'**
** **
** WHEN shmast.fshipdate >= '20160626'**
** AND shmast.fshipdate < '20160801' THEN '12'**
** ELSE 'X'**
** END AS FM,**
** CASE **
** WHEN shmast.fshipdate >= '20140801'**
** AND shmast.fshipdate < '20150801' THEN '2015'**
** WHEN shmast.fshipdate >= '20150801'**
** AND shmast.fshipdate < '20160731' THEN '2016'**
** ELSE 'X'**
** END AS FY,**
DATEDIFF(DD, shmast.fshipdate,SOITEM_EXT.FIELD3) AS [Days Late]
from soitem

LEFT JOIN soitem_ext
on soitem.identity_column = soitem_ext.fkey_id

**LEFT JOIN somast **
**ON soitem.fsono = somast.fsono **

**JOIN shmast **
ON (somast.fsono + somast.fsorev) = (shmast.fcsono + shmast.fcsorev)

**LEFT JOIN SORELS **
ON (SOITEM.FSONO + SOITEM.FPARTNO + SOITEM.FINUMBER + SOITEM.FSONO) = (SORELS.FSONO + SORELS.FPARTNO + SORELS.FINUMBER + SORELS.FSONO)

**LEFT JOIN SHITEM **
ON ((SHMAST.FSHIPNO + SOITEM.FPARTNO + SOITEM.FAC) + (SORELS.FSONO + SORELS.FINUMBER + SORELS.FRELEASE)) = ((SHITEM.FSHIPNO + SHITEM.FPARTNO + SHITEM.FAC) + (SHITEM.FSOKEY))

**--JOIN jomast **
--ON shmast.fcsono = jomast.fsono

**where **
shitem.fshipqty = shitem.forderqty
and
shmast.fshipdate > SOITEM_EXT.FIELD3
and
shitem.fshipqty <> '0'
and
SOITEM_EXT.FIELD3 <> '1/1/1900 12:00:00 AM'
**AND **
(soitem.fprodcl <> '54'
and
soitem.fprodcl <> '55'
and
soitem.fprodcl <> '70'
and
soitem.fprodcl <> '71'
and
soitem.fprodcl <> '72'
and
soitem.fprodcl <> '73'
and
soitem.fprodcl <> '76'
and
soitem.fprodcl <> '77'
and
soitem.fprodcl <> '78'
and
soitem.fprodcl <> '79'
and
soitem.fprodcl <> '7A'
and
soitem.fprodcl <> 'FR'
and
soitem.fprodcl <> 'SH')

order by soitem.fsono ASC

one final feature I need is to only bring in lines where there are no more items left to ship on the order. There is no field in the tables that flags the order as complete or the line item as the last one in the order, so I have this query that will show me that info. This is the query I desperately need to incorporate into the first attached code.

**SELECT shmast.fcsono, shitem.fenumber, Sum(shitem.fshipqty) AS SumOffshipqty, **
sorels.forderqty, Max(shmast.fshipdate) AS MaxOffshipdate
**FROM sorels **
**INNER JOIN (shmast INNER JOIN shitem ON shmast.fshipno = shitem.fshipno) **
ON (sorels.fsono = shmast.fcsono) AND (sorels.fenumber = shitem.fenumber)

Thanks again for your time. I hope I've explained this properly.


#5

Since table description, sample data and expected output wasn't provided, I can only guess the following query will work. I have rewritten the query, the way I would have written it. I really question the way you have joined the tables sorels and shitem (this is why table definition, sample data and expected output was requested).
Anyway - here goes:

select soi.fac as [Fac]
      ,som.fcompany as [Customer]
      ,shm.fccompany as [End Customer]
      ,soi.fsono as [SO No]
      ,som.fsorev as [SO Rev]
      ,soi.fenumber as [Line Item]
      ,soi.fquantity as [SO Qty]
      ,shi.fshipqty as [Ship Qty]
      ,sor.fnetprice as [Sales Price]
      ,soi.fpartno as [Part No]
      ,soi.fpartrev as [Rev]
      ,soi.fdesc as [Desc]
      ,soi.fprodcl as [Prod Cls]
      ,shm.fshipdate as [Ship Dt]
      ,shi.fitenno as [Ship Ln Item]
      ,soie.field3 as [Prom Dt]
      ,soie.field9 as [Reason]
      ,jom.fstatus as [Status]
      ,case soi.fprodcl
          when '10' then 'SUB'
          when '11' then 'PDU'
          when '12' then 'PDU'
          when '13' then 'PDU'
          when '14' then 'PDU'
          when '15' then 'X'
          when '16' then 'X'
          when '17' then 'X'
          when '18' then 'RPP'
          when '19' then 'X'
          when '1a' then 'RPP'
          when '1b' then 'RPP'
          when '1c' then 'PDU'
          when '1d' then 'X'
          when '1e' then 'RPP'
          when '1f' then 'BCMS'
          when '1g' then 'X'
          when '1h' then 'X'
          when '1J' then 'PDU'
          when '1K' then 'TRANS'
          when '20' then 'X'
          when '21' then 'PDU'
          when '22' then 'X'
          when '23' then 'PDU'
          when '24' then 'X'
          when '25' then 'HCS'
          when '26' then 'HCS'
          when '27' then 'HCS'
          when '30' then 'RPS'
          when '31' then 'LC'
          when '32' then 'X'
          when '35' then 'RPS'
          when '36' then 'RPS'
          when '37' then 'RPS'
          when '3a' then 'X'
          when '3b' then 'X'
          when '40' then 'STS - Sub'
          when '41' then 'X'
          when '42' then 'X'
          when '43' then 'X'
          when '44' then 'STS'
          when '45' then 'STS'
          when '46' then 'STS'
          when '47' then 'STS'
          when '4a' then 'STS'
          when '4b' then 'STS?'
          when '4c' then 'X'
          when '4z' then 'X'
          when '50' then 'X'
          when '51' then 'X'
          when '52' then 'X'
          when '53' then 'X'
          when '56' then 'BCMS'
          when '57' then 'X'
          when '58' then 'X'
          when '59' then 'X'
          when '60' then 'X'
          when '61' then 'X'
          when '62' then 'X'
          when '63' then 'X'
          when '64' then 'X'
          when '65' then 'X'
          when '66' then 'X'
          when '67' then 'X'
          when '68' then 'X'
          when '69' then 'X'
          when '6a' then 'SERV'
          when '6b' then 'X'
          when '6c' then 'X'
          when '6d' then 'SERV'
          when '6E' then 'SERV'
          when '6F' then 'SERV'
          when '6I' then 'X'
          when '6J' then 'X'
          when '6K' then 'X'
          when '6L' then 'X'
          when '6M' then 'X'
          when '6N' then 'X'
          when '6R' then 'X'
          when '74' then 'SERV'
          when '75' then 'SERV'
          when '80' then 'TRANS'
          when '81' then 'TRANS'
          when '82' then 'TRANS'
          when '83' then 'TRANS'
          when '84' then 'TRANS'
          when '85' then 'TRANS'
          when '8A' then 'TRANS'
          when '8B' then 'TRANS'
          when '90' then 'X'
          when '91' then 'X'
          when '93' then 'X'
          when '98' then 'X'
          when '99' then 'X'
          when '9a' then 'X'
          when '9b' then 'X'
          when '9C' then 'X'
          when 'A1' then 'SHT'
          when 'A2' then 'SHT'
          when 'A3' then 'SHT'
          when 'A4' then 'SHT'
          when 'A5' then 'BUS'
          when 'B1' then 'X'
          when 'B3' then 'X'
          when 'B4' then 'X'
          when 'CN' then 'X'
          when 'ON' then 'X'
          when 'PI' then 'X'
       end as ProdCls1
      ,case
          when shm.fshipdate>soie.field3
          then '0' /* 1 equals on time, 0 equals late */
          else '1'
       end as [OnTime]
      ,case
          when shm.fshipdate>=cast('2015-07-31 00:00:00' as datetime)
           and shm.fshipdate< cast('2015-08-29 00:00:00' as datetime) then '1'
          when shm.fshipdate>=cast('2015-08-30 00:00:00' as datetime)
           and shm.fshipdate< cast('2015-09-27 00:00:00' as datetime) then '2'
          when shm.fshipdate>=cast('2015-09-27 00:00:00' as datetime)
           and shm.fshipdate< cast('2015-10-31 00:00:00' as datetime) then '3'
          when shm.fshipdate>=cast('2015-11-01 00:00:00' as datetime)
           and shm.fshipdate< cast('2015-11-28 00:00:00' as datetime) then '4'
          when shm.fshipdate>=cast('2015-11-29 00:00:00' as datetime)
           and shm.fshipdate< cast('2015-12-26 00:00:00' as datetime) then '5'
          when shm.fshipdate>=cast('2015-12-27 00:00:00' as datetime)
           and shm.fshipdate< cast('2016-01-30 00:00:00' as datetime) then '6'
          when shm.fshipdate>=cast('2016-02-01 00:00:00' as datetime)
           and shm.fshipdate< cast('2016-02-27 00:00:00' as datetime) then '7'
          when shm.fshipdate>=cast('2016-02-28 00:00:00' as datetime)
           and shm.fshipdate< cast('2016-03-26 00:00:00' as datetime) then '8'
          when shm.fshipdate>=cast('2016-03-27 00:00:00' as datetime)
           and shm.fshipdate< cast('2016-04-30 00:00:00' as datetime) then '9'
          when shm.fshipdate>=cast('2016-05-01 00:00:00' as datetime)
           and shm.fshipdate< cast('2016-05-28 00:00:00' as datetime) then '10'
          when shm.fshipdate>=cast('2016-05-29 00:00:00' as datetime)
           and shm.fshipdate< cast('2016-06-25 00:00:00' as datetime) then '11'
          when shm.fshipdate>=cast('2016-06-26 00:00:00' as datetime)
           and shm.fshipdate< cast('2016-08-01 00:00:00' as datetime) then '12'
          else 'X'
       end as FM
      ,case
          when shm.fshipdate>=cast('2014-08-01 00:00:00' as datetime)
           and shm.fshipdate< cast('2015-08-01 00:00:00' as datetime) then '2015'
          when shm.fshipdate>=cast('2015-08-01 00:00:00' as datetime)
           and shm.fshipdate< cast('2016-07-31 00:00:00' as datetime) then '2016'
          else 'X'
       end as FY
      ,datediff(day,shm.fshipdate,soie.field3) as [Days Late]
  from soitem as si
       inner join soitemext as soie
               on soie.fkey_id=soi.identitycolumn
              and soie.field3<>cast('1900-01-01 00:00:00' as datetime)
       inner join somast as som
               on som.fsono=soi.fsono
       inner join shmast as shm
               on shm.fcsono=som.fsono
              and shm.fcsorev=som.fsorev
              and shm.fshipdate>soie.field3
       inner join sorels as sor
               on sor.fsono=soi.fsono
              and sor.fpartno=soi.fpartno
              and sor.finumber=soi.finumber
       inner join shitem as shi
               on shi.fshipno=shm.fshipno
              and shi.fpartno=soi.fpartno
              and shi.fac=soi.fac
              and shi.fsokey=sor.fsono
                            +sor.finumber
                            +sor.frelease
              and shi.fshipqty=shi.forderqty
              and shi.fshipqty<>0
       inner join jomast as jom
               on shm.fcsono=jom.fsono
 where soi.fprodcl not in ('54','55','70','71','72','73','76','77','78','79','7A','FR','SH')
   and sor.forderqty=(select sum(shi2.fshipqty)
                        from shitem as shi2
                       where shi2.fshipno=shm.fshipno
                         and shi2.fenumber=sor.fenumber
                     )
 order by soi.fsono
;

#6

What about anything dated 29th?

Do you not have a "Accounting Periods" table, or somesuch, that you can associate with the query, rather than hard-wiring the dates? With dates hardwired (probably in multiple reports) if anything changes, or is wrong, its a lot of work to fix and also a high probability that one, or several, of them will be subtly different and thus silently report the wrong figures.