CODEWITHSUNDEEP
sqloracle-database
LuckySevens
LuckySevens

Reputation: 333

Oracle SQL - Return Data Ignoring Last 2 Characters

I'm trying to return a count of all stock in a database using a product number. I would also like to include the style names for that stock, however the stock comes in multiple sizes. ie,

Size Desc         SKU Desc      Quantity
20351779           Cool 1          10
20351779           Cool 1+         5
20351779           Cool 2          5
20351779           Cool 2+         10
20354807           Awkward 1       10
20354807           Awkward 1+      10

Unfortunately the style names are a variety of sizes so i can't substring them down, and if i do that it will still split the totals anyway.

What i'm looking for is something like this:-

Size Desc         SKU Desc      Quantity
20351779           Cool            30
20354807           Awkward         20

The code i have so far looks like this:-

SELECT * FROM
(select substr(SIZE_DESC,0,8), sum(actl_invn_Qty) as Bin, 
nvl(sum(actl_qty),0) as Crane, 
SUM(ACTL_INVN_QTY+nvl(ACTL_QTY,0)) As Total from item_master im
left outer join 
(Select cd.sku_id, SUM(cd.actl_qty) AS ACTL_QTY
FROM CASE_HDR CH
INNER JOIN CASE_DTL CD ON
CH.CASE_NBR = CD.CASE_NBR
INNER JOIN LOCN_HDR LH ON
CH.LOCN_ID = LH.LOCN_ID
WHERE CH.STAT_CODE = '30'
AND LH.AREA = '0'
AND LH.ZONE = 'Z'
GROUP BY CD.SKU_ID)tmp1 on im.sku_id = tmp1.sku_id
inner join (select pld.sku_id, SUM(pld.actl_invn_qty) AS ACTL_INVN_QTY
from pick_locn_dtl pld
GROUP BY PLD.SKU_ID)tmp2 on 
im.sku_id = tmp2.sku_id
where substr(im.size_desc,0,8) in 
('20351779',
'20354807')
GROUP BY substr(SIZE_DESC,0,8)

Is it possible to return the SKU Desc while ignoring the final two characters as they will always be the size on the SKU?

Upvotes: 1

Views: 174

Answers (2)

Gary_W
Gary_W

Reputation: 10360

Just for fun, one could use a regular expression to strip from the last space to the end of the line, inclusive. This assumes your description ALWAYS has a size component following the last space that will be stripped:

select regexp_replace('Awkward 1', '(.*) [^$]+$', '\1') from dual;

Read it like this: regexp_replace(string to operate on, regular expression to match, replace string) where regular expression to match means:

( Start a "remembered" group
. of any character
* and any number of any characters
) End the "remembered" group
  followed by a space character
[ Define a character class
^$ that matches any character that is NOT the end of the line character
] End the character class definition
+ Match multiple of the previous class
$ Match the end of the line

and the replace string is just the "remembered" part of the string (\1 stands for the 1st remembered group, there could be more), which is everything up to but not including the last space followed by anything until the end of the line.

EDIT: Actually, this may be simpler:

select regexp_replace('Awkward test 1', ' [^$| ]+$', '') from dual;

Search for a space followed any character that is NOT the end of the line or NOT a space until the end of the line is found. Replace with nothing.

Upvotes: 1

UltraCommit
UltraCommit

Reputation: 2286

Use the following approach:

SELECT  
         SUBSTR ('MY STRING',
                 1,
                 LENGTH ('MY STRING') - INSTR (REVERSE ('MY STRING'), ' ', 1))
  FROM   DUAL;

The previous query removes all the part of the string at the right of the last space.

So, it returns 'MY'.

The following:

SELECT   
         SUBSTR ('MY FIRST STRING',
                 1,
                 LENGTH ('MY FIRST STRING') - INSTR (REVERSE ('MY FIRST STRING'), ' ', 1))
  FROM   DUAL;

returns 'MY FIRST'.

You have only to understand which part of your query:

SELECT   *
  FROM   (  SELECT   SUBSTR (SIZE_DESC, 0, 8),
                     SUM (ACTL_INVN_QTY) AS BIN,
                     NVL (SUM (ACTL_QTY), 0) AS CRANE,
                     SUM (ACTL_INVN_QTY + NVL (ACTL_QTY, 0)) AS TOTAL
              FROM         ITEM_MASTER IM
                        LEFT OUTER JOIN
                           (  SELECT   CD.SKU_ID, SUM (CD.ACTL_QTY) AS ACTL_QTY
                                FROM         CASE_HDR CH
                                          INNER JOIN
                                             CASE_DTL CD
                                          ON CH.CASE_NBR = CD.CASE_NBR
                                       INNER JOIN
                                          LOCN_HDR LH
                                       ON CH.LOCN_ID = LH.LOCN_ID
                               WHERE       CH.STAT_CODE = '30'
                                       AND LH.AREA = '0'
                                       AND LH.ZONE = 'Z'
                            GROUP BY   CD.SKU_ID) TMP1
                        ON IM.SKU_ID = TMP1.SKU_ID
                     INNER JOIN
                        (  SELECT   PLD.SKU_ID,
                                    SUM (PLD.ACTL_INVN_QTY) AS ACTL_INVN_QTY
                             FROM   PICK_LOCN_DTL PLD
                         GROUP BY   PLD.SKU_ID) TMP2
                     ON IM.SKU_ID = TMP2.SKU_ID
             WHERE   SUBSTR (IM.SIZE_DESC, 0, 8) IN ('20351779', '20354807')
          GROUP BY   SUBSTR (SIZE_DESC, 0, 8));

represents 'MY STRING', and then you can use my formula.

From this:

SELECT   ALFA, BETA, GAMMA
  FROM   (  SELECT   SUBSTR (SIZE_DESC, 0, 8),
                     SUM (ACTL_INVN_QTY) AS BIN,
                     NVL (SUM (ACTL_QTY), 0) AS CRANE,
                     SUM (ACTL_INVN_QTY + NVL (ACTL_QTY, 0)) AS TOTAL
              FROM         ITEM_MASTER IM
                        LEFT OUTER JOIN
                           (  SELECT   CD.SKU_ID, SUM (CD.ACTL_QTY) AS ACTL_QTY
                                FROM         CASE_HDR CH
                                          INNER JOIN
                                             CASE_DTL CD
                                          ON CH.CASE_NBR = CD.CASE_NBR
                                       INNER JOIN
                                          LOCN_HDR LH
                                       ON CH.LOCN_ID = LH.LOCN_ID
                               WHERE       CH.STAT_CODE = '30'
                                       AND LH.AREA = '0'
                                       AND LH.ZONE = 'Z'
                            GROUP BY   CD.SKU_ID) TMP1
                        ON IM.SKU_ID = TMP1.SKU_ID
                     INNER JOIN
                        (  SELECT   PLD.SKU_ID,
                                    SUM (PLD.ACTL_INVN_QTY) AS ACTL_INVN_QTY
                             FROM   PICK_LOCN_DTL PLD
                         GROUP BY   PLD.SKU_ID) TMP2
                     ON IM.SKU_ID = TMP2.SKU_ID
             WHERE   SUBSTR (IM.SIZE_DESC, 0, 8) IN ('20351779', '20354807')
          GROUP BY   SUBSTR (SIZE_DESC, 0, 8));

..... to this:

SELECT   ALFA,
         SUBSTR (BETA, 1, LENGTH (BETA) - INSTR (REVERSE (BETA), ' ', 1)),
         GAMMA
  FROM   (  SELECT   SUBSTR (SIZE_DESC, 0, 8),
                     SUM (ACTL_INVN_QTY) AS BIN,
                     NVL (SUM (ACTL_QTY), 0) AS CRANE,
                     SUM (ACTL_INVN_QTY + NVL (ACTL_QTY, 0)) AS TOTAL
              FROM         ITEM_MASTER IM
                        LEFT OUTER JOIN
                           (  SELECT   CD.SKU_ID, SUM (CD.ACTL_QTY) AS ACTL_QTY
                                FROM         CASE_HDR CH
                                          INNER JOIN
                                             CASE_DTL CD
                                          ON CH.CASE_NBR = CD.CASE_NBR
                                       INNER JOIN
                                          LOCN_HDR LH
                                       ON CH.LOCN_ID = LH.LOCN_ID
                               WHERE       CH.STAT_CODE = '30'
                                       AND LH.AREA = '0'
                                       AND LH.ZONE = 'Z'
                            GROUP BY   CD.SKU_ID) TMP1
                        ON IM.SKU_ID = TMP1.SKU_ID
                     INNER JOIN
                        (  SELECT   PLD.SKU_ID,
                                    SUM (PLD.ACTL_INVN_QTY) AS ACTL_INVN_QTY
                             FROM   PICK_LOCN_DTL PLD
                         GROUP BY   PLD.SKU_ID) TMP2
                     ON IM.SKU_ID = TMP2.SKU_ID
             WHERE   SUBSTR (IM.SIZE_DESC, 0, 8) IN ('20351779', '20354807')
          GROUP BY   SUBSTR (SIZE_DESC, 0, 8));

Upvotes: 2

Related Questions