Database

SELECT SUBSTR(A.CHAR_STR, 0,  LENGTH(A.CHAR_STR) - LEVEL + 1) OUTPUT
FROM (SELECT 'HPN12345' CHAR_STR, 3 MIN_DEPTH FROM DUAL) A
CONNECT BY LENGTH(A.CHAR_STR) - LEVEL + 1 >= A.MIN_DEPTH;

Member 9999112 wrote:

How can i over come this error.

select * 
from @t
where
cast((case when isnumeric(val) = 1 then val else 0 end) as int) > 3

Bernhard Hiller wrote:

JavaScript could cast '12r' into an int

Mycroft Holmes wrote:

There is certain crappy banking software that will allow the traders to enter 12.65m or 328k as a trade values, WTF!

Richard.Berry100 wrote:

what does J-script convert '12r' to???

WHERE C.ROW_ID        = T5.PR_EMP_ID
  AND T1.ROW_ID       = T4.ROW_ID(+)
  AND T1.PR_POSTN_ID  = T5.ROW_ID(+)
--AND T1.PR_INDUST_ID = T6.ROW_ID(+)
  AND C.EMP_FLG(+)    = 'Y'
  AND T1.CUST_STAT_CD IN ('Active','Inactive','Pending - Workflow Wizard')
  AND T1.X_CLIENT_TYPE IS NOT NULL

SELECT MSGNUM, LISTAGG(DESCR, ',') WITHIN GROUP (ORDER BY LVL) AS MSGDESCR
FROM (SELECT DISTINCT MSGNUM, LEVEL LVL
            ,SUBSTR(NVL2(MSGNUM, MSGNUM || ',', NULL)
                    ,(CASE WHEN LEVEL > 1 THEN 
                          INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1 
                        ELSE LEVEL END)
                    ,INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL) 
                      - (CASE WHEN LEVEL > 1 THEN 
                              INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1 
                            ELSE LEVEL END)
                  ) MSGNUM_TO_ID
        FROM
        -- REPLACE FOLLOWING BLOCK WITH YOUR FIRST TABLE
            (SELECT SYSDATE - 1 STARTDATE, '1,2,3' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 2 STARTDATE, '4' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 3 STARTDATE, '5,6' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 4 STARTDATE, '' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 5 STARTDATE, '4,2,5' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 6 STARTDATE, '' MSGNUM FROM DUAL)
        CONNECT BY INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL) != 0
      ) T1
      -- REPLACE FOLLOWING BLOCK WITH YOUR SECOND TABLE
    ,(SELECT 1 ID, 'ONE' DESCR FROM DUAL UNION
      SELECT 2 ID, 'TWO' DESCR FROM DUAL UNION
      SELECT 3 ID, 'THREE' DESCR FROM DUAL UNION
      SELECT 4 ID, 'FOUR' DESCR FROM DUAL UNION
      SELECT 5 ID, 'FIVE' DESCR FROM DUAL UNION
      SELECT 6 ID, 'SIX' DESCR FROM DUAL) T2
 WHERE T1.MSGNUM_TO_ID = T2.ID
GROUP BY MSGNUM
  ;

declare @counter int
declare @max int
SET @counter = 0
SET @max = 12

while @counter < @max
begin
INSERT INTO tableName (FruitCodeName) VALUES(CONVERT(nvarchar(3), @counter) + 'Apple Fruit Granny Smith' )
SET @counter = @counter + 1
end