Trying to Display Certain Columns and Sum from Table

Question

I have my SQL Query that displays all the needed data, but I need help with getting a certain output. I need to display a column labeled Loop (which isn't in the mass query), LPT, and the SUM of PLAN_MOVES using the query below. I can do a select sum on Plan_Moves and label it Target_Daily_Moves but not sure how to display the other two columns. Please help me as I am still learning SQL.

I'm trying to display this expected outcome.

New Mass Query SQL W/o " reserved loop name":

    select
da.family,
sysdate update_dttm,
max(dds.sched_update_dttm) max_sched_update_dttm,
dds.lpt,
( case WHEN dds.lpt = '1400' THEN 'MOAT'
WHEN dds.lpt = '3300' THEN 'GATE'
  WHEN dds.lpt = '4800' THEN 'S/D'
    WHEN dds.lpt = '5130' THEN 'PMD'
      WHEN dds.lpt = '5800' THEN 'CONTACT'
        WHEN dds.lpt = '6400' THEN 'VIA 1'
          WHEN dds.lpt = '6900' THEN 'VIA 2'
            WHEN dds.lpt = '7900' THEN 'VIA 3-4'
              WHEN dds.lpt = '9200' THEN 'PO'
                WHEN dds.lpt = '9348' THEN 'PARAMETRICS'
                  WHEN dds.lpt = '9950' THEN 'OUTS'
else 'none'
  end) as LOOPS_INFO,

sum(dds.sched_cur_qty) plan_moves
from device_daily_sched@smsdwde2.itg.ti.com dds, dm_lpt_attributes@Smsdwde2.itg.ti.com la, dm_device_attributes@smsdwde2.itg.ti.com da
where 
dds.facility = 'DP1DM5'
and dds.facility = la.facility
and dds.lpt = la.lpt
and dds.device = da.device
and dds.lpt in('1400','3300','4800','5130','5800','6400','6900','7900','9200','9348','9950')
and (dds.device like 'SN/%'or dds.device like 'SS/%')
and (dds.sched_dttm = trunc(sysdate))
and dds.device not in('SN/BC3ZLTM','SN/DMD8SF','SN/DMD8','SN/DMDASSEM','SN/R035','SN/RFSIGNET','SN/SMIC','SS/BC3ZL','SS/BICOM3ZL','SS/BICOM3','SS/BICOMPBO','SS/R05S','SS/THERM')
group by
da.family,
dds.lpt

Original Mass Query SQL W/o " reserved loop name" OutPut:

UPDATED: SQL from Last comment to include "loops_info" instead of "loop":

     SELECT a.LOOPS_INFO AS 'LOOPS_INFO', 
          a.lpt AS 'LPT', 
          SUM(a.plan_moves) AS 'Target Daily Moves' 
    FROM (
  select
da.family,
sysdate update_dttm,
max(dds.sched_update_dttm) max_sched_update_dttm,
dds.lpt,
( case WHEN dds.lpt = '1400' THEN 'MOAT'
WHEN dds.lpt = '3300' THEN 'GATE'
  WHEN dds.lpt = '4800' THEN 'S/D'
    WHEN dds.lpt = '5130' THEN 'PMD'
      WHEN dds.lpt = '5800' THEN 'CONTACT'
        WHEN dds.lpt = '6400' THEN 'VIA 1'
          WHEN dds.lpt = '6900' THEN 'VIA 2'
            WHEN dds.lpt = '7900' THEN 'VIA 3-4'
              WHEN dds.lpt = '9200' THEN 'PO'
                WHEN dds.lpt = '9348' THEN 'PARAMETRICS'
                  WHEN dds.lpt = '9950' THEN 'OUTS'
else 'none'
  end) as LOOPS_INFO,

sum(dds.sched_cur_qty) plan_moves
from device_daily_sched@smsdwde2.itg.ti.com dds, dm_lpt_attributes@Smsdwde2.itg.ti.com la, dm_device_attributes@smsdwde2.itg.ti.com da
where 
dds.facility = 'DP1DM5'
and dds.facility = la.facility
and dds.lpt = la.lpt
and dds.device = da.device
and dds.lpt in('1400','3300','4800','5130','5800','6400','6900','7900','9200','9348','9950')
and (dds.device like 'SN/%'or dds.device like 'SS/%')
and (dds.sched_dttm = trunc(sysdate))
and dds.device not in('SN/BC3ZLTM','SN/DMD8SF','SN/DMD8','SN/DMDASSEM','SN/R035','SN/RFSIGNET','SN/SMIC','SS/BC3ZL','SS/BICOM3ZL','SS/BICOM3','SS/BICOMPBO','SS/R05S','SS/THERM')
group by
da.family,
dds.lpt ) AS a 
ORDER BY a.lpt ASC 
GROUP BY a.lpt 
   UNION 
  SELECT 'TOTAL' AS 'LOOPS_INFO', 
         '' AS 'LPT', 
         SUM(b.plan_moves) AS 'Target Daily Moves' 
   FROM (
    select
da.family,
sysdate update_dttm,
max(dds.sched_update_dttm) max_sched_update_dttm,
dds.lpt,
( case WHEN dds.lpt = '1400' THEN 'MOAT'
WHEN dds.lpt = '3300' THEN 'GATE'
  WHEN dds.lpt = '4800' THEN 'S/D'
    WHEN dds.lpt = '5130' THEN 'PMD'
      WHEN dds.lpt = '5800' THEN 'CONTACT'
        WHEN dds.lpt = '6400' THEN 'VIA 1'
          WHEN dds.lpt = '6900' THEN 'VIA 2'
            WHEN dds.lpt = '7900' THEN 'VIA 3-4'
              WHEN dds.lpt = '9200' THEN 'PO'
                WHEN dds.lpt = '9348' THEN 'PARAMETRICS'
                  WHEN dds.lpt = '9950' THEN 'OUTS'
else 'none'
  end) as LOOPS_INFO,

sum(dds.sched_cur_qty) plan_moves
from device_daily_sched@smsdwde2.itg.ti.com dds, dm_lpt_attributes@Smsdwde2.itg.ti.com la, dm_device_attributes@smsdwde2.itg.ti.com da
where 
dds.facility = 'DP1DM5'
and dds.facility = la.facility
and dds.lpt = la.lpt
and dds.device = da.device
and dds.lpt in('1400','3300','4800','5130','5800','6400','6900','7900','9200','9348','9950')
and (dds.device like 'SN/%'or dds.device like 'SS/%')
and (dds.sched_dttm = trunc(sysdate))
and dds.device not in('SN/BC3ZLTM','SN/DMD8SF','SN/DMD8','SN/DMDASSEM','SN/R035','SN/RFSIGNET','SN/SMIC','SS/BC3ZL','SS/BICOM3ZL','SS/BICOM3','SS/BICOMPBO','SS/R05S','SS/THERM')
group by
da.family,
dds.lpt ) AS b

Same Output Error: ORA-00923: FROM keyword not found where expected

talijj · Answer 1 · 2022-08-01T17:54:11.937

0

You could nest several if commands together to test the value of lpt and return a string value for each as one of your result set parameters, based on lpt's value, if my understanding of what 'loop' means is correct. I'm assuming each distinct value of lpt determines what loop's value is...so when dds.lpt='1400', for example, Loop would always be the value 'MOAT', correct?

Or...use a case block (which would look cleaner).

see this post for more info on the syntax of both of those: How do I perform an IF...THEN in an SQL SELECT?.

The nested if's or case would be included as another result column, followed by 'AS loop' so the result set includes a column named loop with the corresponding value.

Something like this:

select
da.family,
sysdate update_dttm,
max(dds.sched_update_dttm) max_sched_update_dttm,
dds.lpt,
( case-or-if-testing-dds.lpt-values... ) loop, 
sum(dds.sched_cur_qty) plan_moves
...etc.

Additional modified answer after first 5 comments were added to original answer:

Then try wrapping your whole query around 2 union'ed separate SELECT statements like this...not pretty but it should give you what you want if you can't do any post-processing in other non-SQL code:

   SELECT a.loop "Loop", 
          a.lpt "LPT", 
          SUM(a.plan_moves) "Target Daily Moves" 
    FROM (
  <your entire select SQL in here> 
         ) a 
ORDER BY a.lpt ASC 
GROUP BY a.lpt 
   UNION 
  SELECT "TOTAL" "Loop", 
         "" "LPT", 
         SUM(b.plan_moves) "Target Daily Moves" 
   FROM (
  <your entire select SQL in here> 
         ) b

edited Aug 01 '22 at 17:54

answered Jul 06 '22 at 19:06

talijj

68
8

Yes everything you said is correct. When LPT = 1400 then it would be MOAT. – MEDR Jul 06 '22 at 19:17
I was able to add the Case block and it made another column for Loops. How do I get the total to display by loops? – MEDR Jul 06 '22 at 19:35
I updated the SQL and Screenshot – MEDR Jul 06 '22 at 19:37
I'm not sure I understand your question....are you wanting to return the row labeled 'TOTAL', 15588 as part of your result set? I don't think you can do that in SQL, unless you UNION the big query with an identical query wrapped to do a SUM on plan_moves to a single row....kind of ugly. Are you doing any post-processing of your result set in java or some other type of language that could do that? That would be much simpler and straightforward than imbedding an extra row union'ed in to your original query to get it from a second, identical query (though it's possible). – talijj Jul 06 '22 at 20:45
yeah I'm trying to make my SQL table output like the one above where it shows Loop, LPT, and the total for each loop. Example: I want to add all the Plan moves which should equal 1564 for moat, LPT 1400 instead of showing many rows of raw data. – MEDR Jul 06 '22 at 20:56
see modifications to my answer above – talijj Jul 07 '22 at 22:02
got the following error: FROM keyword not found where expected. Not sure where I'm missing the FROM keyword – MEDR Jul 11 '22 at 16:28
The red mark came after the `a.loop AS 'loop',` – MEDR Jul 11 '22 at 16:31
case sensitivity...change the section in both inner queries at the end of your case blocks where it says: "else 'none' end) as Loop," to "else 'none' end) as loop," or else change my outer SQL queries to look for "a.Loop" insted of "a.loop". case needs to match. Sorry...didn't catch your original script using the capitalized 'L' since everywhere else it was using lower case. – talijj Jul 12 '22 at 17:33
I tried both and still getting the ORA-00923: "FROM keyword not found where expected. – MEDR Jul 12 '22 at 20:50
I updated the SQL to reflect your change. – MEDR Jul 12 '22 at 21:21
according to: https://docs.oracle.com/cd/A97630_01/appdev.920/a42525/apb.htm, 'loop' is a reserved word, so it has to be in quotes after your case block. Did your original query work when you just added the case block a couple days back? I'm thinking you might get the same error if you just ran that query by itself (with the case included). If it was generating the same error, that's where the issue is. – talijj Jul 13 '22 at 15:23
yeah, the original case query worked. I went ahead and updated the SQL and changed `loop` for `LOOPS_INFO` . I made the same changes but still the same error. I added new SQL query to reflect those changes and included the outputs too. I hope this helps in finding a solution and I really appreciate your help! – MEDR Jul 13 '22 at 16:54
one more thing to try...remove the 'as' from 'AS a' and 'AS b' in my outer queries, as I did in my original modification above. – talijj Jul 13 '22 at 18:52
yeah same outcome :( not sure why this error keeps happening and it seems like it gets hanged up on the first `LOOPS_INFO` – MEDR Jul 13 '22 at 19:28
try removing the 'as' from your case (change "as LOOPS_INFO" to just "LOOPS_INFO" after close paren). I'm used to MySql syntax rather than Oracle. I'm basing that off what I see at https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm. You may have to do the same thing for the 'sum' lines in my outer queries as I did in my original modification above. – talijj Jul 14 '22 at 20:49
and if you get the same error on another line with 'as', try removing all the 'as' words....but to do that you may have to wrap the part before the as in parens. for example: a.lpt AS LPT becomes (a.lpt) LPT – talijj Jul 14 '22 at 20:54
updated my mods to remove 'AS'....did that work for you, MEDR? – talijj Aug 01 '22 at 17:54

Trying to Display Certain Columns and Sum from Table

1 Answers1