• RELEVANCY SCORE 4.98

    DB:4.98:Querying A Registerd Table ? pm




    Hello friends Well I have been assigned a task to compute all the holidays from a calendar year 2006.Well the holidays include all the General bank holidays like New years day and ,independence day and so on ,also I have to include all the weekends viz Saturday and sunday .So for that what I have done so far is I have written an SQL script for that.Once I write this script I will hand it over to the database ppl.However since they want to put it permanently in documentum I need to create a registerd Table and then write the code.I want to know how do I create this Reg Table.Who will be the owner of this table and how will be the design?.Kindly suggest.My script looks like this...... CREATE Table CP_HOLIDAY_LIST ( MONTH Number(2), DAY Number(2), DESCRIPTION varchar2(50) ); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,01,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,02,'Observed New Years Day'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,07,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,08,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,14,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,15,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,16,'Human Rights Day'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,21,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,22,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,28,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 01,29,'Sunday'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 02,04,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 02,05,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 02,11,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 02,12,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 02,18,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 02,19,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 02,20,'PRESIDENTS DAY'); INSERT INTO Table CP_HOLIDAY_LIST values ( 02,25,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 02,26,'Sunday'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 03,04,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 03,05,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 03,11,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 03,12,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 03,18,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 03,19,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 03,25,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 03,26,'Sunday'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 04,01,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 04,02,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 04,08,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 04,09,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 04,15,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 04,16,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 04,22,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 04,23,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 04,29,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 04,30,'Sunday'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 05,06,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 05,07,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 05,13,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 05,14,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 05,20,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 05,21,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 05,27,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 05,28,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 05,29,'Memorial Day'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 06,03,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 06,04,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 06,10,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 06,11,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 06,17,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 06,18,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 06,24,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 06,25,'Sunday'); ---------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 07,01,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,02,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,04,'INDEPENDENCE DAY'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,08,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,09,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,15,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,16,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,22,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,23,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,29,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 07,30,'Sunday'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 08,05,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 08,06,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 08,12,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 08,13,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 08,19,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 08,20,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 08,26,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 08,27,'Sunday'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 09,02,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 09,03,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 09,04,'LABOR DAY'); INSERT INTO Table CP_HOLIDAY_LIST values ( 09,09,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 09,10,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 09,16,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 09,17,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 09,23,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 09,24,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 09,30,'Saturday'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 01,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 07,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 08,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 09,'Columbus Day'); INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 14,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 15,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 21,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 22,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 28,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 10, 29,'Sunday'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 11,04,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 11,05,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 11,11,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 11,12,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 11,18,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 11,19,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 11,23,'ThanksGiving Day'); INSERT INTO Table CP_HOLIDAY_LIST values ( 11,25,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 11,26,'Sunday'); ----------------------------------------------------------- INSERT INTO Table CP_HOLIDAY_LIST values ( 12,02,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,03,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,09,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,10,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,16,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,17,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,23,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,24,'Sunday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,25,'CHRISTMAS'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,30,'Saturday'); INSERT INTO Table CP_HOLIDAY_LIST values ( 12,31,'Sunday'); COMMIT;Thank u

    DB:4.98:Querying A Registerd Table ? pm

    The problem I m facing is I m the only technical person here as of now.So I do not have anybody to discuss wid wat exactly I have to do .However they want me to put this directly into documentum and make it permanent.Ya frm my design it appears that i will have to change the table manually everyyear .Can u or anybody suggest a different way out.Also I was told that since we r using Vm ware no need to put it in the database as in the vmware i m the sole admin n i have all the rights to create tables n permissins n so on .So they say I can directly regisrter tables in the vmware ie in my docbase...Thanks

  • RELEVANCY SCORE 4.89

    DB:4.89:Poduct Query Working Fine On Ms Sql &Amp; Not On Oracle 7x




    Hi All,

    Till this moment I always feel oracle is more powerful DB tool, but recently I fail to run some queries on oracle 10g same working fine on MS SQL2005. Thanks in advance

    --On oracle 10g
    select ((EXP(SUM(LN(id))) - 1) * 100) from twrr
    --Answer
    ORA-01426: numeric overflow

    --MS SQL 2005
    select(EXP (SUM (log (id))) - 1) * 100 from twrr
    --Answer
    5.085882

    --Table
    create table twrr(id numeric(18,2))

    --Data
    INSERT INTO twrr VALUES(8.61);
    INSERT INTO twrr VALUES(35.64);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(37.40);
    INSERT INTO twrr VALUES(36.62);
    INSERT INTO twrr VALUES(36.90);
    INSERT INTO twrr VALUES(42.87);
    INSERT INTO twrr VALUES(19.40);
    INSERT INTO twrr VALUES(8.40);
    INSERT INTO twrr VALUES(8.64);
    INSERT INTO twrr VALUES(8.64);
    INSERT INTO twrr VALUES(8.57);
    INSERT INTO twrr VALUES(8.62);
    INSERT INTO twrr VALUES(37.15);
    INSERT INTO twrr VALUES(8.67);
    INSERT INTO twrr VALUES(8.67);
    INSERT INTO twrr VALUES(37.18);
    INSERT INTO twrr VALUES(8.62);
    INSERT INTO twrr VALUES(8.60);
    INSERT INTO twrr VALUES(8.62);
    INSERT INTO twrr VALUES(8.61);
    INSERT INTO twrr VALUES(8.59);
    INSERT INTO twrr VALUES(8.63);
    INSERT INTO twrr VALUES(8.62);
    INSERT INTO twrr VALUES(37.22);
    INSERT INTO twrr VALUES(8.62);
    INSERT INTO twrr VALUES(8.63);
    INSERT INTO twrr VALUES(8.66);
    INSERT INTO twrr VALUES(8.64);
    INSERT INTO twrr VALUES(43.42);
    INSERT INTO twrr VALUES(8.68);
    INSERT INTO twrr VALUES(8.64);
    INSERT INTO twrr VALUES(8.64);
    INSERT INTO twrr VALUES(8.59);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(8.17);
    INSERT INTO twrr VALUES(37.61);
    INSERT INTO twrr VALUES(8.32);
    INSERT INTO twrr VALUES(8.27);
    INSERT INTO twrr VALUES(8.25);
    INSERT INTO twrr VALUES(8.25);
    INSERT INTO twrr VALUES(36.90);
    INSERT INTO twrr VALUES(34.89);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(37.25);
    INSERT INTO twrr VALUES(37.25);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(35.25);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(37.25);
    INSERT INTO twrr VALUES(37.25);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(35.24);
    INSERT INTO twrr VALUES(35.24);
    INSERT INTO twrr VALUES(35.24);
    INSERT INTO twrr VALUES(8.37);
    INSERT INTO twrr VALUES(8.11);
    INSERT INTO twrr VALUES(8.11);
    INSERT INTO twrr VALUES(8.11);
    INSERT INTO twrr VALUES(42.81);
    INSERT INTO twrr VALUES(7.72);
    INSERT INTO twrr VALUES(7.72);
    INSERT INTO twrr VALUES(42.81);
    INSERT INTO twrr VALUES(43.02);
    INSERT INTO twrr VALUES(8.08);
    INSERT INTO twrr VALUES(19.56);
    INSERT INTO twrr VALUES(28.00);
    INSERT INTO twrr VALUES(18.55);
    INSERT INTO twrr VALUES(18.55);
    INSERT INTO twrr VALUES(18.49);
    INSERT INTO twrr VALUES(18.60);
    INSERT INTO twrr VALUES(8.16);
    INSERT INTO twrr VALUES(42.96);
    INSERT INTO twrr VALUES(17.89);
    INSERT INTO twrr VALUES(8.20);
    INSERT INTO twrr VALUES(19.14);
    INSERT INTO twrr VALUES(19.14);
    INSERT INTO twrr VALUES(19.13);
    INSERT INTO twrr VALUES(8.05);
    INSERT INTO twrr VALUES(41.64);
    INSERT INTO twrr VALUES(42.93);
    INSERT INTO twrr VALUES(33.84);
    INSERT INTO twrr VALUES(19.87);
    INSERT INTO twrr VALUES(17.96);
    INSERT INTO twrr VALUES(40.95);
    INSERT INTO twrr VALUES(40.57);
    INSERT INTO twrr VALUES(40.22);
    INSERT INTO twrr VALUES(39.97);
    INSERT INTO twrr VALUES(40.05);
    INSERT INTO twrr VALUES(40.05);
    INSERT INTO twrr VALUES(8.33);
    INSERT INTO twrr VALUES(36.67);
    INSERT INTO twrr VALUES(17.43);
    INSERT INTO twrr VALUES(17.42);
    INSERT INTO twrr VALUES(42.51);
    INSERT INTO twrr VALUES(17.49);
    INSERT INTO twrr VALUES(8.27);
    INSERT INTO twrr VALUES(42.78);
    INSERT INTO twrr VALUES(17.56);
    INSERT INTO twrr VALUES(17.56);
    INSERT INTO twrr VALUES(42.61);
    INSERT INTO twrr VALUES(8.19);
    INSERT INTO twrr VALUES(17.67);
    INSERT INTO twrr VALUES(43.61);
    INSERT INTO twrr VALUES(7.67);
    INSERT INTO twrr VALUES(7.72);
    INSERT INTO twrr VALUES(19.10);
    INSERT INTO twrr VALUES(8.12);
    INSERT INTO twrr VALUES(8.12);
    INSERT INTO twrr VALUES(35.77);
    INSERT INTO twrr VALUES(42.76);
    INSERT INTO twrr VALUES(8.13);
    INSERT INTO twrr VALUES(8.11);
    INSERT INTO twrr VALUES(37.03);
    INSERT INTO twrr VALUES(37.03);
    INSERT INTO twrr VALUES(37.03);
    INSERT INTO twrr VALUES(35.48);
    INSERT INTO twrr VALUES(36.86);
    INSERT INTO twrr VALUES(42.67);
    INSERT INTO twrr VALUES(8.61);
    INSERT INTO twrr VALUES(37.18);
    INSERT INTO twrr VALUES(37.18);
    INSERT INTO twrr VALUES(41.17);
    INSERT INTO twrr VALUES(8.61);
    INSERT INTO twrr VALUES(8.61);
    INSERT INTO twrr VALUES(8.95);
    INSERT INTO twrr VALUES(8.19);
    INSERT INTO twrr VALUES(35.43);
    INSERT INTO twrr VALUES(8.21);
    INSERT INTO twrr VALUES(35.02);
    INSERT INTO twrr VALUES(8.24);
    INSERT INTO twrr VALUES(42.82);
    INSERT INTO twrr VALUES(27.06);
    INSERT INTO twrr VALUES(8.13);
    INSERT INTO twrr VALUES(8.21);
    INSERT INTO twrr VALUES(8.19);
    INSERT INTO twrr VALUES(35.31);
    INSERT INTO twrr VALUES(8.59);
    INSERT INTO twrr VALUES(36.67);
    INSERT INTO twrr VALUES(8.59);
    INSERT INTO twrr VALUES(8.59);
    INSERT INTO twrr VALUES(42.46);
    INSERT INTO twrr VALUES(42.04);
    INSERT INTO twrr VALUES(42.04);
    INSERT INTO twrr VALUES(8.75);
    INSERT INTO twrr VALUES(8.75);
    INSERT INTO twrr VALUES(41.61);
    INSERT INTO twrr VALUES(8.75);
    INSERT INTO twrr VALUES(8.78);
    INSERT INTO twrr VALUES(8.86);
    INSERT INTO twrr VALUES(37.70);
    INSERT INTO twrr VALUES(8.78);
    INSERT INTO twrr VALUES(37.70);
    INSERT INTO twrr VALUES(8.80);
    INSERT INTO twrr VALUES(8.77);
    INSERT INTO twrr VALUES(37.54);
    INSERT INTO twrr VALUES(37.48);
    INSERT INTO twrr VALUES(8.61);
    INSERT INTO twrr VALUES(8.61);
    INSERT INTO twrr VALUES(41.17);
    INSERT INTO twrr VALUES(8.61);

    DB:4.89:Poduct Query Working Fine On Ms Sql &Amp; Not On Oracle 7x

    I suppose this thread should've gone in {forum:id=75} instead.

    868119 wrote:
    Till this moment I always feel oracle is more powerful DB tool, but recently I fail to run some queries on oracle 10g same working fine on MS SQL2005. So you concluded that's a flaw in the "tool"?

    --Table
    create table twrr(id numeric(18,2))And what's the actual resulting data types in respective rdbms?

    I think the key here is not reading the docs (for data types and functions) - e.g. from BOL:
    +"Trigonometric and other functions, including EXP, LOG, LOG10, SQUARE, and SQRT, cast their input values to float and return a float value. "+

    In Oracle I believe this means you should have used BINARY_DOUBLE datatype. Try it and come back.

  • RELEVANCY SCORE 4.80

    DB:4.80:Splitfullname k3




    Hi All,
    I have table wit sample data as:-

    CREATE TABLE #TEMP
    (
    FULLNAME VARCHAR(100),
    ID INT
    )

    INSERT INTO #TEMP VALUES (TUCKER, KEVIN G, 1)
    INSERT INTO #TEMP VALUES (SCOTT, JOHN, 2)
    INSERT INTO #TEMP VALUES (ERIC, T W, 3)
    INSERT INTO #TEMP VALUES (MUNICH, SMITH D, 4)
    INSERT INTO #TEMP VALUES (LYOD SR, CLIVE G, 5)
    INSERT INTO #TEMP VALUES (HANSEN JR, CHARLES S, 6)
    INSERT INTO #TEMP VALUES (BROWN,SHERMAN, 7)
    INSERT INTO #TEMP VALUES (ANDREWS III, CLARK A, 8)
    INSERT INTO #TEMP VALUES (MAMMTAN, MARY LOU, 9)

    SELECT FULLNAME, ID

    FROM
    #TEMP
    ORDER BY
    2

    DROP TABLE #TEMP

    DB:4.80:Splitfullname k3

    Hi Naomi,
    Thank You for your help, here is the modified version depending on my requirement.

    CREATE TABLE #temp
    (
    FULLNAME VARCHAR(100),
    ID INT
    )

    INSERT INTO #TEMP VALUES (TUCKER, KEVIN G, 1)
    INSERT INTO #TEMP VALUES (SCOTT, JOHN, 2)
    INSERT INTO #TEMP VALUES (ERIC, T W, 3)
    INSERT INTO #TEMP VALUES (MUNICH, SMITH D, 4)
    INSERT INTO #TEMP VALUES (LYOD SR, CLIVE G, 5)
    INSERT INTO #TEMP VALUES (HANSEN JR, CHARLES S, 6)
    INSERT INTO #TEMP VALUES (BROWN,SHERMAN, 7)
    INSERT INTO #TEMP VALUES (ANDREWS III, CLARK A, 8)
    INSERT INTO #TEMP VALUES (MAMMTAN, MARY LOU, 9)

    DECLARE @Suffixes TABLE (Suffix VARCHAR(5))
    INSERT INTO @Suffixes
    VALUES (I),(II),(III),(IV),(V),(SR),(JR),(1st),(2nd),(3rd)

    SELECT
    T.id, T.Fullname , F7.*, F4.[Last Name], F4.Suffix--, F1.cFirstName, F5.FName, F5.MInitial, F6.MIExists
    FROM
    #TEMP T
    CROSS APPLY (
    SELECT
    LEFT(T.FullName, CHARINDEX(,, T.FULLNAME + ,) - 1) AS cLastName,
    LTRIM(SUBSTRING (T.FullName, CHARINDEX(,, T.FULLNAME + ,) +1, LEN(T.FullName))) AS cFirstName
    ) F1
    CROSS APPLY (
    SELECT
    LEFT(F1.cLastName, CHARINDEX( , F1.cLastName + ) - 1) AS LName,
    SUBSTRING(F1.cLastName, CHARINDEX( , F1.cLastName + ) + 1, LEN(F1.cLastName)) AS pSuffix
    ) F2
    CROSS APPLY (
    SELECT
    CASE
    WHEN LEN(pSuffix) 0 AND EXISTS (SELECT 1 FROM @Suffixes S WHERE S.Suffix = pSuffix)
    THEN Y
    ELSE N
    END AS SuffixExists
    ) F3
    CROSS APPLY (
    SELECT
    CASE
    WHEN F3.SuffixExists = Y THEN F2.LName
    ELSE RTRIM(F2.LName + + F2.pSuffix)
    END AS [Last Name],
    CASE
    WHEN F3.SuffixExists = Y THEN F2.pSuffix
    ELSE
    END AS [Suffix]
    ) F4
    CROSS APPLY (
    SELECT
    LEFT(F1.cFirstName, CHARINDEX( , F1.cFirstName + ) - 1) AS FName,
    SUBSTRING(F1.cFirstName, CHARINDEX( , F1.cFirstName + ) + 1, LEN(F1.cFirstName)) AS MInitial
    ) F5
    CROSS APPLY (
    SELECT
    CASE
    WHEN LEN(LTRIM(RTRIM(MInitial))) = 1 THEN Y
    ELSE N
    END AS MIExists
    ) F6
    CROSS APPLY (
    SELECT
    CASE
    WHEN F6.MIExists = Y THEN F5.FName
    ELSE RTRIM(F5.FName + + F5.MInitial)
    END AS [First Name],
    CASE
    WHEN F6.MIExists = Y THEN F5.MInitial
    ELSE
    END AS [Middle Initial]
    ) F7

    DROP TABLE #TEMP

  • RELEVANCY SCORE 4.70

    DB:4.70:Analytic Function Help zd


    TABLE T1(R1_ID,R2,R3)
    insert into t1 values(63,800,'1/1/2005')
    insert into t1 values(64,841,'1/1/2005')
    insert into t1 values(64,862,'1/1/2006')
    insert into t1 values(64,879,'4/1/2007')
    insert into t1 values(64,952,'4/1/2008')
    insert into t1 values(64,980,'2/1/2009')
    insert into t1 values(64,1010,'2/1/2010')
    insert into t1 values(64,1041,'2/1/2011')
    insert into t1 values(66,841,'1/1/2005')
    insert into t1 values(66,862,'1/1/2006')
    insert into t1 values(66,879,'4/1/2007')
    insert into t1 values(66,952,'4/1/2008')
    insert into t1 values(66,980,'2/1/2009')
    insert into t1 values(66,1010,'2/1/2010')
    insert into t1 values(66,1042,'2/1/2011')
    insert into t1 values(67,841,'1/1/2005')
    insert into t1 values(67,862,'1/1/2006')
    insert into t1 values(67,879,'4/1/2007')
    insert into t1 values(67,952,'4/1/2008')
    insert into t1 values(67,980,'2/1/2009')
    insert into t1 values(67,1009,'2/1/2010')
    insert into t1 values(67,1035,'2/1/2011')
    insert into t1 values(112,3660,'1/1/2005')
    insert into t1 values(112,3806,'1/1/2006')
    insert into t1 values(112,4500,'8/1/2006')
    insert into t1 values(112,7280,'3/1/2007')
    insert into t1 values(112,8600,'2/1/2008')
    insert into t1 values(112,8818,'5/1/2008')
    insert into t1 values(112,9170,'2/1/2009')
    insert into t1 values(112,9489,'2/1/2010')
    insert into t1 values(112,9778,'2/1/2011')
    insert into t1 values(537,7000,'11/27/2005')
    insert into t1 values(537,7000,'12/1/2005')

    SELECT distinct R1_ID,MAX(R2) OVER(PARTITION BY R1_ID),MAX(R3) OVER(PARTITION BY R1_ID)
    FROM T1
    order by R1_ID

    I WANT MAX OF R2 R3 IN ONE SQL STATEMENT. IS THAT POSSIBLE?
    ALTHOUGH I CAN WRITE A SUBQUERY WITH R3 TO GET THE RESULT.

    DB:4.70:Analytic Function Help zd

    with temp as(
    SELECT R1_ID,R2,R3,
    row_number() over(partition by R1_ID
    order by R2 desc,R3 desc) rn
    FROM T1)
    select * from tmp where rn=1 order by R1_ID

  • RELEVANCY SCORE 4.64

    DB:4.64:Order By Clause af


    Hi Experts,
    I have sql script as:-

    CREATE TABLE #TEMP
    (
    MONTHSEC INT,
    MONTHDES VARCHAR(50)
    )

    INSERT INTO #TEMP VALUES (1, January, 2010)
    INSERT INTO #TEMP VALUES (2, February, 2010)
    INSERT INTO #TEMP VALUES (3, March, 2010)
    INSERT INTO #TEMP VALUES (4, April, 2010)
    INSERT INTO #TEMP VALUES (5, May, 2010)
    INSERT INTO #TEMP VALUES (6, June, 2010)
    INSERT INTO #TEMP VALUES (7, July, 2010)
    INSERT INTO #TEMP VALUES (8, August, 2010)
    INSERT INTO #TEMP VALUES (9, September, 2010)
    INSERT INTO #TEMP VALUES (10, October, 2010)
    INSERT INTO #TEMP VALUES (11, November, 2010)
    INSERT INTO #TEMP VALUES (12, December, 2010)

    INSERT INTO #TEMP VALUES (1, January, 2011)
    INSERT INTO #TEMP VALUES (2, February, 2011)
    INSERT INTO #TEMP VALUES (3, March, 2011)
    INSERT INTO #TEMP VALUES (4, April, 2011)
    INSERT INTO #TEMP VALUES (5, May, 2011)
    INSERT INTO #TEMP VALUES (6, June, 2011)
    INSERT INTO #TEMP VALUES (7, July, 2011)
    INSERT INTO #TEMP VALUES (8, August, 2011)
    INSERT INTO #TEMP VALUES (9, September, 2011)
    INSERT INTO #TEMP VALUES (10, October, 2011)
    INSERT INTO #TEMP VALUES (11, November, 2011)
    INSERT INTO #TEMP VALUES (12, December, 2011)

    SELECT MONTHSEC, MONTHDES--,
    --RIGHT(MONTHDES, 4) AS YEAR,
    --SUBSTRING(MONTHDES, 1, LEN(MONTHDES) - 6) AS MONTH
    FROM
    #TEMP
    WHERE
    CAST(REPLACE(MONTHDES, ,, 1 ) AS DATE) BETWEEN
    CAST(DATENAME(MONTH, DATEADD(MONTH, DATEPART(MONTH, GETDATE()), 0)) +, + CAST(DATEPART(YEAR, GETDATE()) - 1 AS VARCHAR(50)) AS VARCHAR(50))
    AND
    CAST(DATENAME(MONTH, DATEADD(MONTH, DATEPART(MONTH, GETDATE()) - 1, 0)) +, + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(50)) AS VARCHAR(50))
    --ORDER BY
    -- CASE
    -- WHEN RIGHT(MONTHDES, 4) = 2010 THEN MONTHSEC
    -- END
    -- SUBSTRING(MONTHDES, 1, LEN(MONTHDES) - 6) DESC

    DROP TABLE #TEMP

    DB:4.64:Order By Clause af

    Thank You Tom,

    SELECT *
    FROM
    #TEMP
    WHERE
    CAST(REPLACE(MONTHDES, ,, 1 ) As DATE) BETWEEN
    CAST(DATENAME(MONTH, DATEADD(MONTH, DATEPART(MONTH, GETDATE()), 0)) +, + CAST(DATEPART(YEAR, GETDATE()) - 1 AS VARCHAR(50)) AS VARCHAR(50))
    AND
    CAST(DATENAME(MONTH, DATEADD(MONTH, DATEPART(MONTH, GETDATE()) - 1, 0)) +, + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(50)) AS VARCHAR(50))
    ORDER BY CAST(REPLACE(MONTHDES, ,, 1 ) As DATE) DESC

  • RELEVANCY SCORE 4.50

    DB:4.50:Debuging Problem 8c


    Hi Masters,Begin insert into t1 values (.............................................); insert into t3 values (.............................................); insert into t2 values (.............................................); insert into t5 values (.............................................); insert into t8 values (.............................................); insert into t4 values (.............................................); insert into t6 values (.............................................); insert into t7 values (.............................................); insert into t9 values (.............................................);Like this....I have 100 insert statement are there in my pl/sql block...So, if any insert statement failed..how to know which statement failed? How to debug ?Without using dbms_error_log table ?Please advise..!!RegardsAr

    DB:4.50:Debuging Problem 8c

    Maybedeclare loc varchar2(10);begin loc := 'insert t1'; insert into t1 values (.............................................); loc := 'insert t3'; insert into t3 values (.............................................); loc := 'insert t2'; insert into t2 values (.............................................); loc := 'insert t5'; insert into t5 values (.............................................);-- .....exception raise_application_error(-20000,loc || ' FAILED !!!');end;RegardsEtbin

  • RELEVANCY SCORE 4.43

    DB:4.43:Insert Using For Loop 3s


    Hi all,
    Let us consider the following:

    CREATE TABLE XX_SEG
    (
    SEGMENT3 VARCHAR2(20),
    SEGMENT4 VARCHAR2(20 )
    )CREATE TABLE XX_SEG3
    (
    SEG3 VARCHAR2(20)
    )CREATE TABLE XX_SEG4
    (
    SEG4 VARCHAR2(20)
    )Insert into xx_seg3 (SEG3) values ('0000');
    Insert into xx_seg3 (SEG3) values ('0001');
    Insert into xx_seg3 (SEG3) values ('0002');
    Insert into xx_seg3 (SEG3) values ('0003');
    Insert into xx_seg3 (SEG3) values ('0004');
    Insert into xx_seg3 (SEG3) values ('0005');
    Insert into xx_seg3 (SEG3) values ('0008');
    Insert into xx_seg3 (SEG3) values ('0015');
    Insert into xx_seg3 (SEG3) values ('0017');
    Insert into xx_seg3 (SEG3) values ('0100');
    Insert into xx_seg3 (SEG3) values ('0101');
    Insert into xx_seg3 (SEG3) values ('0121');
    Insert into xx_seg3 (SEG3) values ('0141');
    Insert into xx_seg3 (SEG3) values ('0142');

    Insert into xx_seg4 (SEG4) values ('1000');
    Insert into xx_seg4 (SEG4) values ('1004');
    Insert into xx_seg4 (SEG4) values ('1306');
    Insert into xx_seg4 (SEG4) values ('1308');
    Insert into xx_seg4 (SEG4) values ('1309');
    Insert into xx_seg4 (SEG4) values ('1310');
    Insert into xx_seg4 (SEG4) values ('1312');
    Insert into xx_seg4 (SEG4) values ('1321');
    Insert into xx_seg4 (SEG4) values ('1400');
    Insert into xx_seg4 (SEG4) values ('1407');
    Insert into xx_seg4 (SEG4) values ('1500');
    Insert into xx_seg4 (SEG4) values ('1600');
    Insert into xx_seg4 (SEG4) values ('1801');
    Insert into xx_seg4 (SEG4) values ('1803');
    INSERT INTO XX_SEG4 (SEG4) VALUES ('1806');
    Insert into xx_seg4 (SEG4) values ('1807');The issue is: For each seg3 i want to insert into table xx_seg all values for seg4.

    i try the following:

    DECLARE

    BEGIN
    FOR I IN (SELECT SEG3 FROM XX_SEG3)LOOP
    FOR I2 IN (SELECT seg4 FROM X_SEG4)LOOP
    INSERT INTO XX_SEG
    values (i.seg3,i2.seg4);
    end loop;
    end loop;
    end;but i receive error:
    ORA-06550: line 5, column 33:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 5, column 16:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 7, column 24:
    PLS-00364: loop index variable 'I2' use is invalid
    ORA-06550: line 7, column 27:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 6, column 7:Any ideas?
    Version: 11g

    Thanks in advance,
    Bahchevanov.

    DB:4.43:Insert Using For Loop 3s

    Hi,

    You can just use :

    insert into XX_SEG(SEGMENT3,SEGMENT4)
    select SEG3,SEG4
    from XX_SEG3,XX_SEG4;It's simple , and it will be faster than what you want to use.

  • RELEVANCY SCORE 4.42

    DB:4.42:Sql: How To Differentiate Between Numeric And Alpha Numeric Value 81


    Pls. check the following requirements and let me know how to prepare the SQL:

    1. I want to find out the product which starts with Alpha value seperately

    2. I want to find out the product which starts with Numeric value seperately

    data is as follows:

    CREATE TABLE prod
    (product_code VARCHAR2(4) NOT NULL)INSERT INTO prod
    VALUES
    ('L016')
    /
    INSERT INTO prod
    VALUES
    ('A035')
    /
    INSERT INTO prod
    VALUES
    ('B001')
    /
    INSERT INTO prod
    VALUES
    ('1717')
    /
    INSERT INTO prod
    VALUES
    ('7151')
    /
    INSERT INTO prod
    VALUES
    ('7019')
    /
    INSERT INTO prod
    VALUES
    ('0729')
    /
    INSERT INTO prod
    VALUES
    ('0730')
    /
    INSERT INTO prod
    VALUES
    ('L007')
    /
    INSERT INTO prod
    VALUES
    ('C013')
    /
    INSERT INTO prod
    VALUES
    ('D008')
    /
    INSERT INTO prod
    VALUES
    ('L021')
    /
    INSERT INTO prod
    VALUES
    ('0710')
    /
    INSERT INTO prod
    VALUES
    ('0718')
    /
    INSERT INTO prod
    VALUES
    ('L005')
    /
    INSERT INTO prod
    VALUES
    ('0716')
    /
    INSERT INTO prod
    VALUES
    ('0711')
    /
    INSERT INTO prod
    VALUES
    ('0701')
    /
    INSERT INTO prod
    VALUES
    ('0724')
    /
    INSERT INTO prod
    VALUES
    ('0728')
    /
    INSERT INTO prod
    VALUES
    ('0714')
    /
    INSERT INTO prod
    VALUES
    ('0704')
    /
    INSERT INTO prod
    VALUES
    ('7150')
    /
    INSERT INTO prod
    VALUES
    ('L024')
    /
    INSERT INTO prod
    VALUES
    ('L033')
    /
    INSERT INTO prod
    VALUES
    ('0721')
    /
    INSERT INTO prod
    VALUES
    ('0708')
    /
    INSERT INTO prod
    VALUES
    ('0723')
    /
    INSERT INTO prod
    VALUES
    ('L004')
    /
    INSERT INTO prod
    VALUES
    ('L018')
    /
    INSERT INTO prod
    VALUES
    ('0725')
    /
    INSERT INTO prod
    VALUES
    ('0719')
    /
    INSERT INTO prod
    VALUES
    ('0726')
    /
    INSERT INTO prod
    VALUES
    ('0712')
    /
    INSERT INTO prod
    VALUES
    ('0727')
    /
    INSERT INTO prod
    VALUES
    ('0703')
    /
    INSERT INTO prod
    VALUES
    ('TEST')
    /Regards,

    Shariful

  • RELEVANCY SCORE 4.35

    DB:4.35:How To...Order By df


    Hi all,
    create table tt_1 (col varchar2(10));

    insert into TT_1 values('AAA');
    insert into tt_1 values('BBB');
    insert into tt_1 values('CCC');
    insert into TT_1 values('DDD');
    insert into tt_1 values('TEST');
    insert into tt_1 values('UUUU');
    insert into tt_1 values('ZZZ');
    insert into TT_1 values('LLL');
    insert into TT_1 values('PPP');

    select * from tt_1 ;

    ------------
    AAA
    BBB
    CCC
    DDD
    TEST
    UUUU
    ZZZ
    LLL
    PPPDoes anybody knows how can value 'TEST' be the last of the result from query? Any specific order by clause which i don't know? Maybe any aggregation functions?

    Pls help

    Version: 11g

    Thanks in advance,
    Bahchevanov.

    DB:4.35:How To...Order By df

    Do it in a subquery :

    select col from (
    select col from tt_1 where col in ('AAA', 'CCC', 'ZZZ')
    union -- or UNION ALL ?
    select col from tt_1 where col in ('TEST', 'PPP', 'UUU')
    )
    order by nullif(col,'TEST') nulls last;Edited by: odie_63 on 2 oct. 2012 14:07

  • RELEVANCY SCORE 4.32

    DB:4.32:Rows In Desired Format js


    Hi, How I can get results 10 number in one row untill the end of record and I want to include comma (,) after every number. Help please.
    drop table #temp
    Create table #temp (e_id int)
    insert into #temp values(0012)
    insert into #temp values(0018)
    insert into #temp values(0019)
    insert into #temp values(0022)
    insert into #temp values(0122)
    insert into #temp values(0233)
    insert into #temp values(0344)
    insert into #temp values(0566)
    insert into #temp values(0677)
    insert into #temp values(0569)
    insert into #temp values(0607)
    insert into #temp values(0107)
    insert into #temp values(0999)
    insert into #temp values(0991)
    insert into #temp values(0992)
    insert into #temp values(0993)
    insert into #temp values(0994)
    insert into #temp values(0893)
    insert into #temp values(0894)
    insert into #temp values(0899)
    insert into #temp values(0394)
    insert into #temp values(0294)
    Select * from #temp
    --Desired results--------------------------------------
    (12,18,19,22,122,233,344,566,677,569,
    607,107,999,991,992,993,994,893,894,899,
    394,294)

  • RELEVANCY SCORE 4.30

    DB:4.30:Re: Values From Inline View Giving Wrong Result... 9s


    create table a123(accode number,amount number,drcr varchar2(1));insert into A123 (ACCODE, AMOUNT, DRCR)values (1234, 1000, 'D');insert into A123 (ACCODE, AMOUNT, DRCR)values (1234, 5000, 'C');insert into A123 (ACCODE, AMOUNT, DRCR)values (12345, 2000, 'C');insert into A123 (ACCODE, AMOUNT, DRCR)values (12345, 7000, 'D');

  • RELEVANCY SCORE 4.30

    DB:4.30:Query Help Name 1c


    Experts,
    I have table with some sample values :-

    CREATE TABLE #TEMP
    (
    NAME VARCHAR(100)
    )

    INSERT INTO #TEMP VALUES (STACY L BENNET)
    INSERT INTO #TEMP VALUES (ALAN A. BATEMAN)
    INSERT INTO #TEMP VALUES (STACY BENNET U)
    INSERT INTO #TEMP VALUES (ALAN BATEMAN O.)
    INSERT INTO #TEMP VALUES (TERENA COOKER)
    INSERT INTO #TEMP VALUES (VICKY DAVIS)
    INSERT INTO #TEMP VALUES (PATRICIA ANN MORE GRAHAM)
    INSERT INTO #TEMP VALUES (LORII JAN COOSERT WILLIAMS)

    SELECT NAME
    FROM
    #TEMP

    DROP TABLE #TEMP

    DB:4.30:Query Help Name 1c

    Hi, Go through the following link. you will get your answer..
    http://stackoverflow.com/questions/5266313/how-do-i-chunk-the-records-in-sql-server-2005
    Thanks

  • RELEVANCY SCORE 4.29

    DB:4.29:Retriveial Of Data Based On Inner Joins 97


    I have Five tables (Subject,English,Hindi,Maths,Science,Social)
    where Subject is the Primary table and rest all lookup table For Subject

    My select Statement need to display all chapter names Based on the ID's in subject table

    SELECT English.ChapterName AS English, Hindi.ChapterName AS Hindi, Maths.ChapterName AS Maths, Social.ChapterName AS Social,
    Science.ChapterName AS Science
    FROM Subjects INNER JOIN
    English ON Subjects.English = English.ChapterNo INNER JOIN
    Hindi ON Subjects.Hindi = Hindi.ChapterNo INNER JOIN
    Maths ON Subjects.Maths = Maths.ChapterNo INNER JOIN
    Science ON Subjects.Science = Science.ChapterNo INNER JOIN
    Social ON Subjects.Social = Social.ChapterNo
    WHERE (Subjects.SubjectID = 1)

    If it's SubjectID 1 we can get the result set as complete data is populated What about SubjectID 3and 4 as some value's are null

    How can I retrive the output for them

    Sample Data

    Create table English (ChapterNo int,ChapterName varchar(50))

    Insert into English values (1,'English1')

    Insert into English values (2,'English2')

    Insert into English values (3,'English3')

    Insert into English values (4,'English4')

    Insert into English values (5,'English5')

    Insert into English values (6,'English6')

    Create table Maths (ChapterNo int,ChapterName varchar(50))

    Insert into Maths values (1,'Maths1')

    Insert into Maths values (2,'Maths2')

    Insert into Maths values (3,'Maths3')

    Insert into Maths values (4,'Maths4')

    Insert into Maths values (5,'Maths5')

    Insert into Maths values (6,'Maths6')

    Create table Hindi (ChapterNo int,ChapterName varchar(50))

    Insert into Hindi values (1,'Hindi1')

    Insert into Hindi values (2,'Hindi2')

    Insert into Hindi values (3,'Hindi3')

    Insert into Hindi values (4,'Hindi4')

    Insert into Hindi values (5,'Hindi5')

    Insert into Hindi values (6,'Hindi6')

    Create table Science (ChapterNo int,ChapterName varchar(50))

    Insert into Science values (1,'Science1')

    Insert into Science values (2,'Science2')

    Insert into Science values (3,'Science3')

    Insert into Science values (4,'Science4')

    Insert into Science values (5,'Science5')

    Insert into Science values (6,'Science6')

    Create table Social (ChapterNo int,ChapterName varchar(50))

    Insert into Social values (1,'Social1')

    Insert into Social values (2,'Social2')

    Insert into Social values (3,'Social3')

    Insert into Social values (4,'Social4')

    Insert into Social values (5,'Social5')

    Insert into Social values (6,'Social6')

    Create Table Subjects (SubjectID int,English int,Maths int ,Hindi int ,Science int,Social int )

    Insert into Subjects (SubjectID,English,Maths,Hindi,Science,Social) values (1,1,1,1,1,1)

    Insert into Subjects (SubjectID,English,Maths,Hindi,Science,Social) values (2,1,2,3,2,4)

    Insert into Subjects (SubjectID,English,Maths) values (3,1,2)

    Insert into Subjects (SubjectID,English,Social) values (2,1,4)

    Insert into Subjects (SubjectID,English,Maths,Social) values (2,1,3,4)

    Insert into Subjects (SubjectID,English,Maths,Hindi,Science) values (2,1,2,3,2)

  • RELEVANCY SCORE 4.28

    DB:4.28:How To Get Columns Horizontal In Pl/Sql Procedure cp


    My task is I want to retrieve all the Insert Statements of the rows that are present in each table of a particular Schema.
    When I give input as Schema name(Scott) in a Procedure, Then the procedure retrieves all the Insert statements of rows of all tables that are present in scott user.

    For this I started like this

    CREATE OR REPLACE PROCEDURE MYPROC(UID DBA_OBJECTS.OWNER%TYPE)
    AS
    CURSOR MYCUR IS select o.owner, o.object_name,c.column_name
    from dba_objects o, dba_tab_cols c where o.owner=c.OWNER(+) AND O.OWNER=UID
    AND O.OBJECT_NAME=C.TABLE_NAME AND ROWNUM20 AND O.OBJECT_TYPE='TABLE';
    BEGIN
    FOR EREC IN MYCUR LOOP
    DBMS_OUTPUT.PUT_LINE('insert into '||erec.object_name||'('||erec.column_name||')'||'values(');
    END LOOP;
    END MYPROC;
    /

    When I execute this procedure I got the result as follows.
    EXEC MYPROC('SCOTT');

    insert into DEPT(DEPTNO)values(
    insert into DEPT(DNAME)values(
    insert into DEPT(LOC)values(
    insert into EMP(EMPNO)values(
    insert into EMP(ENAME)values(
    insert into EMP(JOB)values(
    insert into EMP(MGR)values(
    insert into EMP(HIREDATE)values(
    insert into EMP(SAL)values(
    insert into EMP(COMM)values(
    insert into EMP(DEPTNO)values(
    insert into BONUS(ENAME)values(
    insert into BONUS(JOB)values(
    insert into BONUS(SAL)values(
    insert into BONUS(COMM)values(
    insert into SALGRADE(GRADE)values(
    insert into SALGRADE(LOSAL)values(
    insert into SALGRADE(HISAL)values(
    insert into PROJECT(PNO)values(
    As you see the columns are coming in line by line.
    Now My question is I want the columns of each table to come in only One Insert Statement.i.e. one insert statement for one table. All columns of one table should in one row. How to do this?
    Please Help me
    Thanks in Advance.

    DB:4.28:How To Get Columns Horizontal In Pl/Sql Procedure cp

    Hi,

    it goes almost the same, the next assumes l_line is filled with the basis insert statement, shown before, l_value_line is an insert statement line:
    for r_values in (select * from your_table)
    loop
    l_value_line := l_line||r_values.column_1||','||r_values.column_2||','||r_values.column_3||')';
    dbms_output.output_line(l_value_line);
    end loop;
    end;you have to replace column_1 etc with the columns of your table. If there are more columns, you have to extend the line with the concatenations.

    Hopes this will help, I think if you look around the net, you find already procedures which will produce the output you want.

    Herald ten Dam
    Superconsult.nl

  • RELEVANCY SCORE 4.27

    DB:4.27:Display % Of Data kz


    create table mytype(id number, mtype varchar2(20))

    insert into mytype values (1,'A');
    insert into mytype values (2,'A');
    insert into mytype values (1,'A');
    insert into mytype values (1,'A');
    insert into mytype values (1,'A');
    insert into mytype values (1,'A');
    insert into mytype values (1,'B');
    insert into mytype values (1,'A');
    insert into mytype values (1,'B');
    COMMIT;

    select distinct count(*), MTYPE from MYTYPE
    group by MTYPE

    How do I display in %, data A vs B.

    Eg: 90% of records are A, and 10% records are B.

    Any analytic function can be used ?

    DB:4.27:Display % Of Data kz

    Hi,

    Thanks for posting the sample data in such a useful form!
    Don't forget to post the results you want from that data. Do you really want:
    MTYPE PCT
    ----- ---
    A 90
    B 10from that data? I'm guessing you don't.

    You can do this, using the analytic RATIO_TO_REPORT function:
    SELECT mtype
    , 100 * RATIO_TO_REPORT (COUNT (*)) OVER ()AS pct
    FROM mytype
    GROUP BY mtype
    ;

  • RELEVANCY SCORE 4.27

    DB:4.27:Query Help Name jd


    Experts,
    I have table with some sample values :-

    CREATE TABLE #TEMP
    (
    NAME VARCHAR(100)
    )

    INSERT INTO #TEMP VALUES ('STACY L BENNET')
    INSERT INTO #TEMP VALUES ('ALAN A. BATEMAN')
    INSERT INTO #TEMP VALUES ('STACY BENNET U')
    INSERT INTO #TEMP VALUES ('ALAN BATEMAN O.')
    INSERT INTO #TEMP VALUES ('TERENA COOKER')
    INSERT INTO #TEMP VALUES ('VICKY DAVIS')
    INSERT INTO #TEMP VALUES ('PATRICIA ANN MORE GRAHAM')
    INSERT INTO #TEMP VALUES ('LORII JAN COOSERT WILLIAMS')

    SELECT NAME
    FROM
    #TEMP

    DROP TABLE #TEMP

    DB:4.27:Query Help Name jd

    Hi All,
    I made it worked myself with some blog from Naomi and some help from google.
    It is working perfectly now.
    Thanks BTW for looking into this SQLUSA.com.
    Regards,
    KumarKG, MCTS

  • RELEVANCY SCORE 4.24

    DB:4.24:Pick Up 3% Of Rows Per Day pc


    create table test1(x number, y date)

    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '03-OCT-2010');
    insert into test1 VALUES (1, '03-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '03-OCT-2010');
    insert into test1 VALUES (1, '03-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '03-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '01-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '02-OCT-2010');
    insert into test1 VALUES (1, '03-OCT-2010');
    COMMIT;

    I need an sql which would pick 3% of the records per day

    select count(*), y from test1
    group by y

    2110/1/2010
    1410/2/2010
    610/3/2010

    So I need 3% of 21 (.63) randomely picked up for 10/1.

    similarly 3% of total number of rows from 10/2, 10/3 to be picked up

    DB:4.24:Pick Up 3% Of Rows Per Day pc


    Syntatically? No.

    Are you sure? I get

    ORA-30485: missing ORDER BY expression in the window specification
    30485. 00000 - "missing ORDER BY expression in the window specification"
    *Cause: Either the ORDER BY expression is mandatory for this function, or
    there is an aggregation group without any ORDER by expression.
    Regards,
    Bob

  • RELEVANCY SCORE 4.24

    DB:4.24:Splitting Up Sum Function Into 2 Columns 37


    There are only 2 possible values for t1_type (1 or 2)

    create table t1 (id number, t1_type number, amount number);
    insert into t1 values (1,1,5);
    insert into t1 values (1,1,10);
    insert into t1 values (1,2,35);
    insert into t1 values (1,2,20);

    insert into t1 values (2,1,5);
    insert into t1 values (2,1,10);
    insert into t1 values (2,2,35);
    insert into t1 values (2,2,20);

    for each id I require an sql statement to return exactly one row with 3 columns,
    id, sum(amount) /*for t1_type 1*/,sum(amount)/*for t1_type 2*/

    Desired output from above data would be:
    1,15,55
    2,15,55

    Thanks
    Sam.

    DB:4.24:Splitting Up Sum Function Into 2 Columns 37

    Yes Jeneesh,
    I had many other columns and a group by that complicated things a little but Mr Barry put me on the right track and I used something very similar to what you have in your example.

  • RELEVANCY SCORE 4.24

    DB:4.24:Sum Subset Rows In Column For Particular Rows 98


    I need to get a Sum of all Analyte Values for a given patient. I only want to get a sum if all Analyte Values are not null. I wrote some code to show my source table and how the new query should look. This is an elementary version of what
    I'm really working with. I have several other columns in the table.
    I need to sum the Analyte Values A thru D and put that sum in Analyte E Value. Any suggestions?

    create table SourceTable (PatientID int, Analyte nchar(1), Value float)
    insert into SourceTable values (1, 'A', 10)
    insert into SourceTable values (1, 'B', 20)
    insert into SourceTable values (1, 'C', 30)
    insert into SourceTable values (1, 'D', 40)
    insert into SourceTable values (1, 'E', null)
    insert into SourceTable values (2, 'A', 11)
    insert into SourceTable values (2, 'B', 22)
    insert into SourceTable values (2, 'C', null)
    insert into SourceTable values (2, 'D', 44)
    insert into SourceTable values (2, 'E', null)

    select * from SourceTable

    drop table SourceTable

    create table FinalTable (PatientID int, Analyte nchar(1), Value float)
    insert into FinalTable values (1, 'A', 10)
    insert into FinalTable values (1, 'B', 20)
    insert into FinalTable values (1, 'C', 30)
    insert into FinalTable values (1, 'D', 40)
    insert into FinalTable values (1, 'E', 100)
    insert into FinalTable values (1, 'A', 11)
    insert into FinalTable values (1, 'B', 22)
    insert into FinalTable values (1, 'C', null)
    insert into FinalTable values (1, 'D', 44)
    insert into FinalTable values (1, 'E', null)

    select * from FinalTable

    drop table FinalTable

    Ryan

    DB:4.24:Sum Subset Rows In Column For Particular Rows 98

    Try:
    SELECT PatientID, Analyte,
    CASE WHEN Analyte = 'E' THEN ((SELECT SUM(VALUE)
    FROM SourceTable S2 WHERE S2.PatientID = S.PatientID
    AND S2.Analyte IN ('A','B','C','D')
    GROUP BY PatientID HAVING COUNT(VALUE) = 4)) ELSE VALUE END
    FROM SourceTable S

    I assume that PatientID/Analyte combination is unique.For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

  • RELEVANCY SCORE 4.23

    DB:4.23:Search Substring In Clob 7a


    Hello everyone,

    We have two tables, and one has CLOB column.
    The sample data like these:
    create table t1(
    id number,
    cons clob);
    insert into t1 values(1, '8562');
    insert into t1 values(2, '876730');
    insert into t1 values(3, '805100');
    insert into t1 values(4, '437868');
    insert into t1 values(5, '626220');
    insert into t1 values(6, '380124');
    insert into t1 values(7, '009462');
    insert into t1 values(8, '778730');
    insert into t1 values(9, '567100');
    insert into t1 values(10, '089868');
    insert into t1 values(11, '797220');
    insert into t1 values(12, '556124');
    commit;
    create table t2(
    type_id number,
    type_name varchar2(2000));
    insert into t2 values(1, '8562');
    insert into t2 values(1, '876730');
    insert into t2 values(1, '805100');
    insert into t2 values(1, '437868');
    insert into t2 values(1, '626225');
    insert into t2 values(1, '380126');
    insert into t2 values(2, '999962');
    insert into t2 values(2, '999930');
    insert into t2 values(2, '888880');
    insert into t2 values(3, '787868');
    insert into t2 values(3, '556520');
    insert into t2 values(3, '123424');
    commit;

    We try to get info from t1 which t2.type_id = 1 and t2.type_name in t1.cons. Because t1.cons is CLOB, we are not able to use IN function here. Is there a way to pull out these data???

    select * from t1
    where cons in (select type_name from t2 where type_id = 1);
    where cons in (select type_name from t2 where type_id = 1)
    *
    ERROR at line 2:
    ORA-00932: inconsistent datatypes: expected - got CLOB

    Here is the out put we expected:
    select * from t1
    2 where cons like '8562' or
    3 cons like '876730' or
    4 cons like '805100' or
    5 cons like '437868' or
    6 cons like '626225' or
    7 cons like '380126';

    ID CONS
    ---------- ----------------------------------
    1 8562
    2 876730
    3 805100
    4 437868

    Thanks for the help!!!

    DB:4.23:Search Substring In Clob 7a

    Thanks to Tubby!!!
    Now I know how to use dbms_lob.instr properly.

  • RELEVANCY SCORE 4.23

    DB:4.23:Need To Update Table Col With Same Cols Different Values 73


    Dear All,

    create table tab1 (col1 varchar2(20), col2 varchar2(20))

    insert into tab1 values (111,111);
    insert into tab1 values (111,111);
    insert into tab1 values (111,111);
    insert into tab1 values (222,222);
    insert into tab1 values (222,222);
    insert into tab1 values (222,222);
    insert into tab1 values (222,222);
    insert into tab1 values (666,666);
    insert into tab1 values (666,666);
    insert into tab1 values (666,666);
    insert into tab1 values (666,666);
    insert into tab1 values (999,999);
    insert into tab1 values (999,999);
    insert into tab1 values (919,919);
    insert into tab1 values (919,919);
    insert into tab1 values (919,919);
    insert into tab1 values (919,919);
    insert into tab1 values (55,55);
    insert into tab1 values (55,55);
    insert into tab1 values (671,671);

    Tab1
    ----------
    Col1 Col2
    111 111
    111 111
    111 111
    222 222
    222 222
    222 222
    222 222
    666 666
    666 666
    666 666
    919 919
    919 919
    919 919
    55 671

    Iam looking for update where updateing from 111 to 222 and 666 to 919,55 to 671 likewise.
    Pls help me get the proper update script to update

    Thanks
    Junu

  • RELEVANCY SCORE 4.22

    DB:4.22:Comma Separated mz


    CREATE TABLE TEST12(TNO VARCHAR2(10));

    INSERT INTO TEST12 VALUES('ASCTT');
    INSERT INTO TEST12 VALUES('scott');
    INSERT INTO TEST12 VALUES('MILLER');

    SELECT ASCTT,scott,MILLER FROM TEST12

    comma saparated row

    DB:4.22:Comma Separated mz

    SQL select listagg(a,',') within group (order by rownum) from
    2 (select distinct tno a from test12);

    LISTAGG(A,',')WITHINGROUP(ORDERBYROWNUM)
    --------------------------------------------------------------------------------
    MILLER,ASCTT,scott

  • RELEVANCY SCORE 4.21

    DB:4.21:Denormalized View For Normalized Tables c1


    Hi,

    We have historical table that is denormalized and sample is shown in following code as table test where for each row we use to store 4*3 values(a_*,b_*,c_*,d_*) and that restrict us to only store 4(a,b,c,d) values for each column col1. Now we are redesigning
    and creating 2 new tables shown as new_test1(store all not repeated columns) and new_test2 (store values in different rows and no longer have restriction to store only 4 different type of values. But some part of our code can't be changed so is there a way
    we can create view on new tables that can show the output similar to old table for this application (we can't use the PIVOT function since the values in all a_*,b_*,c_*,d_* are dynamic)
    create table test(col1 int primary key, col2 int, col3 int,a_1 int, a_2 int, a_3 int,b_1 int, b_2 int, b_3 int,c_1 int, c_2 int, c_3 int,d_1 int, d_2 int, d_3 int,)
    go
    insert into test values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
    insert into test values (2,1,1,2,1,1,2,1,1,3,1,1,4,1,1)
    insert into test values (3,2,1,2,1,1,3,1,1,3,1,1,3,1,1)
    go
    --select * from test
    create table new_test1 (col1 int primary key, col2 int, col3 int)
    --drop table new_test2
    create table new_test2 (col1 int , mnemonic int,common_1 int, common_2 int, common_3 int)
    go
    insert into new_test1 values(1,1,1)
    insert into new_test1 values(2,1,1)
    insert into new_test1 values(3,2,1)
    --select * from new_test1
    insert into new_test2 values(1,1,1,1,1)
    insert into new_test2 values(1,2,1,1,1)
    insert into new_test2 values(1,3,1,1,1)
    insert into new_test2 values(1,4,1,1,1)
    insert into new_test2 values(2,1,2,1,1)
    insert into new_test2 values(2,2,2,1,1)
    insert into new_test2 values(2,3,3,1,1)
    insert into new_test2 values(2,4,4,1,1)
    insert into new_test2 values(3,1,2,1,1)
    insert into new_test2 values(3,2,3,1,1)
    insert into new_test2 values(3,3,3,1,1)
    insert into new_test2 values(3,4,3,1,1)
    --select * from new_test2
    go
    select * from test
    select * from new_test2

    DB:4.21:Denormalized View For Normalized Tables c1

    This is a common way to pivot data, by grouping (group by A.col1), spreading (case function), and aggregating (max in this case).

    SELECT
    A.col1,
    MAX(A.col2) AS col2,
    MAX(A.col3) AS col3,

    MAX(CASE WHEN B.mnemonic = 1 THEN common_1 END) AS a_1,
    MAX(CASE WHEN B.mnemonic = 1 THEN common_2 END) AS a_2,
    MAX(CASE WHEN B.mnemonic = 1 THEN common_3 END) AS a_3,

    MAX(CASE WHEN B.mnemonic = 2 THEN common_1 END) AS b_1,
    MAX(CASE WHEN B.mnemonic = 2 THEN common_2 END) AS b_2,
    MAX(CASE WHEN B.mnemonic = 2 THEN common_3 END) AS b_3,

    MAX(CASE WHEN B.mnemonic = 3 THEN common_1 END) AS c_1,
    MAX(CASE WHEN B.mnemonic = 3 THEN common_2 END) AS c_2,
    MAX(CASE WHEN B.mnemonic = 3 THEN common_3 END) AS c_3,

    MAX(CASE WHEN B.mnemonic = 4 THEN common_1 END) AS d_1,
    MAX(CASE WHEN B.mnemonic = 4 THEN common_2 END) AS d_2,
    MAX(CASE WHEN B.mnemonic = 4 THEN common_3 END) AS d_3
    FROM
    new_test1 AS A
    INNER JOIN
    new_test2 AS B
    ON B.col1 = A.col1
    GROUP BY
    A.col1
    GO

  • RELEVANCY SCORE 4.21

    DB:4.21:How Do I Set 1 Field Based On Another Field's Value za


    Ready to use DDL and DML is at bottom of post.

    Here is requirement

    Select Storeid, ResultSTr from MYINVEN
    where storeid in (select storeid from mystores);

    Is this possible:
    Assumptions:
    1) Values in Mystores are unique
    2) Store 2 and 3 do not have records in the the inventory table
    3) Store 257 has 2 entries (shouldn't be, but is) in the inventory table : 1 for posted, 1 for the not_posted
    only the value for posted should be returned

    Business Rule:
    if no records for a store then resultstring = -2 which means None
    if IsPosted = 0 then resultstring = -1 for not posted
    otherwise returnstring = DollarVariance

    IF I do it this way, the program will do some extra work to populate the DollarVariance column of my report.
    so i could also do query where "NP", "None" or a value (for NP=not posted, none = 0, or a value) and avoid program
    having to do translation:

    How do i accomplish this?

    Script:

    CREATE TABLE MYINVEN (
    BusinessDate DATE NOT NULL,
    StoreId NUMBER NOT NULL,
    IS_POSTED NUMBER NOT NULL,
    DollarVariance NUMBER NOT NULL
    );

    Insert into MYINVEN values('01-JAN-09',22,1,56.1962380373016);
    Insert into MYINVEN values('01-JAN-09',23,1,27.9953138565476);
    Insert into MYINVEN values('01-JAN-09',24,1,84.6182868793651);
    Insert into MYINVEN values('01-JAN-09',27,1,-26.388040847371);
    Insert into MYINVEN values('01-JAN-09',28,1,7.74762463239088);
    Insert into MYINVEN values('01-JAN-09',29,1,106.95623092996);
    Insert into MYINVEN values('01-JAN-09',30,0,48.7675861274802);
    Insert into MYINVEN values('01-JAN-09',31,1,15.6467153790675);
    Insert into MYINVEN values('01-JAN-09',32,1,-77.9898243777282);
    Insert into MYINVEN values('01-JAN-09',33,1,30.833365141369);
    Insert into MYINVEN values('01-JAN-09',34,1,233.272943187698);
    Insert into MYINVEN values('01-JAN-09',35,1,30.6456266606895);
    Insert into MYINVEN values('01-JAN-09',36,0,52.8851368701389);
    Insert into MYINVEN values('01-JAN-09',37,1,-3.90164880610118);
    Insert into MYINVEN values('01-JAN-09',38,1,225.935420401488);
    Insert into MYINVEN values('01-JAN-09',39,1,47.6557908494048);
    Insert into MYINVEN values('01-JAN-09',41,0,-124.744067559524);
    Insert into MYINVEN values('01-JAN-09',42,1,53.4204130670635);
    Insert into MYINVEN values('01-JAN-09',43,1,6.38381515208333);
    Insert into MYINVEN values('01-JAN-09',61,1,12.0223148722718);
    Insert into MYINVEN values('01-JAN-09',62,1,29.2444190115575);
    Insert into MYINVEN values('01-JAN-09',64,1,15.3725159140377);
    Insert into MYINVEN values('01-JAN-09',65,1,26.0484708912699);
    Insert into MYINVEN values('01-JAN-09',66,1,35.2802144904266);
    Insert into MYINVEN values('01-JAN-09',67,1,76.7794684834325);
    Insert into MYINVEN values('01-JAN-09',68,1,33.9220582379464);
    Insert into MYINVEN values('01-JAN-09',69,1,36.5029198174107);
    Insert into MYINVEN values('01-JAN-09',70,0,-77.0349676615079);
    Insert into MYINVEN values('01-JAN-09',71,1,39.6852832103175);
    Insert into MYINVEN values('01-JAN-09',72,1,74.5517848573909);
    Insert into MYINVEN values('01-JAN-09',73,1,10.0015442719246);
    Insert into MYINVEN values('01-JAN-09',75,1,22.7711470987103);
    Insert into MYINVEN values('01-JAN-09',76,1,81.5757000496032);
    Insert into MYINVEN values('01-JAN-09',78,1,17.5540669945437);
    Insert into MYINVEN values('01-JAN-09',81,1,68.2291666878472);
    Insert into MYINVEN values('01-JAN-09',82,1,123.602010710169);
    Insert into MYINVEN values('01-JAN-09',83,0,32.9594028041171);
    Insert into MYINVEN values('01-JAN-09',101,1,45.5224420896825);
    Insert into MYINVEN values('01-JAN-09',141,1,9.67715574002975);
    Insert into MYINVEN values('01-JAN-09',181,1,17.0124727906746);
    Insert into MYINVEN values('01-JAN-09',202,1,115.189752645387);
    Insert into MYINVEN values('01-JAN-09',203,1,15.5709022411458);
    Insert into MYINVEN values('01-JAN-09',204,1,66.374818209375);
    Insert into MYINVEN values('01-JAN-09',205,1,91.7158080706349);
    Insert into MYINVEN values('01-JAN-09',206,1,51.9813382005457);
    Insert into MYINVEN values('01-JAN-09',221,1,23.5417287154762);
    Insert into MYINVEN values('01-JAN-09',222,0,55.7924602968254);
    Insert into MYINVEN values('01-JAN-09',223,1,.814344456398814);
    Insert into MYINVEN values('01-JAN-09',224,1,43.2908852316468);
    Insert into MYINVEN values('01-JAN-09',241,1,86.2738173619048);
    Insert into MYINVEN values('01-JAN-09',243,1,53.8434677798611);
    Insert into MYINVEN values('01-JAN-09',244,0,239.814497402307);
    Insert into MYINVEN values('01-JAN-09',245,1,81.9958321151042);
    Insert into MYINVEN values('01-JAN-09',246,1,111.074670395164);
    Insert into MYINVEN values('01-JAN-09',247,1,75.2238771077877);
    Insert into MYINVEN values('01-JAN-09',248,1,48.6550748165179);
    Insert into MYINVEN values('01-JAN-09',249,1,-38.3536754705357);
    Insert into MYINVEN values('01-JAN-09',250,1,11.0105870511905);
    Insert into MYINVEN values('01-JAN-09',251,1,52.12213418125);
    Insert into MYINVEN values('01-JAN-09',253,1,46.9922463030507);
    Insert into MYINVEN values('01-JAN-09',255,1,19.1265949856151);
    Insert into MYINVEN values('01-JAN-09',256,1,74.7250347577133);
    Insert into MYINVEN values('01-JAN-09',257,1,4.88379714310516);
    Insert into MYINVEN values('01-JAN-09',257,0,-504.00);
    Insert into MYINVEN values('01-JAN-09',258,1,-45.3839983225694);
    Insert into MYINVEN values('01-JAN-09',259,1,36.6420978767361);
    Insert into MYINVEN values('01-JAN-09',260,1,14.6296297192956);
    Insert into MYINVEN values('01-JAN-09',262,1,21.5277724625496);
    Insert into MYINVEN values('01-JAN-09',263,1,58.5077986515377);
    Insert into MYINVEN values('01-JAN-09',264,1,1251.96544654812);
    Insert into MYINVEN values('01-JAN-09',266,1,-64.5363244219246);
    Insert into MYINVEN values('01-JAN-09',267,1,105.403476957986);
    Insert into MYINVEN values('01-JAN-09',268,1,5.28495281651786);
    Insert into MYINVEN values('01-JAN-09',269,1,35.8050916130208);
    Insert into MYINVEN values('01-JAN-09',271,1,33.5548317578869);
    Insert into MYINVEN values('01-JAN-09',272,1,122.655467788591);
    Insert into MYINVEN values('01-JAN-09',273,1,58.2453744848214);
    Insert into MYINVEN values('01-JAN-09',274,1,18.677713696627);
    Insert into MYINVEN values('01-JAN-09',276,1,6.28903578080357);
    Insert into MYINVEN values('01-JAN-09',277,1,146.155907327282);
    Insert into MYINVEN values('01-JAN-09',278,1,19.229526562004);
    Insert into MYINVEN values('01-JAN-09',279,1,37.9020666440972);
    Insert into MYINVEN values('01-JAN-09',280,1,70.6423695895833);
    Insert into MYINVEN values('01-JAN-09',281,1,14.2208384347718);
    Insert into MYINVEN values('01-JAN-09',282,1,60.0890863941964);
    Insert into MYINVEN values('01-JAN-09',285,1,27.9210758427084);
    Insert into MYINVEN values('01-JAN-09',286,1,14.4786017618055);
    Insert into MYINVEN values('01-JAN-09',287,1,31.3959733856151);
    Insert into MYINVEN values('01-JAN-09',289,1,33.6455208041666);
    Insert into MYINVEN values('01-JAN-09',290,1,.444137189632956);
    Insert into MYINVEN values('01-JAN-09',291,1,36.6552250985119);
    Insert into MYINVEN values('01-JAN-09',292,0,82.1052773777778);
    Insert into MYINVEN values('01-JAN-09',293,1,-70.6865030993056);
    Insert into MYINVEN values('01-JAN-09',294,1,27.3479393623016);
    Insert into MYINVEN values('01-JAN-09',295,1,72.4566623146329);
    Insert into MYINVEN values('01-JAN-09',296,1,-7.00232020099208);
    Insert into MYINVEN values('01-JAN-09',298,1,23.4004074976687);
    Insert into MYINVEN values('01-JAN-09',321,1,14.5780986745536);
    Insert into MYINVEN values('01-JAN-09',362,1,9.79801404950396);
    Insert into MYINVEN values('01-JAN-09',363,1,29.5149886612103);
    Insert into MYINVEN values('01-JAN-09',364,1,-296.451372662202);
    Insert into MYINVEN values('01-JAN-09',381,1,85.2999895050595);
    Insert into MYINVEN values('01-JAN-09',461,1,10.6145443503968);
    Insert into MYINVEN values('01-JAN-09',462,1,1.59701843521826);
    Insert into MYINVEN values('01-JAN-09',501,1,226.911175904266);
    Insert into MYINVEN values('01-JAN-09',521,1,-11.2910508754961);
    Insert into MYINVEN values('01-JAN-09',522,1,44.2152966406746);
    Insert into MYINVEN values('01-JAN-09',523,1,33.4726750488095);
    Insert into MYINVEN values('01-JAN-09',524,1,48.883513880506);
    Insert into MYINVEN values('01-JAN-09',564,1,-103.33631587252);
    Insert into MYINVEN values('01-JAN-09',624,1,56.1693590329613);

    /

    CREATE TABLE MYSTORES (
    STOREID NUMBER(10) NOT NULL);
    /

    Insert into MYSTORES values(101);
    Insert into MYSTORES values(141);
    Insert into MYSTORES values(181);
    Insert into MYSTORES values(202);
    Insert into MYSTORES values(203);
    Insert into MYSTORES values(204);
    Insert into MYSTORES values(205);
    Insert into MYSTORES values(206);
    Insert into MYSTORES values(22);
    Insert into MYSTORES values(221);
    Insert into MYSTORES values(222);
    Insert into MYSTORES values(223);
    Insert into MYSTORES values(224);
    Insert into MYSTORES values(23);
    Insert into MYSTORES values(24);
    Insert into MYSTORES values(241);
    Insert into MYSTORES values(243);
    Insert into MYSTORES values(244);
    Insert into MYSTORES values(245);
    Insert into MYSTORES values(246);
    Insert into MYSTORES values(247);
    Insert into MYSTORES values(248);
    Insert into MYSTORES values(249);
    Insert into MYSTORES values(250);
    Insert into MYSTORES values(251);
    Insert into MYSTORES values(253);
    Insert into MYSTORES values(255);
    Insert into MYSTORES values(256);
    Insert into MYSTORES values(257);
    Insert into MYSTORES values(258);
    Insert into MYSTORES values(259);
    Insert into MYSTORES values(260);
    Insert into MYSTORES values(262);
    Insert into MYSTORES values(263);
    Insert into MYSTORES values(264);
    Insert into MYSTORES values(266);
    Insert into MYSTORES values(267);
    Insert into MYSTORES values(268);
    Insert into MYSTORES values(269);
    Insert into MYSTORES values(27);
    Insert into MYSTORES values(271);
    Insert into MYSTORES values(272);
    Insert into MYSTORES values(273);
    Insert into MYSTORES values(274);
    Insert into MYSTORES values(276);
    Insert into MYSTORES values(277);
    Insert into MYSTORES values(278);
    Insert into MYSTORES values(279);
    Insert into MYSTORES values(28);
    Insert into MYSTORES values(280);
    Insert into MYSTORES values(281);
    Insert into MYSTORES values(282);
    Insert into MYSTORES values(285);
    Insert into MYSTORES values(286);
    Insert into MYSTORES values(287);
    Insert into MYSTORES values(289);
    Insert into MYSTORES values(29);
    Insert into MYSTORES values(290);
    Insert into MYSTORES values(291);
    Insert into MYSTORES values(292);
    Insert into MYSTORES values(293);
    Insert into MYSTORES values(294);
    Insert into MYSTORES values(295);
    Insert into MYSTORES values(296);
    Insert into MYSTORES values(298);
    Insert into MYSTORES values(30);
    Insert into MYSTORES values(31);
    Insert into MYSTORES values(32);
    Insert into MYSTORES values(321);
    Insert into MYSTORES values(33);
    Insert into MYSTORES values(34);
    Insert into MYSTORES values(35);
    Insert into MYSTORES values(36);
    Insert into MYSTORES values(362);
    Insert into MYSTORES values(363);
    Insert into MYSTORES values(364);
    Insert into MYSTORES values(37);
    Insert into MYSTORES values(38);
    Insert into MYSTORES values(381);
    Insert into MYSTORES values(39);
    Insert into MYSTORES values(41);
    Insert into MYSTORES values(42);
    Insert into MYSTORES values(43);
    Insert into MYSTORES values(461);
    Insert into MYSTORES values(462);
    Insert into MYSTORES values(501);
    Insert into MYSTORES values(521);
    Insert into MYSTORES values(522);
    Insert into MYSTORES values(523);
    Insert into MYSTORES values(524);
    Insert into MYSTORES values(564);
    Insert into MYSTORES values(61);
    Insert into MYSTORES values(62);
    Insert into MYSTORES values(624);
    Insert into MYSTORES values(64);
    Insert into MYSTORES values(65);
    Insert into MYSTORES values(66);
    Insert into MYSTORES values(67);
    Insert into MYSTORES values(68);
    Insert into MYSTORES values(69);
    Insert into MYSTORES values(70);
    Insert into MYSTORES values(71);
    Insert into MYSTORES values(72);
    Insert into MYSTORES values(73);
    Insert into MYSTORES values(75);
    Insert into MYSTORES values(76);
    Insert into MYSTORES values(78);
    Insert into MYSTORES values(81);
    Insert into MYSTORES values(82);
    Insert into MYSTORES values(83);
    Insert into MYSTORES values(2);
    Insert into MYSTORES values(3);
    /

    DB:4.21:How Do I Set 1 Field Based On Another Field's Value za

    Hi Etbin

    Just curious: even when FULL OUTER JOIN has to be used ?I know about the FULL OUTER JOIN and played a bit with it, but I never had to use it in 'real life', never got such a requirement...

  • RELEVANCY SCORE 4.21

    DB:4.21:No Puedo Consultar Datos 3p


    no puedo hacer las siguientes consultas al parecer cree mal las restricciones? o algo tengo mal hecho ya que no puedo ingresar datos en la tabla asignado_a.

    // CREACION DE TABLAS
    create table PROGRAMADOR (DNI varchar2(10),NOMBRE varchar2(200));
    create table ASIGNADO_A (PROGRAMADOR varchar2(10), PROYECTO number(5));
    create table PROYECTO (ID number(5), NOMBRE_EMPRESA varchar2(100),HORAS number(10));
    // RESTRICCIONES
    alter table PROGRAMADOR add primary key(DNI);
    alter table asignado_a add primary key(programador,proyecto);
    alter table PROYECTO add primary key(ID);
    alter table asignado_a add foreign key(programador) references programador(dni);
    alter table asignado_a add foreign key(proyecto) references proyecto(id);

    INSERT INTO PROGRAMADOR VALUES('101','Pedro');
    INSERT INTO PROGRAMADOR VALUES('102','Andres');
    INSERT INTO PROGRAMADOR VALUES('103','Patricio');
    INSERT INTO PROGRAMADOR VALUES('104','Claudio');
    INSERT INTO PROGRAMADOR VALUES('105','Ana');
    INSERT INTO PROGRAMADOR VALUES('106','Maria');
    INSERT INTO PROGRAMADOR VALUES('107','Juana');
    INSERT INTO PROGRAMADOR VALUES('108','Julia');
    INSERT INTO PROGRAMADOR VALUES('109','Carlos');
    INSERT INTO PROGRAMADOR VALUES('110','Andrea');

    INSERT INTO asignado_a VALUES('101','1');
    INSERT INTO asignado_a VALUES('101',2);
    INSERT INTO asignado_a VALUES('102',3);
    INSERT INTO asignado_a VALUES('103',4);
    INSERT INTO asignado_a VALUES('103',5);
    INSERT INTO asignado_a VALUES('105',6);
    INSERT INTO asignado_a VALUES('106',7);
    INSERT INTO asignado_a VALUES('107',8);
    INSERT INTO asignado_a VALUES('107',9);
    INSERT INTO asignado_a VALUES('109',10);
    INSERT INTO asignado_a VALUES('110',11);
    INSERT INTO asignado_a VALUES('110',12);
    INSERT INTO asignado_a VALUES('103',13);
    INSERT INTO asignado_a VALUES('103',14);
    INSERT INTO asignado_a VALUES('103',15);
    INSERT INTO asignado_a VALUES('102',4);
    INSERT INTO asignado_a VALUES('108',5);
    INSERT INTO asignado_a VALUES('102',6);
    INSERT INTO asignado_a VALUES('108',7);
    INSERT INTO asignado_a VALUES('108',8);

    INSERT INTO PROYECTO VALUES(1,'Coca-Cola',100);
    INSERT INTO PROYECTO VALUES(2,'DAEM',15);
    INSERT INTO PROYECTO VALUES(3,'Microsoft',500);
    INSERT INTO PROYECTO VALUES(4,'HP',100);
    INSERT INTO PROYECTO VALUES(5,'Oracle',320);
    INSERT INTO PROYECTO VALUES(6,'AIEP',410);
    INSERT INTO PROYECTO VALUES(7,'Lenovo',100);
    INSERT INTO PROYECTO VALUES(8,'Google',100);
    INSERT INTO PROYECTO VALUES(9,'Yahoo',680);
    INSERT INTO PROYECTO VALUES(10,'IBM',770);
    INSERT INTO PROYECTO VALUES(11,'Banco Santander',860);
    INSERT INTO PROYECTO VALUES(12,'Municipalidad de Talca',605);
    INSERT INTO PROYECTO VALUES(13,'Corfo',110);
    INSERT INTO PROYECTO VALUES(14,'Falabella',80);
    INSERT INTO PROYECTO VALUES(15,'La Polar',10);

    1-SELECT DISTINCT p.dni, p.nombre pr.id, pr.nombre
    FROM programador p, proyecto pr asignado_a asi
    WHERE p.dni = asi.programador AND asi.proyecto = p.id AND asi.proyecto=p.id
    ORDER BY p.dni, p.nombre, pr.id,pr.nombre;

    DB:4.21:No Puedo Consultar Datos 3p

    Varios errores en tu consulta SELECT:
    - Falta una coma en la lista de columnas
    - Falta una coma en la lista de tablas
    - p.id no existe (p es la tabla programador, que no tiene un campo id)
    - pr.nombre no existe (pr s la tabla proyecto, que no tiene un campo nombre)
    Funcionará si la escribes así:
    SELECT DISTINCT p.dni, p.nombre, pr.id, p.nombre
    FROM programador p, proyecto pr, asignado_a asi
    WHERE p.dni = asi.programador AND asi.proyecto = pr.id AND asi.proyecto=pr.id
    ORDER BY p.dni, p.nombre, pr.id;

    Pero lógicamente si los campos que querías presentar eran otros, tendrás que corregirla según proceda.

  • RELEVANCY SCORE 4.21

    DB:4.21:Joining 4 Or More Tables 8s


    Hello,
    I want to join these5 tables: AllMonths, E, L, I, M to get theRESULT. They all have unitid, yyyy, mm as the key. However, they date ranges in the tables are not continuous and there are dates after and before the desired result.
    I have tried todo this using many variations of INNER, FULL, LEFT, RIGHT JOINS e.g.
    SELECT
    A.UNITID
    ,A.yyyy
    ,A.mm
    ,E.E
    ,L.L
    ,M.M
    ,I.I
    FROM AllMonths A left join E
    on A.UNITID = E.unitid and A.yyyy =E.yyyy and A.mm = E.mm
    left join L on L.UNITID = E.unitid and L.yyyy
    =E.yyyy and L.mm = E.mm
    left join M on L.UNITID = M.unitid and L.yyyy
    =M.yyyy and L.mm = M.mm
    left join I on M.UNITID = I.unitid and I.yyyy =M.yyyy
    and I.mm = M.mm

    order by unitid, yyyy, mm
    Or, do I have to join each of E,L,I and M to allmonths first and then join the 4 results together?
    Or, could I nest the Joins, adding one table each time?
    Thanks in advance,
    John
    CREATE TABLE AllMonths (UnitID int,yyyy int,mmint);
    INSERT INTO AllMonths VALUES (1,2011,11)
    INSERT INTO [AllMonths] VALUES (1,2011,12)
    INSERT INTO [AllMonths] VALUES (1,2012,1)
    INSERT INTO [AllMonths] VALUES (1,2012,2)
    INSERT INTO [AllMonths] VALUES (1,2012,3)
    INSERT INTO [AllMonths] VALUES (2,2011,11)
    INSERT INTO [AllMonths] VALUES (2,2011,12)
    INSERT INTO [AllMonths] VALUES (2,2012,1)
    INSERT INTO [AllMonths] VALUES (2,2012,2)
    INSERT INTO [AllMonths] VALUES (2,2012,3)
    INSERT INTO [AllMonths] VALUES (3,2011,11)
    INSERT INTO [AllMonths] VALUES (3,2011,12)
    INSERT INTO [AllMonths] VALUES (3,2012,1)
    INSERT INTO [AllMonths] VALUES (3,2012,2)
    INSERT INTO [AllMonths] VALUES (2,2012,3)
    ;
    CREATE TABLE E (UnitIDint, yyyyint, mmint, Eint);
    INSERT INTO E VALUES (1,2011,10,NULL)
    INSERT INTO E VALUES (1,2011,12,5)
    INSERT INTO E VALUES (1,2012,2,NULL)
    INSERT INTO E VALUES (1,2012,3,3)
    INSERT INTO E VALUES (1,2012,4,2)
    INSERT INTO E VALUES (2,2011,11,1)
    INSERT INTO E VALUES (2,2011,12,1)
    INSERT INTO E VALUES (2,2012,1,1)
    INSERT INTO E VALUES (2,2012,2,1)
    INSERT INTO E VALUES (2,2012,3,1)
    INSERT INTO E VALUES (3,2011,12,NULL)
    INSERT INTO E VALUES (3,2012,1,2)
    INSERT INTO E VALUES (3,2012,2,1)
    INSERT INTO E VALUES (3,2012,3,4)
    INSERT INTO E VALUES (3,2012,4,3)
    ;
    CREATE TABLE L (UnitIDint, yyyyint, mmint, Lint);
    INSERT INTO L VALUES (1,2011,11,200)
    INSERT INTO L VALUES (1,2011,12,205)
    INSERT INTO L VALUES (1,2012,1,180)
    INSERT INTO L VALUES (1,2012,2,NULL)
    INSERT INTO L VALUES (1,2012,3,210)
    INSERT INTO L VALUES (2,2011,10,NULL)
    INSERT INTO L VALUES (2,2011,12,401)
    INSERT INTO L VALUES (2,2012,3,399)
    INSERT INTO L VALUES (2,2012,4,405)
    INSERT INTO L VALUES (2,2012,5,400)
    INSERT INTO L VALUES (3,2011,12,600)
    INSERT INTO L VALUES (3,2012,1,560)
    INSERT INTO L VALUES (3,2012,2,580)
    INSERT INTO L VALUES (3,2012,3,NULL)
    INSERT INTO L VALUES (3,2012,4,555)
    ;
    CREATE TABLE M (UnitIDint, yyyyint, mmint, Mint);
    INSERT INTO M VALUES (1,2011,10,75)
    INSERT INTO M VALUES (1,2011,12,70)
    INSERT INTO M VALUES (1,2012,2,50)
    INSERT INTO M VALUES (1,2012,3,3)
    INSERT INTO M VALUES (1,2012,4,NULL)
    INSERT INTO M VALUES (2,2011,11,20)
    INSERT INTO M VALUES (2,2011,12,19)
    INSERT INTO M VALUES (2,2012,1,21)
    INSERT INTO M VALUES (2,2012,2,22)
    INSERT INTO M VALUES (2,2012,3,24)
    INSERT INTO M VALUES (3,2011,12,10)
    INSERT INTO M VALUES (3,2012,1,11)
    INSERT INTO M VALUES (3,2012,2,12)
    INSERT INTO M VALUES (3,2012,3,NULL)
    INSERT INTO M VALUES (3,2012,4,18)
    ;
    CREATE TABLE I (UnitIDint, yyyyint, mmint,Iint);
    INSERT INTO I VALUES (1,2011,10,NULL)
    INSERT INTO I VALUES (1,2011,12,2)
    INSERT INTO I VALUES (1,2012,2,NULL)
    INSERT INTO I VALUES (1,2012,3,3)
    INSERT INTO I VALUES (1,2012,4,2)
    INSERT INTO I VALUES (2,2011,11,1)
    INSERT INTO I VALUES (2,2011,12,3)
    INSERT INTO I VALUES (2,2012,1,NULL)
    INSERT INTO I VALUES (2,2012,2,1)
    INSERT INTO I VALUES (2,2012,3,1)
    INSERT INTO I VALUES (3,2011,12,Null)
    INSERT INTO I VALUES (3,2012,1,2)
    INSERT INTO I VALUES (3,2012,2,1)
    INSERT INTO I VALUES (3,2012,3,4)
    INSERT INTO I VALUES (3,2012,4,3)
    ;
    CREATE TABLE [RESULT] (UnitIDint, yyyyint, mmint, Eint, Lint, Mint, Iint);
    INSERT INTO [RESULT] VALUES (1,2011,11,NULL,200,NULL,NULL)
    INSERT INTO [RESULT] VALUES (1,2011,12,5,205,70,2)
    INSERT INTO [RESULT] VALUES (1,2012,1,NULL,180,NULL,NULL)
    INSERT INTO [RESULT] VALUES (1,2012,2,NULL,NULL,50,NULL)
    INSERT INTO [RESULT] VALUES (1,2012,3,3,210,3,3)
    INSERT INTO [RESULT] VALUES (2,2011,11,1,NULL,20,1)
    INSERT INTO [RESULT] VALUES (2,2011,12,1,401,19,3)
    INSERT INTO [RESULT] VALUES (2,2012,1,1,399,21,NULL)
    INSERT INTO [RESULT] VALUES (2,2012,2,1,NULL,22,1)
    INSERT INTO [RESULT] VALUES (2,2012,3,1,NULL,24,1)
    INSERT INTO [RESULT] VALUES (3,2011,11,NULL,NULL,NULL,NULL)
    INSERT INTO [RESULT] VALUES (3,2011,12,NULL,600,10,NULL)
    INSERT INTO [RESULT] VALUES (3,2012,1,2,560,11,2)
    INSERT INTO [RESULT] VALUES (3,2012,2,1,580,12,1)
    INSERT INTO [RESULT] VALUES (2,2012,3,4,NULL,NULL,4);

    DB:4.21:Joining 4 Or More Tables 8s

    Thanks Erland and Chintar.
    I have a Monday deadline.
    Despite Mr. Celko's disdain for the amateurs and mere analysts like myself, your contribution to the forum allows me to get it done and then go back and take the time to understand it.

    I greatly appreciate it,
    John

  • RELEVANCY SCORE 4.20

    DB:4.20:Rewrite Query From Sql Server To Oracle cs


    Recently I stared to learn MS SQL Server's T-SQL...

    Can anybody rewrite it from SQL Server to Oracle?

    I have noticed that TOP work in a different way than ROWNUM, it works more like analytic function LAG, am I right?
    CREATE TABLE dbo.Sales
    (
    mnth DATETIME NOT NULL PRIMARY KEY,
    qty INT NOT NULL
    );

    INSERT INTO dbo.Sales(mnth, qty) VALUES('20041201', 100);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20050101', 110);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20050201', 120);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20050301', 130);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20050401', 140);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20050501', 140);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20050601', 130);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20050701', 120);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20050801', 110);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20050901', 100);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20051001', 110);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20051101', 100);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20051201', 120);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20060101', 130);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20060201', 140);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20060301', 100);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20060401', 100);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20060501', 100);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20060601', 110);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20060701', 120);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20060801', 110);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20060901', 120);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20061001', 130);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20061101', 140);
    INSERT INTO dbo.Sales(mnth, qty) VALUES('20061201', 100);
    GO

    SELECT mnth, qty,
    SIGN((S1.qty -
    (SELECT TOP 1 qty
    FROM dbo.Sales AS S2
    WHERE S2.mnth S1.mnth
    ORDER BY S2.mnth DESC))) AS sgn
    FROM dbo.Sales AS S1Thx!

  • RELEVANCY SCORE 4.20

    DB:4.20:Cursor For Update pf


    i want to ypdate stock table , column qty.
    can someone help me with that.
    please check it out.
    create table orderlines
    (
    orderno number(4),
    stockno number(3),
    qty number(2),
    price number(2),
    total number(7)
    );
    INSERT INTO ORDERLINES VALUES (1201,852,1,0,0);
    INSERT INTO ORDERLINES VALUES (1201,496,2,0,0);
    INSERT INTO ORDERLINES VALUES (1202,991,1,0,0);
    INSERT INTO ORDERLINES VALUES (1203,991,1,0,0);
    INSERT INTO ORDERLINES VALUES (1203,998,1,0,0);
    INSERT INTO ORDERLINES VALUES (1204,991,1,0,0);
    INSERT INTO ORDERLINES VALUES (1204,992,2,0,0);
    INSERT INTO ORDERLINES VALUES (1204,998,1,0,0);
    INSERT INTO ORDERLINES VALUES (1205,496,2,0,0);
    INSERT INTO ORDERLINES VALUES (1205,852,1,0,0);
    INSERT INTO ORDERLINES VALUES (1206,998,3,0,0);
    INSERT INTO ORDERLINES VALUES (1206,991,2,0,0);
    INSERT INTO ORDERLINES VALUES (1206,992,1,0,0);

    CREATE TABLE stock
    (
    stockno NUMBER(3),
    qty NUMBER(2),
    price NUMBER(2)
    );

    INSERT INTO STOCK VALUES( 852,5,55 );
    INSERT INTO STOCK VALUES( 496,10,60 );
    INSERT INTO STOCK VALUES( 991,20,75 );
    INSERT INTO STOCK VALUES( 992,25,80 );
    INSERT INTO STOCK VALUES( 998,30,95 );

    select * from stock;
    DECLARE cursor CURstock IS
    SELECT * FROM stock
    FOR UPDATE;

    VPRICE STOCK.PRICE%TYPE;
    VPRICES ORDERLINES.PRICE%TYPE;
    VQTY ORDERLINES.QTY%TYPE;
    VTOTAL ORDERLINES.TOTAL%TYPE;
    vqtys stock.qty%type;

    BEGIN

    FOR RECstock IN CURstock LOOP

    SELECT qty
    INTO vqtys
    FROM stock
    WHERE STOCKNO=RECstock.STOCKNO;

    vqtys := vqtys - vqty;

    UPDATE stock
    SET qty=vqtys
    WHERE CURRENT OF CURstock;
    END LOOP;
    END;
    /
    rollback;
    SELECT * FROM ORDERLINES;
    select * from stock;

    DB:4.20:Cursor For Update pf

    You mean this?

    SQL merge into stock s
    2 using ( select stockno
    3 , sum(qty) sumqty
    4 from orderlines
    5 group by stockno
    6 ) o
    7 on ( s.stockno = o.stockno )
    8 when matched then
    9 update set qty = qty - sumqty
    10 /

    5 rijen zijn samengevoegd.

    SQL select * from stock
    2 /

    STOCKNO QTY PRICE
    ---------- ---------- ----------
    852 3 55
    496 6 60
    991 15 75
    992 22 80
    998 25 95

    5 rijen zijn geselecteerd.Regards,
    Rob.

    Message was edited by:
    Rob van Wijk

    Lol, almost exactly the same ... although your line 14 is not necessary

  • RELEVANCY SCORE 4.19

    DB:4.19:Ora-00936 - Exclude Data From Plsql Table ax


    create table gtest(mykey number);

    insert into gtest values(2);

    insert into gtest values(3);

    commit;

    create table source(mykey number,col1 varchar2(30));

    insert into source values (1,'abcd');
    insert into source values (2,'should not');
    insert into source values (3,'should not');
    insert into source values (4,'abcd');
    insert into source values (5,'abcd');
    insert into source values (6,'abcd');
    insert into source values (7,'abcd');
    insert into source values (8,'abcd');
    insert into source values (9,'abcd');
    insert into source values (10,'abcd');
    insert into source values (11,'abcd');
    insert into source values (12,'abcd');
    insert into source values (13,'abcd');

    commit;

    create table target(mykey number,col1 varchar2(30));The mykey values 2 and 3 (values in gtest table and are present in source), should not go to target table

    I wrote a procedure that gets data from gtest and puts it in a pl/sql table, How do I filter out those
    in the insert statement ?

    create or replace procedure ptest is

    cursor c1 is select mykey from gtest;

    TYPE t_key IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;

    W_key t_key;

    begin

    OPEN C1;
    LOOP

    FETCH C1 BULK COLLECT INTO W_key LIMIT 3000;

    EXIT WHEN C1%NOTFOUND;
    END LOOP;

    close c1;

    insert into target(mykey, col1)
    select mykey, col1
    from source where mykey not in table (w_key); -- getting ORA-00936 error

    commit;

    end;

    DB:4.19:Ora-00936 - Exclude Data From Plsql Table ax

    Rahul India wrote:
    select s.* from source s,gtest gt
    where s.mykey != gt.mykeybut why is my query including

    3should notsince 3 is also present in gtest table.Run the query without the where clause, outputting the values that you are wanting to compare in the where clause...

    SQL ed
    Wrote file afiedt.buf

    1 select s.mykey, gt.mykey
    2 ,s.*
    3* from source s,gtest gt
    SQL /

    MYKEY MYKEY MYKEY COL1
    ---------- ---------- ---------- ------------------------------
    1 2 1 abcd
    2 2 2 should not
    3 2 3 should not
    4 2 4 abcd
    5 2 5 abcd
    6 2 6 abcd
    7 2 7 abcd
    8 2 8 abcd
    9 2 9 abcd
    10 2 10 abcd
    11 2 11 abcd
    12 2 12 abcd
    13 2 13 abcd
    1 3 1 abcd
    2 3 2 should not
    3 3 3 should not
    4 3 4 abcd
    5 3 5 abcd
    6 3 6 abcd
    7 3 7 abcd
    8 3 8 abcd
    9 3 9 abcd
    10 3 10 abcd
    11 3 11 abcd
    12 3 12 abcd
    13 3 13 abcd

    26 rows selected.Because you have a cartesian join you have all values of gtest against all values of source, so sure enough your where clause will exclude the row where both values are 3, but will include the row where source has a vlaue of 3 and gtest has a value of 2. This is basic join theory.

  • RELEVANCY SCORE 4.19

    DB:4.19:Cannot Resolve The Collation Conflict Between Sql_Latin1_General_Cp1_Ci_Ai And Latin1_General_Ci_As_Ks_Ws In The Equal To Operation. j8


    Boa tarde. Estou desenvolvendo uma procedure e esta dando esse erro Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AI and Latin1_General_CI_AS_KS_WS in the equal to operation.
    Crio 2 Tabela temporaria, adiciono uns registro na primeira e depois ao tentar adicionar na segunda tabela usando a primeira na clausula WHERE esta dando esse erro.
    DECLARE @Tabela_Objetos TABLE(Registro INT IDENTITY(1, 1), Objeto VARCHAR(100), ID_Tipo VARCHAR(2))
    DECLARE @Tabela_Tipos TABLE(Registro INT IDENTITY(1, 1), Tipo VARCHAR(2), Descricao VARCHAR(100))

    INSERT INTO @Tabela_Tipos VALUES('U', 'TABLE (user-defined)')
    INSERT INTO @Tabela_Tipos VALUES('V', 'VIEW')
    INSERT INTO @Tabela_Tipos VALUES('P', 'SQL STORED PROCEDURE')
    INSERT INTO @Tabela_Tipos VALUES('TR', 'SQL DML TRIGGER')
    --INSERT INTO @Tabela_Tipos VALUES('S', 'System base TABLE')
    --INSERT INTO @Tabela_Tipos VALUES('IT', 'Internal TABLE')
    INSERT INTO @Tabela_Tipos VALUES('FN', 'SQL scalar FUNCTION')
    INSERT INTO @Tabela_Tipos VALUES('IF', 'SQL inline TABLE-valued FUNCTION')
    INSERT INTO @Tabela_Tipos VALUES('TF', 'SQL TABLE-valued-FUNCTION')
    INSERT INTO @Tabela_Tipos VALUES('X', 'Extended STORED PROCEDURE')
    INSERT INTO @Tabela_Tipos VALUES('AF', 'Aggregate FUNCTION (CLR)')
    INSERT INTO @Tabela_Tipos VALUES('PC', 'Assembly (CLR) STORED PROCEDURE')
    INSERT INTO @Tabela_Tipos VALUES('FS', 'Assembly (CLR) scalar FUNCTION')
    INSERT INTO @Tabela_Tipos VALUES('FT', 'Assembly (CLR) TABLE-valued FUNCTION')
    INSERT INTO @Tabela_Tipos VALUES('TA', 'Assembly (CLR) DML TRIGGER')

    INSERT INTO @Tabela_Objetos SELECT Sys.Objects.Name, Sys.Objects.Type FROM Sys.Objects WHERE Sys.Objects.Type IN (SELECT Tipo FROM @Tabela_Tipos) ORDER BY Sys.Objects.Name

    O que pode ser? Obrigado!

    DB:4.19:Cannot Resolve The Collation Conflict Between Sql_Latin1_General_Cp1_Ci_Ai And Latin1_General_Ci_As_Ks_Ws In The Equal To Operation. j8

    Gabriel obrigado! Eu sei o que é Collate.
    Utilizando o seu exemplo que voce sugeriu do site do Gustavo eu fiz assim e funcionou:
    INSERT INTO @Tabela_Objetos SELECT Sys.Objects.Name, Sys.Objects.Type FROM Sys.Objects WHERE Sys.Objects.Type COLLATE Latin1_General_CI_AI IN (SELECT Tipo COLLATE Latin1_General_CI_AI FROM @Tabela_Tipos) ORDER BY Sys.Objects.Name
    Agradeço, valeu!

  • RELEVANCY SCORE 4.19

    DB:4.19:Re: Error: Ora-06533: Subscript Beyond Count 91


    DROP TABLE sp_table;
    DROP TABLE test_table;
    CREATE TABLE test_table(
    col001 NUMBER
    ,col002 NUMBER
    ,col003 NUMBER
    ,col004 NUMBER
    ,col005 NUMBER
    ,col006 NUMBER
    ,col007 NUMBER
    ,col008 NUMBER
    ,col009 NUMBER
    ,col010 NUMBER
    ,col011 NUMBER
    ,col012 NUMBER
    ,col013 NUMBER
    ,col014 NUMBER
    ,col015 NUMBER
    ,col016 NUMBER
    ,col017 NUMBER
    ,col018 NUMBER
    ,col019 NUMBER
    ,col020 NUMBER
    ,col021 NUMBER
    ,col022 NUMBER
    ,col023 NUMBER
    ,col024 NUMBER
    ,col025 NUMBER
    ,col026 NUMBER
    ,col027 NUMBER
    ,col028 NUMBER
    ,col029 NUMBER
    ,col030 NUMBER
    ,col031 NUMBER
    ,col032 NUMBER
    ,col033 NUMBER
    ,col034 NUMBER
    ,col035 NUMBER
    ,col036 NUMBER
    ,col037 NUMBER
    ,col038 NUMBER
    ,col039 NUMBER
    ,col040 NUMBER
    ,col041 NUMBER
    ,col042 NUMBER
    ,col043 NUMBER
    ,col044 NUMBER
    ,col045 NUMBER
    ,col046 NUMBER
    ,col047 NUMBER
    ,col048 NUMBER
    ,col049 NUMBER
    ,col050 NUMBER
    ,col051 NUMBER
    ,col052 NUMBER
    ,col053 NUMBER
    ,col054 NUMBER
    ,col055 NUMBER
    ,col056 NUMBER
    ,col057 NUMBER
    ,col058 NUMBER
    ,col059 NUMBER
    ,col060 NUMBER
    ,col061 NUMBER
    ,col062 NUMBER
    ,col063 NUMBER
    ,col064 NUMBER
    ,col065 NUMBER
    ,col066 NUMBER
    ,col067 NUMBER
    ,col068 NUMBER
    ,col069 NUMBER
    ,col070 NUMBER
    ,col071 NUMBER
    ,col072 NUMBER
    ,col073 NUMBER
    ,col074 NUMBER
    ,col075 NUMBER
    ,col076 NUMBER
    ,col077 NUMBER
    ,col078 NUMBER
    ,col079 NUMBER
    ,col080 NUMBER
    ,col081 NUMBER
    ,col082 NUMBER
    ,col083 NUMBER
    ,col084 NUMBER
    ,col085 NUMBER
    ,col086 NUMBER
    ,col087 NUMBER
    ,col088 NUMBER
    ,col089 NUMBER
    ,col090 NUMBER
    ,col091 NUMBER
    ,col092 NUMBER
    ,col093 NUMBER
    ,col094 NUMBER
    ,col095 NUMBER
    ,col096 NUMBER
    ,col097 NUMBER
    ,col098 NUMBER
    ,col099 NUMBER
    ,col100 NUMBER
    );

    CREATE TABLE sp_table(splitid NUMBER,delimiterlength NUMBER);
    INSERT INTO sp_table values(1 ,1);
    INSERT INTO sp_table values(2 ,1);
    INSERT INTO sp_table values(3 ,1);
    INSERT INTO sp_table values(4 ,1);
    INSERT INTO sp_table values(5 ,1);
    INSERT INTO sp_table values(6 ,1);
    INSERT INTO sp_table values(7 ,1);
    INSERT INTO sp_table values(8 ,1);
    INSERT INTO sp_table values(9 ,1);
    INSERT INTO sp_table values(10,1);
    INSERT INTO sp_table values(11,1);
    INSERT INTO sp_table values(12,1);
    INSERT INTO sp_table values(13,1);
    INSERT INTO sp_table values(14,1);
    INSERT INTO sp_table values(15,1);
    INSERT INTO sp_table values(16,1);
    INSERT INTO sp_table values(17,1);
    INSERT INTO sp_table values(18,1);
    INSERT INTO sp_table values(19,1);
    INSERT INTO sp_table values(20,1);
    INSERT INTO sp_table values(21,1);
    INSERT INTO sp_table values(22,1);
    INSERT INTO sp_table values(23,1);
    INSERT INTO sp_table values(24,1);
    INSERT INTO sp_table values(25,1);
    INSERT INTO sp_table values(26,1);
    INSERT INTO sp_table values(27,1);
    INSERT INTO sp_table values(28,1);
    INSERT INTO sp_table values(29,1);
    INSERT INTO sp_table values(30,1);
    INSERT INTO sp_table values(31,1);
    INSERT INTO sp_table values(32,1);
    INSERT INTO sp_table values(33,1);
    INSERT INTO sp_table values(34,1);
    INSERT INTO sp_table values(35,1);
    INSERT INTO sp_table values(36,1);
    INSERT INTO sp_table values(37,1);
    INSERT INTO sp_table values(38,1);
    INSERT INTO sp_table values(39,1);
    INSERT INTO sp_table values(40,1);
    INSERT INTO sp_table values(41,1);
    INSERT INTO sp_table values(42,1);
    INSERT INTO sp_table values(43,1);
    INSERT INTO sp_table values(44,1);
    INSERT INTO sp_table values(45,1);
    INSERT INTO sp_table values(46,1);
    INSERT INTO sp_table values(47,1);
    INSERT INTO sp_table values(48,1);
    INSERT INTO sp_table values(49,1);
    INSERT INTO sp_table values(50,1);
    INSERT INTO sp_table values(51,1);
    INSERT INTO sp_table values(52,1);
    INSERT INTO sp_table values(53,1);
    INSERT INTO sp_table values(54,1);
    INSERT INTO sp_table values(55,1);
    INSERT INTO sp_table values(56,1);
    INSERT INTO sp_table values(57,1);
    INSERT INTO sp_table values(58,1);
    INSERT INTO sp_table values(59,1);
    INSERT INTO sp_table values(60,1);
    INSERT INTO sp_table values(61,1);
    INSERT INTO sp_table values(62,1);
    INSERT INTO sp_table values(63,1);
    INSERT INTO sp_table values(64,1);
    INSERT INTO sp_table values(65,1);
    INSERT INTO sp_table values(66,1);
    INSERT INTO sp_table values(67,1);
    INSERT INTO sp_table values(68,1);
    INSERT INTO sp_table values(69,1);
    INSERT INTO sp_table values(70,1);
    INSERT INTO sp_table values(71,1);
    INSERT INTO sp_table values(72,1);
    INSERT INTO sp_table values(73,1);
    INSERT INTO sp_table values(74,1);
    INSERT INTO sp_table values(75,1);
    INSERT INTO sp_table values(76,1);
    INSERT INTO sp_table values(77,1);
    INSERT INTO sp_table values(78,1);
    INSERT INTO sp_table values(79,1);
    INSERT INTO sp_table values(80,1);
    INSERT INTO sp_table values(81,1);
    INSERT INTO sp_table values(82,1);
    INSERT INTO sp_table values(83,1);
    INSERT INTO sp_table values(84,1);
    INSERT INTO sp_table values(85,1);
    INSERT INTO sp_table values(86,1);
    INSERT INTO sp_table values(87,1);
    INSERT INTO sp_table values(88,1);
    INSERT INTO sp_table values(89,1);
    INSERT INTO sp_table values(90,1);
    INSERT INTO sp_table values(91,1);
    INSERT INTO sp_table values(92,1);
    INSERT INTO sp_table values(93,1);
    INSERT INTO sp_table values(94,1);
    INSERT INTO sp_table values(95,1);
    INSERT INTO sp_table values(96,1);
    INSERT INTO sp_table values(97,1);
    INSERT INTO sp_table values(98,1);
    INSERT INTO sp_table values(99,1);
    INSERT INTO sp_table values(100,1);
    INSERT INTO sp_table values(101,1);
    INSERT INTO sp_table values(102,1);
    INSERT INTO sp_table values(103,1);
    INSERT INTO sp_table values(104,1);
    INSERT INTO sp_table values(105,1);
    INSERT INTO sp_table values(106,1);
    INSERT INTO sp_table values(107,1);
    INSERT INTO sp_table values(108,1);
    COMMIT;

    DECLARE
    TYPE varchar2_table IS TABLE OF VARCHAR2(120)
    INDEX BY BINARY_INTEGER;
    TYPE number_table IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
    delarray number_table;
    cn number_table;
    cxn varchar2_table;
    xpos NUMBER :=1;
    strx VARCHAR2(4000) := '10101101101101101101101101101101101101101101101101101101101101101101101101101101101101101101101101101101101111';
    execstr VARCHAR2(500);
    cnt Number(5);

    BEGIN
    SELECT Count(delimiterlength) into cnt from sp_table;
    execstr := 'insert into test_table values(';
    FOR i IN 1..100 LOOP
    delarray(i) := i;
    SELECT delimiterlength
    INTO cn(i)
    FROM sp_table
    WHERE splitid = delarray(i);
    cxn(i) := SUBSTR(strx,xpos,cn(i));
    execstr :=execstr || cxn(i) || ',';
    xpos :=cn(i) + xpos;
    END LOOP;

    execstr := RTRIM(execstr,',')|| ')';
    dbms_output.put_line(execstr);
    EXECUTE IMMEDIATE execstr;
    END;
    /

    insert into test_table values(1,0,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0,1,1,0)

    COMMIT;

    SELECT COUNT(*) FROM test_table;

    COUNT(*)
    --------
    1no errors

    Edited by: Marwim on 25.10.2012 16:15

  • RELEVANCY SCORE 4.19

    DB:4.19:Inserting Row In Result Set Using Sql 18


    Hi
    need to insert a row in resultset when ord =1 and typ=c and ord=2 and typ=p then need to insert one row in between typ c and p with typ =s see the #temp_result table for result expected
    present data:-
    create table #temp
    (id int,
    ord int,
    typ varchar(2))

    insert into #temp values (101, 1, 'c')
    insert into #temp values (101, 2, 'p')
    insert into #temp values (102, 1, 'c')
    insert into #temp values (102, 2, 'p')
    insert into #temp values (102, 3, 'p')
    insert into #temp values (103, 1, 'c')
    insert into #temp values (103, 2, 'p')
    select * from #temp
    /*********************************************/
    result expected:-
    create table #temp_result
    (id int,
    ord int,
    typ varchar(2))
    insert into #temp_result values (101, 1, 'c')
    insert into #temp_result values (101, 2, 's')
    insert into #temp_result values (101, 3, 'p')
    insert into #temp_result values (102, 1, 'c')
    insert into #temp_result values (102, 2, 's')
    insert into #temp_result values (102, 3, 'p')
    insert into #temp_result values (102, 4, 'p')
    insert into #temp_result values (103, 1, 'c')
    insert into #temp_result values (103, 2, 's')
    insert into #temp_result values (103, 3, 'p')
    select * from #temp_result
    let me know if you need more info...
    thanks regards

    DB:4.19:Inserting Row In Result Set Using Sql 18

    Did you tried the latest code posted ??Thanks and regards, Rishabh , Microsoft Community Contributor

  • RELEVANCY SCORE 4.18

    DB:4.18:Query-Select zf


    I had a Table: Test
    Test:

    CREATE TABLE Test(Eid int)

    GO

    INSERT INTO Test VALUES(1)

    INSERT INTO Test VALUES(2)

    INSERT INTO Test VALUES(3)

    INSERT INTO Test VALUES(4)

    INSERT INTO Test VALUES(5)

    INSERT INTO Test VALUES(6)

    GO

    SELECT * FROM Test

    GO

    --Update statement

    UPDATE Test

    SET eid=0

    WHERE eid=3

    GO

    SELECT * FROM test

    GO

    DB:4.18:Query-Select zf

    Hi
    Probably this is what you are looking for
    http://msdn.microsoft.com/en-us/library/ms191300.aspx

    If this post answers your question, please click Mark As Answer. If this post is helpful please click
    Mark as Helpful.

  • RELEVANCY SCORE 4.16

    DB:4.16:How To Pick Values From Two Tables Into A Third? c8


    Hello!
    I'm having the following tables.

    create table A (Fname varchar2 (25));
    create table B (Lname varchar2 (25));
    create table AB (Fname varchar2 (25), Lname varchar2 (25));

    insert into A (Fname) VALUES ('Anders');
    insert into A (Fname) VALUES ('Johan');
    insert into A (Fname) VALUES ('Kim');
    insert into A (Fname) VALUES ('Jonas');
    insert into A (Fname) VALUES ('Johanna');
    insert into A (Fname) VALUES ('Vera');
    insert into A (Fname) VALUES ('Siri');

    insert into B (Lname) VALUES ('OLSBACKA');
    insert into B (Lname) VALUES ('ANDERSSON');
    insert into B (Lname) VALUES ('SODERBERG');
    insert into B (Lname) VALUES ('KARLSSON');
    insert into B (Lname) VALUES ('WALLANDER');
    insert into B (Lname) VALUES ('WHALSTROM');
    insert into B (Lname) VALUES ('MARDBERG');

    I would like to combine values from the table A and B using SQL-statement and insert them in table AB with the following result:
    table AB:
    FNAME LNAME
    ----------- ---------------------
    Anders Olsbacka
    Johan Andersson
    Kim Soderberg
    .
    .
    .

    also is it possible to make the query so a combination of FNAME and LNAME only exist one time in table AB.

    DB:4.16:How To Pick Values From Two Tables Into A Third? c8

    Note that ROWNUM is not necessarily the order in which rows were inserted (though it is likely to be the same for a small test like this). It sounds like you don't care which row in A is matched to which row in B, so this probably isn't an issue. Just an FYI.

    Justin

  • RELEVANCY SCORE 4.16

    DB:4.16:Replace Repeating Values In Column j9


    Hi!
    I have a table where i need to update integer values in one column ifthevalueinthe field is the same as in theprevious field.The value should be updated as long as the value is the same in each row.I attached
    two tables as example where the table Solutionshows how it should look likeand the table RepeatingValues is an example of the structure i would like to Update. Anybody that has any solution?
    USE tempdb
    CREATE TABLE RepeatingValues
    (Row int PRIMARY KEY,
    Level Varchar(50) NULL)

    INSERT INTO RepeatingValues VALUES (1, '10')
    INSERT INTO RepeatingValues VALUES (2, '20')
    INSERT INTO RepeatingValues VALUES (3, '30')
    INSERT INTO RepeatingValues VALUES (4, '40')
    INSERT INTO RepeatingValues VALUES (5, '40')
    INSERT INTO RepeatingValues VALUES (6, '10')
    INSERT INTO RepeatingValues VALUES (7, '20')
    INSERT INTO RepeatingValues VALUES (8, '20')
    INSERT INTO RepeatingValues VALUES (9, '10')
    INSERT INTO RepeatingValues VALUES (10, '20')
    INSERT INTO RepeatingValues VALUES (11, '30')
    INSERT INTO RepeatingValues VALUES (12, '30')
    INSERT INTO RepeatingValues VALUES (13, '30')
    INSERT INTO RepeatingValues VALUES (14, '30')
    INSERT INTO RepeatingValues VALUES (15, '30')
    INSERT INTO RepeatingValues VALUES (16, '10')

    SELECT *
    FROM RepeatingValues

    USE tempdb
    CREATE TABLE Solution
    (Row int PRIMARY KEY,
    Level Varchar(50) NULL)

    INSERT INTO Solution VALUES (1, '10')
    INSERT INTO Solution VALUES (2, '20')
    INSERT INTO Solution VALUES (3, '30')
    INSERT INTO Solution VALUES (4, '40')
    INSERT INTO Solution VALUES (5, '41')
    INSERT INTO Solution VALUES (6, '10')
    INSERT INTO Solution VALUES (7, '20')
    INSERT INTO Solution VALUES (8, '21')
    INSERT INTO Solution VALUES (9, '10')
    INSERT INTO Solution VALUES (10, '20')
    INSERT INTO Solution VALUES (11, '30')
    INSERT INTO Solution VALUES (12, '31')
    INSERT INTO Solution VALUES (13, '32')
    INSERT INTO Solution VALUES (14, '33')
    INSERT INTO Solution VALUES (15, '34')
    INSERT INTO Solution VALUES (16, '10')

    SELECT *
    FROM Solution
    Arne Olsson

    DB:4.16:Replace Repeating Values In Column j9

    Hi!
    Thanks for all responses. I ve tried them and their working excellent.
    Best regards
    ArneArne Olsson

  • RELEVANCY SCORE 4.16

    DB:4.16:Sub Query 3s


    My Tables:
    --------------------------------------Groups table-----------------------
    CREATE TABLE G
    (
    ID INTEGER,
    NAME VARCHAR2(30 BYTE)
    );

    --------------------------------------Users table-----------------------
    CREATE TABLE U
    (
    ID INTEGER,
    NAME VARCHAR2(20 BYTE)
    );

    --------------------------------------Group User maping-----------------------
    CREATE TABLE GU
    (
    GI INTEGER,
    UI INTEGER
    );

    GroupTables Values

    insert into g values (11,'g1');
    insert into g values (22,'g2');
    insert into g values (33,'g3');
    insert into g values (44,'g4');
    insert into g values (55,'g5');

    User Table Values

    insert into u values (1,'u1');
    insert into u values (2,'u2');
    insert into u values (3,'u3');
    insert into u values (4,'u4');
    insert into u values (5,'u5');
    insert into u values (6,'u6');
    insert into u values (7,'u7');
    insert into u values (8,'u8');
    insert into u values (9,'u9');
    insert into u values (10,'u10');

    Users to Groups maping

    insert into gu values (11,2);
    insert into gu values (11,3);
    insert into gu values (11,4);
    insert into gu values (11,8);

    insert into gu values (22,2);
    insert into gu values (22,5);
    insert into gu values (22,7);

    insert into gu values (44,5);
    insert into gu values (44,6);
    insert into gu values (44,8);
    insert into gu values (44,10);

    insert into gu values (33,1);
    insert into gu values (33,3);
    insert into gu values (33,5);
    insert into gu values (33,7);

    insert into gu values (22,2);
    insert into gu values (22,4);
    insert into gu values (22,7);

    insert into gu values (55,1);
    insert into gu values (55,10);

    I would like to list group to users maping with the names.

    Please help me on this.

    DB:4.16:Sub Query 3s

    Thank you very much. It works.

  • RELEVANCY SCORE 4.15

    DB:4.15:Regex Question 8z


    Hi All;

    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
    Connected as SYS

    SQL
    SQL drop table regex_test;

    Table dropped

    SQL create table regex_test( x varchar(32));

    Table created

    SQL BEGIN
    2 INSERT INTO regex_test VALUES (' insert into regetest ');
    3 INSERT INTO regex_test VALUES (' INSERT into regetest ');
    4 INSERT INTO regex_test VALUES ('INSERT into regetest ');
    5 INSERT INTO regex_test VALUES (' insert into regetest');
    6 COMMIT;
    7 END;
    8 /

    PL/SQL procedure successfully completed

    SQL SELECT *
    2 FROM regex_test
    3 WHERE regexp_like(upper(x), '[[:space:]*](INSERT[[:space:]*]INTO|UPDATE)[[:space:]*]REGETEST[[:space:]*]');

    X
    --------------------------------
    insert into regetest
    INSERT into regetest

    SQL SELECT * FROM REGEX_TEST;

    X
    --------------------------------
    insert into regetest
    INSERT into regetest
    INSERT into regetest
    insert into regetest

    SQLI want all records. How can it be?

    Regards...

  • RELEVANCY SCORE 4.15

    DB:4.15:Like + In Operator Together 3d


    Experts,
    I have below query and it works fine(SINGLE VALUE):-

    CREATE TABLE #TABLE1
    (
    DESCRIPTION VARCHAR(30)
    )

    INSERT INTO #TABLE1 VALUES ( LTD1006031000 )
    INSERT INTO #TABLE1 VALUES ( LTD1006031003 )
    INSERT INTO #TABLE1 VALUES ( LTD1006031002 )
    INSERT INTO #TABLE1 VALUES ( 1006031004LTD )

    CREATE TABLE #TABLE2
    (
    ORGID VARCHAR(10)
    )

    INSERT INTO #TABLE2 VALUES ( 1006031004 )
    INSERT INTO #TABLE2 VALUES ( 1006031002 )
    INSERT INTO #TABLE2 VALUES ( 1045031002 )

    SELECT *
    FROM
    #TABLE1
    WHERE
    DESCRIPTION LIKE % + 1006031004 + %

    DROP TABLE #TABLE1

    DROP TABLE #TABLE2

    DB:4.15:Like + In Operator Together 3d


    SELECT *
    FROM
    #TABLE1
    WHERE
    Exists
    (Select 1 From #TABLE2 Where #TABLE1.DESCRIPTION Like '%' #TABLE2.ORGID '%')

  • RELEVANCY SCORE 4.14

    DB:4.14:Filter Numeric Data In A Character Column jx


    Hello Al,

    I have a requirement; need to filter Numeric data in a Character Column in a Table.

    Ex :

    create table ksk1 (N varchar2(500) );

    Table Contains following test data :

    begin
    insert into ksk1 values ('satish') ;
    insert into ksk1 values ('kapaka');
    insert into ksk1 values ('sita123');
    insert into ksk1 values ('123sata');
    insert into ksk1 values ('pavan334sdfsd');
    insert into ksk1 values ('123');
    insert into ksk1 values ('32');
    insert into ksk1 values ('4576');
    insert into ksk1 values ('qwerw23452346wefyg56345');
    insert into ksk1 values ('2323');
    insert into ksk1 values ('878');
    insert into ksk1 values ('444');
    insert into ksk1 values ('sadf452345sdg');
    insert into ksk1 values ('sdghd456534');
    end;

    select n from ksk1;

    I need to get the data like:

    123
    32
    4576
    2323
    878
    444

    Only ..

    Can we write the query for the same with using declaration/ Procedural blocks ?

    Thanks,
    Kumar

    DB:4.14:Filter Numeric Data In A Character Column jx

    Best way is to do like this.

    Create a function to check if the value is number like this.

    SQL create or replace function is_number(pVal in varchar2) return number
    2 as
    3 lNum number;
    4 begin
    5 lNum := to_number(pVal);
    6 return 1;
    7 exception
    8 when value_error then
    9 return 0;
    10 end;
    11 /

    Function created.Now use this to filter from the table. Here is a sample.

    SQL create table t (no varchar2(10))
    2 /

    Table created.

    SQL insert into t values(1)
    2 /

    1 row created.

    SQL insert into t values(1.1)
    2 /

    1 row created.

    SQL insert into t values('karthick')
    2 /

    1 row created.

    SQL insert into t values(1000)
    2 /

    1 row created.

    SQL insert into t values('kar123')
    2 /

    1 row created.

    SQL select * from t where is_number(no) = 1
    2 /

    NO
    ----------
    1
    1.1
    1000

    SQLThanks,
    Karthick.

  • RELEVANCY SCORE 4.14

    DB:4.14:Cronological Order Of Inserted Records 9a


    i have inserted 10 rows in a table.

    insert into a values(10);
    insert into a values(5);
    insert into a values(11);
    insert into a values(15);
    insert into a values(7);
    insert into a values(3);
    insert into a values(8);
    insert into a values(4);
    insert into a values(33);
    insert into a values(1);

    how will i identify cronological order of inserted records

    DB:4.14:Cronological Order Of Inserted Records 9a

    user571324 wrote:
    i think u can use order by ROWIDNo you can't because you can't guarantee where in the datafiles the data has been inserted. If it was inserted with the APPEND hint then this would work initially, but then would not be reliable if the table was reorganised at any time.

    Lets be clear. the ONLY way to determine the order of data in the table is to attach a sequence or a timestamp as additional data in the table.

  • RELEVANCY SCORE 4.14

    DB:4.14:Need Help With Query fk


    Is there a way to identify the names in table_a that's only linked to "one" value in the item_id column.
    the result from the query on table_a should be:
    NAME
    - - - - - -
    bart
    julia
    kenny

    create table TABLE_A(item_id number(4), name varchar2(10));

    insert into table_a (item_id, name) Values (1,'scott');
    insert into table_a (item_id, name) Values (2,'scott');
    insert into table_a (item_id, name) Values (3,'scott');
    insert into table_a (item_id, name) Values (1,'lisa');
    insert into table_a (item_id, name) Values (2,'lisa');
    insert into table_a (item_id, name) Values (1,'bart');
    insert into table_a (item_id, name) Values (1,'ken');
    insert into table_a (item_id, name) Values (1,'ken');
    insert into table_a (item_id, name) Values (4,'ken');
    insert into table_a (item_id, name) Values (1,'julia');
    insert into table_a (item_id, name) Values (1,'julia');
    insert into table_a (item_id, name) Values (5,'Kenny');

    DB:4.14:Need Help With Query fk

    Hallo,

    select name from
    ( select distinct item_id, name, count(distinct item_id) over (partition by name) count_val
    from scott.table_a
    order by name, item_id)
    where count_val = 1Regards
    Dmytro Dekhtyaryuk

  • RELEVANCY SCORE 4.13

    DB:4.13:Re: Union All 7c


    I don't think we need to wrap the query. OP needs to write order by at the end of the query. thats it.

    DROP TABLE TEST1; DROP TABLE TEST2;
    CREATE TABLE TEST1 (COL VARCHAR2(1));
    INSERT INTO TEST1 VALUES ('D');
    INSERT INTO TEST1 VALUES ('X');
    INSERT INTO TEST1 VALUES ('F');
    INSERT INTO TEST1 VALUES ('G');

    CREATE TABLE TEST2 (COL VARCHAR2(1));
    INSERT INTO TEST2 VALUES ('J');
    INSERT INTO TEST2 VALUES ('T');
    INSERT INTO TEST2 VALUES ('E');
    INSERT INTO TEST2 VALUES ('W');

    SELECT * FROM TEST1
    UNION ALL
    SELECT * FROM TEST2
    ORDER BY 1;Edited by: SaadL on Nov 22, 2012 4:39 PM

    DB:4.13:Re: Union All 7c

    I am sorry...I will go back and mark the posts...

  • RELEVANCY SCORE 4.12

    DB:4.12:By Using Scalar Sub Query : kc


    Hi Experts Can you please help me on this:

    Can you please haelp me please
    Here Hdd_ttk is the master table
    1)Each and every table here should be compared with hdd_ttk and corresponding dessc
    should be obtained from evey table through scalar sub queries in the same query

    create table hdd_ttk
    (
    rule_id varchar2(10)
    );

    create table pdd_ttk
    (
    rule_id varchar2(10),
    pdd_dessc varchar2(10)
    );

    create table kdd_ttk
    (
    rule_id varchar2(10),
    kdd_dessc varchar2(10)
    );

    create table idd_ttk
    (
    rule_id varchar2(10),
    idd_dessc varchar2(10)
    );

    create table rdd_ttk
    (
    rule_id varchar2(10),
    rdd_dessc varchar2(10)
    );

    insert into hdd_ttk values(rule_id) values ('1');
    insert into hdd_ttk values(rule_id) values ('2');
    insert into hdd_ttk values(rule_id) values ('3');
    insert into hdd_ttk values(rule_id) values ('4');
    ---------------------------------------------------------
    insert into pdd_ttk values(rule_id,pdd_desc) values ('1','Card type');
    insert into pdd_ttk values(rule_id,pdd_desc) values ('2','Plank type');
    insert into pdd_ttk values(rule_id,pdd_desc) values ('5','Plathora');
    insert into pdd_ttk values(rule_id,pdd_desc) values ('5','Plathora');
    --------------------------------------------------------
    insert into kdd_ttk values(rule_id,kdd_desc) values ('3','PlaCard type');
    insert into kdd_ttk values(rule_id,kdd_desc) values ('32','Plank type');
    insert into kdd_ttk values(rule_id,kdd_desc) values ('35','Plathora');
    insert into kdd_ttk values(rule_id,kdd_desc) values ('53','Plathora');

    ---------------------------------------------------------------

    insert into idd_ttk values(rule_id,idd_desc) values ('4','Tran type');
    insert into idd_ttk values(rule_id,idd_desc) values ('32','Plank type');
    insert into idd_ttk values(rule_id,idd_desc) values ('35','Plathora');
    insert into idd_ttk values(rule_id,idd_desc) values ('53','Plathora');
    -----------------------------------------------------------------

    Required Output :
    ---------------

    pdd_desc kdd_desc idd_dessc rdd_dessc
    ------------------------------------------------------------------------------
    Card type PlaCard type Tran type
    Plank type

    DB:4.12:By Using Scalar Sub Query : kc

    Please make sure your script is workbale and the struture of the table adhere to the length of the column.

    one possible way...

    select
    min(case when rnk=3 then desc END) as pdd_desc,
    min(case when rnk=2 then desc END) as kdd_desc,
    min(case when rnk=1 then desc END) as idd_desc,
    min(case when rnk=4 then desc END) as rdd_desc
    from
    (
    select x.*,y.*, row_number()over(partition by tb_name order by desc) as rno, dense_rank()over(order by tb_name) as rnk

    from hdd_ttk x
    Left JOin
    (
    select 'pdd_ttk' as tb_name, RULE_ID,PDD_DESSC as desc from pdd_ttk
    UNION ALL
    select 'kdd_ttk' as tb_name, RULE_ID,KDD_DESSC as desc from kdd_ttk
    UNION ALL
    select 'idd_ttk' as tb_name, RULE_ID,IDD_DESSC as desc from idd_ttk
    )Y
    on x.RULE_ID=y.RULE_ID

    )X group by rno

  • RELEVANCY SCORE 4.12

    DB:4.12:Pivot Table - Dimension Repeat kx



    below is the create table script

    create table region (area varchar2(30), district varchar2(30), city varchar2(30));

    insert into region values ('chandigarh', 'chandigarh', 'chandigarh');

    insert into region values ('punjab', 'patiala', 'patiala');

    insert into region values ('punjab', 'patiala', 'nabha');

    insert into region values ('punjab', 'patiala', 'SAN');

    insert into region values ('punjab', 'SAS', 'SAS');

    insert into region values ('punjab', 'SAS', 'ZRK');

    i added all column area , district, and city in dimension of PIVOT table

    in city i have repeated values same reside in DISTRICT

    i dont want to see highlighted values in red box in CITY column

    how can i limit this from -CHART - DIMENSION - PROPERTIES

    DB:4.12:Pivot Table - Dimension Repeat kx


    thanks i have checked ur answer ..

    actullay i am writing the if statement in "Enable conditional" box,, i.e. where i performing wrong.

  • RELEVANCY SCORE 4.12

    DB:4.12:Sorting Problem 7x


    hi,
    I am havin trouble in sorting the values in this table,,
    it has the following content

    create table day_week
    (day varchar2(20));

    insert into day_week values('Monday');
    insert into day_week values('Wednesday');
    insert into day_week values('Saturday');
    insert into day_week values('Friday);
    insert into day_week values('Tuesday');
    insert into day_week values('Thursday');
    insert into day_week values('Sunday');

    I have to get the result after sorting in calendar order from monday to sunday
    that is like this

    day
    ------
    Monday
    Tuesday
    Wednesday
    Thursday
    Friday
    Saturday
    Sunday

    DB:4.12:Sorting Problem 7x

    Also possible:

    Drop table day_week;

    select to_char(trunc(sysdate,'w')+level+1,'Day','nls_date_language=American') from dual
    connect by level = 7;

  • RELEVANCY SCORE 4.11

    DB:4.11:Inserting Decimal Values m7


    Hello,

    I need some clarification about the way Oracle stores decimal values with trailing zeros. Say, I have a table

    Create Table TestDecimal( DecimalValue Number(10,5) );

    and I insert values

    Insert into TestDecimal Values (10.10);
    Insert into TestDecimal Values (20.0);
    Insert into TestDecimal Values (1000.444);
    Insert into TestDecimal Values (33.0);
    Insert into TestDecimal Values (99);

    The values inserted are

    Select * from TestDecimal

    TESTDECIMAL
    ------------------
    10.10
    20
    1000.444
    33
    99

    In case of 20.0 and 33.0, the values are stored as 20 and 33. Can I get an clarification on this behavior?

    Thank you in advance

    Regards,
    Vamsi Mohan Harish

    DB:4.11:Inserting Decimal Values m7

    Hi Laurent,

    We do agree to the fact that value with and without trailing zeros do not make any difference, but was just curious to know if it is possible or not, as I have seen Informix database storing the values this way.

    Anyway, Thank you for the help and information provided.

    Regards,
    Vamsi Mohan Harish

  • RELEVANCY SCORE 4.11

    DB:4.11:Hierchiacal Queries Help x7


    HI ALL,

    Can anyone please help me in getting parent child realtion for below scenario using start with connect by.

    create table test1 (dest1 number,source1 number);

    INSERT INTO TEST1 VALUES (22,15740);
    INSERT INTO TEST1 VALUES (22,15740);
    INSERT INTO TEST1 VALUES (22,15741);
    INSERT INTO TEST1 VALUES (22,15741);
    INSERT INTO TEST1 VALUES (22,15742);
    INSERT INTO TEST1 VALUES (22,15742);
    INSERT INTO TEST1 VALUES (22,15743);
    INSERT INTO TEST1 VALUES (22,15743);
    INSERT INTO TEST1 VALUES (22,15744);
    INSERT INTO TEST1 VALUES (22,15744);
    INSERT INTO TEST1 VALUES (22,15745);
    INSERT INTO TEST1 VALUES (22,15745);
    INSERT INTO TEST1 VALUES (22,15746);
    INSERT INTO TEST1 VALUES (22,15747);
    INSERT INTO TEST1 VALUES (22,15748);
    INSERT INTO TEST1 VALUES (22,15749);
    INSERT INTO TEST1 VALUES (22,15750);

    create table test2 (dest2 number,source2 number);

    INSERT INTO TEST2 VALUES (15740,23);
    INSERT INTO TEST2 VALUES (15740,24);
    INSERT INTO TEST2 VALUES (15741,25);
    INSERT INTO TEST2 VALUES (15741,26);
    INSERT INTO TEST2 VALUES (15742,27);
    INSERT INTO TEST2 VALUES (15742,28);
    INSERT INTO TEST2 VALUES (15743,29);
    INSERT INTO TEST2 VALUES (15743,30);
    INSERT INTO TEST2 VALUES (15744,31);
    INSERT INTO TEST2 VALUES (15744,32);
    INSERT INTO TEST2 VALUES (15745,33);
    INSERT INTO TEST2 VALUES (15745,34);
    INSERT INTO TEST2 VALUES (15746,35);
    INSERT INTO TEST2 VALUES (15747,36);
    INSERT INTO TEST2 VALUES (15748,11821);
    INSERT INTO TEST2 VALUES (15749,37);
    INSERT INTO TEST2 VALUES (15750,38);

    below sql query is returning 17 rows.

    I want to display these 17 rows using the hierachichal query.

    becasue we have more than one level

    select distinct a.dest1,B.source2 from TEST1 A,TEST2 B
    where A.SOURCE1=B.DEST2
    and A.DEST1=22;

    Thanks in advance.

    DB:4.11:Hierchiacal Queries Help x7

    This is the sql query to get first parent and last child.

    SELECT DISTINCT A.*
    FROM
    (SELECT DEST_ID,SOURCE_ID, CONNECT_BY_ISLEAF AS LEAF,level ll,CONNECT_BY_ROOT source_id AS parent_id
    FROM MN_MAP_EDGE
    START WITH DEST_ID =1
    CONNECT BY PRIOR source_id=dest_id) a
    where leaf=1

  • RELEVANCY SCORE 4.11

    DB:4.11:Looking Data From More Than One Table And Inserting Into Another. mk


    Hello,

    I am giving you the Table structures as per my requirement..

    CREATE TABLE TEMP_A
    (NAME VARCHAR2(100) primary key);

    CREATE TABLE TEMP_B
    (STRUCTURE VARCHAR2(10));

    CREATE TABLE TEMP_C
    ( NAME VARCHAR2(100),
    STRUCTURE VARCHAR2(10),
    VALUE VARCHAR2(10));

    Alter table TEMP_C
    add constraint fk_name_tempc foreign key(name) references TEMP_A(name)
    /

    INSERT INTO TEMP_A VALUES('SMITH');
    INSERT INTO TEMP_A VALUES('ALLEN');
    INSERT INTO TEMP_A VALUES('WARD');
    INSERT INTO TEMP_A VALUES('JONES');
    COMMIT;

    INSERT INTO TEMP_B VALUES('IN');
    INSERT INTO TEMP_B VALUES('IN_MIN');
    INSERT INTO TEMP_B VALUES('IN_TYP');
    INSERT INTO TEMP_B VALUES('IN_MAX');
    INSERT INTO TEMP_B VALUES('DIP');
    INSERT INTO TEMP_B VALUES('TIM');
    COMMIT;

    INSERT INTO TEMP_c VALUES('SMITH','C1','');
    INSERT INTO TEMP_c VALUES('SMITH','C2','');
    INSERT INTO TEMP_c VALUES('SMITH','D1','');
    INSERT INTO TEMP_c VALUES('ALLEN','D2','');
    INSERT INTO TEMP_c VALUES('ALLEN','R1','');
    INSERT INTO TEMP_c VALUES('WARD','R2','');
    COMMIT;

    i want to say is it should insert into table 'TEMP_C' values as :
    For 'SMITH' there should be (6 * 3) = 18 records.
    ( 6 distinct values of TEMP_B for SMITH to be inserted into TEMP_C against 'C1')
    ( 6 distinct values of TEMP_B for SMITH to be inserted into TEMP_C against 'C2')
    ( 6 distinct values of TEMP_B for SMITH to be inserted into TEMP_C against 'D1')
    For 'ALLEN' there should be (6 * 2) = 12 records.
    ( 6 distinct values of TEMP_B for ALLEN to be inserted into TEMP_C against 'D2')
    ( 6 distinct values of TEMP_B for ALLEN to be inserted into TEMP_C against 'R1')
    For 'WARD' there should be (6 * 1) = 6 records.
    ( 6 distinct values of TEMP_B for WARD to be inserted into TEMP_C against 'R2')

    Like this if there are records for JONES also , it should also do the same way( Depending on the No. of records present
    in the table 'TEMP_C' for 'JONES').

    Thanks in advance,
    Amkotz

    DB:4.11:Looking Data From More Than One Table And Inserting Into Another. mk

    Hi, Thanks i thinki need the same output.

    I dont think , i am sure.

    Thanks a lot.
    Bye.

  • RELEVANCY SCORE 4.11

    DB:4.11:Need Sql Query. xx


    Create Table CPITbl(ID int,Preority int,Com Char(20))
    insert into CpiTbl values(28843366,1,'abc')
    insert into CpiTbl values(28843366,2,'bdc')
    insert into CpiTbl values(28843367,1,'abc')
    insert into CpiTbl values(28843367,2,'bc')
    insert into CpiTbl values(28843367,3,'c')
    insert into CpiTbl values(28843368,3,'zx')
    insert into CpiTbl values(28843369,1,'xz')
    insert into CpiTbl values(28843369,2,'h')
    insert into CpiTbl values(28843370,1,'')
    insert into CpiTbl values(28843370,2,'K')
    insert into CpiTbl values(28843370,3,'KL')
    select * from CpiTbl
    Please provide me SQL Query Produce my output like the following

    Valuse in Com1,com2,com3 are coming based on Values in Preority Column...

    each Id having Different Preority values(1,2,3..) when an ID having 1 in preority Com1 should disply1..
    whenPreority having 2 then it should disply1 in Com2...
    Please getme the SQL Query

    Thanks & Regards $@m

    DB:4.11:Need Sql Query. xx

    Pivot approach...

    select id , [Com1],[Com2],[Com3],[Com4] from
    (select ID, case when Preority = 1 then 1 else Preority end as Preority, 'Com' CAST(Preority as varchar(10)) as headers from CpiTbl) x
    pivot (min (Preority) for headers in ([Com1],[Com2],[Com3],[Com4]) ) pvt



    Thanks,
    Saurabhhttp://www.linkedin.com/profile/view?id=36482856=tab_pro http://www.experts-exchange.com/M_6313078.html

  • RELEVANCY SCORE 4.10

    DB:4.10:Sql Query Returns Repeating Rows ak


    Oracle Version : 10g R2.

    DDL DML
    ---------
    CREATE TABLE T1
    (CCODE VARCHAR2(4 BYTE),
    PNUM VARCHAR2(16 BYTE),
    CTYPCDE VARCHAR2(4 BYTE)
    );

    INSERT INTO T1 VALUES ('0001','003526892','0007');
    INSERT INTO T1 VALUES ('0001','06767019','0006');
    INSERT INTO T1 VALUES ('0001','14787-10-900','0007');
    INSERT INTO T1 VALUES ('0002','003445803','0009');
    INSERT INTO T1 VALUES ('0002','000000','0018');
    INSERT INTO T1 VALUES ('0002','001645156','0008');
    INSERT INTO T1 VALUES ('0002','001646283','0008');
    INSERT INTO T1 VALUES ('0025','002587509','0008');
    INSERT INTO T1 VALUES ('0025','02507462', '0008');
    INSERT INTO T1 VALUES ('0025','02565229', '0008');
    INSERT INTO T1 VALUES ('0025','000943965','0007');

    CREATE TABLE XWLK
    (CTYPCDE VARCHAR2(4 BYTE),
    CODE VARCHAR2(85 BYTE)
    );

    INSERT INTO XWLK VALUES ( '0004','70');
    INSERT INTO XWLK VALUES ( '0005','05');
    INSERT INTO XWLK VALUES ( '0006','05');
    INSERT INTO XWLK VALUES ( '0007','05');
    INSERT INTO XWLK VALUES ( '0007','10');
    INSERT INTO XWLK VALUES ( '0008','15');
    INSERT INTO XWLK VALUES ( '0008','20');
    INSERT INTO XWLK VALUES ( '0009','25');
    INSERT INTO XWLK VALUES ( '0010','75');
    INSERT INTO XWLK VALUES ( '0010','80');
    INSERT INTO XWLK VALUES ( '0011','75');
    INSERT INTO XWLK VALUES ( '0011','80');
    INSERT INTO XWLK VALUES ( '0012','75');
    INSERT INTO XWLK VALUES ( '0012','80');
    INSERT INTO XWLK VALUES ( '0013','75');
    INSERT INTO XWLK VALUES ( '0013','80');
    INSERT INTO XWLK VALUES ( '0014','75');
    INSERT INTO XWLK VALUES ( '0014','80');
    INSERT INTO XWLK VALUES ( '0015','75');
    INSERT INTO XWLK VALUES ( '0015','80');
    INSERT INTO XWLK VALUES ( '0016','75');
    INSERT INTO XWLK VALUES ( '0016','80');
    INSERT INTO XWLK VALUES ( '0017','15');
    INSERT INTO XWLK VALUES ( '0017','70');
    INSERT INTO XWLK VALUES ( '0018','15');
    INSERT INTO XWLK VALUES ( '0018','25');
    INSERT INTO XWLK VALUES ( '0018','70');
    INSERT INTO XWLK VALUES ( '0019','25');
    INSERT INTO XWLK VALUES ( '0019','70');
    INSERT INTO XWLK VALUES ( '0020','70');
    INSERT INTO XWLK VALUES ( '0021','75');
    INSERT INTO XWLK VALUES ( '0021','80');
    INSERT INTO XWLK VALUES ( '0022','75');
    INSERT INTO XWLK VALUES ( '0022','80');
    INSERT INTO XWLK VALUES ( '0023','10');
    INSERT INTO XWLK VALUES ( '0025','05');
    INSERT INTO XWLK VALUES ( '0025','10');
    INSERT INTO XWLK VALUES ( '0025','25');
    INSERT INTO XWLK VALUES ( '0025','30');
    INSERT INTO XWLK VALUES ( '0025','35');
    INSERT INTO XWLK VALUES ( '0025','40');
    INSERT INTO XWLK VALUES ( '0025','45');
    INSERT INTO XWLK VALUES ( '0025','50');
    INSERT INTO XWLK VALUES ( '0025','55');
    INSERT INTO XWLK VALUES ( '0025','60');
    INSERT INTO XWLK VALUES ( '0025','65');
    INSERT INTO XWLK VALUES ( '0026','75');
    INSERT INTO XWLK VALUES ( '0026','80');
    INSERT INTO XWLK VALUES ( '0027','05');
    INSERT INTO XWLK VALUES ( '0027','10');
    INSERT INTO XWLK VALUES ( '0027','25');
    INSERT INTO XWLK VALUES ( '0027','30');
    INSERT INTO XWLK VALUES ( '0027','35');
    INSERT INTO XWLK VALUES ( '0027','40');
    INSERT INTO XWLK VALUES ( '0027','45');
    INSERT INTO XWLK VALUES ( '0027','50');
    INSERT INTO XWLK VALUES ( '0027','55');
    INSERT INTO XWLK VALUES ( '0027','60');
    INSERT INTO XWLK VALUES ( '0027','65');
    INSERT INTO XWLK VALUES ( '0028','15');
    INSERT INTO XWLK VALUES ( '0069','75');

    CREATE TABLE PTB
    ( PNUM VARCHAR2(20 BYTE));

    INSERT INTO PTB VALUES('003526892');
    INSERT INTO PTB VALUES('06767019');
    INSERT INTO PTB VALUES('14787-10-900');
    INSERT INTO PTB VALUES('003445803');
    INSERT INTO PTB VALUES('000000');
    INSERT INTO PTB VALUES('001645156');
    INSERT INTO PTB VALUES('001646283');
    INSERT INTO PTB VALUES('002587509');
    INSERT INTO PTB VALUES('02507462');
    INSERT INTO PTB VALUES('02565229');
    INSERT INTO PTB VALUES('000943965');

    Query
    -----
    SELECT
    SAK.CCODE,
    SAK.CTYPCDE,
    CASE
    WHEN SAK.CCODE = '0001' and SAK.CTYPCDE != '0088' THEN '75'
    WHEN SAK.CCODE = '0002' and SAK.CTYPCDE != '0088' THEN '75'
    WHEN SAK.CCODE = '0002' and SAK.CTYPCDE != '0088' THEN '80'
    WHEN SAK.CCODE = '0003' and SAK.CTYPCDE != '0088' THEN '80'
    ELSE XWLK.CODE
    END AS PCD,
    PTB.PNUM
    FROM T1 SAK INNER JOIN XWLK ON SAK.CTYPCDE = XWLK.CTYPCDE
    INNER JOIN PTB ON SAK.PNUM = PTB.PNUM
    Order by 4

    Output

    CCODECTYPCODE PCDPNUM
    0002001875000000
    0002001875000000
    0002001875000000
    0025000705000943965
    0025000710000943965
    0002000875001645156
    0002000875001645156
    0002000875001646283
    0002000875001646283
    0025000815002587509
    0025000820002587509
    0002000975003445803
    0001000775003526892
    0001000775003526892
    002500082002507462
    002500081502507462
    002500081502565229
    002500082002565229
    000100067506767019
    000100077514787-10-900
    000100077514787-10-900

    But the Output is not the expected one if we see for the PNUM = 000000, the records are displayed
    3 times it should be only ones displayed and also for PNUM = 001645156. My Query is why is it displaying
    more than once, is there anyway that we can display it only once. Can some please advice me where am making
    wrong. Thanks in advance.

    DB:4.10:Sql Query Returns Repeating Rows ak

    Hi,

    Sorry, I don't understand the results you want.
    Let's just look at ptb.pnum='000-06-5393-05'. Why do you want these results:
    PSAKPCDPNUMSEQ_NUM
    ------------------------------
    946200305000-06-5393-051
    946200410000-06-5393-052and not these?
    PSAKPCDPNUMSEQ_NUM
    ------------------------------
    946200305000-06-5393-051
    946200310000-06-5393-051
    946200410000-06-5393-052
    946200405000-06-5393-052What relates pcd='05' to seq_num=1? Wby isn't pcd='05' related to seq_num='02' instead, or to both, as in the current results?
    Why do you want pcd='10' on the same output row as seq_num=1, but not seq_num=2?

    Also, I noticed something odd in the CASE expression:
    CASE
    WHEN SAK.CCODE = '0001' and SAK.CTYPCDE != '0088' THEN '75'
    WHEN SAK.CCODE = '0002' and SAK.CTYPCDE != '0088' THEN '75'
    WHEN SAK.CCODE = '0002' and SAK.CTYPCDE != '0088' THEN '80' ...The conditions on the last two lines look exactly the same. The expressions are evaluated in order, so if ccode='0002' and ctypcde != '0088', then the CASE expression will return '75'. The last line above will only be looked at when ccode != '0002' (or is NULL) or ctypcde='0088' (or is NULL), so the conditions on last line above will never be true.
    I'm sure this has nothing to do with the problem you're asking about.

  • RELEVANCY SCORE 4.10

    DB:4.10:Please Solve The Query ss


    Hi,

    please solve below query

    create table base_order(cnt_num number,qty number);
    insert into base_order values(1,25);
    insert into base_order values(2,22);
    insert into base_order values(3,5);
    insert into base_order values(4,13);
    insert into base_order values(5,19);
    insert into base_order values(6,31);
    insert into base_order values(7,6);
    insert into base_order values(8,17);
    insert into base_order values(9,35);
    insert into base_order values(10,45);
    insert into base_order values(11,25);
    insert into base_order values(12,27);
    insert into base_order values(13,29);
    insert into base_order values(14,15);
    insert into base_order values(15,5);
    insert into base_order values(16,4);
    insert into base_order values(17,3);
    insert into base_order values(18,21);
    insert into base_order values(19,29);
    insert into base_order values(20,20);
    insert into base_order values(20,2);
    commit;

    select * from base_order

    My output should be

    first 5 rows total ---- 84
    second 5 rows total --- 134
    third 5 rows total ---100
    (in last we have 6 rows then it should show)
    Last 6 rows total 79

    DB:4.10:Please Solve The Query ss

    You don't want the totals for groups of 5 rows, since you say the last total comes from the last six rows. I am assuming from your data that you want to sum by groups of 5 CNT_NUM. If this is not the case then please restate your requirement in general terms. select min(CNT_NUM) MIN_CNT_NUM, max(CNT_NUM) MAX_CNT_NUM, SUM(QTY) SUM_QTY
    from BASE_ORDER
    group by TRUNC((CNT_NUM-1)/5)
    order by 1
    /
    MIN_CNT_NUM MAX_CNT_NUM SUM_QTY
    ---------------------- ---------------------- ----------------------
    1 5 84
    6 10 134
    11 15 101
    16 20 79P.S. Thanks to lee200 below, CEIL(cnt_num/5) is semantically preferable to TRUNC((CNT_NUM-1)/5)

    Edited by: Stew Ashton on Jan 31, 2012 11:44 AM

  • RELEVANCY SCORE 4.10

    DB:4.10:Reading From Text File 3j


    Hi all,
    I have text file like these

    example.txt:

    Insert into example(col1,col2,col3) values(values1,values2,values');
    Insert into example(col1,col2,col3) values(values1,values2,values');
    Insert into example(col1,col2,col3) values(values1,values2,values');
    Insert into example(col1,col2,col3) values(values1,values2,values');
    Insert into example(col1,col2,col3) values(values1,values2,values');
    Insert into example(col1,col2,col3) values(values1,values2,values');
    Insert into example(col1,col2,col3) values(values1,values2,values');
    Insert into example(col1,col2,col3) values(values1,values2,values');

    I want plsql to insert the values into example table through this file.

    Edited by: 00125 on Jun 5, 2012 1:46 AM

    DB:4.10:Reading From Text File 3j

    Hi,

    For OS Files:

    We can Use the UTL_FILE Package in Oracle to read and Write Operating System Files in PL/SQL.

    If you want to execute a Script, that is available in a text file, then

    For Example if the location of the text file with Script is at D:\example.txt

    Then,

    At SQL Prompt,

    SQL @D:\example.txt Enter

    The Script inside the File is executed.

    Thanks,
    Shankar

  • RELEVANCY SCORE 4.09

    DB:4.09:Need Help With T-Sql j9



    drop table HelpMe
    Create Table HelpMe
    (
    DBName varchar(100),
    DBProperty varchar(100),
    PropertyVALUE varchar(100)
    )

    Insert into HelpMe
    values (Master, cmpt, 80)
    Insert into HelpMe
    values (Master, recoverymodel, FULL)
    Insert into HelpMe
    values (Master, autoclose, Yes)
    Insert into HelpMe
    values (Model, cmpt, 90)
    Insert into HelpMe
    values (Model, recoverymodel, Simple)
    Insert into HelpMe
    values (Model, autoclose, Yes)
    Insert into HelpMe
    values (MSDB, cmpt, 100)
    Insert into HelpMe
    values (MSDB, recoverymodel, Full)
    Insert into HelpMe
    values (MSDB, autoclose, No)

    Select * from HelpMe

    DB:4.09:Need Help With T-Sql j9

    Getting Error

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near '('.

    What is your sql server version??? The query runs fine on my machine(sql server 2008 sp2)

    Thanks and regards, Rishabh , Microsoft Community Contributor

  • RELEVANCY SCORE 4.09

    DB:4.09:Uk Postcode Match Further Question 8z


    Hi I recently posted the following question
    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/8632d7dd-40d6-4467-ab63-a8009487c0b5
    The solution provided worked great.
    I have run the solution over the database that I am using and have come across some of the data where there is no space between the 2 parts of the postcode so it will not match the data.
    How can I get around this?
    Here is some sample data
    CREATE TABLE #TMPTOWNS1
    (POSTCODE VARCHAR(10) NOT NULL
    ,REGION VARCHAR(30) NOT NULL)


    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L1','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L10','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L11','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L12','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L13','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L14','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L15','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L16','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L17','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L18','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L19','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L2','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L20','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L21','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L22','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L23','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L24','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L25','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L26','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L27','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L28','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L29','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L3','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L30','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L31','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L32','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L33','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L34','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L35','St Helens')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L36','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L37','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L38','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L39','Lancashire')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L4','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L40','Lancashire')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L5','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L6','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L69','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L7','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L70','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L8','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L9','Liverpool')

    CREATE TABLE #TMPTOWNS2
    (POSTCODE VARCHAR(10) NOT NULL
    ,REGION VARCHAR(30) NOT NULL)


    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO1','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO10','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO11','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO12','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO13','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO14','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO15','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO16','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO17','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO18','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO19','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO21','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO22','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO23','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO24','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO25','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO26','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO30','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO31','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO32','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO41','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO42','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO43','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO51','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO60','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO61','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO62','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO7','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO8','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('ZE1','Shetland Islands')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('ZE2','Shetland Islands')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('ZE3','Shetland Islands')

    CREATE TABLE #MAINTABLE
    (POSTCODE VARCHAR(10) NOT NULL)


    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('YO1 6ZY')
    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('YO25 7AP')
    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('L3 2BT')
    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('L38 1LT')
    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('L381LT')
    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('YO16ZY')

    SELECT M.POSTCODE
    ,REGION =coalesce(T1.REGION , T2.REGION)
    FROM #MAINTABLE AS M
    LEFT OUTER JOIN #TMPTOWNS1 AS T1 ON LEFT(M.POSTCODE,CHARINDEX(' ',M.POSTCODE))=T1.POSTCODE
    LEFT OUTER JOIN #TMPTOWNS2 AS T2 ON LEFT(M.POSTCODE,CHARINDEX(' ',M.POSTCODE))=T2.POSTCODE

    DROP TABLE #MAINTABLE
    DROP TABLE #TMPTOWNS1
    DROP TABLE #TMPTOWNS2

    DB:4.09:Uk Postcode Match Further Question 8z

    Thanks both solutions worked for me, hopefully it will translate to the database I am working on.

  • RELEVANCY SCORE 4.09

    DB:4.09:Qery Issue zd


    hi,

    CREATE TABLE "NEO"."TABLE1"
    ("ID" NUMBER
    )

    -- INSERTING into TABLE1
    Insert into TABLE1 (ID) values (1);
    Insert into TABLE1 (ID) values (2);
    Insert into TABLE1 (ID) values (3);
    Insert into TABLE1 (ID) values (4);
    Insert into TABLE1 (ID) values (5);
    Insert into TABLE1 (ID) values (6);
    Insert into TABLE1 (ID) values (7);

    and i am executing this query

    selet * from table1 where id in (select id from table1 order by id desc)

    but getting error . thanks

    DB:4.09:Qery Issue zd

    selet * from table1 where id in (select id from table1 order by id desc) Please do not post syntax errors.

    SELECT is spelled wrong.
    The next eroor you will get, will say something like "can't have ORDER BY in subquery". Handle this error by removing the order criteria to the outer query.

    Btw: this query is stupid. Why not directly selecting from the table1. THis would give the same result.
    select id from table1 order by id desc;

  • RELEVANCY SCORE 4.09

    DB:4.09:Need Help With Sql 9c


    Hi guys
    i need to get rid of id where ord ='c' only for all rows per id.... see the sample data
    presently 4 ids are coming in the result (102, 105, 106,108) , need to get rid of id 105,108
    create table #temp
    (id int,
    ord int,
    typ varchar(2))

    insert into #temp values (101, 1, 'p')
    insert into #temp values (101, 2, 'p')
    insert into #temp values (101, 3, 'c')
    insert into #temp values (101, 4, 'p')
    insert into #temp values (102, 1, 'c')
    insert into #temp values (102, 2, 'c')
    insert into #temp values (102, 3, 'p')
    insert into #temp values (103, 1, 'c')
    insert into #temp values (103, 2, 's')
    insert into #temp values (103, 3, 'p')
    insert into #temp values (104, 1, 'c')
    insert into #temp values (104, 2, 's')
    insert into #temp values (105, 1, 'c')
    insert into #temp values (105, 2, 'c')
    insert into #temp values (106, 1, 'c')
    insert into #temp values (106, 2, 'p')
    insert into #temp values (106, 3, 'c')
    insert into #temp values (107, 1, 'p')
    insert into #temp values (107, 2, 'p')
    insert into #temp values (107, 3, 'p')
    insert into #temp values (108, 1, 'c')
    SELECT Id FROM #temp
    GROUP BY Id
    HAVING MAX(CASE WHEN typ = 's' THEN 1 ELSE 0 END) = 0
    AND COUNT(CASE WHEN typ ='c' THEN ord END) =1
    and min(case when typ ='c' then ord end )=1

    DB:4.09:Need Help With Sql 9c

    Hello,
    Try this
    AND COUNT(CASE WHEN typ ='c' THEN ord else NULL END) =1

  • RELEVANCY SCORE 4.09

    DB:4.09:Outer Join Question 7x


    So for the following data:

    CREATE TABLE MOVIE
    (TK NUMBER(10,0) primary key,
    TITLE VARCHAR2(40),
    CODE_GENRE VARCHAR2(2));
    /
    CREATE TABLE ACTOR
    (TK NUMBER(10,0) primary key,
    NAME VARCHAR2(40));
    /
    CREATE TABLE MOVIE_ACTOR_XREF
    (TK_MOVIE NUMBER(10,0) references movie (tk),
    TK_ACTOR NUMBER(10,0) references actor (tk));
    /
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (1,'MAN ON FIRE','D');
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (2,'THE MATRIX','SF');
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (3,'PEE WEE''S BIG ADVENTURE','C');
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (4,'NACHO LIBRE','C');
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (5,'UP','CH');
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (6,'HELLRAISER','H');
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (7,'BILL AND TED''S EXCELLENT ADVENTURE','C');
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (8,'MEMENTO','M');
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (9,'GHOST BUSTERS','C');
    INSERT INTO MOVIE (TK,TITLE,CODE_GENRE) VALUES (10,'MUMFORD','C');

    INSERT INTO ACTOR (TK,NAME) VALUES (1,'DENZEL WASHINGTON');
    INSERT INTO ACTOR (TK,NAME) VALUES (2,'DAKOTA FANNING');
    INSERT INTO ACTOR (TK,NAME) VALUES (3,'KEANU REAVES');
    INSERT INTO ACTOR (TK,NAME) VALUES (4,'PEE WEE HERMAN');
    INSERT INTO ACTOR (TK,NAME) VALUES (5,'JACK BLACK');
    INSERT INTO ACTOR (TK,NAME) VALUES (6,'ED ASNER');
    INSERT INTO ACTOR (TK,NAME) VALUES (7,'PINHEAD');
    INSERT INTO ACTOR (TK,NAME) VALUES (8,'GUY PIERCE');
    INSERT INTO ACTOR (TK,NAME) VALUES (9,'BILL MURRAY');
    INSERT INTO ACTOR (TK,NAME) VALUES (10,'DAN AKROYD');
    INSERT INTO ACTOR (TK,NAME) VALUES (11,'JASON LEE');

    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (1,1);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (1,2);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (2,3);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (3,4);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (4,5);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (5,6);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (6,7);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (7,3);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (8,8);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (9,9);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (9,10);
    INSERT INTO MOVIE_ACTOR_XREF (TK_MOVIE,TK_ACTOR) VALUES (10,11);Why don't the following 2 queries produce the same results? The MOVIE table is the LEFTermost table in the outer joins, so I thought I could place criteria against it in the FROM clause, and end up with the same output:

    select m.title, m.code_genre, a.name
    from movie m
    left outer
    join movie_actor_xref x on (m.tk = x.tk_movie
    and m.code_genre = 'C')
    left outer
    join actor a on (x.tk_actor = a.tk);

    select m.title, m.code_genre, a.name
    from movie m
    left outer
    join movie_actor_xref x on (m.tk = x.tk_movie)
    left outer
    join actor a on (x.tk_actor = a.tk)
    where m.code_genre = 'C';Thanks,
    --=Chuck

    DB:4.09:Outer Join Question 7x

    Thanks Frank Solomon. That makes sense now. I was thinking that the two following queries were producing identical results, but after your explanations (and a little attention to detail), I see the difference. Thanks :)

    select m.tk, m.title, m.code_genre, A.NAME
    from movie m
    left outer
    join movie_actor_xref x on (m.tk = x.tk_movie
    and m.code_genre = 'C')
    left outer
    join actor a on (x.tk_actor = a.tk)select m.tk, m.title, m.code_genre, A.NAME
    from movie m
    left outer
    join movie_actor_xref x on (m.tk = x.tk_movie)
    left outer
    join actor a on (x.tk_actor = a.tk)

  • RELEVANCY SCORE 4.08

    DB:4.08:Get 7 File Groups At Any Given Time 17


    Hi,

    I have filegroup mapping table as below
    Create table FgMap
    ( CurrFG varchar(50),
    NxtFG Varchar(50));

    Insert into FGMAP values('FG_1','FG_2')
    Insert into FGMAP values('FG_2','FG_3')
    Insert into FGMAP values('FG_3','FG_4')
    Insert into FGMAP values('FG_4','FG_5')
    Insert into FGMAP values('FG_5','FG_6')
    Insert into FGMAP values('FG_6','FG_7')
    Insert into FGMAP values('FG_7','FG_8')
    Insert into FGMAP values('FG_8','FG_9')
    Insert into FGMAP values('FG_9','FG_10')
    Insert into FGMAP values('FG_10','FG_11')
    Insert into FGMAP values('FG_11','FG_12')
    Insert into FGMAP values('FG_12','FG_13')
    Insert into FGMAP values('FG_13','FG_14')
    Insert into FGMAP values('FG_14','FG_15')
    Insert into FGMAP values('FG_15','FG_16')
    Insert into FGMAP values('FG_16','FG_17')
    Insert into FGMAP values('FG_17','FG_18')
    Insert into FGMAP values('FG_18','FG_19')
    Insert into FGMAP values('FG_19','FG_20')
    Insert into FGMAP values('FG_20','FG_21')
    Insert into FGMAP values('FG_21','FG_1')

    I need a query which pick from 1-7,8-15,15-21, once 21 record is reached it should pick again1-7 literally rotating window for 21 days.

    Regards,
    Navin
    Navin.D http://dnavin.wordpress.com

    DB:4.08:Get 7 File Groups At Any Given Time 17

    If you want to split it into groups of 7 rows you can use NTILE ranking function

    Regards
    EmilKatie & Emil Microsoft SQL BI related blog & tutorials

    SQL Tutorial |
    SSRS Tutorial |
    SSIS Tutorial

  • RELEVANCY SCORE 4.07

    DB:4.07:Select Until Sum = X 7f


    create table t (id number, amount number);
    insert into t values (1,20);
    insert into t values (2,20);
    insert into t values (3,20);
    insert into t values (4,20);

    -- Pseudo code of what I require --
    select id
    from t
    until sum of amount is greater than or equal to 40
    order by id

    -- Desired output --
    -------
    1
    2
    -------

    DB:4.07:Select Until Sum = X 7f

    select t1.id
    from t t1,t t2
    where t2.id = t1.id
    group by t1.id
    having sum(t2.amount) = 40
    order by t1.id
    ;

  • RELEVANCY SCORE 4.07

    DB:4.07:Urgent Help In In Sql Ora-01719: Outer Join Operator (+) Not Allowed In Ope 77


    drop table t1;

    create table t1(c1 varchar2(10));
    insert into t1 values('a');
    insert into t1 values('b');
    insert into t1 values(' ');
    insert into t1 values(' ');

    drop table t2;

    create table t2(c1 varchar2(10));
    insert into t2 values('b');
    insert into t2 values('');
    insert into t2 values('');

    select a.*
    from t1 a, t2 b
    where (a.c1 = nvl(b.c1,' ')
    or a.c1 = b.c1 (+))
    ;

    getting this erro
    ERROR at line 4:
    ORA-01719: outer join operator (+) not allowed in operand of OR or IN

    DB:4.07:Urgent Help In In Sql Ora-01719: Outer Join Operator (+) Not Allowed In Ope 77

    I'm not quite sure what you want for the results. maybe this?
    SQL select a.*
    2 from t1 a
    3 left join t2 b
    4 on (a.c1 = nvl(b.c1,' '))
    5 ;

    C1
    ----------
    b

    a

    6 rows selected.

  • RELEVANCY SCORE 4.07

    DB:4.07:How To: Create Partition Function/Scheme km


    Here is the sample data...

    DECLARE @t TABLE (Id int, Value varchar(50));
    INSERT INTO @t VALUES (-1, a)
    INSERT INTO @t VALUES (-2, b)
    INSERT INTO @t VALUES (-4, c)
    INSERT INTO @t VALUES (1, t)
    INSERT INTO @t VALUES (6, w)
    INSERT INTO @t VALUES (8, v)

    SELECT *
    FROM @t

    DB:4.07:How To: Create Partition Function/Scheme km


    Please refer below: (as per ur example)
    CREATE PARTITION FUNCTIONtestrange (char(2))
    AS RANGE RIGHT FOR VALUES ('a', 'd', 'v');
    For more information:
    http://msdn.microsoft.com/en-us/library/ms187802.aspx
    http://msdn.microsoft.com/en-us/library/ms179854.aspx
    Example:
    http://sqlserverpedia.com/wiki/Example_of_Creating_Partitioned_Tables
    Thanks,

    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

  • RELEVANCY SCORE 4.06

    DB:4.06:Sql Server 2008 R2 Query Issue 87


    Hi, I am using SQL Server 2008 R2 and I am working on a very typical scenario. We are doing some root cause analysis to improve business and reduce overhead cost, a business user wants to sample data by picking an id at uniform intervals randomly. Per say
    I have 10,000 records of member data and 100 members so 10,000/100 would be 100 records. So the business user will be picking a record randomly for every 100 records. 1-100 - 1 record, 101-200 - 1 record and so on.....
    To acheive this he wants us to provide a resultsetusing @sample parameter.
    For the sample data below where I have 100 recordstheId with a value is5 and@sample = 10, every 10th record [Value]from [Id] =5 needs to be updated with the [Id]. In my case
    Expected Resultset:
    Id Value
    55
    15 15
    25 25
    35 35
    45 45
    55 55
    65 65
    75 75
    85 85
    95 95

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Status]') AND type in (N'U'))
    DROP TABLE [dbo].[Status]
    GO
    CREATE TABLE [dbo].[Status](
    [Id] [int] NOT NULL,
    [Value] [int] NULL
    ) ON [PRIMARY]
    GO
    --Insert statement
    INSERT INTO [Status] ([Id],[Value])VALUES(1,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(2,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(3,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(4,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(5,5)
    INSERT INTO [Status] ([Id],[Value])VALUES(6,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(7,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(8,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(9,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(10,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(11,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(12,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(13,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(14,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(15,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(16,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(17,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(18,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(19,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(20,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(21,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(22,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(23,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(24,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(25,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(26,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(27,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(28,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(29,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(30,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(31,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(32,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(33,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(34,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(35,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(36,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(37,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(38,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(39,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(40,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(41,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(42,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(43,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(44,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(45,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(46,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(47,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(48,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(49,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(50,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(51,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(52,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(53,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(54,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(55,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(56,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(57,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(58,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(59,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(60,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(61,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(62,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(63,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(64,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(65,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(66,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(67,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(68,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(69,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(70,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(71,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(72,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(73,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(74,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(75,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(76,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(77,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(78,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(79,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(80,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(81,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(82,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(83,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(84,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(85,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(86,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(87,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(88,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(89,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(90,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(91,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(92,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(93,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(94,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(95,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(96,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(97,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(98,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(99,NULL)
    INSERT INTO [Status] ([Id],[Value])VALUES(100,NULL)
    GO
    Thanks in advance............

    DB:4.06:Sql Server 2008 R2 Query Issue 87

    declare @sample int =12
    declare @firstval int

    Select @firstval= value from status WHERE value is not null

    ;with mycte as (

    Select *, row_number() Over(order by id) rn from status )

    Update mycte
    Set Value=id
    where (rn-@firstval)% @sample=0
    AND rn@firstval

    SELECT * from status
    --WHERE value is not null

  • RELEVANCY SCORE 4.06

    DB:4.06:Select/Flag N Number Of Records kz


    Hi ,
    I need a query to select/flag 90,000 records in a table group by dealerName,Car,Type. The 90,000 should go equally into each grouping of dealerName,Car,Type.
    The table it self has 200,000 records.For sample data here are 90 records (would like to know how to distribute these evenly group by dealerName,Car,Type)

    -- Test table
    DECLARE @DealerNames TABLE (
    DealerName varchar(50),
    car varchar(20),
    type varchar(20),
    flag varchar (20)

    )

    -- 1st set of Dealers
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 1,'')
    INSERT INTO @DealerNames values ('AA Ltd','Repair', 2,'')
    INSERT INTO @DealerNames values ('AA Ltd','Normal', 3,'')
    -- 2nd set of Dealers
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 1,'')
    INSERT INTO @DealerNames values ('BTM','Repair', 2,'')
    INSERT INTO @DealerNames values ('BTM','Normal', 3,'')

    -- 3rd set of Dealers
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 1,'')
    INSERT INTO @DealerNames values ('CAT','Repair', 2,'')
    INSERT INTO @DealerNames values ('CAT','Normal', 3,'')

    Select * from @DealerNames

    DB:4.06:Select/Flag N Number Of Records kz

    Thanks Peso,
    NTILE ABS(CHECKSUM(NEWID()))will prove very helpful here.
    Cheers

  • RELEVANCY SCORE 4.06

    DB:4.06:Unique Row ad


    drop table ssn;
    create table ssn
    (ssn varchar2(10));

    drop table inv;
    create table inv
    (pid varchar2(10),
    ssn_id varchar2(10));

    insert into ssn values ('12345689');
    insert into ssn values ('341256982');
    insert into ssn values ('231564879');
    insert into ssn values ('123456789');
    insert into ssn values ('012345678');

    insert into inv values (1, '123456789');
    insert into inv values (2, '123456789');
    insert into inv values (3, '012345678');
    insert into inv values (4, '231564879');
    insert into inv values (5, '341256982');

    select distinct a.pid
    ,b.pid
    ,c.ssn
    from inv a join inv b on a.ssn_id = b.ssn_id
    join ssn c on c.ssn = a.ssn_id
    where a.pid != b.pid

    PID PIDSSN
    112123456789
    221123456789

    I would like to get one row with ssn 123456789. I've tried mod(rownum,2) = 1, but the actual table has thousands of rows and some dups are both on odd rows. It's possible I used the wrong construct.

    Any help would be greatly appreciated.

    Thanks,

    Dane

    DB:4.06:Unique Row ad

    It works. I'm assuming the relation operator on the last line should be: HAVING MIN(pid) !=MAX(pid).

    Thanks,

    Dane

  • RELEVANCY SCORE 4.06

    DB:4.06:Joining With Some Format 3a


    Please help to get the required results after joining.
    Table #volume_2 should take three number from starting and #volume's Vol column should repeat three times.
    drop table #Volume
    CREATE TABLE #Volume (cnt int, Vol int)
    insert into #Volume values (1,1300)
    insert into #Volume values (2,1000)
    insert into #Volume values (3,1200)
    CREATE TABLE #Volume_2 (num int)
    insert into #Volume_2 values (2)
    insert into #Volume_2 values (3)
    insert into #Volume_2 values (4)
    insert into #Volume_2 values (6)
    insert into #Volume_2 values (7)
    insert into #Volume_2 values (8)
    insert into #Volume_2 values (10)
    insert into #Volume_2 values (11)
    insert into #Volume_2 values (12)
    --Required output
    num Vol
    --- ----
    2 1300
    3 1300
    4 1300
    6 1000
    7 1000
    8 1000
    10 1200
    11 1200
    12 1200

  • RELEVANCY SCORE 4.06

    DB:4.06:Find Duplicates With Only Specific Env. Values k7


    Experts,
    We have some table sample values as:-

    CREATE TABLE #TEMP
    (
    Ky_Ot INT,
    PanNo INT,
    Env VARCHAR(10)
    )

    INSERT INTO #TEMP VALUES ( 2, 483000012, 'VB.NET' )
    INSERT INTO #TEMP VALUES ( 2, 483000012, 'C#.NET' )
    INSERT INTO #TEMP VALUES ( 2, 483000012, 'ADO.NET' )
    INSERT INTO #TEMP VALUES ( 2, 483000012, 'T-SQL' )
    INSERT INTO #TEMP VALUES ( 2, 483000012, 'PL/SQL' )

    INSERT INTO #TEMP VALUES ( 4, 534800518, 'VB.NET' )
    INSERT INTO #TEMP VALUES ( 4, 534800518, 'T-SQL' )

    SELECT *
    FROM
    #TEMP

    DROP TABLE #TEMP

    DB:4.06:Find Duplicates With Only Specific Env. Values k7

    This simple query will work:

    select PanNo from #TEMP
    where Env IN ('VB.NET','PL/SQL')
    GROUP BY PanNo
    HAVING COUNT(*) = 2

  • RELEVANCY SCORE 4.05

    DB:4.05:How To Filter Less Value From Table? mc


    I have two tables called @Products and @Rates. I need to take out minimum price from @Rates, how?

    SET NOCOUNT ON DECLARE @Products TABLE (product_id VarChar(50),product_name VarChar(50) )INSERT INTO @Products Values ('1','Pen');INSERT INTO @Products Values ('2','Pencil');INSERT INTO @Products Values ('3','School Bag');INSERT INTO @Products Values ('4','Book');INSERT INTO @Products Values ('5','Pencil Box');select * from @productsSET NOCOUNT ON DECLARE @Rates TABLE (product_id VarChar(50),price int )INSERT INTO @Rates Values ('1','10');INSERT INTO @Rates Values ('3','5');INSERT INTO @Rates Values ('1','5');INSERT INTO @Rates Values ('4','20');INSERT INTO @Rates Values ('4','15');INSERT INTO @Rates Values ('5','30');select * from @Rates

    DB:4.05:How To Filter Less Value From Table? mc

    Hi,
    You can go with CTE bv partitioning on the rate column.Cheers,
    IT-Singh

  • RELEVANCY SCORE 4.05

    DB:4.05:How To Get The Decimal Part For An Interger Value p1


    Hi All,I have a task to round off the Price column in the price_value table upto 2 decimal values.create table price_value (price number(20,4));insert into price_value values(5);insert into price_value values(55);insert into price_value values(55.5);insert into price_value values(55.12);insert into price_value values(55.456);select * from price_value;Expected Output 5.0055.0055.5055.1255.45 Please help me how to do this.Thanks,

    DB:4.05:How To Get The Decimal Part For An Interger Value p1

    Hi
    943026 wrote:

    .. Can you please explain me about 'fm9999999999999999D00' for my Information....

    TO_CHAR (x, '9999999999999999D00' ) returns a string representation of the number x, with 16 digits to left of the decimal point, and 2 digits to the right of the decimal point. '9' means pad with spaces, if the number doesn't need that many digits, and '0' means pad with 0's.'fm' means don't include the space padding. (TO_CHAR doesn't have a more direct way of saying "use only as many digits as necessary before the decimal point".)For more details, look upFormat Modelsin the SQL language manual.

  • RELEVANCY SCORE 4.05

    DB:4.05:Convert Rows Into A Single Column 9c


    create table suresh
    (
    id number,
    ch char(1)
    )
    ;

    insert into suresh values(1,'i');
    insert into suresh values(1,'a');
    insert into suresh values(1,'m');
    insert into suresh values(1,'b');
    insert into suresh values(1,'o');
    insert into suresh values(1,'y');

    select * from suresh

    idch
    1i
    1a
    1m
    1b
    1o
    1y

    I am looking for output somethign like this

    id ch
    1 iamboy
    ..

    DB:4.05:Convert Rows Into A Single Column 9c

    MichaelS wrote:
    Can I write without using any built-in functionNot sure if XMLTable would be considered a function:

    SQL select * from xmltable('ora:view("suresh")/ROW/CH/text()')
    /
    COLUMN_VALUE
    ------------
    iamboy
    1 row selected.You pass it a parameter and it returns something. It's a function. ; )

  • RELEVANCY SCORE 4.05

    DB:4.05:Sql Select Between In Varchar Column 7s


    I want Four row only...(PRE/1004,PRE/1005,PRE/10005,PRE/10006)

    CREATE TABLE #Table2 (VouNo VARCHAR(50))

    INSERT INTO #Table2 values ('PRE/1001')
    INSERT INTO #Table2 values ('PRE/1002')
    INSERT INTO #Table2 values ('SRT/2002')
    INSERT INTO #Table2 values ('PRE/1003')
    INSERT INTO #Table2 values ('PRE/1004')
    INSERT INTO #Table2 values ('PRE/1005')
    INSERT INTO #Table2 values ('PRE/10005')
    INSERT INTO #Table2 values ('PRE/10006')
    INSERT INTO #Table2 values ('SRT/2003')
    INSERT INTO #Table2 values ('PRE/10007')
    INSERT INTO #Table2 values ('PRE/10008')

    select * from #Table2 where VouNo = 'PRE/1004' and VouNo ='PRE/10006'

    DROP TABLE #Table2
    Pls help

    DB:4.05:Sql Select Between In Varchar Column 7s


    Select

    *From#Table2

    Where

    VouNolike'PRE%'AndConvert(Int,(Substring(VouNo,(CharIndex('/',VouNo))1,Len(VouNo))))Between1004
    and10006
    Please have look on the comment

  • RELEVANCY SCORE 4.05

    DB:4.05:Sql: How To Get The Maximum Value Of Loan Schedule 9f


    I have to display cod_acct_no, amt_disbursed, bal_book from LOAN_MAST table and amt_instal of maximum version of CTR_STAGE_NO from LOAN_SCHEDULE table. that is; amt_instal of highest ctr_stage_no of LOAN_SCHEDULE table to be displayed with the other column value of LOAN_MAST.

    Would you pls. help me.

    Data of those two table is given below:

    Loan master table

    INSERT INTO loan_mast
    VALUES
    ('00057110000431 ',2000000,1010605.39)
    /
    INSERT INTO loan_mast
    VALUES
    ('00057110000291 ',3500000,1212038.57)
    /
    INSERT INTO loan_mast
    VALUES
    ('00057110000450 ',2000000,1179199.79)
    /
    INSERT INTO loan_mast
    VALUES
    ('00057010001457 ',2000000,1555836.32)
    /
    INSERT INTO loan_mast
    VALUES
    ('00057010001650 ',9500000,8060419.1)
    /
    INSERT INTO loan_mast
    VALUES
    ('00057110050065 ',2000000,1742388.45)
    /
    INSERT INTO loan_mast
    VALUES
    ('00057010001947 ',2000000,1933540.07)
    /
    INSERT INTO loan_mast
    VALUES
    ('00057110050213 ',2000000,1978588.06)
    /
    INSERT INTO loan_mast
    VALUES
    ('00037010001104 ',6175000,3241694.48)
    /
    INSERT INTO loan_mast
    VALUES
    ('00037010001766 ',12000000,11715781.01)
    /
    INSERT INTO loan_mast
    VALUES
    ('00037300050025 ',7500000,7472981.78)
    /
    INSERT INTO loan_mast
    VALUES
    ('00037210000694 ',6500000,6588648.16)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017210000087 ',5000000,4105246.38)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017210000129 ',5000000,3478303.49)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017210000141 ',5000000,3685293.79)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017210000246 ',5000000,4029883.61)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017210000268 ',5000000,4499322.1)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017210000322 ',5000000,4966885.32)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017300050014 ',7000000,6625970.85)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017300050036 ',5700000,5687617.61)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017300050047 ',7500000,7527560)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017240000050 ',4300000,4300000)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017210000449 ',7000000,7000000)
    /
    INSERT INTO loan_mast
    VALUES
    ('03017180001733 ',500000,335164.83)
    /
    INSERT INTO loan_mast
    VALUES
    ('01117110050071 ',700000,625123.9)
    /
    INSERT INTO loan_mast
    VALUES
    ('01077300050011 ',1500000,1447149.08)
    /
    INSERT INTO loan_mast
    VALUES
    ('00927180002119 ',500000,441488.25)
    /
    INSERT INTO loan_mast
    VALUES
    ('01507150051043 ',700000,633340.47)
    /
    INSERT INTO loan_mast
    VALUES
    ('03317290050040 ',300000,273601.45)
    /
    INSERT INTO loan_mast
    VALUES
    ('01027150051360 ',200000,179240.42)
    /
    INSERT INTO loan_mast
    VALUES
    ('01067150052782 ',300000,270794.1)
    /
    INSERT INTO loan_mast
    VALUES
    ('01137110050082 ',2000000,1786359.87)
    /
    INSERT INTO loan_mast
    VALUES
    ('01107150050610 ',200000,191399.56)
    /
    INSERT INTO loan_mast
    VALUES
    ('01117150051071 ',300000,271560.17)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017210001052 ',6800000,6609837.58)
    /
    INSERT INTO loan_mast
    VALUES
    ('01157010001078 ',450000,425069.61)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017180001173 ',700000,484447.94)
    /
    INSERT INTO loan_mast
    VALUES
    ('09997120004696 ',93099,64369.52)
    /
    INSERT INTO loan_mast
    VALUES
    ('01067010001654 ',285000,255579.7)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017210001063 ',7000000,6930746)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017210001105 ',7000000,6934246)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017210001138 ',7000000,7000000)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017210001116 ',7000000,7000000)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017210001161 ',7000000,7000000)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017210001150 ',7000000,7000000)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017210001127 ',7000000,6930746)
    /
    INSERT INTO loan_mast
    VALUES
    ('00067110050043 ',700000,602416.77)
    /
    INSERT INTO loan_mast
    VALUES
    ('01147150052362 ',200000,178682.78)
    /
    INSERT INTO loan_mast
    VALUES
    ('01167150051647 ',75000,60057.01)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017150053252 ',150000,125205.66)
    /
    INSERT INTO loan_mast
    VALUES
    ('00917150050232 ',100000,91972.04)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017180003081 ',990000,663015.26)
    /
    INSERT INTO loan_mast
    VALUES
    ('09997220000893 ',154160,138665.08)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017210000955 ',5000000,4510523.32)
    /
    INSERT INTO loan_mast
    VALUES
    ('00087150001049 ',1000000,904601.41)
    /
    INSERT INTO loan_mast
    VALUES
    ('01107010000288 ',300000,268703.55)
    /
    INSERT INTO loan_mast
    VALUES
    ('00087150001038 ',1000000,906085.9)
    /
    INSERT INTO loan_mast
    VALUES
    ('01107010000277 ',200000,184905.75)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017080050030 ',500000,450047.42)
    /
    INSERT INTO loan_mast
    VALUES
    ('00037210000533 ',4000000,3346908.17)
    /
    INSERT INTO loan_mast
    VALUES
    ('01117180000631 ',990000,712874.67)
    /
    INSERT INTO loan_mast
    VALUES
    ('02007180001216 ',990000,792437.11)
    /
    INSERT INTO loan_mast
    VALUES
    ('01957180000928 ',400000,320317.9)
    /
    INSERT INTO loan_mast
    VALUES
    ('00037210000544 ',600000,518404.4)
    /
    INSERT INTO loan_mast
    VALUES
    ('01117110050060 ',1000000,893034.07)
    /
    INSERT INTO loan_mast
    VALUES
    ('00037180001877 ',800000,640405)
    /
    INSERT INTO loan_mast
    VALUES
    ('01047150052651 ',320000,288850.72)
    /
    INSERT INTO loan_mast
    VALUES
    ('01027160050103 ',100000,79915.86)
    /
    INSERT INTO loan_mast
    VALUES
    ('01067150052759 ',150000,129601.15)
    /
    INSERT INTO loan_mast
    VALUES
    ('01057150051287 ',800000,723681.12)
    /
    INSERT INTO loan_mast
    VALUES
    ('03317290050021 ',400000,359312.25)
    /
    INSERT INTO loan_mast
    VALUES
    ('01157150051316 ',500000,452300.73)
    /
    INSERT INTO loan_mast
    VALUES
    ('00037150050598 ',700000,633221.03)
    /
    INSERT INTO loan_mast
    VALUES
    ('01047150052673 ',150000,129610.95)
    /
    INSERT INTO loan_mast
    VALUES
    ('01147150052384 ',400000,361840.54)
    /
    INSERT INTO loan_mast
    VALUES
    ('01067150052802 ',150000,130840.94)
    /
    INSERT INTO loan_mast
    VALUES
    ('01157150051338 ',300000,259202.22)
    /
    INSERT INTO loan_mast
    VALUES
    ('01057150051307 ',400000,361840.54)
    /
    INSERT INTO loan_mast
    VALUES
    ('00017110050160 ',500000,397640.38)
    /
    INSERT INTO loan_mast
    VALUES
    ('01077150050910 ',100000,79915.86)
    /
    INSERT INTO loan_mast
    VALUES
    ('00057150050702 ',400000,360841.83)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017150053271 ',150000,119955.86)
    /
    INSERT INTO loan_mast
    VALUES
    ('01057150051329 ',400000,360029.85)
    /
    INSERT INTO loan_mast
    VALUES
    ('01067150002665 ',500000,452300.73)
    /
    INSERT INTO loan_mast
    VALUES
    ('01077150050921 ',250000,225526.17)
    /
    INSERT INTO loan_mast
    VALUES
    ('00047150050693 ',200000,160494.64)
    /
    INSERT INTO loan_mast
    VALUES
    ('01077080050026 ',100000,91150.55)
    /
    INSERT INTO loan_mast
    VALUES
    ('01017150053293 ',150000,129675.2)
    /
    INSERT INTO loan_mast
    VALUES
    ('09997040001950 ',2500000,2439161.01)
    /
    INSERT INTO loan_mast
    VALUES
    ('09997120004685 ',75000,51855.74)
    /Loan Schedule Table:
    INSERT INTO loan_schedule
    VALUES
    ('00017080050030 ',1,12426.29)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00017110050160 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00017110050160 ',2,17702.2)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00017180001173 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00017180001173 ',2,35116.22)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00037150050598 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00037150050598 ',2,18158.39)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00037180001877 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00037180001877 ',2,29122.98)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00037210000533 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00037210000533 ',2,144609.59)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00037210000544 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00037210000544 ',2,17625)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00047150050693 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00047150050693 ',2,7331.21)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00057150050702 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00057150050702 ',2,10157.38)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00067110050043 ',1,20017.91)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00087150001038 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00087150001038 ',2,25940.56)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00087150001049 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00087150001049 ',2,25940.56)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00917150050232 ',1,2937.5)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00927180002119 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('00927180002119 ',2,25083.02)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01017150053252 ',1,5422.86)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01017150053271 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01017150053271 ',2,5422.86)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01017150053293 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01017150053293 ',2,4406.25)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01017180003081 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01017180003081 ',2,49664.37)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01017210000955 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01017210000955 ',2,126967.14)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01027150051360 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01027150051360 ',2,5980.03)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01027160050103 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01027160050103 ',2,3615.24)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01047150052651 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01047150052651 ',2,8125.9)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01047150052673 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01047150052673 ',2,4406.25)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01057150051287 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01057150051287 ',2,20752.45)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01057150051307 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01057150051307 ',2,10376.23)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01057150051329 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01057150051329 ',2,11960.05)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067010001654 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067010001654 ',2,6855.16)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067150002665 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067150002665 ',2,12970.28)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067150052759 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067150052759 ',2,4406.25)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067150052782 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067150052782 ',2,7618.03)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067150052802 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01067150052802 ',2,5422.86)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01077080050026 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01077080050026 ',2,2485.26)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01077150050910 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01077150050910 ',2,3615.24)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01077150050921 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01077150050921 ',2,6348.36)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01077300050011 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01077300050011 ',2,23289.97)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01107010000277 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01107010000277 ',2,4550.62)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01107010000288 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01107010000288 ',2,7215.96)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01107150050610 ',1,5188.12)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01117110050060 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01117110050060 ',2,23528.29)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01117110050071 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01117110050071 ',2,16469.8)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01117150051071 ',1,7782.17)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01117180000631 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01117180000631 ',2,49664.37)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01137110050082 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01137110050082 ',2,47056.57)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01147150052362 ',1,5875)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01147150052384 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01147150052384 ',2,10376.23)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01157010001078 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01157010001078 ',2,10764.62)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01157150051316 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01157150051316 ',2,12970.28)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01157150051338 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01157150051338 ',2,8812.5)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01167150051647 ',1,2711.43)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01507150051043 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01507150051043 ',2,18158.39)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01957180000928 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('01957180000928 ',2,14561.49)
    /
    INSERT INTO loan_schedule
    VALUES
    ('02007180001216 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('02007180001216 ',2,36039.68)
    /
    INSERT INTO loan_schedule
    VALUES
    ('03017180001733 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('03017180001733 ',2,25083.02)
    /
    INSERT INTO loan_schedule
    VALUES
    ('03317290050021 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('03317290050021 ',2,9833.81)
    /
    INSERT INTO loan_schedule
    VALUES
    ('03317290050040 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('03317290050040 ',2,7375.36)
    /
    INSERT INTO loan_schedule
    VALUES
    ('09997040001950 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('09997040001950 ',2,13864.94)
    /
    INSERT INTO loan_schedule
    VALUES
    ('09997120004685 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('09997120004685 ',2,3495.59)
    /
    INSERT INTO loan_schedule
    VALUES
    ('09997120004696 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('09997120004696 ',2,4339.15)
    /
    INSERT INTO loan_schedule
    VALUES
    ('09997220000893 ',1,0)
    /
    INSERT INTO loan_schedule
    VALUES
    ('09997220000893 ',2,3429.21)
    /Regards,

    Shariful

    DB:4.05:Sql: How To Get The Maximum Value Of Loan Schedule 9f

    Hi,

    "ORA-03113: end-of-file on communication channel" after you're connected to Oracle usually indicates a bug or an installation error, sometimes caused by doing something that's not allowed in your version of Oracle. Contact Oracle support. They'll want to see a trace file, if one was generated.
    What version are you using?

    Here's another way to get the same results. I think it's better than the scalar sub-query I posted earlier, anyway.
    WITHgot_rnum AS
    (
    SELECTcod_acct_no
    ,amt_instal
    ,ROW_NUMBER () OVER ( PARTITION BY cod_acct_no
    ORDER BY ctr_stage_no DESC
    ) AS rnum
    FROM loan_schedule
    )
    SELECTm.cod_acct_no, m.amt_disbursed, m.bal_book
    ,r.amt_instal AS high_amt_instal
    FROMloan_mastm
    JOINgot_rnumrONr.cod_acct_no= m.cod_acct_no
    ANDr.rnum= 1
    ;As written, this requires Oracle 9, but will run on 8.1 if you turn the WITH clause into an in-line view and use the old join notation.

  • RELEVANCY SCORE 4.04

    DB:4.04:Cursor For Update fp


    i would like to write query with cursor which will update my stock table-exactly qty column.
    i have another table which is orderlines where i keep all orders from customers.
    in this case i have apply arithmetic sentences: qty from stock table - qty fromorderlines table
    can someone help me with that.
    please check it out.
    create table orderlines
    (
    orderno number(4),
    stockno number(3),
    qty number(2),
    price number(2),
    total number(7)
    );
    INSERT INTO ORDERLINES VALUES (1201,852,1,0,0);
    INSERT INTO ORDERLINES VALUES (1201,496,2,0,0);
    INSERT INTO ORDERLINES VALUES (1202,991,1,0,0);
    INSERT INTO ORDERLINES VALUES (1203,991,1,0,0);
    INSERT INTO ORDERLINES VALUES (1203,998,1,0,0);
    INSERT INTO ORDERLINES VALUES (1204,991,1,0,0);
    INSERT INTO ORDERLINES VALUES (1204,992,2,0,0);
    INSERT INTO ORDERLINES VALUES (1204,998,1,0,0);
    INSERT INTO ORDERLINES VALUES (1205,496,2,0,0);
    INSERT INTO ORDERLINES VALUES (1205,852,1,0,0);
    INSERT INTO ORDERLINES VALUES (1206,998,3,0,0);
    INSERT INTO ORDERLINES VALUES (1206,991,2,0,0);
    INSERT INTO ORDERLINES VALUES (1206,992,1,0,0);

    CREATE TABLE stock
    (
    stockno NUMBER(3),
    qty NUMBER(2),
    price NUMBER(2)
    );

    INSERT INTO STOCK VALUES( 852,5,55 );
    INSERT INTO STOCK VALUES( 496,10,60 );
    INSERT INTO STOCK VALUES( 991,20,75 );
    INSERT INTO STOCK VALUES( 992,25,80 );
    INSERT INTO STOCK VALUES( 998,30,95 );

    select * from stock;
    DECLARE cursor CURstock IS
    SELECT * FROM stock
    FOR UPDATE;

    VQTY ORDERLINES.QTY%TYPE;

    vqtys stock.qty%type;

    BEGIN

    FOR RECstock IN CURstock LOOP

    SELECT qty
    INTO vqtys
    FROM stock
    WHERE STOCKNO=RECstock.STOCKNO;

    vqtys := vqtys - vqty;

    UPDATE stock
    SET qty=vqtys
    WHERE CURRENT OF CURstock;
    END LOOP;
    END;
    /
    rollback;
    SELECT * FROM ORDERLINES;
    select * from stock;

    DB:4.04:Cursor For Update fp

    Then go back to your original post and add this information, i'll not help you in this post.....

  • RELEVANCY SCORE 4.04

    DB:4.04:Restituire Il Valore Di Una Colonna Corrispondente Al Valore Minimo Di Unaltra Colonna kx


    Salve, qual è il metodomigliore/ottimale per restituire il valore di una colonna corrispondente al valore minimo di un'altra?
    Io attualmente utilizzo delle select nidificate.
    Faccio un esempio:
    DECLARE @Table as table
    ( Codice varchar(50),
    Colore varchar(50),
    Importo int)


    INSERT INTO @Table values ('a1', 'Giallo', 10)
    INSERT INTO @Table values ('a2', 'Verde', 5)
    INSERT INTO @Table values ('a3', 'Rosso', 8)
    INSERT INTO @Table values ('a4', 'Rosso', 7)
    INSERT INTO @Table values ('a5', 'Rosso', 87)
    INSERT INTO @Table values ('a6', 'Verde', 100)
    INSERT INTO @Table values ('a7', 'Giallo', 1)

    Select * from @Table

    Select Codice
    from @Table
    where Importo=
    (Select MIN(Importo) as Importo
    from @Table
    where Colore='Rosso')

    DB:4.04:Restituire Il Valore Di Una Colonna Corrispondente Al Valore Minimo Di Unaltra Colonna kx

    Ciao.
    Questa domanda mi sembra interessante e quindi vorrei dare il mio contributo anche se è un po' datata.
    Potrà sembrare più complessa come soluzione, però io farei così:
    DECLARE @Table as table
    ( Codice varchar(50),
    Colore varchar(50),
    Importo int)


    INSERT INTO @Table values ('a1', 'Giallo', 10)
    INSERT INTO @Table values ('a2', 'Verde', 5)
    INSERT INTO @Table values ('a3', 'Rosso', 8)
    INSERT INTO @Table values ('a4', 'Rosso', 7)
    INSERT INTO @Table values ('a5', 'Rosso', 87)
    INSERT INTO @Table values ('a6', 'Verde', 100)
    INSERT INTO @Table values ('a7', 'Giallo', 1)

    select * from @table

    SELECT
    TABLE0.*
    FROM
    @Table AS TABLE0
    INNER JOIN
    (
    SELECT
    Colore
    , MIN(Importo) as MIN_Importo
    FROM
    @Table
    GROUP BY
    Colore
    ) AS TABLE1
    ON
    TABLE1.Colore = TABLE0.Colore
    AND TABLE1.MIN_Importo = TABLE0.Importo
    ORDER BY
    TABLE0.COLORE
    , TABLE0.CODICE

  • RELEVANCY SCORE 4.03

    DB:4.03:How Does Minus Work? d9


    Hi,

    I want to do a count(*) table 1 minus count(*) table 2 in a test case. How does minus work in functions?
    create table count1( col1 number);
    create table count2( col2 number);

    truncate table count1;
    truncate table count2;
    insert into count1 values(1);
    insert into count1 values(2);
    insert into count1 values(3);
    insert into count1 values(4);
    insert into count1 values(5);
    commit;
    insert into count2 values(1);
    insert into count2 values(2);
    insert into count2 values(3);
    insert into count2 values(5);
    commit;SQL select * from count1
    2 minus
    3 select * from count2;

    COL1
    ----------
    4

    SQL
    SQL select count(*) from count1
    2 minus
    3 select count(*) from count2;

    COUNT(*)
    ----------
    5

    SQL

    DB:4.03:How Does Minus Work? d9

    Minus works like this. Consider this SQL

    query1 MINUS query2Now what MINUS does is removes records from query1 which occur in query2 and displays the remaining records of query1.

    So in your case

    select count(*) from count1 -- Result is 5
    minus
    select count(*) from count2 -- Result is 4This returns 5 as the count of table count1 is 5.

  • RELEVANCY SCORE 4.03

    DB:4.03:To Find Only Integer Values Using Dynamic Sql p8


    Hi Experts,
    CREATE TABLE #TEMP
    (
    ID INT,
    CH VARCHAR(10)
    )

    INSERT INTO #TEMP VALUES (1, 'ab')
    INSERT INTO #TEMP VALUES (2, '12')
    INSERT INTO #TEMP VALUES (3, '45')
    INSERT INTO #TEMP VALUES (4, 'bc')
    INSERT INTO #TEMP VALUES (5, 'b4')
    INSERT INTO #TEMP VALUES (6, '6b')

    SELECT *
    FROM #TEMP

    DROP TABLE #TEMP
    Expected Output:-
    id ch
    2 12
    3 45

    Any help
    Thanks
    Regards,
    Kumar

  • RELEVANCY SCORE 4.03

    DB:4.03:Pivot Com Datas dx


    Boa tarde,
    alguém sabe se é possivel fazer um pivot automatico, onde a partir do select ele retornaria as colunas sem declaração.
    A ideia é colocar em linhas o Produto e em colunas as Datas.
    Lembrando que as datas podem variar conforme o select, apenas coloquei uma amostragem abaixo

    DECLARE @Teste TABLE (produto VARCHAR(50), data varchar(8), Qtd int);

    INSERT INTO @Teste VALUES ('Prod 1','20130520', 864);
    INSERT INTO @Teste VALUES ('Prod 1','20130521', 94);
    INSERT INTO @Teste VALUES ('Prod 1','20130522', 282);
    INSERT INTO @Teste VALUES ('Prod 1','20130523', 903);
    INSERT INTO @Teste VALUES ('Prod 1','20130524', 644);
    INSERT INTO @Teste VALUES ('Prod 1','20130525', 86);
    INSERT INTO @Teste VALUES ('Prod 1','20130526', 829);
    INSERT INTO @Teste VALUES ('Prod 1','20130527', 662);
    INSERT INTO @Teste VALUES ('Prod 1','20130528', 975);
    INSERT INTO @Teste VALUES ('Prod 1','20130529', 37);
    INSERT INTO @Teste VALUES ('Prod 1','20130530', 563);
    INSERT INTO @Teste VALUES ('Prod 1','20130531', 164);
    INSERT INTO @Teste VALUES ('Prod 1','20130532', 223);
    INSERT INTO @Teste VALUES ('Prod 1','20130601', 598);
    INSERT INTO @Teste VALUES ('Prod 1','20130602', 177);
    INSERT INTO @Teste VALUES ('Prod 1','20130603', 731);
    INSERT INTO @Teste VALUES ('Prod 1','20130604', 229);
    INSERT INTO @Teste VALUES ('Prod 1','20130605', 818);
    INSERT INTO @Teste VALUES ('Prod 1','20130606', 242);
    INSERT INTO @Teste VALUES ('Prod 1','20130607', 438);
    INSERT INTO @Teste VALUES ('Prod 1','20130608', 641);
    INSERT INTO @Teste VALUES ('Prod 1','20130609', 908);
    INSERT INTO @Teste VALUES ('Prod 1','20130610', 742);
    INSERT INTO @Teste VALUES ('Prod 1','20130611', 133);
    INSERT INTO @Teste VALUES ('Prod 1','20130612', 553);
    INSERT INTO @Teste VALUES ('Prod 1','20130613', 974);
    INSERT INTO @Teste VALUES ('Prod 1','20130614', 612);

    SELECT * FROM @Teste

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    Érica Tohoma | http://bloggirlsa.wordpress.com

    DB:4.03:Pivot Com Datas dx

    Além da solução acima, postada pelo Alexandre, acrescento outra abordagem para o mesmo problema.
    A rotina abaixo primeiro coleta todos os valores de data, independente de produto, descartando as repetições de data (caso existam). Após, monta a consulta SQL usando pivot. A consulta montada soma os valores de quantidade, para o caso de existir mais de
    uma linha para o mesmo par produto/data.
    -- coleta datasdeclare @ColunasData varchar(2000);
    set @ColunasData='';
    select @ColunasData = '['data'],'
    from (select distinct top (100) percent data from Teste order by data) as X;
    set @ColunasData = left(@ColunasData, datalength(@ColunasData)-1);

    -- monta consulta SQLdeclare @ComandoSQL varchar(5000);
    set @ComandoSQL = 'select produto,' @ColunasData ' from Teste as T pivot (sum(Qtd) '
    'for data in (' @ColunasData ')) as P';
    print @ComandoSQL;
    -- execute (@ComandoSQL);

    Belo Horizonte, MG - Brasil

  • RELEVANCY SCORE 4.03

    DB:4.03:Sustracin Entre El Mximo Y El Mnimo Cada Vez Que El Valor Decrezca x8



    Saludos comunidad. Algo medio raro de explicar, pero para que me entiendan mejor:
    DECLARE @Tabla AS TABLE
    (ID INT IDENTITY,
    Valor INT)

    INSERT INTO @Tabla (Valor) VALUES (90)
    INSERT INTO @Tabla (Valor) VALUES (80)
    INSERT INTO @Tabla (Valor) VALUES (70)
    INSERT INTO @Tabla (Valor) VALUES (60)
    INSERT INTO @Tabla (Valor) VALUES (100)
    INSERT INTO @Tabla (Valor) VALUES (90)
    INSERT INTO @Tabla (Valor) VALUES (80)
    INSERT INTO @Tabla (Valor) VALUES (70)
    INSERT INTO @Tabla (Valor) VALUES (60)
    INSERT INTO @Tabla (Valor) VALUES (90)
    INSERT INTO @Tabla (Valor) VALUES (80)
    INSERT INTO @Tabla (Valor) VALUES (70)

    SELECT * FROM @Tabla
    El resulta sería:
    (90 - 60) (100 - 60) (90 - 70) = 90

    Es para determinar el consumo de una batería, pero la batería se descarga y se llena entre los usos. Y debo saber cuánto consumió entre usos donde pudo haberse cargado varias veces.

    DB:4.03:Sustracin Entre El Mximo Y El Mnimo Cada Vez Que El Valor Decrezca x8

    Hola
    Espero que las soluciones que te han proporcionado sean capaces de resolver tu consulta, de lo contrario te pido de favor desmarques la respuesta y nos lo hagas saber, esto se hace para tener un mayor control sobre los foros.
    SaludosEL CONTENIDO SE PROVEE COMO ES SIN GARANTA DE NINGN TIPO, AS SEA EXPRESA O IMPLCITA

  • RELEVANCY SCORE 4.03

    DB:4.03:Average Issue. Splitting In A Row Average For City, State, Country mc


    Hy guys. Having this schema:
    create table sls (citycode int, city nvarchar(20), state nvarchar (20), country nvarchar(20))
    insert into sls values (1,'Paris','Seine','France')
    insert into sls values (2,'Munich','Bayern','Germany')
    insert into sls values (3,'Washington','DC','Usa')
    insert into sls values (4,'Austin','Texas','Usa')
    insert into sls values (5,'Los Angeles','California','Usa')
    insert into sls values (6,'Baltimora','California','Usa')
    insert into sls values (7,'Houston','Texas','Usa')
    insert into sls values (8,'Rio de Janeiro','Oraio','Brazil')
    insert into sls values (9,'Fortaleza','Amazzonia','Brazil')
    insert into sls values (10,'Seattle','Oregon','Usa')
    insert into sls values (11,'Portland','Oregon','Usa')
    insert into sls values (12,'Tokyo','Hakimura','Japan')
    insert into sls values (13,'Nagasaki','Hakimura','Japan')
    insert into sls values (14,'Hokinawa','Hokin','Japan')
    insert into sls values (15,'Miami','Florida','Usa')

    create table slsshop (idshop int, idcity int)
    insert into slsshop values (1,2)
    insert into slsshop values (2,1)
    insert into slsshop values (3,2)
    insert into slsshop values (4,11)
    insert into slsshop values (5,11)
    insert into slsshop values (6,3)
    insert into slsshop values (7,4)
    insert into slsshop values (8,5)
    insert into slsshop values (9,6)
    insert into slsshop values (10,7)
    insert into slsshop values (11,8)
    insert into slsshop values (12,9)
    insert into slsshop values (13,10)
    insert into slsshop values (14,11)
    insert into slsshop values (15,13)
    insert into slsshop values (16,14)
    insert into slsshop values (17,15)
    insert into slsshop values (18,15)
    insert into slsshop values (19,14)
    insert into slsshop values (20,10)
    insert into slsshop values (21,8)
    insert into slsshop values (22,6)
    insert into slsshop values (23,10)
    insert into slsshop values (24,9)
    insert into slsshop values (25,6)
    insert into slsshop values (26,5)
    insert into slsshop values (27,6)
    insert into slsshop values (28,7)
    insert into slsshop values (29,9)
    insert into slsshop values (30,7)
    insert into slsshop values (31,8)
    insert into slsshop values (32,11)
    insert into slsshop values (33,13)
    insert into slsshop values (34,12)

    create table sales (idsoldshop int, sold int )
    insert into sales values (1,30)
    insert into sales values (2,44)
    insert into sales values (3,102)
    insert into sales values (4,45)
    insert into sales values (6,540)
    insert into sales values (7,88)
    insert into sales values (8,45)
    insert into sales values (9,211)
    insert into sales values (10,332)
    insert into sales values (11,21)
    insert into sales values (12,43)
    insert into sales values (13,65)
    insert into sales values (14,71)
    insert into sales values (15,44)
    insert into sales values (16,88)
    insert into sales values (17,21)
    insert into sales values (18,90)
    insert into sales values (19,31)
    insert into sales values (20,44)
    insert into sales values (21,41)
    insert into sales values (22,55)
    insert into sales values (23,79)
    insert into sales values (24,51)
    insert into sales values (25,55)
    insert into sales values (26,71)
    insert into sales values (27,61)
    insert into sales values (28,21)
    insert into sales values (29,31)
    insert into sales values (30,341)

    select * from sales inner join slsshop on idsoldshop=idshop inner join sls on citycode=idcity order by idsoldshop

    I retrieve:
    1 30
    1 2
    2 Munich
    Bayern Germany
    2 44
    2 1
    1 Paris
    Seine France
    3 102
    3 2
    2 Munich
    Bayern Germany
    4 45
    4 11
    11 Portland
    Oregon Usa
    6 540
    6 3
    3 Washington
    DC Usa
    7 88
    7 4
    4 Austin
    Texas Usa
    8 45
    8 5
    5 Los Angeles
    California
    Usa
    9 211
    9 6
    6 Baltimora
    California
    Usa
    10 332
    10 7
    7 Houston
    Texas Usa
    11 21
    11 8
    8 Rio de Janeiro
    Oraio Brazil
    12 43
    12 9
    9 Fortaleza
    Amazzonia
    Brazil
    13 65
    13 10
    10 Seattle
    Oregon Usa
    14 71
    14 11
    11 Portland
    Oregon Usa
    15 44
    15 13
    13 Nagasaki
    Hakimura Japan
    16 88
    16 14
    14 Hokinawa
    Hokin Japan
    17 21
    17 15
    15 Miami
    Florida Usa
    18 90
    18 15
    15 Miami
    Florida Usa
    19 31
    19 14
    14 Hokinawa
    Hokin Japan
    20 44
    20 10
    10 Seattle
    Oregon Usa
    21 41
    21 8
    8 Rio de Janeiro
    Oraio Brazil
    22 55
    22 6
    6 Baltimora
    California
    Usa
    23 79
    23 10
    10 Seattle
    Oregon Usa
    24 51
    24 9
    9 Fortaleza
    Amazzonia
    Brazil
    25 55
    25 6
    6 Baltimora
    California
    Usa
    26 71
    26 5
    5 Los Angeles
    California
    Usa
    27 61
    27 6
    6 Baltimora
    California
    Usa
    28 21
    28 7
    7 Houston
    Texas Usa
    29 31
    29 9
    9 Fortaleza
    Amazzonia
    Brazil
    30 341
    30 7
    7 Houston
    Texas Usa

    Now, I'm wondering how can I have this result:
    shop TotalSales AvgCity AvgState AvgCountry
    Where AvgCity should be TotalSales/NumberofShop
    AvgState the same but splitted for State and AvgCountry the same but splitted for Country.
    For example for the shop 16:
    select * from sales inner join slsshop on idsoldshop=idshop inner join sls on citycode=idcity where idshop=16 order by idsoldshop

    It should be:
    idshop sales avgcity avgstate avgcountry
    16 88 59,5 59,5 54,33

    Any suggestion?

    DB:4.03:Average Issue. Splitting In A Row Average For City, State, Country mc

    select A.idsoldshop,
    SUM(A.SOLD) OVER(PARTITION BY C.CITY ) AS TOTALSALES
    ,AVG(A.SOLD) OVER(PARTITION BY C.CITY ) AS AVGCITY
    ,AVG(A.SOLD) OVER(PARTITION BY C.STATE ) AS AVGSTATE
    ,AVG(A.SOLD) OVER(PARTITION BY C.COUNTRY ) AS AVGCOUNTRY

    from sales A
    inner join slsshop
    on idsoldshop=idshop
    inner join sls C
    on citycode=idcity order by idsoldshop

  • RELEVANCY SCORE 4.03

    DB:4.03:Error In Bulk Collect Into Clause jp


    I have problem when I am using BULK Collect Clause as shown below. Pls. help me

    --Table
    CREATE TABLE test (t_no NUMBER(5) PRIMARY KEY, t_value NUMBER(5));

    --Inserting values into table
    INSERT INTO test VALUES (1,500);
    INSERT INTO test VALUES (2,501);
    INSERT INTO test VALUES (3,502);
    INSERT INTO test VALUES (4,503);
    INSERT INTO test VALUES (5,504);
    INSERT INTO test VALUES (6,505);
    INSERT INTO test VALUES (7,506);
    INSERT INTO test VALUES (8,507);
    INSERT INTO test VALUES (9,508);
    INSERT INTO test VALUES (10,509);

    When I run the foll. plsql code I get the error

    DECLARE
    l_val test.t_value%TYPE;
    BEGIN
    SELECT t_value
    FROM test
    BULK COLLECT INTO l_val;
    END;
    \

    -- Error
    ORA-06550: line 6, column 7:
    PL/SQL: ORA-00933: SQL command not properly ended
    ORA-06550: line 4, column 2:
    PL/SQL: SQL Statement ignored

    I Am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0. What does this means

    DB:4.03:Error In Bulk Collect Into Clause jp

    this is much better

    1 u need a collection array(in c) to hold the value as there are multiple value coming out of it here v_a is collection.

    set serveroutput on;
    DECLARE
    type l_val is table of test.t_value%TYPE;
    v_a l_val;
    BEGIN
    SELECT t_value
    BULK COLLECT INTO v_a
    FROM test;
    for i in 1..v_a.count loop
    dbms_output.put_line(v_a(i));
    end loop;
    END;
    /

  • RELEVANCY SCORE 4.03

    DB:4.03:Does Ssis Lookup Work Like Inner Join x8


    Hi
    I run this in SQL we get 2 values (rows) for t=2 returned in the recordset:

    create table t1 (t int)

    insert into t1 (t) values (1)
    insert into t1 (t) values (2)
    insert into t1 (t) values (3)

    create table t2 (t int)

    insert into t2 (t) values (1)
    insert into t2 (t) values (2)
    insert into t2 (t) values (2)
    insert into t2 (t) values (3)

    select * from t2 inner join t1 on t1.t = t2.t

    Question: would using the lookup component in SSIS provide the same result ?

    Thanks

    BiF

    DB:4.03:Does Ssis Lookup Work Like Inner Join x8


    Hi
    I run this in SQL we get 2 values (rows) for t=2 returned in the recordset:

    create table t1 (t int)

    insert into t1 (t) values (1)
    insert into t1 (t) values (2)
    insert into t1 (t) values (3)

    create table t2 (t int)

    insert into t2 (t) values (1)
    insert into t2 (t) values (2)
    insert into t2 (t) values (2)
    insert into t2 (t) values (3)

    select * from t2 inner join t1 on t1.t = t2.t

    Question: would using the lookup component in SSIS provide the same result ?

    Thanks

    BiF

    No. An INNER JOIN will join everything from the left that matches everythign on the right i.e. It can return many matches. The Lookup component will only return one match - the first one it findshttp://sqlblog.com/blogs/jamie_thomson/ |
    @jamiet |
    About me

  • RELEVANCY SCORE 4.03

    DB:4.03:Query Required 81


    i have two tables i.e. table1 and table2.

    CREATE TABLE Table1
    (
    SET_NAME VARCHAR2(30),
    STATUS_ID VARCHAR2(1) NOT NULL ENABLE,
    SET_ID VARCHAR2(20) primary key,
    )

    CREATE TABLE Table2
    (
    SET_NAME VARCHAR2(30),
    STATUS_ID VARCHAR2(1) NOT NULL ENABLE,
    SET_ID VARCHAR2(20),
    )

    TABLE1

    insert into table1 values('S010054','R','ST9749109')
    insert into table1 values('S010179','N','ST9749147')
    insert into table1 values('E010011','R','ST9749228')
    insert into table1 values('S010054','Y','ST9749109')

    table2

    insert into table2 values('S010054','N','ST9749109')
    insert into table2 values('S010054','N','ST9749109')
    insert into table2 values('S010054','N','ST9749109')
    insert into table2 values('S010054','N','ST9749109')
    insert into table2 values('S010179','Y','ST9749147')
    insert into table2 values('S010179','R','ST9749147')
    insert into table2 values('S010179','N','ST9749147')
    insert into table2 values('S010179','Y','ST9749147')
    insert into table2 values('E010011','Y','ST9749228')
    insert into table2 values('E010011','N','ST9749228')
    insert into table2 values('E010011','Y','ST9749228')
    insert into table2 values('E010011','N','ST9749228')

    i want the qry to chk the first table with status_id is 'R' (SET_ID:'ST9749109') and 2nd table status_id should be 'N' (SET_ID:'ST9749109') for all rows. if atleast one row in second table have status_id other then 'N' for single set then i dont want whole set in output .
    My output from the above data :
    ----------------------
    S010054,N,ST9749109
    S010054,N,ST9749109
    S010054,N,ST9749109
    S010054,N,ST9749109

    DB:4.03:Query Required 81

    Like this

    select set_name, set_id
    from (
    select t1.set_name, t1.set_id, sum(decode(t2.status_id,'N',0,1)) over(partition by t1.set_id) cnt
    from table1 t1
    join table2 t2
    on t1.set_name = t2.set_name
    where t1.status_id = 'R'
    )
    where cnt = 0

  • RELEVANCY SCORE 4.02

    DB:4.02:Search % String Query? kf


    Hi All,

    CREATE TABLE #TEMP
    (
    STRING VARCHAR(10)
    )

    INSERT INTO #TEMP VALUES ('ABC')
    INSERT INTO #TEMP VALUES ('ABC %')
    INSERT INTO #TEMP VALUES ('ABC')
    INSERT INTO #TEMP VALUES ('%AB%C')

    SELECT *
    FROM
    #TEMP
    WHERE
    STRING LIKE '%%%'

    DROP TABLE #TEMP

    DB:4.02:Search % String Query? kf


    CREATE TABLE #TEMP
    (
    STRING VARCHAR(10)
    )

    INSERT INTO #TEMP VALUES (ABC)
    INSERT INTO #TEMP VALUES (ABC %)
    INSERT INTO #TEMP VALUES (ABC)
    INSERT INTO #TEMP VALUES (%AB%C)

    ;WITH CTE AS (
    SELECT DISTINCT STRING FROM #TEMP
    WHERE STRING LIKE %%%)

    SELECT * FROM CTE
    ORDER BY CASE WHEN PATINDEX([a-z]%[%],string) 0 THEN 0 ELSE 1 END

  • RELEVANCY SCORE 4.02

    DB:4.02:Left Join Using String Concatenation 8z


    hi

    I have two tables and i want to join them using left join based on string concatenation as per limitation of my application i can only use it after where clause like below

    create table shav_test1 (idd varchar(20), sec int, qty float )

    insert into shav_test1 values ('1aa',2,100);
    insert into shav_test1 values ('1bb',3,100);
    insert into shav_test1 values ('1cc',4,100);
    insert into shav_test1 values ('1dd',5,100);
    insert into shav_test1 values ('2ee',2,100);
    insert into shav_test1 values ('3ff',2,100);
    insert into shav_test1 values ('4gg',2,100);

    create table shav_test2 (id1 varchar(10),id2 varchar(10), sec int, qty float )

    insert into shav_test2 values ('1','aa',2,100);
    insert into shav_test2 values ('2','ee',2,100);
    insert into shav_test2 values ('3','cc',2,100);
    insert into shav_test2 values ('1','dd',5,100);

    select * from shav_test1 s1, shav_test2 s2 where
    s1.idd=(s2.id1||id2)(+)

    I am getting error using above sql.

    Please help
    Thanks

    DB:4.02:Left Join Using String Concatenation 8z

    You're welcome.

    Here's another way (a 'variation on DudeAndy's theme' ;) ):
    with s2 as ( select id1||id2 ids
    , sec
    , qty
    from shav_test2
    )
    select *
    from shav_test1 s1
    , s2
    where s1.idd = s2.ids(+);You might want to test and find out which of the solutions offered performs best for you...

  • RELEVANCY SCORE 4.02

    DB:4.02:Hierarchy Or Tree Structure Query f3


    Hi,

    I have to display each level total amount from the below tables
    We have two tables.
    Table 1 having the all level columns and table 2 having amount for each level

    Below are the table scripts and sample data

    CREATE TABLE LEVEL_TABLE
    (
    LEVEL1 VARCHAR2(50),
    LEVEL2 VARCHAR2(50),
    LEVEL3 VARCHAR2(50),
    LEVEL4 VARCHAR2(50),
    LEVEL5 VARCHAR2(50)
    )

    CREATE TABLE LEVEL_TABLE_AMT
    (
    AMT NUMBER(15),
    LEVEL_VALUE VARCHAR2(50)
    )

    INSERT INTO LEVEL_TABLE VALUES ('LEVEL_1', 'LEVEL_1.1', 'LEVEL_1.1.1', 'LEVEL_1.1.1.1', 'LEVEL_1.1.1.1.1');
    INSERT INTO LEVEL_TABLE VALUES ('LEVEL_1', 'LEVEL_1.1', 'LEVEL_1.1.1', 'LEVEL_1.1.1.1', 'LEVEL_1.1.1.1.2');
    INSERT INTO LEVEL_TABLE VALUES ('LEVEL_1', 'LEVEL_1.1', 'LEVEL_1.1.2', 'LEVEL_1.1.2.1', null);
    INSERT INTO LEVEL_TABLE VALUES ('LEVEL_1', 'LEVEL_1.1', 'LEVEL_1.1.2', 'LEVEL_1.1.2.2', null);
    INSERT INTO LEVEL_TABLE VALUES ('LEVEL_1', 'LEVEL_1.2', 'LEVEL_1.2.1', null, null);
    INSERT INTO LEVEL_TABLE VALUES ('LEVEL_1', 'LEVEL_1.2', 'LEVEL_1.2.2', 'LEVEL_1.2.2.1', null);
    INSERT INTO LEVEL_TABLE VALUES ('LEVEL_1', 'LEVEL_1.3', 'LEVEL_1.3.1', 'LEVEL_1.3.1.1', null);
    INSERT INTO LEVEL_TABLE VALUES ('LEVEL_1', 'LEVEL_1.3', 'LEVEL_1.3.1', 'LEVEL_1.3.1.2', null);

    INSERT INTO LEVEL_TABLE_AMT VALUES (100, 'LEVEL_1.1.1.1.1');
    INSERT INTO LEVEL_TABLE_AMT VALUES (200, 'LEVEL_1.1.1.1.2');
    INSERT INTO LEVEL_TABLE_AMT VALUES (100, 'LEVEL_1.1.2.1');
    INSERT INTO LEVEL_TABLE_AMT VALUES (300, 'LEVEL_1.1.2.2');
    INSERT INTO LEVEL_TABLE_AMT VALUES (100, 'LEVEL_1.2.1');
    INSERT INTO LEVEL_TABLE_AMT VALUES (400, 'LEVEL_1.2.2.1');
    INSERT INTO LEVEL_TABLE_AMT VALUES (100, 'LEVEL_1.3.1.1');
    INSERT INTO LEVEL_TABLE_AMT VALUES (200, 'LEVEL_1.3.1.2');

    COMMIT;

    Output of the Quey should be as below

    LEVEL_1(Amt)
    -- LEVEL_1.1(Amt)
    -- LEVEL_1.1.1(Amt)
    -- LEVEL_1.1.1.1(Amt)
    -- LEVEL_1.1.1.1.1(Amt)
    -- LEVEL_1.1.1.1.2(Amt)
    -- LEVEL_1.1.2(Amt)
    -- LEVEL_1.1.2.1(Amt)
    -- LEVEL_1.1.2.2(Amt)
    -- LEVEL_1.2(Amt)
    -- LEVEL_1.2.1(Amt)
    -- LEVEL_1.2.2(Amt)
    -- LEVEL_1.2.2.1(Amt)
    -- LEVEL_1.3(Amt)
    -- LEVEL_1.3.1(Amt)
    -- LEVEL_1.3.1.1(Amt)
    -- LEVEL_1.3.1.2(Amt)

    Please check and help me to display the query.

    Thanks,
    Pradeep.

    DB:4.02:Hierarchy Or Tree Structure Query f3

    Seems level_table is not needed
    with
    level_table_amt as
    (select 100 amt, 'LEVEL_1.1.1.1.1' level_value from dual union all
    select 200, 'LEVEL_1.1.1.1.2' from dual union all
    select 100, 'LEVEL_1.1.2.1' from dual union all
    select 300, 'LEVEL_1.1.2.2' from dual union all
    select 100, 'LEVEL_1.2.1' from dual union all
    select 400, 'LEVEL_1.2.2.1' from dual union all
    select 100, 'LEVEL_1.3.1.1' from dual union all
    select 200, 'LEVEL_1.3.1.2' from dual
    ),
    ancestors(l,amt,list) as
    (select 1,amt,substr(level_value,7) from level_table_amt
    union all
    select l + 1,amt,substr(list,1,instr(list,'.',-1,1) - 1)
    from ancestors
    where instr(list,'.') 0
    )
    select 'LEVEL_' || list,sum(amt) amt
    from ancestors
    group by list
    order by listRegards

    Etbin

    Edited by: Etbin on 8.3.2012 14:26
    A quick and dirty hierarhical approach
    with
    level_table_amt as
    (select 100 amt, 'LEVEL_1.1.1.1.1' level_value from dual union all
    select 200, 'LEVEL_1.1.1.1.2' from dual union all
    select 100, 'LEVEL_1.1.2.1' from dual union all
    select 300, 'LEVEL_1.1.2.2' from dual union all
    select 100, 'LEVEL_1.2.1' from dual union all
    select 400, 'LEVEL_1.2.2.1' from dual union all
    select 100, 'LEVEL_1.3.1.1' from dual union all
    select 200, 'LEVEL_1.3.1.2' from dual
    )
    select level_value,amount
    from (select 'LEVEL_' || ltrim(ancestor,'.') level_value,sum(amt) amount
    from (select distinct
    amt,
    level_value,
    substr(level_value,1,instr(level_value,'.',-1,level) - 1) ancestor
    from (select amt,'.' || substr(level_value,7) level_value
    from level_table_amt
    )
    connect by level length(level_value) - length(replace(level_value,'.',''))
    )
    group by ancestor
    union all
    select level_value,amt
    from level_table_amt
    )
    order by level_value

  • RELEVANCY SCORE 4.01

    DB:4.01:Deleting Duplicate Records Based On Condition 9f


    Hi All,

    I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records.

    The customer_id or contract_number data in the test table are not unique.

    Sample Table Script:

    CREATE TABLE TEST
    (
    CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL,
    CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
    CREATION_DATE DATE NOT NULL
    );Sample data script

    insert into TEST values('ANTON','123',sysdate-9);
    insert into TEST values('ANTON','123',sysdate-9);
    insert into TEST values('ANTON','123',sysdate-9);
    insert into TEST values('RAMKI','223',sysdate-2);
    insert into TEST values('RAMKI','225',sysdate-2);
    insert into TEST values('RAMKI','225',sysdate);
    insert into TEST values('PUGI','235',sysdate);
    insert into TEST values('ANTON','123',sysdate);
    insert into TEST values('ANTON','123',sysdate);
    insert into TEST values('ANTON','123',sysdate);
    insert into TEST values('ANTON','143',sysdate);
    insert into TEST values('ANTON','143',sysdate);
    insert into TEST values('ANTON','143',sysdate);
    insert into TEST values('RAMKI','223',sysdate);
    insert into TEST values('RAMKI','225',sysdate);
    insert into TEST values('RAMKI','225',sysdate);
    insert into TEST values('PUGI','235',sysdate);I have played around with some scripts, still no success, This displayed results somewhat near to what i am expecting.

    select * from
    test
    where rowid in
    (select rowid from
    (select
    rowid,
    row_number()
    over
    (partition by customer_id order by customer_id) dup
    from test)
    where dup 3);

    DB:4.01:Deleting Duplicate Records Based On Condition 9f

    Thanks, you understood me well, this is what i had in mind, thanks a lot, this worked.

  • RELEVANCY SCORE 4.01

    DB:4.01:Join &Amp; Empty Fields ma


    Hello,

    is there a way to force this join to match on null fields ?
    Thank you

    create table x (a integer, b integer, c char(50));
    create table y (d integer, e integer, f char(50));

    insert into x (a,b,c) values(1,2,'');
    insert into x (a,b,c) values(1,3,'');
    insert into x (a,b,c) values(1,4,'');
    insert into x (a,b,c) values(1,5,'');
    insert into x (a,b,c) values(1,7,'');
    insert into x (a,b,c) values(1,9,'');

    insert into y (d,e,f) values(1,2,'');
    insert into y (d,e,f) values(1,3,'');
    insert into y (d,e,f) values(1,4,'');
    insert into y (d,e,f) values(1,5,'');
    insert into y (d,e,f) values(1,7,'');
    insert into y (d,e,f) values(1,9,'');

    select a,b,c from x inner join y
    on a=d and b=e and c=f;

    DB:4.01:Join &Amp; Empty Fields ma

    Why use the longer way when the shorter will do as well?

    The whole approach assumes that c and f can not be '-1'. If one of them is '-1' and the other is NULL, the condition above will treat them as a match.Yes, you are correct. :)

  • RELEVANCY SCORE 4.01

    DB:4.01:Help For Order By Clause sd


    Hi ,

    i have a below table with data , when i run my select with order by option its gives me
    1Card

    as a first row but i need this
    12atm

    as a first row ,how this is possible .

    create table test
    (ID number ,
    val varchar2(20)
    )

    insert into test values (1,'Card')
    insert into test values (2,'Card')
    insert into test values (3,'Card')

    insert into test values (4,'Cash')
    insert into test values (5,'Cash')
    insert into test values (6,'Cash')

    insert into test values (7,'Checque')
    insert into test values (8,'Checque')
    insert into test values (9,'Checque')

    insert into test values (10,'atm')
    insert into test values (11,'atm')
    insert into test values (12,'atm')

    select * from test
    order by val asc

    DB:4.01:Help For Order By Clause sd

    YOu can also use Nlssort like

    SQL select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

    SQL select *
    2 from test
    3 order by nlssort(val, 'NLS_SORT = WEST_EUROPEAN_AI') asc, id desc
    4 /

    ID VAL
    ---------- --------------------
    12 atm
    11 atm
    10 atm
    3 Card
    2 Card
    1 Card
    6 Cash
    5 Cash
    4 Cash
    9 Checque
    8 Checque

    ID VAL
    ---------- --------------------
    7 Checque

    12 rows selected.

  • RELEVANCY SCORE 4.01

    DB:4.01:Count Functions km


    craete table t (a number);

    insert into t values(null);
    insert into t values(null);
    insert into t values(1);
    insert into t values(2);
    insert into t values(3);
    insert into t values(2);

    commit;

    select count(*) from t;
    6-- count every row

    select count(all a) from t;
    4-- does not count null

    select count(distinct a) from t;
    3---does not countso do all group functions ignore null?

    DB:4.01:Count Functions km

    Hi,
    Rahul India wrote:
    ... so do all group functions ignore null?Basically, yes.

    COUNT (*), as you showed, does not ignore NULLs. It includes rows even when all columns are NULL.

    Would you say GROUPING is a "group function"? (I would.) Would you say GROUPING ignores NULLs? (I wouldn't.)
    How about FIRST and LAST?

  • RELEVANCY SCORE 4.01

    DB:4.01:How To Format Unformated Phone Numbers Please..., mz


    Hello Good Evening, i have a phone number table which has single column in it with different style of phone numbers, i am trying to change it as required format
    Required format are
    1) Always start with a 1
    2) Only Left 14 charcaters after spaces elimination (if there is any)
    3) No spaces
    4) special characters like (,),-,/ need to replace with '.'
    5) if there is no separation/space then format 1.800.###.#### need to apply
    create table #myphoneno (phone varchar(20))
    Insert into #myphoneno values ('(866) 987-3847' ) --- Rule 1,3,4
    Insert into #myphoneno values ('1-800-222-SAVE(7283)') --- Rule 2,4
    Insert into #myphoneno values ('(800) 111-4015') --Rule 3,4
    Insert into #myphoneno values ('18002288775') ---Rule 5
    Insert into #myphoneno values ('(800) 2223415')--- Rule 1,3,4,5
    Insert into #myphoneno values ('(800) 2-MOHAN')--- Rule 1,,3,4
    Insert into #myphoneno values ('1-800-228-4995') ---Rule 4
    Insert into #myphoneno values ('877-233-0112')---- Rule 1,4
    Insert into #myphoneno values ('800 228 3315')--- 1,3,4
    Insert into #myphoneno values ('18002284225')---Rule 5
    Insert into #myphoneno values ('800/219-1140') --- Rule 1,4
    Insert into #myphoneno values ('1 (855) ECO-8107') -- Rule 3,4
    Insert into #myphoneno values ('(888) BOM4BQC')-- Rule1,3,4
    Insert into #myphoneno values ('877-421K-MOX')-- Rule 1,4
    Select * from #myphoneno
    ---- Expected results as below
    create table #myphonenoExpected (phone varchar(20))
    Insert into #myphonenoExpected values ('1.866.987.3847' )
    Insert into #myphonenoExpected values ('1.800.222.SAVE')
    Insert into #myphonenoExpected values ('1.800.111.4015')
    Insert into #myphonenoExpected values ('1.800.228.8775')
    Insert into #myphonenoExpected values ('1.800.222.3415')
    Insert into #myphonenoExpected values ('1.800.2.MOHAN')
    Insert into #myphonenoExpected values ('1.800.228.4995')
    Insert into #myphonenoExpected values ('1.877.233.0112')
    Insert into #myphonenoExpected values ('1.800.228.3315')
    Insert into #myphonenoExpected values ('1.800.228.4225')
    Insert into #myphonenoExpected values ('1.800.219.1140')
    Insert into #myphonenoExpected values ('1.855.ECO.8107')
    Insert into #myphonenoExpected values ('1.888.BOM.4BQC')
    Insert into #myphonenoExpected values ('1.877.421K.MOX')
    Select * from #myphonenoExpected

    Thank you in advance
    Asiti

    DB:4.01:How To Format Unformated Phone Numbers Please..., mz

    USE [Northwind]
    create table #myphoneno (phone varchar(20))

    Insert into #myphoneno values ('(866) 987-3847' ) --- Rule 1,3,4
    Insert into #myphoneno values ('1-800-222-SAVE(7283)') --- Rule 2,4
    Insert into #myphoneno values ('(800) 111-4015') --Rule 3,4
    Insert into #myphoneno values ('18002288775') ---Rule 5
    Insert into #myphoneno values ('(800) 2223415')--- Rule 1,3,4,5
    Insert into #myphoneno values ('(800) 2-MOHAN')--- Rule 1,,3,4
    Insert into #myphoneno values ('1-800-228-4995') ---Rule 4
    Insert into #myphoneno values ('877-233-0112')---- Rule 1,4
    Insert into #myphoneno values ('800 228 3315')--- 1,3,4
    Insert into #myphoneno values ('18002284225')---Rule 5
    Insert into #myphoneno values ('800/219-1140') --- Rule 1,4
    Insert into #myphoneno values ('1 (855) ECO-8107') -- Rule 3,4
    Insert into #myphoneno values ('(888) BOM4BQC')-- Rule1,3,4
    Insert into #myphoneno values ('877-421K-MOX')-- Rule 1,4

    ;with mycte as(
    Select phone,

    replace(replace(replace(replace(replace( replace(phone,'(','.'),')','.'),'-','.'),'/','.'),' ','.'),'..','.') tmpCol
    from #myphoneno)
    ,mycte1 as (select phone, case when left(tmpCol,1)='1' then tmpCol else '1.'tmpCol end tmpcol1 from mycte)

    select phone, Case when charindex('.', tmpcol1) 0 then left(replace(tmpcol1,'..','.') ,14) Else
    substring (tmpcol1,1,1)'.' substring (tmpcol1,2,3)'.'
    substring(tmpcol1,5,3)'.' substring (tmpcol1,8,4) end as newPhone from mycte1

    drop table #myphoneno

  • RELEVANCY SCORE 4.00

    DB:4.00:Query Help? dx


    Hi All,

    CREATE TABLE #TEMP
    (
    ID1 INT,
    ID2 INT,
    ID3 INT
    )

    INSERT INTO #TEMP VALUES (1, 1, 3)
    INSERT INTO #TEMP VALUES (1, 2, 3)
    INSERT INTO #TEMP VALUES (1, 3, 3)
    INSERT INTO #TEMP VALUES (1, 1, 3)
    INSERT INTO #TEMP VALUES (1, 3, 2)
    INSERT INTO #TEMP VALUES (1, 3, 3)

    SELECT COUNT(DISTINCT ID1, ID2, ID3)
    FROM
    #TEMP

    DROP TABLE #TEMP

    DB:4.00:Query Help? dx

    COUNT(DISTINCT ID1, ID2, ID3) is like every other aggregate function. They take a scalar parameter, not a list. First all NULLs are remove from the set, if tyhere is a DISTINCT option, all redundant duplicates are removed. Finally, the function -- COUNT(), AVG(), MIN(), MAX(), SUM(), etc -- is performed on what is left. If the set is empty, then a NULL is returned.

    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it

  • RELEVANCY SCORE 4.00

    DB:4.00:Compare Two Tables And Delete The Common Rows From Table 1. fk


    Hi ,

    I want to compare two tables ,
    and delete the common rows from
    the first table

    Here is what i have done :

    Create table test1(Test1C1 Number,
    Test1C2 varchar2(50));

    Create table test2(Test2C1 Number,
    Test2C2 varchar2(50));

    Insert into test1 values(1,'testdata1');
    Insert into test1 values(2,'testdata2');
    Insert into test1 values(3,'testdata3');
    Insert into test1 values(4,'testdata4');
    Insert into test1 values(5,'testdata5');
    Insert into test1 values(6,'testdata6');
    Insert into test1 values(7,'testdata7');
    Insert into test1 values(8,'testdata8');
    Insert into test1 values(9,'testdata9');
    Insert into test1 values(10,'testdata10');

    commit;

    Insert into test2 values(1,'testdata1');
    Insert into test2 values(3,'testdata3');
    Insert into test2 values(5,'testdata5');
    Insert into test2 values(7,'testdata7');
    Insert into test2 values(9,'testdata9');

    commit;

    delete from test1 t1 where exists(
    select * from test1
    INTERSECT
    select * from test2);

    it deletes all the records from Table test1.

    What should I modify here ? or should I write a differnet query ?

    The desired contents in table test1 will be

    2 testdata2
    4 testdata4
    6 testdata6
    8 testdata8
    10 testdata10

    DB:4.00:Compare Two Tables And Delete The Common Rows From Table 1. fk

    DELETE FROM test1
    WHERE test1c1 IN ( SELECT test1c1
    FROM test1 t1, test2 t2
    WHERE t1.test1c1= t2.test2c1
    );

  • RELEVANCY SCORE 4.00

    DB:4.00:Compile Error For Simple Procedure 17


    I have written a simple procedure for a small sized book inventory. It compiles, but with the strangest error codes, such as not allowed to use IS or BEGIN, the error list is half a page long. I know there is probably one little thing I missed but I just can't spot it. Here is my procedure followed by the data.

    CREATE OR REPLACE PROCEDURE Surplus_Books
    (b_inv IN VARCHAR2,
    b_price IN NUMBER,
    b_id IN NUMBER,
    b_title IN VARCHAR,
    o_qty IN NUMBER)

    IS
    BEGIN

    SELECT book_inventory.inv_id, book_inventory.book_price, books.book_id, books.book_title, orders_filed.order_qty
    INTO b_inv, b_price, b_id, b_title, o_qty
    FROM book_inventory, books, orders_filed
    WHERE book_inventory.book_id = b_id;
    END;My expected output is to simply list the book title, the number of books ordered and the price of the book, something like this:

    Book | Num Ordered | Book cost

    The Myth of Scarecrows | 1 | $19.99
    Somebody Knocked | 1 | $19.95

    CREATE TABLE books
    (book_id NUMBER(8),
    book_title VARCHAR2(30));

    CREATE TABLE book_inventory
    (inv_id NUMBER(10),
    book_id NUMBER(8),
    book_condition VARCHAR2(20),
    book_price NUMBER(6,2));

    CREATE TABLE orders_filed
    (o_id NUMBER(8),
    inv_id NUMBER(10),
    order_qty NUMBER(4) NOT NULL);

    INSERT INTO books VALUES (1, 'History of France');
    INSERT INTO books VALUES (2, 'WWII and the Aftermath');
    INSERT INTO books VALUES (3, 'The Myth of Scarecrows');
    INSERT INTO books VALUES (4, 'Somebody Knocked');
    INSERT INTO books VALUES (5, 'Romantic Love');
    INSERT INTO books VALUES (6, 'The End of Time');
    INSERT INTO books VALUES (7, 'Great Baby Name''s');

    INSERT INTO book_inventory VALUES (1, 2, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (2, 2, 'New', 29.99);
    INSERT INTO book_inventory VALUES (3, 3, 'Like New', 49.99);
    INSERT INTO book_inventory VALUES (4, 3, 'Like New', 19.99);
    INSERT INTO book_inventory VALUES (5, 3, 'Used', 49.99);
    INSERT INTO book_inventory VALUES (6, 3, 'New', 49.99);
    INSERT INTO book_inventory VALUES (7, 3, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (8, 3, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (9, 4, 'New', 29.99);
    INSERT INTO book_inventory VALUES (10, 4, 'Like New', 19.99);
    INSERT INTO book_inventory VALUES (11, 4, 'Like New', 49.99);
    INSERT INTO book_inventory VALUES (12, 4, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (13, 4, 'New', 19.99);
    INSERT INTO book_inventory VALUES (14, 4, 'New', 29.99);
    INSERT INTO book_inventory VALUES (15, 5, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (16, 5, 'Like New', 49.99);
    INSERT INTO book_inventory VALUES (17, 5, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (18, 5, 'New', 29.99);
    INSERT INTO book_inventory VALUES (19, 5, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (20, 5, 'Used', 49.99);
    INSERT INTO book_inventory VALUES (21, 5, 'Like New', 49.99);
    INSERT INTO book_inventory VALUES (22, 5, 'Like New', 19.99);
    INSERT INTO book_inventory VALUES (23, 1, 'New', 19.99);
    INSERT INTO book_inventory VALUES (24, 1, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (25, 1, 'New', 29.99);
    INSERT INTO book_inventory VALUES (26, 1, 'Like New', 19.99);
    INSERT INTO book_inventory VALUES (27, 6, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (28, 6, 'Used', 49.99);
    INSERT INTO book_inventory VALUES (29, 6, 'Like New', 49.99);
    INSERT INTO book_inventory VALUES (30, 7, 'Like New', 19.99);
    INSERT INTO book_inventory VALUES (31, 7, 'Used', 19.99);
    INSERT INTO book_inventory VALUES (32, 7, 'New', 19.99);

    INSERT INTO orders_filed VALUES (1, 1, 1);
    INSERT INTO orders_filed VALUES (1, 14, 2);
    INSERT INTO orders_filed VALUES (2, 19, 1);
    INSERT INTO orders_filed VALUES (3, 24, 1);
    INSERT INTO orders_filed VALUES (3, 26, 1);
    INSERT INTO orders_filed VALUES (4, 12, 2);
    INSERT INTO orders_filed VALUES (5, 8, 1);
    INSERT INTO orders_filed VALUES (5, 13, 1);
    INSERT INTO orders_filed VALUES (6, 2, 1);
    INSERT INTO orders_filed VALUES (6, 7, 3);Edited by: user12987698 on Apr 14, 2010 11:04 AM

    DB:4.00:Compile Error For Simple Procedure 17

    Hi,

    Welcome to the forum!

    user12987698 wrote:
    I have written a simple procedure for a small sized book inventory. It compiles, but with the strangest error codes, such as not allowed to use IS or BEGIN, the error list is half a page long. If you want help with an error, post the complete error message(s) You can post a lot of stuff on one page, but if you're getting so many errors, then it makes sense to start with a much smaller procedure.
    I know there is probably one little thing I missed but I just can't spot it. Here is my procedure followed by the data.

    CREATE OR REPLACE PROCEDURE Surplus_Books
    (b_inv IN VARCHAR2,
    b_price IN NUMBER,
    b_id IN NUMBER,
    b_title IN VARCHAR,
    o_qty IN NUMBER)

    IS
    BEGIN

    SELECT book_inventory.inv_id, book_inventory.book_price, books.book_id, books.book_title, orders_filed.order_qty
    INTO b_inv, b_price, b_id, b_title, o_qty
    FROM book_inventory, books, orders_filed
    WHERE book_inventory.book_id = b_id;
    END;That is much too much code to write at once.
    Always start small, and then take baby steps.
    You don't have to throw away all the typing you've done so far; you can comment it out, and gradually add stuff back in.
    For example, let's start with something that only uses one argument, and only one table:
    CREATE OR REPLACE PROCEDURE Surplus_Books
    ( b_inv IN VARCHAR2--,
    -- b_price IN NUMBER,
    -- b_id IN NUMBER,
    -- b_title IN VARCHAR,
    -- o_qty IN NUMBER
    )

    IS
    BEGIN

    SELECT book_inventory.inv_id --, book_inventory.book_price, books.book_id, books.book_title, orders_filed.order_qty
    INTO b_inv --, b_price, b_id, b_title, o_qty
    FROM book_inventory -- , books, orders_filed
    WHERE book_inventory.book_id = 1; -- after debugging, replace 1 with b_id;
    END;or, if you prefer:
    CREATE OR REPLACE PROCEDURE Surplus_Books
    ( b_inv IN VARCHAR2
    )

    IS
    BEGIN

    SELECT book_inventory.inv_id
    INTO b_inv
    FROM book_inventory
    WHERE book_inventory.book_id = 1; -- after debugging, replace 1 with b_id;
    END;This gets the errors:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    8/5 PL/SQL: SQL Statement ignored
    9/13 PLS-00403: expression 'B_INV' cannot be used as an INTO-target of
    a SELECT/FETCH statement

    10/5 PL/SQL: ORA-00904: : invalid identifierA single error sometimes causes multiple error messages. Look for the first message that seems to really mean siomething.
    In this case, the first message "PL/SQL Statement ignored' merely flags the beginning of what could be a very long statement.
    The second message "'B_INV' cannot be used as an INTO-target" seems to really mean something. Specifically, you can't assign a value to an IN-argument in an INTO clause. If you want to change the argument, you should make it an OUT-argument, or perhaps IN OUT. Another possibility is to use a local variable in the INTO clause.
    My expected output is to simply list the book title, the number of books ordered and the price of the book, something like this:

    Book | Num Ordered | Book cost

    The Myth of Scarecrows | 1 | $19.99
    Somebody Knocked | 1 | $19.95 Why are you writing a procedure for this, rather than a pure SQL query?
    CREATE TABLE books
    (book_id NUMBER(8),
    book_title VARCHAR2(30));
    ...Thanks for posting the CREATE TABLE and INSERT statements! That's very helpful.

  • RELEVANCY SCORE 4.00

    DB:4.00:Matching Of Uk Postcodes To Show Region mz


    I am looking to do some matching on UK postcodes from one table to extract the region of the postcode from 2 tables.
    I am not convinced the method I have used to do the matching (left outer joins) is the most accurate way to do it. It works on the sample data but I think I am going to run into problems when used on the main database.
    here is the query
    CREATE TABLE #TMPTOWNS1
    (POSTCODE VARCHAR(10) NOT NULL
    ,REGION VARCHAR(30) NOT NULL)


    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L1','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L10','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L11','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L12','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L13','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L14','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L15','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L16','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L17','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L18','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L19','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L2','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L20','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L21','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L22','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L23','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L24','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L25','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L26','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L27','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L28','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L29','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L3','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L30','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L31','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L32','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L33','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L34','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L35','St Helens')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L36','Knowsley')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L37','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L38','Sefton')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L39','Lancashire')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L4','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L40','Lancashire')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L5','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L6','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L69','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L7','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L70','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L8','Liverpool')
    INSERT INTO #TMPTOWNS1 (POSTCODE,REGION) VALUES ('L9','Liverpool')

    CREATE TABLE #TMPTOWNS2
    (POSTCODE VARCHAR(10) NOT NULL
    ,REGION VARCHAR(30) NOT NULL)


    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO1','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO10','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO11','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO12','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO13','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO14','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO15','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO16','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO17','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO18','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO19','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO21','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO22','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO23','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO24','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO25','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO26','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO30','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO31','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO32','York')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO41','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO42','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO43','East Riding of Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO51','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO60','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO61','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO62','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO7','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('YO8','North Yorkshire')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('ZE1','Shetland Islands')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('ZE2','Shetland Islands')
    INSERT INTO #TMPTOWNS2(POSTCODE,REGION) VALUES ('ZE3','Shetland Islands')

    CREATE TABLE #MAINTABLE
    (POSTCODE VARCHAR(10) NOT NULL)


    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('YO1 6ZY')
    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('YO25 7AP')
    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('L3 2BT')
    INSERT INTO #MAINTABLE (POSTCODE) VALUES ('L38 1LT')

    SELECT M.POSTCODE,REGION =CASE WHEN T1.REGION IS NULL THEN T2.REGION ELSE T1.REGION END
    FROM #MAINTABLE AS M
    LEFT OUTER JOIN #TMPTOWNS1 AS T1 ON LEFT(M.POSTCODE,3)=LEFT(T1.POSTCODE,3)
    LEFT OUTER JOIN #TMPTOWNS2 AS T2 ON LEFT(M.POSTCODE,4)=LEFT(T2.POSTCODE,4)

    DROP TABLE #MAINTABLE
    DROP TABLE #TMPTOWNS1
    DROP TABLE #TMPTOWNS2

    DB:4.00:Matching Of Uk Postcodes To Show Region mz

    Hi,
    This will work as here matching is done on the chars before space and post codes will have spaces.
    SELECT M.POSTCODE
    ,REGION =coalesce(T1.REGION , T2.REGION)
    FROM #MAINTABLE AS M
    LEFT OUTER JOIN #TMPTOWNS1 AS T1 ON LEFT(M.POSTCODE,CHARINDEX(' ',M.POSTCODE))=T1.POSTCODE
    LEFT OUTER JOIN #TMPTOWNS2 AS T2 ON LEFT(M.POSTCODE,CHARINDEX(' ',M.POSTCODE))=T2.POSTCODE
    - Chintak (My Blog)

  • RELEVANCY SCORE 4.00

    DB:4.00:Query Problem-- z7


    Hi, I need one query

    I want a query with out using union

    select pno from temp where pno not like '9%'
    union select pno from temp2

    select all pno from temp table
    which are not like 9% but it can include
    the pno with 9 if it is exist in table temp2

    I have the following data

    select * from temp

    create table temp(pno number)

    insert into temp values(1)
    insert into temp values(2)
    insert into temp values(3)
    insert into temp values(4)

    insert into temp values(91)

    insert into temp values(92)
    insert into temp values(93)
    insert into temp values(94)
    insert into temp values(90)
    insert into temp values(99)
    temp
    1
    2
    3
    90
    91
    92
    4
    99

    create table temp2 (pno number)

    insert into temp2 values(99)
    insert into temp2 values(90)

    temp 2

    99
    90

    output

    1
    2
    3
    4
    99
    90

    Thank you

    DB:4.00:Query Problem-- z7

    Thank you, i was also doing some thing like this

    select * from temp where
    (pno not like ('9%') or pno in (select pno from temp2))

  • RELEVANCY SCORE 4.00

    DB:4.00:Out Of Range Values zz


    CREATE
    TABLE #table1 (id
    INT , serial
    INT)
    INSERT
    INTO #table1 VALUES
    ( 1 , 1
    )
    INSERT
    INTO #table1 VALUES
    ( 1 , 2
    )
    INSERT
    INTO #table1 VALUES
    ( 1 , 3
    )
    INSERT
    INTO #table1 VALUES
    ( 1 , 4
    )
    INSERT
    INTO #table1 VALUES
    ( 1 , 5
    )
    INSERT
    INTO #table1 VALUES
    ( 2 , 1
    )
    INSERT
    INTO #table1 VALUES
    ( 2 , 2
    )
    INSERT
    INTO #table1 VALUES
    ( 2 , 3
    )
    INSERT
    INTO #table1 VALUES
    ( 2 , 4
    )
    INSERT
    INTO #table1 VALUES
    ( 2 , 5
    )

    CREATE
    TABLE #table2 (id
    INT , startnbr
    INT , ENDnbr INT)
    INSERT
    INTO #table1 VALUES
    ( 1 , 1
    , 4 )
    INSERT
    INTO #table1 VALUES
    ( 1 , 1
    , 3 )

    I want to find the serial in #table1 from #table2 which are not between #table2 startnbr and enbnbr against each id.
    As the result should be like

    ID
    Serial
    1
    5
    2
    4
    2
    5

    Regards, Muhammad Bilal.

    DB:4.00:Out Of Range Values zz


    /*CREATE TABLE #table1 (id INT , serial INT)
    INSERT INTO #table1 VALUES ( 1 , 1 )
    INSERT INTO #table1 VALUES ( 1 , 2 )
    INSERT INTO #table1 VALUES ( 1 , 3 )
    INSERT INTO #table1 VALUES ( 1 , 4 )
    INSERT INTO #table1 VALUES ( 1 , 5 )
    INSERT INTO #table1 VALUES ( 2 , 1 )
    INSERT INTO #table1 VALUES ( 2 , 2 )
    INSERT INTO #table1 VALUES ( 2 , 3 )
    INSERT INTO #table1 VALUES ( 2 , 4 )
    INSERT INTO #table1 VALUES ( 2 , 5 )

    CREATE TABLE #table2 (id INT , startnbr INT , ENDnbr INT)
    INSERT INTO #table2 VALUES ( 1 , 1 , 4 )
    INSERT INTO #table2 VALUES ( 2 , 1 , 3 )

    */

    --select * from #table1
    --select * from #table2
    --truncate table #table2

    select
    a.id
    ,a.Serial
    --,*
    from #table1 a
    inner join #table2 b
    On a.id = b.id
    where a.serial b.startnbr
    or a.serial b.Endnbr

  • RELEVANCY SCORE 4.00

    DB:4.00:Removing The Zeros From The Starting ma


    Hi, How I can remove the Zeros from starting. Help on this pl.
    create table #1 (e_no char(10))
    insert into #1 values ('00100')
    insert into #1 values ('04500)
    insert into values ('00005')
    insert into values ('00050')
    --exprected results
    100
    4500
    5
    50
    Thanks.

    DB:4.00:Removing The Zeros From The Starting ma

    The above 2 solutions will work fine as long as all your entries are always numerical. If not, it will fail.For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

  • RELEVANCY SCORE 4.00

    DB:4.00:Sql Sum Query xf



    HI,
    DECLARE @T TABLE (A INT, B INT)
    INSERT INTO @T VALUES (1,1)
    INSERT INTO @T VALUES (1,2)
    INSERT INTO @T VALUES (2,10)
    INSERT INTO @T VALUES (2,-5)
    INSERT INTO @T VALUES (2,1)
    INSERT INTO @T VALUES (3,10)
    INSERT INTO @T VALUES (3,-5)
    INSERT INTO @T VALUES (3,-3)
    SELECT * FROM @T
    EXPECTING OUTPUT LIKE (WITH IN SINGLE QUERY / CALLING TABLE ONCE TIME)

    A
    SUM(ALL)
    SUM(ONLY - VALUE)

    1
    3
    -3

    2
    6
    -5

    3
    2
    -8

    PLEASELET ME KNOW IF POSSIBLE

  • RELEVANCY SCORE 4.00

    DB:4.00:Programming ac


    Base on the Query below: What result i get? Is there any wasted code? explain
    create table #Tbl1( n int )
    insert into #Tbl1 values( 15 )
    insert into #Tbl1 values( 5 )
    insert into #Tbl1 values( 34 )
    insert into #Tbl1 values( 8 )
    insert into #Tbl1 values( 51 )
    insert into #Tbl1 values( 63 )
    insert into #Tbl1 values( 72 )
    insert into #Tbl1 values( 8 )

    create table #Tbl2( n int )
    insert into #Tbl2 values( 1 )
    insert into #Tbl2 values( 6 )
    insert into #Tbl2 values( 7 )
    insert into #Tbl2 values( 10 )
    insert into #Tbl2 values( 51 )
    insert into #Tbl2 values( 63 )
    insert into #Tbl2 values( 72 )
    select
    #Tbl1.*
    from
    #Tbl1
    LEFT OUTER JOIN
    #Tbl2
    on
    #Tbl1.n = #Tbl2.n
    and
    #Tbl1.n 50

    DB:4.00:Programming ac

    893185 wrote:
    Base on the Query below: What result i get? Is there any wasted code? explain
    create table #Tbl1( n int )
    insert into #Tbl1 values( 15 )
    insert into #Tbl1 values( 5 )
    insert into #Tbl1 values( 34 )
    insert into #Tbl1 values( 8 )
    insert into #Tbl1 values( 51 )
    insert into #Tbl1 values( 63 )
    insert into #Tbl1 values( 72 )
    insert into #Tbl1 values( 8 )

    create table #Tbl2( n int )
    insert into #Tbl2 values( 1 )
    insert into #Tbl2 values( 6 )
    insert into #Tbl2 values( 7 )
    insert into #Tbl2 values( 10 )
    insert into #Tbl2 values( 51 )
    insert into #Tbl2 values( 63 )
    insert into #Tbl2 values( 72 )
    select
    #Tbl1.*
    from
    #Tbl1
    LEFT OUTER JOIN
    #Tbl2
    on
    #Tbl1.n = #Tbl2.n
    and
    #Tbl1.n 50not VALID Oracle SQL!

  • RELEVANCY SCORE 4.00

    DB:4.00:Update Based On Max Value Of Different Table. a3


    {code}create table rule_table (rule_id number ) ;insert into rule_table values (10) ;insert into rule_table values (20) ;insert into rule_table values (30) ;insert into rule_table values (40) ;create table auth_table (auth_id number, rule_id number);insert into auth_table values (1000, 10);insert into auth_table values (2000, 10);insert into auth_table values (3000, 10);insert into auth_table values (4000, 20);insert into auth_table values (5000, 20);insert into auth_table values (6000, 30);insert into auth_table values (7000, 30);insert into auth_table values (8000, 40);insert into auth_table values (9000, 40);create table pay_table (pay_id number, auth_id number, pay_indicator number) ;insert into pay_table values (11111, 1000, 0 );insert into pay_table values (22222, 1000, 1 );insert into pay_table values (33333, 1000, 0 );insert into pay_table values (44444, 2000, 0 );insert into pay_table values (55555, 2000, 1 );insert into pay_table values (66666, 2000, 0 );insert into pay_table values (77777, 3000, 0 );insert into pay_table values (88888, 3000, 0 );insert into pay_table values (99999, 4000, 0 );insert into pay_table values (111111, 4000, 0 );insert into pay_table values (222222, 5000, 0 );insert into pay_table values (333333, 5000, 0 );insert into pay_table values (444444, 6000, 0 );insert into pay_table values (555555, 7000, 1 );insert into pay_table values (666666, 8000, 0 );insert into pay_table values (777777, 9000, 0 );insert into pay_table values (888888, 9000, 1 );create table rule_pay ( rule_id number, pay_max_indicator number ) ;insert into rule_pay values ( 10, 0 ) ;insert into rule_pay values ( 20, 0 ) ;insert into rule_pay values ( 30, 0 ) ;insert into rule_pay values ( 40, 0 ) ;{code}My intention is as follows:for each rule_id find out the maximum pay_indicator (query 3 tables to get the max pay_indicator as show in below) and on different table i have to update that max value based on rule_id.with max_tabas( select a.rule_id, max(pay_indicator) pay_indicatorfrom rule_table a, auth_table b, pay_table c where a.rule_id = b.rule_id and b.auth_id = c.auth_idgroup by a.rule_id )update rule_pay set pay_max_indicator = ( select max_tab.pay_indicator from max_tab where max_tab.rule_id = rule_pay.rule_id )where rule_id in ( select rule_id from max_tab ) ;The above query not working.Any help or suggestions greatly appreciated.

  • RELEVANCY SCORE 3.99

    DB:3.99:Some Sql Query Questions dx


    Hi, Could you please answer with t-sql code , the folowing questions:
    Quests:
    ----------------------------------------------------------------

    Find the Names of Student who were absent from mathematics examinationFind the name of Students who passed in mathematics but failed in Physics (pass marks: 40)Find the students whose marksobtained is more than the average markof that particular subject.Find the students name who were present in all subjects examination

    I am giving example Table and Data, will be easy for you
    ------------------------------------------------------------------
    create table #student
    (
    [ST_ID] int,
    [ST_Name] varchar(20)
    )
    create table #marks
    (
    [ST_ID] int,
    [Subject] nvarchar(20),
    [MarksObtained] int
    )

    insert into #student values (1,'Student1')
    insert into #student values (2,'Student2')
    insert into #student values (3,'Student3')
    insert into #student values (4,'Student4')
    insert into #student values (5,'Student5')
    insert into #student values (6,'Student6')
    insert into #student values (7,'Student7')
    insert into #student values (8,'Student8')
    insert into #marks values (1,'Maths',88)
    insert into #marks values (2,'Maths',75)
    insert into #marks values (3,'Maths',36)
    insert into #marks values (4,'Maths',47)
    insert into #marks values (5,'Maths',55)
    insert into #marks values (1,'Physics',92)
    insert into #marks values (2,'Physics',40)
    insert into #marks values (3,'Physics',56)
    insert into #marks values (4,'Physics',33)
    insert into #marks values (5,'Physics',99)
    insert into #marks values (6,'Physics',57)
    insert into #marks values (7,'Physics',49)
    insert into #marks values (8,'Physics',74)
    insert into #marks values (1,'Chemistry',48)
    insert into #marks values (2,'Chemistry',54)
    insert into #marks values (3,'Chemistry',69)
    insert into #marks values (4,'Chemistry',91)
    insert into #marks values (5,'Chemistry',39)
    insert into #marks values (6,'Chemistry',56)
    insert into #marks values (7,'Chemistry',64)
    insert into #marks values (8,'Chemistry',67)

    --Select * from #student
    --Select * from #marks
    Drop table #student
    Drop table #marks
    ------------------------------------------------------------------------------------------------

    Blogs: www.sqlserver2005forum.blogspot.com

    DB:3.99:Some Sql Query Questions dx

    Amazing! That looks similar to what I have sitting in my SSMS.
    I was going to try and teach the OPHOW to construct such a query. No need now.

    Premature optimization is the root of all evil. - Knuth
    If I provoked thought, please click the green arrow
    If I provoked Aha! please click Propose as Answer
    We are here to learn, to share knowledge, and to earn points; all in about equal measure.

  • RELEVANCY SCORE 3.99

    DB:3.99:Sql With Conditions sa


    Hello,I have need generate a summary report from a table with conditions. The followings are details:create table tb_class (classId number(5), studentNum number(5));create table tb_grade(classId number(5), gradeNum number(5)):insert into table tb_class values(101, 100);insert into table tb_class values(201, 102);insert into table tb_class values(102, 103);insert into table tb_grade values(101, 1);insert into table tb_grade values(102, 1);insert into table tb_gradfe values(201, 2);I would like to get the total number of student registed in one specific grade with one SQL, the result is expected as following:GradeNum studentNumRegistered studentNumNonRegisted1 203 1022 102 203

    DB:3.99:Sql With Conditions sa

    Hi,
    942572 wrote:

    Hello,

    I have need generate a summary report from a table with conditions. The followings are details:

    create table tb_class (classId number(5), studentNum number(5));
    create table tb_grade(classId number(5), gradeNum number(5)):

    insert into table tb_class values(101, 100);
    insert into table tb_class values(201, 102);
    insert into table tb_class values(102, 103);

    insert into table tb_grade values(101, 1);
    insert into table tb_grade values(102, 1);
    insert into table tb_gradfe values(201, 2);

    I would like to get the total number of student registed in one specific grade with one SQL, the result is expected as following:

    GradeNum studentNumRegistered studentNumNonRegisted
    1 203 102
    2 102 203

    Would you like people to post solutions that work? Then make sure the code you post works first. Test (and, if necessary, correct) your CREATE TABLE and INSERT statements before you post them. Review the syntax of the INSERT statement, and be careful to spell the table names consistently.I think you want something like this:
    WITH gotTotalStudentNum AS
    (
    SELECT SUM (studentNum) AS totalStudentNum
    FROM tb_class
    )
    SELECT g.gradeNum
    , SUM (c.studentnum) AS studentNumRegistered
    , t.totalStudentNum - SUM (c.studentnum) AS studentNumNonRegistered
    FROM tb_grade g
    JOIN tb_class c ON g.classId = c.classId
    CROSS JOIN gotTotalStudentNum t
    GROUP BY g.gradeNum
    , t.totalStudentNum
    ;