• RELEVANCY SCORE 7.33

    DB:7.33:Redbull X1copied From Caparo T1!! 3k





    I was watching an old episode of top gear today and say a car which ooked ver similar to our GT5 favourite the Red Bull X2010

    here is the Red Bull X2010

    and here is the Caparo T1




    Twitter @SilentShadow777Destiny Profiles Hunter | Titan | Warlock

    DB:7.33:Redbull X1copied From Caparo T1!! 3k

    you live in norway or somthing? i know i do, and i also watched that episode. The similarity struck me too, and especially when it comes to speed compared to other cars. Too bad it didn't win because of too low ride height!

  • RELEVANCY SCORE 2.78

    DB:2.78:X2010 ca





    DER RED BULL X2010 SIEHT DOCH BALD AUS WIE DER

    Caparo T1 AUS

    DB:2.78:X2010 ca

    PUHDERBAER schrieb:
    DER RED BULL X2010 SIEHT DOCH BALD AUS WIE DER

    Caparo T1 AUS

    Hättest hier auch posten können:

    http://community.eu.playstation.com/t5/Gran-Turism​o/Red-Bull-X-2010/td-p/12052703

    Aber is eh schon wurscht,doppel,dreifach,vierfach usw Threads,machen wenigstens alles noch etwas undurchschaubarer,da kommt,s auf den einen oder anderen Thread auch nicht mehr an.

    Such Funktion x2010 eingebenhätte dich zum Zeil gebracht.

    PS:Für diesen Post,wurde ich von PD noch nicht bezahlt,auch nicht von Sony LACH.......




    ***FRN mit GT 5 von 22.07.2011 - 22.11.2013***Download 102.400 kBit/s Upload 10.240 kBit/s

  • RELEVANCY SCORE 2.42

    DB:2.42:Redbull Air Race Day 99





    I am not sure what space it is that has the, as you play updates the leaderboard (resistance is it?)

    To me Redbull really needs a leaderboard like this. I know many have mentioned this in the past. If Redbull had an as you play updates leaderboard we could have an air race day like a MVP event, ran weekly and open to all (apart from those who still cant access the game)

    Its one of the most popular minigames in the whole of Home, yet no MVP events can take place because of the leaderboard situation. I think this could be a great event if only we had what we need.

    My idea for the day

    Who would take part in an event like this?




    DB:2.42:Redbull Air Race Day 99


    Thanks for that Miln, first time Ive seen footage of the flugtag space. Still trying to catch up with a lot of developments since extended offline break. First impressions is it looks like a seperate space within Redbull. 2nd impression is it looks like a lot of fun, especially the flying machine

    I hope with an update they dont neglect the original space (if they are 2 different areas) as that could only be the deathnail for the original game. The original game, to me is still one of the best things about Home, It constantly challenges players to better their scores (something which I cant seem to do lately) and really needs an MVP Event to bring it back to full life and increase use of the space and make it more worthwhile in visiting it.




  • RELEVANCY SCORE 2.38

    DB:2.38:New Redbull Video a3



    The camera gose down to the ground!!!!

    Now i realy wanna take up this with the home signs they all point to something wierd.

    and also the messegs steve got from the room it´s that room that they show in the video.
    Message Edited by Jontis_00 on 23-02-2009 06:41 PM



    DB:2.38:New Redbull Video a3

    Looks more like a new Club house to me with a bowls in it.



    Join my Home community @ http://www.yourplaystationhome.com

  • RELEVANCY SCORE 2.32

    DB:2.32:Query Issue - It Does Not Work 39



    Hi there,

    i wrote this query:

    SELECT T1.[CardCode], T1.[CardName], SUM(T0.[DocTotal]),

    (Select Top 1 T2.Descr
    From UFD1 T2
    Where T2.FldValue = T1.U_gruppe AND TableID = 'ACRD' AND FieldID = '1') AS ' Kundengruppe'

    FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
    WHERE T0.[DocDate] = [%1] AND T0.[DocDate] = [%2]
    GROUP BY T1.[CardCode], T1.[CardName], T1.U_gruppe

    DB:2.32:Query Issue - It Does Not Work 39


    Hello.

    You have to put a ' character to date parameter.

    SELECT T1.[CardCode], T1.[CardName], SUM(T0.[DocTotal]),
    (Select Top 1 T2.Descr
    From UFD1 T2
    Where T2.FldValue = T1.U_gruppe AND TableID = 'ACRD' AND FieldID = '1') AS ' Kundengruppe'

    FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
    WHERE T0.[DocDate] BETWEEN '[%1]' AND '[%2]'
    GROUP BY T1.[CardCode], T1.[CardName], T1.U_gruppe

  • RELEVANCY SCORE 2.30

    DB:2.30:Required Sales Executive Name.............. 9f



    Hi Experts,

    How do I get Sales Executive name (From OSLP Table) in the following query?????????????????

    SELECT T0.Docnum,T0.DocStatus, T0.DocDate,T2.SeriesName,

    T0.CardCode, T0.CardName, T1.itemcode, T1.Dscription,T1.Quantity,T1.U_RQT RoboQuantity,

    T1.Price, T1.U_KM, T0.VatSum, T0.DocTotal, T0.DocTotalSy, T1.U_VHNO, T1.U_GRSRMR,T1.U_DCNO,T1.ShipDate,

    T0.comments FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

    INNER JOIN NNM1 T2 ON T0.Series = T2.Series

    WHERE (T0.DocDate = [%0] AND T0.DocDate = [%1]) and T2.SeriesName = [%2] and T0.CANCELED='N'

    Please do the needfull.

    Regards,

    Sree.

    DB:2.30:Required Sales Executive Name.............. 9f


    Hi Sree,

    You may try this one and add your UDF if works:

    SELECT T0.Docnum,T0.DocStatus, T0.DocDate,T2.SeriesName,

    T0.CardCode, T0.CardName, T3.SLPName, T1.itemcode, T1.Dscription,T1.Quantity,

    T1.Price, T0.VatSum, T0.DocTotal, T0.DocTotalSy,T1.ShipDate,T0.comments

    FROM dbo.ORDR T0 INNER JOIN dbo.RDR1 T1 ON T0.DocEntry = T1.DocEntry

    INNER JOIN dbo.NNM1 T2 ON T0.Series = T2.Series

    INNER JOIN dbo.OSLP T3 ON T0.SlpCode = T3.SlpCode

    WHERE T0.DocDate BETWEEN [%0\] AND [%1\] and T2.SeriesName = [%2\] and T0.CANCELED='N'

    Thanks,

    Gordon

  • RELEVANCY SCORE 2.28

    DB:2.28:Trading Redbull X2010 S.Vettel For F2007 7j



    Well, title says it pretty much..




    - MaderMike

    DB:2.28:Trading Redbull X2010 S.Vettel For F2007 7j


    mihgt be interested in f2007 as i have the f2010

  • RELEVANCY SCORE 2.28

    DB:2.28:Ifc Function mm



    PROCSQL;
    CREATE TABLE DATA.Claims_with_identifier AS
    SELECT t1.MEMBER,
    t1.ACCT,
    t1.PPRODUCT,
    /* Platform */
    (IFC(Left(t1.ACCT,1)='0','X','Y')) AS Platform
    FROM DATA.Claims t1;
    QUIT;

    DB:2.28:Ifc Function mm


    Different function. Left in access is like substr in SAS, but copies X characters starting from position 1. Left, in SAS, simply removes space characters from the left side of a field. You may not need it for your current code and only require the substr function.

  • RELEVANCY SCORE 2.28

    DB:2.28:Customer Wise Sales Register-All zj



    hi experts,

    i want a report to show 'item' wise, 'service' wise n both item n service combine whn user select 'I' , 'S' or null

    and date range thn it shud pic up data from below query

    Select t.cardcode, cardname, sum(Quantity) as Quantity, sum(Value) as Value, sum(Tax) as Tax,
    sum(Total) as Total, sum(materialvalue) as 'Material Value',sum(CM) as CM, (sum(CM)/SUM(VALUE)*100) as 'CM%' from
    (SELECT (T0.CardCode) as cardcode,t0.cardname as cardname, sum(T1.Quantity) as Quantity,
    sum(T1.[LineTotal]) as Value, sum(T1.[VatSum]) as Tax, sum( T1.[LineTotal] + T1.[VatSum]) as Total,
    isnull(sum(T1.Quantity * t2.u_cmmc),'00')as materialvalue ,
    isnull ((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) ,
    sum(T1.[LineTotal])) as CM ,
    ISNULL((((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) )/(sum(T1.[LineTotal]))*100),'100') as cm_perc
    FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry left outer join oitm t2
    on t1.itemcode = t2.itemcode
    *where T0.[DocType] = [%0] OR* t0.docdate = [%1] and t0.docdate = [%2]
    group by T0.CardName,T0.CardCode
    union
    SELECT (T0.[CardCode]) as cardcode, T0.[CardName], -sum(T1.[Quantity]) as Quantity,
    -sum(T1.[LineTotal]) as Value, -sum(T1.[VatSum]) as Tax, -sum( T1.[LineTotal] + T1.[VatSum]) as Total, isnull(-sum(T1.Quantity * t2.u_cmmc),'00') as materialvalue,
    isnull((-sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) ,
    -sum(T1.[LineTotal])) as CM ,
    ISNULL((((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum]))) / -(-sum(T1.[LineTotal]))*100),'100') as cm_perc
    FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry left outer join oitm t2
    on t1.itemcode = t2.itemcode
    *where T0.[DocType] = [%0] OR* t0.docdate = [%1] and t0.docdate = [%2]
    group by T0.CardName,T0.CardCode) as t
    group by t.cardname,t.cardcode

    DB:2.28:Customer Wise Sales Register-All zj


    Hi Ketan,

    Try this,

    SELECT K.[CardCode], K.[CardCode], K.[DocType], K.[DocDate], SUM(K.Quantity), SUM(K.Value), SUM(K.Tax),
    SUM(K.Total), SUM(K.Material Value), SUM(K.CM), SUM(K.cm_perc)
    FROM
    (SELECT T0.[CardCode], T0.[CardCode], T0.[DocType], T0.[DocDate],
    sum(T1.[Quantity]) as Quantity,
    sum(T1.[LineTotal]) as Value,
    sum(T1.[VatSum]) as Tax, sum( T1.[LineTotal] + T1.[VatSum]) as Total,
    isnull(sum(T1.Quantity * t2.u_cmmc),'00')as 'Material Value' ,
    isnull (sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc) ,sum(T1.[LineTotal])) as CM ,
    isnull(((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc) )/(sum(T1.[LineTotal]))*100),'100') as 'cm_perc'
    FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry left outer join oitm t2 on t1.itemcode = t2.itemcode

    UNION ALL

    SELECT T3.[CardCode], T3.[CardName], T3.[DocType], T3.[DocDate],
    -sum(T4.[Quantity]) as Quantity,
    -sum(T4.[LineTotal]) as Value,
    -sum(T4.[VatSum]) as Tax,
    -sum( T4.[LineTotal] + T4.[VatSum]) as Total,
    isnull(-sum(T4.Quantity * t5.u_cmmc),'00') as 'Material Value' ,
    isnull(-sum( T4.[LineTotal] + T4.[VatSum]) - sum(T4.Quantity * t5.u_cmmc) ,-sum(T4.[LineTotal]))as CM ,
    isnull(((sum( T4.[LineTotal] + T4.[VatSum]) - sum(T4.Quantity * t5.u_cmmc)) / (sum(T4.[LineTotal]))*100),'100') as 'cm_perc'
    FROM ORIN T3 INNER JOIN RIN1 T4 ON T3.DocEntry = T4.DocEntry left outer join oitm t5 on t5.itemcode = t4.itemcode)K
    WHERE
    K.[DocType]='[%0]'
    AND
    K.DocDate = '[%1]' AND K.DocDate = '[%2]'
    GROUP BY
    K.[CardCode], K.[CardCode], K.[DocType], K.[DocDate]
    ORDER BY
    K.[CardCode], K.[CardCode], K.[DocType], K.[DocDate]

  • RELEVANCY SCORE 2.26

    DB:2.26:Using Max Inside Count 39


    Having an error that says: ORA-00934 group function is not allowed here.

    The query I'm running is: select count(t1.item1||t1.item2|max(t1.item3)) from
    table1t1, table2 t2 where t1.item4=t2.item4 AND max(t1.item3 = t2.item3
    group by t1.item1,t1.item2,t1.item3

    -Thanks

  • RELEVANCY SCORE 2.26

    DB:2.26:Sql Report 8m



    Hi,

    i created an query but want to add an extra line per docnum with the doctotal of the invoice

    SELECT T0.DocNum, CONVERT (VARCHAR(10), T0.DocDate,5) AS [Invoice Date] , T1.BaseRef, T1.LineNum, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price, T1.LineTotal, SUM(T0.DocTotal) AS DocTotal FROM OINV T0

    INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

    WHERE T0.DocDate = '20110701' AND T0.DocDate = '20110731'

    GROUP BY T0.DocNum, T0.DocDate, T1.BaseRef, T1.LineNum,T1.ItemCode, T1.Dscription,

    T1.Quantity, T1.Price, T1.LineTotal

    How can i do this??

    Mark

    DB:2.26:Sql Report 8m


    Hi Mark......

    Try this........

    SELECT T0.DocNum, (CONVERT(VARCHAR(11),T0.DOCDATE,106)) AS 'Invoice Date' , T1.BaseRef, T1.LineNum, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price, T1.LineTotal, SUM(T0.DocTotal) AS DocTotal FROM OINV T0
    INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
    WHERE T0.DocDate = '20100401' AND T0.DocDate = '20110831'
    GROUP BY T0.DocNum, T0.DocDate, T1.BaseRef, T1.LineNum,T1.ItemCode, T1.Dscription,
    T1.Quantity, T1.Price, T1.LineTotal
    Union All
    SELECT T0.DocNum, Null, Null, Null, Null,'Total', Sum(T1.Quantity), Null, Sum(T1.LineTotal), SUM(T0.DocTotal) FROM OINV T0
    INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
    WHERE T0.DocDate = '20110701' AND T0.DocDate = '20110731'
    GROUP BY T0.DocNum, T0.DocDate, T1.BaseRef, T1.LineNum,T1.ItemCode, T1.Dscription,
    T1.Quantity, T1.Price, T1.LineTotal
    Order By 1,6

  • RELEVANCY SCORE 2.26

    DB:2.26:How To Get Redbull X1 Prototype? kk



    Heya title says it all really ^^ How do I get the Redbull X1?




    -------------------------------------------------------------------------http://twitter.com/bunnybabebecki

    DB:2.26:How To Get Redbull X1 Prototype? kk


    It is possible with a controller, even gold apparently.

    Tidgney is the only person I know that has completed this challenge with 2 golds and a silver using a pad.

    Have a look at these threads.

    http://community.eu.playstation.com/t5/Gran-Turismo/Sebastian-Vettel-challenge/td-p/12187686

    http://community.eu.playstation.com/t5/Gran-Turismo/Red-Bull-X10-challenge-difficulty/m-p/12227872#M...




  • RELEVANCY SCORE 2.26

    DB:2.26:Annual Sales Report (Month Wise) 9k



    Hi,

    I have following query, which shows annual sales (month wise). But when i run this query it shows all the parts (my mass production and Spare parts as well). I want it should show only MASS Production items only.

    The differencation i have specified at Item level property. If item property no. 5 (name SPARES) is check then it is SPARE item otherwise it is MASS Production.

    Thanks,

    Vincent

    ======================================================

    SELECT T0.ITEMCODE, T0.ItemName, T0.U_MODEL,

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC QTY'

    FROM dbo.OITM T0

    LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode

    WHERE T0.SellItem = 'Y'

    GROUP BY T0.ItemCode, T0.ItemName, T0.U_MODEL, YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

    DB:2.26:Annual Sales Report (Month Wise) 9k


    Hello,

    please, it is possible to do this query also for usage of another year? I would like to compare results from 2010 and 2009...e.g.

    Thank you

    Jonmar7

  • RELEVANCY SCORE 2.25

    DB:2.25:Alert Message Error 17


    Dear Experts,I have created a query based alert for the cash payment where invoice amount and payment not tallied then from system alert message should generate.This is my Query:SELECT T1.[Series],T1.[DocNum], T1.[DocDate], T1.[CardCode], T1.[CardName],T1.[CashAcct], T1.[CashSum], T1.[NoDocSum] as "BalanceDue", (T1.[CashSum] - T1.[NoDocSum]) as "InvoiceAmount"FROM [dbo].[OVPM] T1 WHERE isnull(T1.[CashAcct],'')'' and T1.[NoDocSum]0 and T1.[CashAcct]='ACH0000001' and (T1.[CashSum] - T1.[NoDocSum]) 0Please check and suggest your opinion in case of correction requiredThanks in advance...Regards,Agranshu

    DB:2.25:Alert Message Error 17

    Hi Agranshu,Try:SELECT T1.Series,T1.DocNum, T1.DocDate, T1.CardCode, T1.CardName,T1.CashAcct, T1.CashSum, T1.NoDocSum as 'BalanceDue', (T1.CashSum - T1.NoDocSum) as 'InvoiceAmount'FROM dbo.OVPM T1WHERE T1.NoDocSum !=0 and T1.CashSum !=0 and (T1.CashSum - T1.NoDocSum) != 0 and T1.CashAcct='ACH0000001'Thanks,Gordon

  • RELEVANCY SCORE 2.25

    DB:2.25:Re: Connect By Output 11.2.0.3 1a


    try this
    SQL
    SQL select substr(t1.c1, t1.l, 1) c1, t2.c2, t1.c1
    2 from ( select c1, level l from t1 connect by level = length(t1.c1) ) t1
    3 join t2
    4 on t2.c1 = substr(t1.c1, t1.l, 1)
    5 /
    C1 C2 C1
    ---- ---------- ----
    A 10 ABCD
    B 20 ABCD
    C 30 ABCD
    D 40 ABCD

    SQL
    ----Ramin Hashimzade

    DB:2.25:Re: Connect By Output 11.2.0.3 1a

    Well, attempting to say exactly what Oracle is doing without seeing the code is always going to be speculation to some extent but the documentation (and explain plan) implies that the join (including any join predicates) would be done first.Assuming LEVEL is 0 at this point (and that is not a side effect of my previous example's function call), this join would yield a single row. SQL SELECT t1.c1, t2.c1, t2.c2 2 FROM t1, t2 3 WHERE t2.c1 = substr(t1.c1, 0, 1);C1 C C2---- - ----------ABCD A 10SQLThe CONNECT BY is then performed without the (already considered) join predicate and it visits the single row four times before LEVEL LENGTH (t1_c1), returning exactly the result set you posted. Whether CONNECT BY LEVEL should ignore cycles is another discussion.I do agree with you that allowing LEVEL to be used in a join predicate when it returns an inappropriate value is unintuitive and probably should not be allowed.

  • RELEVANCY SCORE 2.25

    DB:2.25:Columns Are Not Displayed In The Order Specified In The Select Statement f8


    select t1.field1,t2.field1,t1.field2,t1.field3
    from table t1, table t2
    where t1.field4 = t2.field4

    Note: field1 and field3 should be varchar.
    field2 should be numeric

    output resulted is in following order

    t1.field1 t1.field2 t1.field1 t1.field3

    which is different from the specified order

    t1.field1 t2.field1t1.field2t1.field3

    Work around is using aliases.

    DB:2.25:Columns Are Not Displayed In The Order Specified In The Select Statement f8

    This is still a bug in 1.1.2.25.79, but is now fixed for the next release. Please note that using an alias, as suggested, or the Run Script (F5) shows the correct order of the output. The reordering happens for the Execute Statement command.

    Regards
    Sue

  • RELEVANCY SCORE 2.25

    DB:2.25:Year Sales Report j9



    Hello,

    please, could you give me an advice to change something use this query also for e.g 2009? This query shows only this year.

    Thank you,

    martina

    SELECT T0.ITEMCODE, T0.ItemName,

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC QTY'

    FROM dbo.OITM T0

    LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode

    WHERE T0.SellItem = 'Y'

    GROUP BY T0.ItemCode, T0.ItemName, YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

    DB:2.25:Year Sales Report j9


    Dear specialists,

    both last answers will help me a lot!!! I will try to do the same for purchase.

    Thank you,

    Martina

  • RELEVANCY SCORE 2.24

    DB:2.24:Left To Right Parsing j9


    hi all,
    I wnat to join two tables t1,t2. t1 contains 100000 records and t2
    contains 100 records.
    which query performs better?

    1. select t1.col1,t1.col2,t2.col1 from t1 inner join t2 on t1.col1=t2.col1
    2.1. select t1.col1,t1.col2,t2.col1 from t2 inner join t1 on t2.col1=t1.col1

    Pls show me a link to the details
    thanks

    DB:2.24:Left To Right Parsing j9

    In the simplest situation Oracle knows about extent number in tables and about
    row structure. Using these information it can choose the leading table which is
    t2 in your case.

    SQL create table t1 (col1 int, col2 int);

    Table created.

    SQL create table t2 (col1 int, col2 int);

    Table created.

    SQL insert into t1 select rownum, rownum from dual connect by level = 100000;

    100000 rows created.

    SQL insert into t2 select rownum, rownum from dual connect by level = 100;

    100 rows created.

    SQL commit;

    Commit complete.

    SQL explain plan for select t1.col1,t1.col2,t2.col1 from t1 inner join t2 on t1.col1=t2.col1;

    Explained.

    SQL select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------

    ----------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    ----------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 100 | 3900 | 60 (4)|
    |* 1 | HASH JOIN | | 100 | 3900 | 60 (4)|
    | 2 | TABLE ACCESS FULL| T2 | 100 | 1300 | 3 (0)|
    | 3 | TABLE ACCESS FULL| T1 | 85524 | 2171K| 56 (2)|
    ----------------------------------------------------------------

    Predicate Information (identified by operation id):

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    ---------------------------------------------------

    1 - access("T1"."COL1"="T2"."COL1")

    18 rows selected.

    SQL explain plan for select t1.col1,t1.col2,t2.col1 from t2 inner join t1 on t2.col1=t1.col1;

    Explained.

    SQL select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------

    ----------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    ----------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 100 | 3900 | 60 (4)|
    |* 1 | HASH JOIN | | 100 | 3900 | 60 (4)|
    | 2 | TABLE ACCESS FULL| T2 | 100 | 1300 | 3 (0)|
    | 3 | TABLE ACCESS FULL| T1 | 85524 | 2171K| 56 (2)|
    ----------------------------------------------------------------

    Predicate Information (identified by operation id):

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    ---------------------------------------------------

    1 - access("T2"."COL1"="T1"."COL1")

    18 rows selected.In the real world this depends on the statistic, indexes, etc..

    See Performance Tuning Guide

    Rgds.

  • RELEVANCY SCORE 2.24

    DB:2.24:Gran Turismo 6 Redbull Update ax



    Why doesn"t work Redbull challenge for X2014 fan car and standard car?

    DB:2.24:Gran Turismo 6 Redbull Update ax


    Nah, nothing but rumours and speculation.

    I've read everything from 'Community' to 'B-Spec'.

    Nothing but guess work though as far as I know.

    Maybe nothing ... just RedBull.

    We'll see.

    Oh, yep evening here.

    Good morning to you too.




  • RELEVANCY SCORE 2.24

    DB:2.24:Fast Recovery From T1 Failure In A Dual T1 Lease-Line Setup 17



    We're looking for the best technology solution to most quickly recover from a single T1 outage, in a dual T1 lease-line setup.

    Time sensistive application means we are looking for minimum recovery time from a failure.

    DB:2.24:Fast Recovery From T1 Failure In A Dual T1 Lease-Line Setup 17


    Hi

    Is it a point to point setup between these two locations ?

    if its the case have you configured dual static routes via the leased lines pointing towards the interface on which its terminated ?

    Also do throw some light on the current setup you have in place out there.

    regds

  • RELEVANCY SCORE 2.24

    DB:2.24:Cumulative Balance - Which Table? d1



    Hi there, I am writing a fairly simple query from JDT1, but i can't find the cumulative balance column in either ODJT or JDT1. is it in a different table or is there a formula i should write?

    thanks, my example query is below:

    SELECT T1.[TransId], T1.[Account], T1.[CreatedBy], T1.[ContraAct], T1.[LineMemo], T1.[Credit], T1.[Debit], T1.[DebCred], FROM [dbo].[JDT1] T1 INNER JOIN OJDT T2 ON T1.TransId = T2.TransId WHERE T1.[Account] = '[%0]' and T1.[RefDate] between '[%1]' and '[%2]'

  • RELEVANCY SCORE 2.24

    DB:2.24:Ta 908e "Dm/Css/Es 15 Min Threshold Exceeded" xa



    I have a couple of 908e units configured with data in on ETH 0/1, and PRI connections on NET 0/3 and 0/4 to legacy IVR equipment. We have configured SIP service with a couple of carriers over the data connection. This is working.

    However, we are getting a lot of these "threshold exceeded" errors. Here's a sampling:

    2013.09.25 12:45:03 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 12:46:13 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 12:46:25 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 12:46:30 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 12:46:43 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 13:00:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 13:01:12 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 13:01:41 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 13:01:54 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 13:02:07 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 13:15:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 13:16:09 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 13:16:13 T1.t1 0/4 DM 15 min threshold exceeded, ES 15 min threshold exceeded

    2013.09.25 13:16:26 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 13:30:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 13:31:12 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 13:31:28 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 13:31:37 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 13:31:51 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 13:45:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 13:46:03 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 13:46:09 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 13:46:13 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 13:47:02 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 14:00:03 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 14:01:13 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 14:01:21 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 14:01:32 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 14:01:34 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 14:15:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 14:16:10 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 14:16:12 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 14:16:45 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 14:16:58 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 14:30:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 14:31:13 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 14:31:17 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 14:31:40 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 14:32:10 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 14:45:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 14:46:13 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 14:46:18 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 14:46:28 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 14:46:42 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 15:00:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 15:00:56 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 15:01:12 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 15:01:53 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 15:02:06 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 15:15:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 15:16:11 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 15:16:13 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 15:16:25 T1.t1 0/3 CSS 15 min threshold exceeded, DM 15 min threshold exceeded

    2013.09.25 15:30:03 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 15:31:05 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 15:31:13 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 15:31:37 T1.t1 0/4 DM 15 min threshold exceeded

    2013.09.25 15:31:50 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 15:45:02 T1.t1 0/4 CSS 15 min threshold exceeded

    2013.09.25 15:46:09 T1.t1 0/3 DM 15 min threshold exceeded

    2013.09.25 15:46:12 T1.t1 0/4 ES 15 min threshold exceeded

    2013.09.25 15:46:35 T1.t1 0/3 CSS 15 min threshold exceeded

    2013.09.25 15:47:02 T1.t1 0/4 DM 15 min threshold exceeded

    From my research, it seems that these could be caused by a clock source problem. Our clock source is set to internal, since there is no T1 from a carrier involved. Is this a correct assumption?

    We are also experiencing lags in the audio between caller and recipient. It can reach as much as a second or more, which results in the parties talking over each other. I'm trying to determine if these two could be related. I also have the carrier exploring this issue from their end.

    DB:2.24:Ta 908e "Dm/Css/Es 15 Min Threshold Exceeded" xa


    At this point, all circuits have been clear of errors for the last 24 hours, with the exception of Adtran3, t1 0/4, which had 235 CSS/235 ES. The recommendations by jayh have reduced the errors dramatically, I'm marking as correct. I'm marking olivertwisted suggestion as helpful as well. Because of the external data T1, I cannot act on it, but it's a good idea, nonetheless, and would probably correct the remaining issue.

  • RELEVANCY SCORE 2.24

    DB:2.24:Redbull Fugtag Video 7j


    its so funny an epic lol

    DB:2.24:Redbull Fugtag Video 7j


    luke i just spoke to you on home, i said 'oh you're that guy who makes all those annoying threads on the forum' your exact words to me were "no i don't do those anymore lol"

    you've made two pointless ones in the last hour, stop making stupid threads please




    I like spelunker

  • RELEVANCY SCORE 2.23

    DB:2.23:Nextval For Sequence Via A Db Link 7k


    Hi,

    my select is like :

    select t1.col1,
    t1.col2,
    t2.col3,
    from table1@mylink t1,
    table2@mylink t2
    where t1.id =t2.id ;

    this select is very fast.

    but when i do:

    select t1.col1,
    t1.col2,
    t2.col3,
    myseq.nextval
    from table1@mylink t1,
    table2@mylink t2
    where t1.id =t2.id ;

    it will be very slow.

    what is the problem

    i have Oracle 9i S.E. on Linux v.4.

    Regards.

    DB:2.23:Nextval For Sequence Via A Db Link 7k

    Hi,

    my select is like :

    select t1.col1,
    t1.col2,
    t2.col3,
    from table1@mylink t1,
    table2@mylink t2
    where t1.id =t2.id ;

    this select is very fast.

    but when i do:

    select t1.col1,
    t1.col2,
    t2.col3,
    myseq.nextval
    from table1@mylink t1,
    table2@mylink t2
    where t1.id =t2.id ;

    it will be very slow.

    what is the problem

    i have Oracle 9i S.E. on Linux v.4.

    Regards.

  • RELEVANCY SCORE 2.23

    DB:2.23:Redbull X1 Prototype. c9



    Unvídeode este coche en Nüburgring.

    (sale al final)

    http://www.youtube.com/watch?v=zvxMEOO4a_E




    The whole religious complexion of the modern world is due to the absence from Jerusalem of a lunatic asylum.

    DB:2.23:Redbull X1 Prototype. c9


    Gracias por el video porque está impresionante.Pero por favor, para la proxima vez investigad o leed un poco por el foro para no repetir el hilo muchas veces. Si te fijas tienes un hilo con este coche un poco ma abajo. Aqui te pongo el enlace.Saludos.




    http://www.flickr.com/photos/tonimalaga/

  • RELEVANCY SCORE 2.23

    DB:2.23:Indexing Packaged Function... f3


    Hi,

    Is it possible to index packaged function which returns number. It would be used as shown below:

    select t1.X,
    t1.Y,
    t1.Z,
    p1.f1(t1.X, t1.Y, t1.Z),
    p1.f2(t1.X, t1.Y, t1.Z),
    p1.f3(t1.X, t1.Y, t1.Z)
    from tab1 t1
    where f1(t1.X, t1.Y, t1.Z) + f2(t1.X, t1.Y, t1.Z) + f2(t1.X, t1.Y, t1.Z) 0
    /

    In this scenario, is it possible to index the functions inside package p1.

    Thanks.

    - Pat

  • RELEVANCY SCORE 2.23

    DB:2.23:Conditional Data Show / Change Values To Be Shown For Months js



    Dear All,

    I am trying to make a report for Employee Compensation History with the following query

    SELECT T1.DocEntry, T2.Period, T2.u_payperiod,T0.dept, T3.Name, T0.empID, T0.firstName,
    T4.U_note,T0.lastName, T1.U_ebasic, T1.U_ehra, T1.U_etrspt, T1.U_ecola,
    (T1.U_ebasic + T1.U_ehra + T1.U_etrspt + T1.U_ecola) 'Sub-Total 1',
    T1.U_education,T1.U_grosssal, (T1.U_grosssal + T1.U_education) 'Sub-Total 2',
    T1.U_oadd, T1.U_enothour, T1.U_ebonus, (T1.U_oadd + T1.U_enothour + T1.U_ebonus) 'Additions Sub-Total-3',
    T1.U_dedhra, T1.U_dedtrans, T1.U_dedsa,
    T1.U_oded,T1.U_ducloan,(T1.U_dedhra + T1.U_dedtrans + T1.U_dedsa + T1.U_oded +T1.U_ducloan) 'Deductions Sub-Total-4',
    T1.U_netsal, T1.U_bank, T1.U_accno, T1.U_Remarks
    FROM dbo.OHEM T0
    INNER JOIN [dbo].[OUDP] T3 ON T3.Code = T0.dept
    INNER JOIN [dbo].[@PAYROLL_PR_DETAIL] T1 ON t1.U_ecode=t0.empid
    INNER JOIN [dbo].[@PAYROLL_PROLL_HEAD] T2 ON t2.docentry=t1.docentry
    INNER JOIN [dbo].[@PAYROLE_PP_HEAD] T4 ON T4.u_srlno=t2.u_payperiod

    DB:2.23:Conditional Data Show / Change Values To Be Shown For Months js


    Dear Graham,

    An excellent suggestion. The problem seems to be resolved.

    Mohammed : Thanks to you also for giving suggestions.

    Regards,

    Depika

  • RELEVANCY SCORE 2.23

    DB:2.23:Alert Generates Only For 'Manager' User 98


    Dear All,

    I am facing a problem in using a query for generating alerts....

    SELECT t1.DocNum as 'Requisition No.', t1.U_RequestBy, t1.U_SAPUser, t1.U_Status, t1.U_Approval, t1.U_Remarks, t1.U_ISType, t1.U_ReqTypeM FROM [@REQUISITIONMASTER] t1
    inner join OUSR t2 on t1.U_SAPUser=t2.U_NAME Where t1.U_Approval = 'Approval' and t1.U_Status = 'Open' and t2.USERID = $[USER]

    This query is poping up only for 'manager' user...Not for any other user...Please help me regarding this... What type of permission should this user have..?

    Any kind of help would be appreciated....

    Thanks

    DB:2.23:Alert Generates Only For 'Manager' User 98


    Hello Ankit,

    In the alert management window, you have defined the users that you want to receive that alert. So make sure that all those users are logged into the system (including the manager if he is defined to receive the alert).

    Wait for the alert to be triggered following the rule you set up in the Alert Management Window (hourly, daily, monthly, etc).

    Check if the alert is being sent.

    I had this same problem, and I found the behaviour.

    Kind regards

  • RELEVANCY SCORE 2.23

    DB:2.23:New Redbull Game 1m



    did annyone see the new redbull game at the video screen in presentation podium

    looks cool

    DB:2.23:New Redbull Game 1m


    This what the new redbull game is they do it every couple of years at the serpitine in hyde park.

    http://www.redbullflightlab.com/

    http://www.youtube.com/watch?v=TH9phxcKLXc




  • RELEVANCY SCORE 2.23

    DB:2.23:Sql: Select First 10 Records, Next 10, Etc 7j


    Hi, I need to select the first 10 records (from 0 to 10), then the next 10 (i.e from row 11 to 20), then from row 21 to 30 etc. from my database. My query is as follows:

    select t1.id,t1.total_amount,t1.paid_amount,t1.unpaid_amount,
    to_char(t1.date_created,'YYYY-MM-DD') date_created,
    t1.num_transactions,substr(t2.name,11,99) batch_type,t1.paid_transactions,
    t1.unpaid_transactions,t1.status
    from table1 t1,table2 t2
    where (t1.status = 'Confirmed' or t1.status = 'Rejected')
    and t1.batch_type_id = t2.id
    order by t1.date_created desc

    Thanx!

    DB:2.23:Sql: Select First 10 Records, Next 10, Etc 7j

    No idea.. after all, this forum is about SQL and PL/SQL. Not about XLS. Just who/what interprets that XSL page, creates a connection to Oracle and executes the SQL? The problem would appear to be there and not with the actual query as you have tested the query and determined it to work.

  • RELEVANCY SCORE 2.23

    DB:2.23:Re: Sql Query am


    I think this might work:

    select t1.empno, max(t1.year_month)
    from table t1
    where t1.year_month lt; '200908'
    and t1.sal lt;gt; (select t2.sal
    from table t2
    where t2.empno=t1.empno and t2.year_month='200908')
    group by t1.empno
    /

    DB:2.23:Re: Sql Query am

    Hope it helps now

    WITH t AS
    (
    SELECT 10 emp, 1000 salary, 200908 yr_mon
    FROM DUAL
    UNION ALL
    SELECT 10 emp, 1300 salary, 200905 yr_mon
    FROM DUAL
    UNION ALL
    SELECT 20, 1000, 200908
    FROM DUAL
    UNION ALL
    SELECT 30, 1200, 200908
    FROM DUAL
    UNION ALL
    SELECT 10, 1000, 200907
    FROM DUAL
    UNION ALL
    SELECT 20, 1200, 200907
    FROM DUAL
    UNION ALL
    SELECT 30, 1200, 200907
    FROM DUAL
    UNION ALL
    SELECT 10, 800, 200906
    FROM DUAL
    UNION ALL
    SELECT 20, 1200, 200906
    FROM DUAL
    UNION ALL
    SELECT 30, 1500, 200906
    FROM DUAL),
    cdata AS
    (
    SELECT *
    FROM (SELECT t.*,
    LAG (yr_mon) OVER (PARTITION BY emp ORDER BY yr_mon)
    last_date,
    LAG (salary) OVER (PARTITION BY emp ORDER BY yr_mon)
    last_sal,
    ROW_NUMBER () OVER (PARTITION BY emp ORDER BY yr_mon DESC)
    row_num
    FROM t) a
    WHERE row_num = 1),
    ldata AS
    (
    SELECT emp, yr_mon, salary, last_sal
    FROM (SELECT a.*,
    ROW_NUMBER () OVER (PARTITION BY emp ORDER BY yr_mon DESC)
    row_num
    FROM (SELECT emp, salary, yr_mon,
    LEAD (salary) OVER (PARTITION BY emp ORDER BY yr_mon)
    last_sal
    FROM t) a
    WHERE salary != last_sal) x
    WHERE row_num = 1)
    SELECT c.emp emp, c.yr_mon current_month, c.salary current_salary,
    c.last_date last_month, c.last_sal last_month_salary,
    l.yr_mon last_salary_change_date, l.salary last_changed_salary
    FROM cdata c, ldata l
    WHERE c.emp = l.emp (+)regards,
    Amit

    Added outer join in case emp is newly joined :p

    Edited by: amitgupta1202 on Aug 27, 2009 1:55 AM

  • RELEVANCY SCORE 2.22

    DB:2.22:Bind On Parameters Performance Problem 7s


    For some reason when I select bind on parameters in toplink I get a major performance hit for certain queries. I've enabled the toplink performance profiler to log the sql queries.

    I wrote a little Java class that executes these queries using a statement and using a preparedstatement. The prepared statement is fast, so I'm thinking something in toplink is slowing it down. Does anyone have any ideas?

    With bind:
    Begin profile of{ReadAllQuery(model.RKTask)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t1.ID, t1.ESTSTARTDATE, t1.HASMSGS, t1.HASRESOLVABLE, t1.PROGSTAT, t1.CSI, t1.ENTEREDBYID, t1.TASKCONSTRAINT, t1.PLANNEDCOMPLETIONDATE, t1.BILLINGRECORDID, t1.PARENTLAG, t1.HANDOFFDATE, t1.PLANNEDREVENUE, t1.INDENT, t1.LASTNOTEID, t1.ESTCOMPLETIONDATE, t1.DURTYPE, t1.MASTERTASKID, t1.WORKPERDAY, t1.CPI, t1.PLANNEDCOST, t1.GROUPID, t1.WORKINGDAYS, t1.MILESTONEID, t1.NUMOPENOPTASKS, t1.CATEGORYID, t1.ORIGINALDURATION, t1.HASNOTES, t1.HASEXPENSES, t1.TEMPLATETASKID, t1.ACTUALDURMINUTES, t1.ENTRYDATE, t1.ACTUALCOMPLETIONDATE, t1.ISCRITICAL, t1.ACTUALSTARTDATE, t1.PARENTID, t1.PERCENTCOMPLETE, t1.RESOURCESCOPE, t1.LASTUPDATEDATE, t1.PROJECTEDCOMPLETIONDATE, t1.CUSTOMERID, t1.ACTUALCOST, t1.APPROVALID, t1.ORIGINALWORKREQUIRED, t1.URL, t1.DURUNIT, t1.COMPLETIONPENDINGDATE, t1.PLANNEDSTARTDATE, t1.DURMINUTES, t1.EAC, t1.AUDITTYPES, t1.COSTAMOUNT, t1.EXTREFID, t1.RESERVEDTIMEID, t1.NAME, t1.ACTUALREVENUE, t1.PARENTLAGTYPE, t1.COSTTYPE, t1.TASKNUMBER, t1.PRIORITY, t1.ASSIGNEDTOID, t1.HASTIMEDNOTIFICATIONS, t1.PROJECTID, t1.ACTUALWORK, t1.WORKREQUIRED, t1.SPI, t1.LASTUPDATEDBYID, t1.STATUS, t1.APPROVALREQUIRED, t1.ROLEID, t1.NUMBEROFCHILDREN, t1.REVENUETYPE, t1.TRACKINGMODE, t1.HASDOCUMENTS, t1.BILLINGAMOUNT, t1.DESCRIPTION, t1.PROJECTEDSTARTDATE, t1.CONSTRAINTDATE FROM T_PROJECTS t0, T_ASSIGNMENTS t2, T_TASKS t1 WHERE ((((((t0.STATUS = ?) AND (t1.STATUS IN (?, ?))) AND (t1.PLANNEDSTARTDATE = ?)) AND (t2.ASSIGNEDTOID = ?)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN ? AND ?) AND (t2.ASSIGNEDTOID = ?))) AND ((t0.ID = t1.PROJECTID) AND (t2.TASKID = t1.ID)))
    bind = [CUR, NEW, INP, 1207375200000, 21824, 1206943200000, 1207461599999, 21824]
    Begin profile of{ReadAllQuery(model.RKAssignment)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t0.ID, t0.PROJECTID, t0.AVGWORKPERDAY, t0.ASSIGNMENTPERCENT, t0.ROLEID, t0.ASSIGNEDTOID, t0.OPTASKID, t0.ISPRIMARY, t0.WORKREQUIRED, t0.WORKINGDAYS, t0.CUSTOMERID, t0.WORKPERDAY, t0.TASKID FROM T_ASSIGNMENTS t3, T_PROJECTS t2, T_TASKS t1, T_ASSIGNMENTS t0 WHERE (((t0.TASKID = t1.ID) AND (((((t2.STATUS = ?) AND (t1.STATUS IN (?, ?))) AND (t1.PLANNEDSTARTDATE = ?)) AND (t3.ASSIGNEDTOID = ?)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN ? AND ?) AND (t3.ASSIGNEDTOID = ?)))) AND ((t2.ID = t1.PROJECTID) AND (t3.TASKID = t1.ID)))
    bind = [CUR, NEW, INP, 1207375200000, 21824, 1206943200000, 1207461599999, 21824]

    Profile(ReadAllQuery,
    class=model.RKAssignment,
    number of objects=2,
    total time=5742,
    local time=5742,
    sql prepare=1,
    sql execute=5736,
    logging=1,
    time/object=2871,
    )
    }End profile

    Begin profile of{ReadAllQuery(model.RKPredecessor)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t0.SUCCESSORID, t0.PREDECESSORID, t0.LAGDAYS, t0.DEPENDENCYTYPE, t0.ISCP, t0.LAGTYPE, t0.ISENFORCED FROM T_ASSIGNMENTS t3, T_PROJECTS t2, T_TASKS t1, T_PREDECESSORS t0 WHERE (((t0.SUCCESSORID = t1.ID) AND (((((t2.STATUS = ?) AND (t1.STATUS IN (?, ?))) AND (t1.PLANNEDSTARTDATE = ?)) AND (t3.ASSIGNEDTOID = ?)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN ? AND ?) AND (t3.ASSIGNEDTOID = ?)))) AND ((t2.ID = t1.PROJECTID) AND (t3.TASKID = t1.ID)))
    bind = [CUR, NEW, INP, 1207375200000, 21824, 1206943200000, 1207461599999, 21824]
    Profile(ReadAllQuery,
    class=model.RKPredecessor,
    total time=5754,
    local time=5754,
    logging=1,
    sql execute=5753,
    )
    }End profile
    Profile(ReadAllQuery,
    class=model.RKTask,
    number of objects=1,
    total time=17760,
    local time=6264,
    profiling time=1,
    cache=1,
    query prepare=1,
    sql execute=6258,
    object building=4,
    logging=1,
    sql generation=3,
    time/object=17760,
    )
    }End profile

    Without Bind

    Begin profile of{ReadAllQuery( model.RKTask)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t1.ID, t1.ESTSTARTDATE, t1.HASMSGS, t1.HASRESOLVABLE, t1.PROGSTAT, t1.CSI, t1.ENTEREDBYID, t1.TASKCONSTRAINT, t1.PLANNEDCOMPLETIONDATE, t1.BILLINGRECORDID, t1.PARENTLAG, t1.HANDOFFDATE, t1.PLANNEDREVENUE, t1.INDENT, t1.LASTNOTEID, t1.ESTCOMPLETIONDATE, t1.DURTYPE, t1.MASTERTASKID, t1.WORKPERDAY, t1.CPI, t1.PLANNEDCOST, t1.GROUPID, t1.WORKINGDAYS, t1.MILESTONEID, t1.NUMOPENOPTASKS, t1.CATEGORYID, t1.ORIGINALDURATION, t1.HASNOTES, t1.HASEXPENSES, t1.TEMPLATETASKID, t1.ACTUALDURMINUTES, t1.ENTRYDATE, t1.ACTUALCOMPLETIONDATE, t1.ISCRITICAL, t1.ACTUALSTARTDATE, t1.PARENTID, t1.PERCENTCOMPLETE, t1.RESOURCESCOPE, t1.LASTUPDATEDATE, t1.PROJECTEDCOMPLETIONDATE, t1.CUSTOMERID, t1.ACTUALCOST, t1.APPROVALID, t1.ORIGINALWORKREQUIRED, t1.URL, t1.DURUNIT, t1.COMPLETIONPENDINGDATE, t1.PLANNEDSTARTDATE, t1.DURMINUTES, t1.EAC, t1.AUDITTYPES, t1.COSTAMOUNT, t1.EXTREFID, t1.RESERVEDTIMEID, t1.NAME, t1.ACTUALREVENUE, t1.PARENTLAGTYPE, t1.COSTTYPE, t1.TASKNUMBER, t1.PRIORITY, t1.ASSIGNEDTOID, t1.HASTIMEDNOTIFICATIONS, t1.PROJECTID, t1.ACTUALWORK, t1.WORKREQUIRED, t1.SPI, t1.LASTUPDATEDBYID, t1.STATUS, t1.APPROVALREQUIRED, t1.ROLEID, t1.NUMBEROFCHILDREN, t1.REVENUETYPE, t1.TRACKINGMODE, t1.HASDOCUMENTS, t1.BILLINGAMOUNT, t1.DESCRIPTION, t1.PROJECTEDSTARTDATE, t1.CONSTRAINTDATE FROM T_PROJECTS t0, T_ASSIGNMENTS t2, T_TASKS t1 WHERE ((((((t0.STATUS = 'CUR') AND (t1.STATUS IN ('NEW', 'INP'))) AND (t1.PLANNEDSTARTDATE = 1207375200000)) AND (t2.ASSIGNEDTOID = 21824)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN 1206943200000 AND 1207461599999) AND (t2.ASSIGNEDTOID = 21824))) AND ((t0.ID = t1.PROJECTID) AND (t2.TASKID = t1.ID)))

    Begin profile of{ReadAllQuery( model.RKAssignment)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t0.ID, t0.PROJECTID, t0.AVGWORKPERDAY, t0.ASSIGNMENTPERCENT, t0.ROLEID, t0.ASSIGNEDTOID, t0.OPTASKID, t0.ISPRIMARY, t0.WORKREQUIRED, t0.WORKINGDAYS, t0.CUSTOMERID, t0.WORKPERDAY, t0.TASKID FROM T_ASSIGNMENTS t3, T_PROJECTS t2, T_TASKS t1, T_ASSIGNMENTS t0 WHERE (((t0.TASKID = t1.ID) AND (((((t2.STATUS = 'CUR') AND (t1.STATUS IN ('NEW', 'INP'))) AND (t1.PLANNEDSTARTDATE = 1207375200000)) AND (t3.ASSIGNEDTOID = 21824)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN 1206943200000 AND 1207461599999) AND (t3.ASSIGNEDTOID = 21824)))) AND ((t2.ID = t1.PROJECTID) AND (t3.TASKID = t1.ID)))
    Profile(ReadAllQuery,
    class=model.RKAssignment,
    number of objects=2,
    total time=178,
    local time=178,
    sql execute=153,
    logging=11,
    time/object=89,
    objects/second=11,
    )
    }End profile

    Begin profile of{ReadAllQuery( model.RKPredecessor)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t0.SUCCESSORID, t0.PREDECESSORID, t0.LAGDAYS, t0.DEPENDENCYTYPE, t0.ISCP, t0.LAGTYPE, t0.ISENFORCED FROM T_ASSIGNMENTS t3, T_PROJECTS t2, T_TASKS t1, T_PREDECESSORS t0 WHERE (((t0.SUCCESSORID = t1.ID) AND (((((t2.STATUS = 'CUR') AND (t1.STATUS IN ('NEW', 'INP'))) AND (t1.PLANNEDSTARTDATE = 1207375200000)) AND (t3.ASSIGNEDTOID = 21824)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN 1206943200000 AND 1207461599999) AND (t3.ASSIGNEDTOID = 21824)))) AND ((t2.ID = t1.PROJECTID) AND (t3.TASKID = t1.ID)))
    Profile(ReadAllQuery,
    class=model.RKPredecessor,
    total time=91,
    local time=91,
    logging=1,
    sql execute=90,
    )
    }End profile
    Profile(ReadAllQuery,
    class=model.RKTask,
    number of objects=1,
    total time=336,
    local time=67,
    profiling time=2,
    query prepare=1,
    sql execute=62,
    object building=3,
    logging=1,
    sql generation=2,
    time/object=336,
    objects/second=2,
    )
    }End profile

    DB:2.22:Bind On Parameters Performance Problem 7s

    For some reason when I select bind on parameters in toplink I get a major performance hit for certain queries. I've enabled the toplink performance profiler to log the sql queries.

    I wrote a little Java class that executes these queries using a statement and using a preparedstatement. The prepared statement is fast, so I'm thinking something in toplink is slowing it down. Does anyone have any ideas?

    With bind:
    Begin profile of{ReadAllQuery(model.RKTask)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t1.ID, t1.ESTSTARTDATE, t1.HASMSGS, t1.HASRESOLVABLE, t1.PROGSTAT, t1.CSI, t1.ENTEREDBYID, t1.TASKCONSTRAINT, t1.PLANNEDCOMPLETIONDATE, t1.BILLINGRECORDID, t1.PARENTLAG, t1.HANDOFFDATE, t1.PLANNEDREVENUE, t1.INDENT, t1.LASTNOTEID, t1.ESTCOMPLETIONDATE, t1.DURTYPE, t1.MASTERTASKID, t1.WORKPERDAY, t1.CPI, t1.PLANNEDCOST, t1.GROUPID, t1.WORKINGDAYS, t1.MILESTONEID, t1.NUMOPENOPTASKS, t1.CATEGORYID, t1.ORIGINALDURATION, t1.HASNOTES, t1.HASEXPENSES, t1.TEMPLATETASKID, t1.ACTUALDURMINUTES, t1.ENTRYDATE, t1.ACTUALCOMPLETIONDATE, t1.ISCRITICAL, t1.ACTUALSTARTDATE, t1.PARENTID, t1.PERCENTCOMPLETE, t1.RESOURCESCOPE, t1.LASTUPDATEDATE, t1.PROJECTEDCOMPLETIONDATE, t1.CUSTOMERID, t1.ACTUALCOST, t1.APPROVALID, t1.ORIGINALWORKREQUIRED, t1.URL, t1.DURUNIT, t1.COMPLETIONPENDINGDATE, t1.PLANNEDSTARTDATE, t1.DURMINUTES, t1.EAC, t1.AUDITTYPES, t1.COSTAMOUNT, t1.EXTREFID, t1.RESERVEDTIMEID, t1.NAME, t1.ACTUALREVENUE, t1.PARENTLAGTYPE, t1.COSTTYPE, t1.TASKNUMBER, t1.PRIORITY, t1.ASSIGNEDTOID, t1.HASTIMEDNOTIFICATIONS, t1.PROJECTID, t1.ACTUALWORK, t1.WORKREQUIRED, t1.SPI, t1.LASTUPDATEDBYID, t1.STATUS, t1.APPROVALREQUIRED, t1.ROLEID, t1.NUMBEROFCHILDREN, t1.REVENUETYPE, t1.TRACKINGMODE, t1.HASDOCUMENTS, t1.BILLINGAMOUNT, t1.DESCRIPTION, t1.PROJECTEDSTARTDATE, t1.CONSTRAINTDATE FROM T_PROJECTS t0, T_ASSIGNMENTS t2, T_TASKS t1 WHERE ((((((t0.STATUS = ?) AND (t1.STATUS IN (?, ?))) AND (t1.PLANNEDSTARTDATE = ?)) AND (t2.ASSIGNEDTOID = ?)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN ? AND ?) AND (t2.ASSIGNEDTOID = ?))) AND ((t0.ID = t1.PROJECTID) AND (t2.TASKID = t1.ID)))
    bind = [CUR, NEW, INP, 1207375200000, 21824, 1206943200000, 1207461599999, 21824]
    Begin profile of{ReadAllQuery(model.RKAssignment)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t0.ID, t0.PROJECTID, t0.AVGWORKPERDAY, t0.ASSIGNMENTPERCENT, t0.ROLEID, t0.ASSIGNEDTOID, t0.OPTASKID, t0.ISPRIMARY, t0.WORKREQUIRED, t0.WORKINGDAYS, t0.CUSTOMERID, t0.WORKPERDAY, t0.TASKID FROM T_ASSIGNMENTS t3, T_PROJECTS t2, T_TASKS t1, T_ASSIGNMENTS t0 WHERE (((t0.TASKID = t1.ID) AND (((((t2.STATUS = ?) AND (t1.STATUS IN (?, ?))) AND (t1.PLANNEDSTARTDATE = ?)) AND (t3.ASSIGNEDTOID = ?)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN ? AND ?) AND (t3.ASSIGNEDTOID = ?)))) AND ((t2.ID = t1.PROJECTID) AND (t3.TASKID = t1.ID)))
    bind = [CUR, NEW, INP, 1207375200000, 21824, 1206943200000, 1207461599999, 21824]

    Profile(ReadAllQuery,
    class=model.RKAssignment,
    number of objects=2,
    total time=5742,
    local time=5742,
    sql prepare=1,
    sql execute=5736,
    logging=1,
    time/object=2871,
    )
    }End profile

    Begin profile of{ReadAllQuery(model.RKPredecessor)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t0.SUCCESSORID, t0.PREDECESSORID, t0.LAGDAYS, t0.DEPENDENCYTYPE, t0.ISCP, t0.LAGTYPE, t0.ISENFORCED FROM T_ASSIGNMENTS t3, T_PROJECTS t2, T_TASKS t1, T_PREDECESSORS t0 WHERE (((t0.SUCCESSORID = t1.ID) AND (((((t2.STATUS = ?) AND (t1.STATUS IN (?, ?))) AND (t1.PLANNEDSTARTDATE = ?)) AND (t3.ASSIGNEDTOID = ?)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN ? AND ?) AND (t3.ASSIGNEDTOID = ?)))) AND ((t2.ID = t1.PROJECTID) AND (t3.TASKID = t1.ID)))
    bind = [CUR, NEW, INP, 1207375200000, 21824, 1206943200000, 1207461599999, 21824]
    Profile(ReadAllQuery,
    class=model.RKPredecessor,
    total time=5754,
    local time=5754,
    logging=1,
    sql execute=5753,
    )
    }End profile
    Profile(ReadAllQuery,
    class=model.RKTask,
    number of objects=1,
    total time=17760,
    local time=6264,
    profiling time=1,
    cache=1,
    query prepare=1,
    sql execute=6258,
    object building=4,
    logging=1,
    sql generation=3,
    time/object=17760,
    )
    }End profile

    Without Bind

    Begin profile of{ReadAllQuery( model.RKTask)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t1.ID, t1.ESTSTARTDATE, t1.HASMSGS, t1.HASRESOLVABLE, t1.PROGSTAT, t1.CSI, t1.ENTEREDBYID, t1.TASKCONSTRAINT, t1.PLANNEDCOMPLETIONDATE, t1.BILLINGRECORDID, t1.PARENTLAG, t1.HANDOFFDATE, t1.PLANNEDREVENUE, t1.INDENT, t1.LASTNOTEID, t1.ESTCOMPLETIONDATE, t1.DURTYPE, t1.MASTERTASKID, t1.WORKPERDAY, t1.CPI, t1.PLANNEDCOST, t1.GROUPID, t1.WORKINGDAYS, t1.MILESTONEID, t1.NUMOPENOPTASKS, t1.CATEGORYID, t1.ORIGINALDURATION, t1.HASNOTES, t1.HASEXPENSES, t1.TEMPLATETASKID, t1.ACTUALDURMINUTES, t1.ENTRYDATE, t1.ACTUALCOMPLETIONDATE, t1.ISCRITICAL, t1.ACTUALSTARTDATE, t1.PARENTID, t1.PERCENTCOMPLETE, t1.RESOURCESCOPE, t1.LASTUPDATEDATE, t1.PROJECTEDCOMPLETIONDATE, t1.CUSTOMERID, t1.ACTUALCOST, t1.APPROVALID, t1.ORIGINALWORKREQUIRED, t1.URL, t1.DURUNIT, t1.COMPLETIONPENDINGDATE, t1.PLANNEDSTARTDATE, t1.DURMINUTES, t1.EAC, t1.AUDITTYPES, t1.COSTAMOUNT, t1.EXTREFID, t1.RESERVEDTIMEID, t1.NAME, t1.ACTUALREVENUE, t1.PARENTLAGTYPE, t1.COSTTYPE, t1.TASKNUMBER, t1.PRIORITY, t1.ASSIGNEDTOID, t1.HASTIMEDNOTIFICATIONS, t1.PROJECTID, t1.ACTUALWORK, t1.WORKREQUIRED, t1.SPI, t1.LASTUPDATEDBYID, t1.STATUS, t1.APPROVALREQUIRED, t1.ROLEID, t1.NUMBEROFCHILDREN, t1.REVENUETYPE, t1.TRACKINGMODE, t1.HASDOCUMENTS, t1.BILLINGAMOUNT, t1.DESCRIPTION, t1.PROJECTEDSTARTDATE, t1.CONSTRAINTDATE FROM T_PROJECTS t0, T_ASSIGNMENTS t2, T_TASKS t1 WHERE ((((((t0.STATUS = 'CUR') AND (t1.STATUS IN ('NEW', 'INP'))) AND (t1.PLANNEDSTARTDATE = 1207375200000)) AND (t2.ASSIGNEDTOID = 21824)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN 1206943200000 AND 1207461599999) AND (t2.ASSIGNEDTOID = 21824))) AND ((t0.ID = t1.PROJECTID) AND (t2.TASKID = t1.ID)))

    Begin profile of{ReadAllQuery( model.RKAssignment)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t0.ID, t0.PROJECTID, t0.AVGWORKPERDAY, t0.ASSIGNMENTPERCENT, t0.ROLEID, t0.ASSIGNEDTOID, t0.OPTASKID, t0.ISPRIMARY, t0.WORKREQUIRED, t0.WORKINGDAYS, t0.CUSTOMERID, t0.WORKPERDAY, t0.TASKID FROM T_ASSIGNMENTS t3, T_PROJECTS t2, T_TASKS t1, T_ASSIGNMENTS t0 WHERE (((t0.TASKID = t1.ID) AND (((((t2.STATUS = 'CUR') AND (t1.STATUS IN ('NEW', 'INP'))) AND (t1.PLANNEDSTARTDATE = 1207375200000)) AND (t3.ASSIGNEDTOID = 21824)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN 1206943200000 AND 1207461599999) AND (t3.ASSIGNEDTOID = 21824)))) AND ((t2.ID = t1.PROJECTID) AND (t3.TASKID = t1.ID)))
    Profile(ReadAllQuery,
    class=model.RKAssignment,
    number of objects=2,
    total time=178,
    local time=178,
    sql execute=153,
    logging=11,
    time/object=89,
    objects/second=11,
    )
    }End profile

    Begin profile of{ReadAllQuery( model.RKPredecessor)
    INFO [Log4JSessionLog.log()]: SELECT DISTINCT t0.SUCCESSORID, t0.PREDECESSORID, t0.LAGDAYS, t0.DEPENDENCYTYPE, t0.ISCP, t0.LAGTYPE, t0.ISENFORCED FROM T_ASSIGNMENTS t3, T_PROJECTS t2, T_TASKS t1, T_PREDECESSORS t0 WHERE (((t0.SUCCESSORID = t1.ID) AND (((((t2.STATUS = 'CUR') AND (t1.STATUS IN ('NEW', 'INP'))) AND (t1.PLANNEDSTARTDATE = 1207375200000)) AND (t3.ASSIGNEDTOID = 21824)) OR ((t1.ACTUALCOMPLETIONDATE BETWEEN 1206943200000 AND 1207461599999) AND (t3.ASSIGNEDTOID = 21824)))) AND ((t2.ID = t1.PROJECTID) AND (t3.TASKID = t1.ID)))
    Profile(ReadAllQuery,
    class=model.RKPredecessor,
    total time=91,
    local time=91,
    logging=1,
    sql execute=90,
    )
    }End profile
    Profile(ReadAllQuery,
    class=model.RKTask,
    number of objects=1,
    total time=336,
    local time=67,
    profiling time=2,
    query prepare=1,
    sql execute=62,
    object building=3,
    logging=1,
    sql generation=2,
    time/object=336,
    objects/second=2,
    )
    }End profile

  • RELEVANCY SCORE 2.22

    DB:2.22:Redbull X 2010 kj



    nu vettel wereldkampioen is krijgt iedereen deze auto zal er dan 5 hebben

    DB:2.22:Redbull X 2010 kj


    En het zal ook wel een tijdelijke actie zijn neem ik aan ?

    Als ik in de lijst kijk komt er bij mij geen x11 tevoorschijn. Niet zo'n probleem want zo geweldig vind ik die wagen nou ook weer niet, maar wel apart.

  • RELEVANCY SCORE 2.22

    DB:2.22:Coalesce Error az



    Please input

    Error: Conversion failed when converting datetime from character string .... close to the coalesce

    SELECT

    (t1.First_Name + ' ' + t1.Last_Name + '('+ t1.Work_Status + ')')
    As Name

    ,
    t1.EID

    ,
    t1.CSGID

    ,
    t1.CCFRole

    ,
    t1.CCFLocationID

    , t1.ModifyDate AS
    Updated

    --, t1.logindate

    --, t2.LastLogIn

    ,COALESCE(COALESCE(t2.LastLogIn, t1.LoginDate),'Never')as [Logged In
    ]

    --, t1.CCFLevelID

    --, t2.SecurityLevel_ID

    FROM

    CCFUser
    t1

    LEFT OUTER JOIN

    User_TBL
    t2

    ON

    t1.First_Name =
    t2.first_name

    AND

    t1.Last_Name = t2.Last_name

    WHERE

    t1.CCFLevelID IN (2, 4) And t1.Deleted =
    0

    DB:2.22:Coalesce Error az


    Different db's have different functions that convert numbers and dates to strings. The names include, cast, to_char, and string. Let's say you are using oracle. You want

    coalesce(to_char(date1), to_char(date2), 'never')

  • RELEVANCY SCORE 2.21

    DB:2.21:Please Explain Ranking And Partition? 1m


    I'm familiar with basic SQL and PL/SQL. I've encountered the following clause in a query, which I don't understand: SELECT... T1.valueA, T1.dateValue, T1.valueB, T1.dateValue_2, IsCurrent FROM (SELECT decode(row_number() over ( partition by T1.valueA order by T1.dateValue asc), 1,DECODE(sign(T1.dateValue - sysdate),1,'N','Y'), 'N') as IsCurrent , 'N' as IsCancellable ,dense_rank () over ( partition by T1.valueA order by T1.valueB desc, T1.dateValue desc, T1.dateValue_2 desc) as rank FROM T1 etc...

    DB:2.21:Please Explain Ranking And Partition? 1m

    ah, a very concise and important distinction. thank you :-)

  • RELEVANCY SCORE 2.21

    DB:2.21:? In Sql Queries And Not Using Prepared Statements dd


    Using EclipseLink 1.1.1
    Prepared Statements are disabled

    In our production server something went wrong and one of our Read Queries started erroring. A DatabaseException was thrown and we log the "getQuery.getSQLString()" statement. We found this query in the logs:
    SELECT t1.ID, t1.NAME, t1.DESCRIPTION, t1.EXTREFID, t1.STYLESHEET, t1.DDSVNVERSION, t1.FIRSTNAME, t1.LASTNAME, t1.EMAILADDR, t1.PHONENUMBER, t1.ADDRESS, t1.ADDRESS2, t1.CITY, t1.STATE, t1.POSTALCODE, t1.COUNTRY, t1.ADMINACCTNAME, t1.HASDOCUMENTS, t1.HASTIMEDNOTIFICATIONS, t1.STATUS, t1.ENTRYDATE, t1.EVALEXPDATE, t1.LASTREMINDDATE, t1.FULLUSERS, t1.LIMUSERS, t1.REQUSERS, t1.ISENTERPRISE, t1.EXPDATE, t1.ISDISABLED, t1.DISABLEDDATE, t1.NEEDLICENSEAGREEMENT, t1.ISWARNINGDISABLED, t1.LOCALE, t1.TIMEZONE, t1.CURRENCY, t1.DOMAIN, t1.DOCUMENTSIZE, t1.EXTRADOCUMENTSTORAGE, t1.ONDEMANDOPTIONS, t1.SSOTYPE, t1.RESELLERID, t1.ACCOUNTREPID, t1.LASTUSAGEREPORTDATE, t1.NEXTUSAGEREPORTDATE, t1.USAGEREPORTATTEMPTS FROM T_SSOOPTIONS t0, T_CUSTOMERS t1 WHERE *((((t0.SSOENABLED = ?) AND (t1.SSOTYPE IN (?, ?))) AND (UPPER(t1.DOMAIN) = ?)) AND (t0.CUSTOMERID = t1.ID))*

    Notice the values weren't entered into the where clause. We had to bounce the application to fix the problem. I've never seen this before. I've added more debugging statements to the code - so if this happens again in the future I'll have more information to report on. In the mean time I'm wondering if anyone else has every seen a problem of this nature.

    DB:2.21:? In Sql Queries And Not Using Prepared Statements dd

    Database error due to invalid SQL statement.

    I don't have a stack, we were catching the exception and not printing the stack :(
    Like I mentioned in my first post, I added more debugging code (e.printStackTrace()). I understand this is hard to track down without more information. I was just hoping you guys had seen something like this before and had any insight. Like I mentioned before: this is on our production server. I've never seen this type of error before. That particular server (we run in a cluster mode) had been up for several days and then started generating that error. IT bounced the node and everything went back to normal. We have been using toplink for about 5 years now and have never seen this problem, until August 3rd 2009. The only thing that has changed recently is our migration from toplink 10 to EclipseLink. I was wondering if anyone knows if anything had changed in EclipseLink/toplink 11 with the generation of SQL queries.

    I'll keep looking. There is more debugging code in there now. Since the error was "Database error due to invalid SQL statement" this implies the SQL was generated, exited that part of the code and was sent to the db where it failed. I'm afraid the printStackTrace won't help if this error happens again.

  • RELEVANCY SCORE 2.21

    DB:2.21:This Redbull Does Have Wings! 8d


    So Here I was in my RBX2010 doing the last race on the Dream car race. Going along the Nuburgring approaching the chicane (schumacher) before you turn left onto the Nordschleiffe. A momentary lapse of concentration and my X2010 was airborne and a 360 flip in mid air. Mark webber would be impressed.Heres the pics.

    *****? eep!!

    Houston we have lift off!

    Err Houston I can see my house from here!!

    You sure its not Mark Webber in that RedBull?

    Oh sterwardess, are we there yet?

    Touchdown well kinda.

    Thank you for flying Redbull X2010 Airways. We hope you enjoyed you flight if rather brief.

    I hope to get a vid soted.

    Cheers

    Hentis




    DB:2.21:This Redbull Does Have Wings! 8d

    Haha nice flip and perfect landing too!That video made my day



  • RELEVANCY SCORE 2.21

    DB:2.21:Sales Data In 2 Queries Do Not Match fd



    Hello -

    We have two queries shown below - Inventory and Sales Analysis Queries. However, the YTD Sold Qty in the Inventory Query does not seem to match the Jan Qty sales in the Sales Analysis Query.

    Shouldn't the sum of the Jan total in Sales Analysis match the YTD Sold Qty in the Inventory Query? Please help?!

    Inventory Query:

    SELECT t1.ItemCode, t1.ItemName, (Case When t1.Lastpurprc = 0

    then t1.LstEvlPric else t1.Lastpurprc end) as Price, t1.OnHand as

    'QTY On Hand', T1.OnOrder as 'QTY On PO', T1.IsCommited as

    'QTY Committed', sum(t0.Quantity) as 'YTD Sold Qty',

    sum(t0.linetotal) AS 'YTD Sold Dollar Amt'

    FROM DBO.OITM t1

    Left Join DBO.INV1 t0 on t0.itemcode = t1.itemcode and

    Year(T0.ShipDate) = Year(Getdate())

    WHERE t1.InvntItem = 'Y'

    Group by t1.ItemCode, t1.ItemName, t1.Lastpurprc, t1.LstEvlPric,

    t1.OnHand, T1.OnOrder, T1.IsCommited

    Sales Analysis Query:

    SELECT T0.ITEMCODE,

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'JAN QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'FEB QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'MAR QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'APR QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'MAY QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'JUN QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'JUL QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'AUG QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'SEP QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'OCT QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'NOV QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'DEC QTY'

    FROM [dbo].[OITM] T0

    LEFT JOIN [dbo].[INV1] T1 ON T1.ItemCode = T0.ItemCode

    WHERE T0.SellItem = 'Y'

    GROUP BY T0.ItemCode,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

    ORDER BY T0. ITEMCODE

    DB:2.21:Sales Data In 2 Queries Do Not Match fd


    Hi Mike ,

    Changing previous logic could defeat the original purpose .But for testing , you can

    Try this .

    SELECT T0.ITEMCODE,

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'JAN QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'FEB QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'MAR QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'APR QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'MAY QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'JUN QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'JUL QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'AUG QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'SEP QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'OCT QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'NOV QTY',

    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)

    WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE =

    T0.ITEMCODE) AS 'DEC QTY'

    FROM dbo.OITM T0

    LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode

    WHERE T0.InvntItem = 'Y'

    GROUP BY T0.ItemCode,YEAR(T1.ShipDate) HAVING YEAR(T1.ShipDate) = YEAR(GETDATE())

    ORDER BY T0. ITEMCODE

  • RELEVANCY SCORE 2.21

    DB:2.21:Delete Records d3


    Hi,
    When i use the following query....

    select T1.a,T1.b,t1.c from T1,T2
    where
    (t1.a=T2.a or
    T1.b=t2.b) and t2.b = 'somevalue'

    it is retrieving 100rows....

    a,b,c are the only three columns in T1.....

    select a,b,c from T1 retrieves 200 recoards.......

    i would like to delete the 100rows i got from my first query from T1 table.

    Thanks

    DB:2.21:Delete Records d3

    This?

    delete t1 t
    where (t.a, t.b, t.c) in
    (
    select T1.a,T1.b,t1.c from T1,T2
    where
    (t1.a=T2.a or
    T1.b=t2.b) and t2.b = 'somevalue'
    );Edited by: Tubby on Apr 7, 2010 4:24 PM

  • RELEVANCY SCORE 2.21

    DB:2.21:Redbull Air Race Prizes 99


    Just recieved a message from one of the usa dev team asking me for my mailing addresses so they can send me a keypad. Is this for real ? I replied but dont think they are going to post to Scotland. Im pretty sure the top 5 on leaderboard are from eu countries. Are we getting anything from placing top 5 in leaderboard or is it only usa?

    DB:2.21:Redbull Air Race Prizes 99

    You go guys/gals, keep those dirty ammericanos from they keypads



    Me

  • RELEVANCY SCORE 2.21

    DB:2.21:Redbull Race Stupidly Difficult To Get On p3


    needs a clear start point



    DB:2.21:Redbull Race Stupidly Difficult To Get On p3

    I've found that hanging around that area in general will eventually allow you to get a chance of playing with the planes (you don't have to be ultra close to the planes, I managed to bag myself a good 6+ goes on them before with ease.



    -_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-

  • RELEVANCY SCORE 2.21

    DB:2.21:Need Using Cursor For Query x7


    Hi all,
    can anybody tell me how to get the result of the following query by using cursor?

    SELECT (SUBSTR(' ', 1,LEVEL*2)
    || t1.label) AS t1_label,
    amount
    FROM
    (SELECT t1.label,
    t1.ID,
    t1.parent_id,
    SUM(t2.turnover) AS amount
    FROM t1,
    t2
    WHERE t2.t1_id = t1.id
    AND t2.t1_id IN
    (SELECT id
    FROM t1
    START WITH t1.ID = 2
    CONNECT BY PRIOR t1.ID = t1.parent_id
    )
    GROUP BY t1.label, t1.ID, t1.parent_id
    ) t1
    START WITH t1.ID = 2
    CONNECT BY PRIOR t1.ID = t1.parent_id;...and the result:

    t1_label amount
    ---------------------------------------------------------
    B1111778.54
    B12119980.28
    B121118842.77
    B121225480.56
    B12218339.07
    B12 23455.9
    B13 20876.52Thanks,
    Alex

    Edited by: 860003 on Jun 9, 2011 1:30 AM

    Edited by: 860003 on Jun 9, 2011 1:31 AM

    Edited by: 860003 on Jun 9, 2011 1:31 AM

    DB:2.21:Need Using Cursor For Query x7

    Hi, Billy Verreynne,
    Thanks for detailed answer - it is very helpful. So in fact let's say i wanna use a single cursor to output the required results. I'll keep the phrase in my mind in the future "Maximise SQL. Minimise PL/SQL".

    Thanks,
    Alex

  • RELEVANCY SCORE 2.21

    DB:2.21:How To Get This Prompt Working ? sa



    I did this below. What I'd like is to create a table where the prompt (TEXT) gets for values a where statement. Values are WHERE PERFORMING_FLAG = "Yes" , it display "Yes" to the user, and WHERE PERFORMING_FLAG = "No" that displays "No" to the user, and /* */ which displays "All"

    So I wrote the code below, and it doesn't work.

    What I want is being able To make the user choose between two options that are WHERE statement and one option that is nothing (so no WHERE statement).

    How should I do my prompt or correct the code ? The prompt is PERFORMING_FLAG

    Or I guess there is a possibility to make it more clear, could be you please enlighten me ?

    PROC SQL;

    CREATE TABLE TRAVAIL.Q113_INPUTS AS

    SELECT t1.ID_INTERNATIONAL,

    t1.NOM_TIERS,

    t1.CD_COTATION,

    t1.'Lb Secteur EIS GB'n,

    t1.'Zone Resp'n,

    t1.ID_FAC,

    t1.LB_ENGAGEMENT_GB,

    t1.'Type Encours'n,

    t1.'Type Encours GB'n,

    t1.IND_BILAN_HORS_BILAN,

    t1.DT_AUTO,

    t1.DT_ECH,

    t1.'Devise Final1'n,

    t1.'Devise Final'n,

    t1.SumOfMT_UTIL_EUR,

    t1.SumOfMT_AUTO_EUR,

    t1.PNU_conf,

    t1.'Regulatory_RWA'n,

    t1.SumOfMT_EXPO_IRBA,

    t1.TX_CCF_IRBA,

    t1.SumOfX_EDF1_IRBA,

    t1.SumOfX_GRR_IRBA,

    t1.SumOfX_MAT_IRBA,

    t1.PERFORMING_FLAG,

    t1.IND_CONF,

    t1.'Business Country'n,

    t1.'LB Pays Resp'n,

    t1.'Lib Metier 25'n,

    t1.'Lib act agg 25'n,

    t1.LB_GRP_AFFAIRE,

    t1.RATING_GROUPE,

    t1.C3_CLIENT_LINE,

    t1.CD_GRP_AFFAIRE,

    t1.Credit_Type_Profiling,

    t1.CD_TP_CTRP_CAPRI,

    t1.LIB_METIER_BFI,

    t1.FORTIS_FLAG,

    t1.CD_ENGAGEMENT,

    t1.LB_ENGAGEMENT

    FROM TRAVAIL.Q113_INPUTS_1 t1

    PERFORMING_FLAG;

    QUIT;

    DB:2.21:How To Get This Prompt Working ? sa


    Hi @GuiVtzl,

    First I 'd like to congratulate your success of solving your own problem, while I can't repeat your luck using "_%" with "=" operator in STP (Stored Process). Are your sure you used "=" instead of 'LIKE" as Amir Malik suggested?

    One way going this around if your code is in the format of : WHERE PERFORMING_FLAG = "PERFORMING_FLAG" is to use query builder and check "All possible values" selection.

    Unfortunately it does not work on STP out of box, you need to get it to work through query builder, then convert it to STP, so it may not as flexible as something working straightly on the SAS code in STP, although in your case, query builder seems to be adequate.

    OK, now is the fun part. If going back to your original code:

    FROM TRAVAIL.Q113_INPUTS_1 t1

    PERFORMING_FLAG;

    It came to me that STP has some 'hidden' rules in term of Macro quoting. Notice the code you have for Prompt:

    where PERFORMING_FLAG = "Yes"

    And the error message translated into English : (BTW, I didn't do the translation, rather, I tried to repeat your problem in a hypothetical setting)
    ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, ALL, ANY, BTRIM, CALCULATED, CASE,
    INPUT, PUT, SELECT, SOME, SUBSTRING, TRANSLATE, USER.

    ERROR 200-322: The symbol is not recognized and will be ignored.

    The SAS code is correct in plain SAS code or in the SAS code wrapped with Macro definition, however it does not work in STP, and the reason is hinted in the error message: expecting one of the following: a name, a quoted string. WTH? SAS is complaining the lack of a quoted string while We do have one "Yes"!!! So there is something fishy here, but we have to play along. SAS complaining lack of a quoted string doesn't there isn't one, it could mean it has been quoted out, so SAS doesn't see it. So I tried this:

    FROM TRAVAIL.Q113_INPUTS_1 t1

    %unquote(PERFORMING_FLAG);

    And guess what, It worked!

    Here is the definition of my prompt:


    Please notice that the third one: where 1, which means always true, but I have to put it out there as it does not take blanks.

    I am in the process of learning STP, so I am really glad you have raised this question, it helped me deepen my understanding how STP works.

    Haikuo

  • RELEVANCY SCORE 2.21

    DB:2.21:Redbull Island ca



    i want to see redbull island back with the clothes free

    DB:2.21:Redbull Island ca

    Thank you, now they will try to get it done faster

  • RELEVANCY SCORE 2.21

    DB:2.21:Update Tabelas Diferentes 93


    SELECT T1.CODTB4FAT,T1.QUANTIDADE,SUM (T1.PRECOUNITARIO)                AS 'PRECO UNITARIO',SUM (T1.QUANTIDADE * T1.PRECOUNITARIO)AS 'VALOR BRUTO' ,TMOV.CODTMV FROM TMOV INNER JOIN TITMMOV AS T1 ON T1.CODCOLIGADA = TMOV.CODCOLIGADA AND T1.IDMOV = TMOV.IDMOVINNER JOIN TPRD    AS TP ON TP.CODCOLIGADA = TP.CODCOLIGADA   AND T1.IDPRD = TP.IDPRDWHERE CODTMV IN ('1.2.01','1.2.11','1.2.12','1.2.05','1.2.06','1.2.09','1.2.12') AND TMOV.DATAEMISSAO BETWEEN '2009-01-01' AND '2009-08-11'AND TP.CODIGOPRD LIKE '01.01%'GROUP BY TP.CODIGOPRD,TMOV.CODTMV,TP.DESCRICAO ,T1.CODUND,TMOV.NUMEROMOV,T1.QUANTIDADE,T1.VALORTOTALITEM,T1.CODTB4FATORDER BY TP.CODIGOPRD,TMOV.CODTMVPrezados preciso fazer um update nesse sentenca, nesse campo  CODTB4FAT..agradeço a todos pela ajuda..

    DB:2.21:Update Tabelas Diferentes 93

    Show de Bola, era isso mesmo que estava precisando..obrigado

  • RELEVANCY SCORE 2.20

    DB:2.20:Sql Generation (Nested Ors) fm


    Hi,

    I need to do a select ... from ... where x in (a, b, ... , n);
    I am doing this by building an OQL filter of the form -
    (x == a || x == b || ... || x == n)
    as JDO has no concept of "in".

    Kodo then generates the sql but the result is no longer in this form but the
    following -
    (((((x == a) || (x == b)) || (x == c)) || (x == d)) || ... || (x == n))

    I am concerned that this is far from ideal especially as the number of
    nested ORs increase.

    I understand the current beta version of Kodo has the ability to generate
    "in" clauses using Collections and have not experimented with this yet but
    am concerned that this will not reduce the problem.

    Will the large number of nested ORs cause a degredation of performance? Is
    there any way to prevent Kodo from generating sql queries such as this?

    I have included examples of actual filter and sql queries below -

    Thanks,

    Simon

    oql filter -

    (orgUnit==orgUnit0 || orgUnit==orgUnit1 || orgUnit==orgUnit2 ||
    orgUnit==orgUnit3 || orgUnit==orgUnit4 || orgUnit==orgUnit5 ||
    orgUnit==orgUnit6 || orgUnit==orgUnit7 || orgUnit==orgUnit8 ||
    orgUnit==orgUnit9 || orgUnit==orgUnit10 || orgUnit==orgUnit11 ||
    orgUnit==orgUnit12 || orgUnit==orgUnit13 || orgUnit==orgUnit14 ||
    orgUnit==orgUnit15 || orgUnit==orgUnit16 || orgUnit==orgUnit17 ||
    orgUnit==orgUnit18 || orgUnit==orgUnit19 || orgUnit==orgUnit20 ||
    orgUnit==orgUnit21 || orgUnit==orgUnit22 || orgUnit==orgUnit23 ||
    orgUnit==orgUnit24 || orgUnit==orgUnit25 || orgUnit==orgUnit26 ||
    orgUnit==orgUnit27 || orgUnit==orgUnit28 || orgUnit==orgUnit29 ||
    orgUnit==orgUnit30 || orgUnit==orgUnit31 || orgUnit==orgUnit32 ||
    orgUnit==orgUnit33 || orgUnit==orgUnit34 || orgUnit==orgUnit35 ||
    orgUnit==orgUnit36 || orgUnit==orgUnit37 || orgUnit==orgUnit38 ||
    orgUnit==orgUnit39 || orgUnit==orgUnit40 || orgUnit==orgUnit41 ||
    orgUnit==orgUnit42 || orgUnit==orgUnit43 || orgUnit==orgUnit44 ||
    orgUnit==orgUnit45 || orgUnit==orgUnit46 || orgUnit==orgUnit47 ||
    orgUnit==orgUnit48 || orgUnit==orgUnit49 || orgUnit==orgUnit50 ||
    orgUnit==orgUnit51 || orgUnit==orgUnit52 || orgUnit==orgUnit53 ||
    orgUnit==orgUnit54 || orgUnit==orgUnit55 || orgUnit==orgUnit56 ||
    orgUnit==orgUnit57 || orgUnit==orgUnit58 || orgUnit==orgUnit59 ||
    orgUnit==orgUnit60 || orgUnit==orgUnit61 || orgUnit==orgUnit62 ||
    orgUnit==orgUnit63 || orgUnit==orgUnit64 || orgUnit==orgUnit65 ||
    orgUnit==orgUnit66 || orgUnit==orgUnit67 || orgUnit==orgUnit68 ||
    orgUnit==orgUnit69 || orgUnit==orgUnit70 || orgUnit==orgUnit71 ||
    orgUnit==orgUnit72 || orgUnit==orgUnit73 || orgUnit==orgUnit74 ||
    orgUnit==orgUnit75 || orgUnit==orgUnit76 || orgUnit==orgUnit77 ||
    orgUnit==orgUnit78 || orgUnit==orgUnit79 || orgUnit==orgUnit80 ||
    orgUnit==orgUnit81 || orgUnit==orgUnit82 || orgUnit==orgUnit83 ||
    orgUnit==orgUnit84 || orgUnit==orgUnit85 || orgUnit==orgUnit86 ||
    orgUnit==orgUnit87 || orgUnit==orgUnit88 || orgUnit==orgUnit89 ||
    orgUnit==orgUnit90 || orgUnit==orgUnit91 || orgUnit==orgUnit92 ||
    orgUnit==orgUnit93 || orgUnit==orgUnit94 || orgUnit==orgUnit95 ||
    orgUnit==orgUnit96 || orgUnit==orgUnit97 || orgUnit==orgUnit98 ||
    orgUnit==orgUnit99 || orgUnit==orgUnit100 || orgUnit==orgUnit101 ||
    orgUnit==orgUnit102 || orgUnit==orgUnit103 || orgUnit==orgUnit104 ||
    orgUnit==orgUnit105 || orgUnit==orgUnit106 || orgUnit==orgUnit107 ||
    orgUnit==orgUnit108 || orgUnit==orgUnit109 || orgUnit==orgUnit110 ||
    orgUnit==orgUnit111 || orgUnit==orgUnit112 || orgUnit==orgUnit113 ||
    orgUnit==orgUnit114 || orgUnit==orgUnit115 || orgUnit==orgUnit116 ||
    orgUnit==orgUnit117 || orgUnit==orgUnit118 || orgUnit==orgUnit119 ||
    orgUnit==orgUnit120 || orgUnit==orgUnit121 || orgUnit==orgUnit122 ||
    orgUnit==orgUnit123 || orgUnit==orgUnit124 || orgUnit==orgUnit125 ||
    orgUnit==orgUnit126 || orgUnit==orgUnit127 || orgUnit==orgUnit128 ||
    orgUnit==orgUnit129 || orgUnit==orgUnit130 || orgUnit==orgUnit131 ||
    orgUnit==orgUnit132 || orgUnit==orgUnit133 || orgUnit==orgUnit134 ||
    orgUnit==orgUnit135 || orgUnit==orgUnit136 || orgUnit==orgUnit137 ||
    orgUnit==orgUnit138 || orgUnit==orgUnit139 || orgUnit==orgUnit140 ||
    orgUnit==orgUnit141 || orgUnit==orgUnit142 || orgUnit==orgUnit143 ||
    orgUnit==orgUnit144 || orgUnit==orgUnit145 || orgUnit==orgUnit146 ||
    orgUnit==orgUnit147 || orgUnit==orgUnit148 || orgUnit==orgUnit149 ||
    orgUnit==orgUnit150 || orgUnit==orgUnit151 || orgUnit==orgUnit152 ||
    orgUnit==orgUnit153 || orgUnit==orgUnit154 || orgUnit==orgUnit155 ||
    orgUnit==orgUnit156 || orgUnit==orgUnit157 || orgUnit==orgUnit158 ||
    orgUnit==orgUnit159 || orgUnit==orgUnit160 || orgUnit==orgUnit161 ||
    orgUnit==orgUnit162 || orgUnit==orgUnit163 || orgUnit==orgUnit164 ||
    orgUnit==orgUnit165 || orgUnit==orgUnit166 || orgUnit==orgUnit167 ||
    orgUnit==orgUnit168 || orgUnit==orgUnit169 || orgUnit==orgUnit170 ||
    orgUnit==orgUnit171 || orgUnit==orgUnit172 || orgUnit==orgUnit173 ||
    orgUnit==orgUnit174 || orgUnit==orgUnit175 || orgUnit==orgUnit176 ||
    orgUnit==orgUnit177 || orgUnit==orgUnit178 || orgUnit==orgUnit179 ||
    orgUnit==orgUnit180 || orgUnit==orgUnit181 || orgUnit==orgUnit182 ||
    orgUnit==orgUnit183 || orgUnit==orgUnit184 || orgUnit==orgUnit185 ||
    orgUnit==orgUnit186 || orgUnit==orgUnit187 || orgUnit==orgUnit188 ||
    orgUnit==orgUnit189 || orgUnit==orgUnit190 || orgUnit==orgUnit191 ||
    orgUnit==orgUnit192 || orgUnit==orgUnit193 || orgUnit==orgUnit194 ||
    orgUnit==orgUnit195 || orgUnit==orgUnit196 || orgUnit==orgUnit197 ||
    orgUnit==orgUnit198 || orgUnit==orgUnit199 || orgUnit==orgUnit200 ||
    orgUnit==orgUnit201 || orgUnit==orgUnit202 || orgUnit==orgUnit203 ||
    orgUnit==orgUnit204 || orgUnit==orgUnit205 || orgUnit==orgUnit206 ||
    orgUnit==orgUnit207 || orgUnit==orgUnit208 || orgUnit==orgUnit209 ||
    orgUnit==orgUnit210 || orgUnit==orgUnit211 || orgUnit==orgUnit212 ||
    orgUnit==orgUnit213 || orgUnit==orgUnit214 || orgUnit==orgUnit215 ||
    orgUnit==orgUnit216 || orgUnit==orgUnit217 || orgUnit==orgUnit218 ||
    orgUnit==orgUnit219 || orgUnit==orgUnit220 || orgUnit==orgUnit221 ||
    orgUnit==orgUnit222 || orgUnit==orgUnit223 || orgUnit==orgUnit224 ||
    orgUnit==orgUnit225 || orgUnit==orgUnit226 || orgUnit==orgUnit227 ||
    orgUnit==orgUnit228 || orgUnit==orgUnit229 || orgUnit==orgUnit230 ||
    orgUnit==orgUnit231 || orgUnit==orgUnit232 || orgUnit==orgUnit233 ||
    orgUnit==orgUnit234 || orgUnit==orgUnit235 || orgUnit==orgUnit236 ||
    orgUnit==orgUnit237 || orgUnit==orgUnit238 || orgUnit==orgUnit239 ||
    orgUnit==orgUnit240 || orgUnit==orgUnit241 || orgUnit==orgUnit242 ||
    orgUnit==orgUnit243 || orgUnit==orgUnit244 || orgUnit==orgUnit245 ||
    orgUnit==orgUnit246 || orgUnit==orgUnit247 || orgUnit==orgUnit248 ||
    orgUnit==orgUnit249 || orgUnit==orgUnit250 || orgUnit==orgUnit251 ||
    orgUnit==orgUnit252 || orgUnit==orgUnit253 || orgUnit==orgUnit254 ||
    orgUnit==orgUnit255 || orgUnit==orgUnit256 || orgUnit==orgUnit257 ||
    orgUnit==orgUnit258 || orgUnit==orgUnit259 || orgUnit==orgUnit260 ||
    orgUnit==orgUnit261 || orgUnit==orgUnit262 || orgUnit==orgUnit263 ||
    orgUnit==orgUnit264 || orgUnit==orgUnit265 || orgUnit==orgUnit266 ||
    orgUnit==orgUnit267 || orgUnit==orgUnit268 || orgUnit==orgUnit269 ||
    orgUnit==orgUnit270 || orgUnit==orgUnit271 || orgUnit==orgUnit272 ||
    orgUnit==orgUnit273 || orgUnit==orgUnit274 || orgUnit==orgUnit275 ||
    orgUnit==orgUnit276 || orgUnit==orgUnit277 || orgUnit==orgUnit278 ||
    orgUnit==orgUnit279 || orgUnit==orgUnit280 || orgUnit==orgUnit281 ||
    orgUnit==orgUnit282 || orgUnit==orgUnit283 || orgUnit==orgUnit284 ||
    orgUnit==orgUnit285 || orgUnit==orgUnit286 || orgUnit==orgUnit287 ||
    orgUnit==orgUnit288 || orgUnit==orgUnit289 || orgUnit==orgUnit290 ||
    orgUnit==orgUnit291 || orgUnit==orgUnit292 || orgUnit==orgUnit293 ||
    orgUnit==orgUnit294 || orgUnit==orgUnit295 || orgUnit==orgUnit296 ||
    orgUnit==orgUnit297 || orgUnit==orgUnit298 || orgUnit==orgUnit299 ||
    orgUnit==orgUnit300 || orgUnit==orgUnit301 || orgUnit==orgUnit302 ||
    orgUnit==orgUnit303 || orgUnit==orgUnit304 || orgUnit==orgUnit305 ||
    orgUnit==orgUnit306 || orgUnit==orgUnit307 || orgUnit==orgUnit308 ||
    orgUnit==orgUnit309 || orgUnit==orgUnit310 || orgUnit==orgUnit311 ||
    orgUnit==orgUnit312 || orgUnit==orgUnit313 || orgUnit==orgUnit314 ||
    orgUnit==orgUnit315 || orgUnit==orgUnit316 || orgUnit==orgUnit317 ||
    orgUnit==orgUnit318 || orgUnit==orgUnit319 || orgUnit==orgUnit320 ||
    orgUnit==orgUnit321 || orgUnit==orgUnit322 || orgUnit==orgUnit323 ||
    orgUnit==orgUnit324 || orgUnit==orgUnit325 || orgUnit==orgUnit326 ||
    orgUnit==orgUnit327 || orgUnit==orgUnit328 || orgUnit==orgUnit329 ||
    orgUnit==orgUnit330 || orgUnit==orgUnit331 || orgUnit==orgUnit332 ||
    orgUnit==orgUnit333 || orgUnit==orgUnit334 || orgUnit==orgUnit335 ||
    orgUnit==orgUnit336 || orgUnit==orgUnit337 || orgUnit==orgUnit338 ||
    orgUnit==orgUnit339 || orgUnit==orgUnit340 || orgUnit==orgUnit341 ||
    orgUnit==orgUnit342 || orgUnit==orgUnit343 || orgUnit==orgUnit344 ||
    orgUnit==orgUnit345 || orgUnit==orgUnit346 || orgUnit==orgUnit347 ||
    orgUnit==orgUnit348 || orgUnit==orgUnit349 || orgUnit==orgUnit350 ||
    orgUnit==orgUnit351 || orgUnit==orgUnit352 || orgUnit==orgUnit353 ||
    orgUnit==orgUnit354 || orgUnit==orgUnit355 || orgUnit==orgUnit356 ||
    orgUnit==orgUnit357 || orgUnit==orgUnit358 || orgUnit==orgUnit359 ||
    orgUnit==orgUnit360 || orgUnit==orgUnit361 || orgUnit==orgUnit362 ||
    orgUnit==orgUnit363 || orgUnit==orgUnit364 || orgUnit==orgUnit365 ||
    orgUnit==orgUnit366 || orgUnit==orgUnit367 || orgUnit==orgUnit368 ||
    orgUnit==orgUnit369 || orgUnit==orgUnit370 || orgUnit==orgUnit371 ||
    orgUnit==orgUnit372 || orgUnit==orgUnit373 || orgUnit==orgUnit374 ||
    orgUnit==orgUnit375 || orgUnit==orgUnit376 || orgUnit==orgUnit377 ||
    orgUnit==orgUnit378 || orgUnit==orgUnit379 || orgUnit==orgUnit380 ||
    orgUnit==orgUnit381 || orgUnit==orgUnit382 || orgUnit==orgUnit383 ||
    orgUnit==orgUnit384 || orgUnit==orgUnit385 || orgUnit==orgUnit386 ||
    orgUnit==orgUnit387 || orgUnit==orgUnit388 || orgUnit==orgUnit389 ||
    orgUnit==orgUnit390 || orgUnit==orgUnit391 || orgUnit==orgUnit392 ||
    orgUnit==orgUnit393 || orgUnit==orgUnit394 || orgUnit==orgUnit395 ||
    orgUnit==orgUnit396 || orgUnit==orgUnit397 || orgUnit==orgUnit398 ||
    orgUnit==orgUnit399 || orgUnit==orgUnit400 || orgUnit==orgUnit401 ||
    orgUnit==orgUnit402 || orgUnit==orgUnit403 || orgUnit==orgUnit404 ||
    orgUnit==orgUnit405 || orgUnit==orgUnit406 || orgUnit==orgUnit407 ||
    orgUnit==orgUnit408 || orgUnit==orgUnit409 || orgUnit==orgUnit410 ||
    orgUnit==orgUnit411 || orgUnit==orgUnit412 || orgUnit==orgUnit413 ||
    orgUnit==orgUnit414 || orgUnit==orgUnit415 || orgUnit==orgUnit416 ||
    orgUnit==orgUnit417 || orgUnit==orgUnit418 || orgUnit==orgUnit419 ||
    orgUnit==orgUnit420 || orgUnit==orgUnit421 || orgUnit==orgUnit422 ||
    orgUnit==orgUnit423 || orgUnit==orgUnit424 || orgUnit==orgUnit425 ||
    orgUnit==orgUnit426 || orgUnit==orgUnit427 || orgUnit==orgUnit428 ||
    orgUnit==orgUnit429 || orgUnit==orgUnit430 || orgUnit==orgUnit431 ||
    orgUnit==orgUnit432 || orgUnit==orgUnit433 || orgUnit==orgUnit434 ||
    orgUnit==orgUnit435 || orgUnit==orgUnit436 || orgUnit==orgUnit437 ||
    orgUnit==orgUnit438 || orgUnit==orgUnit439 || orgUnit==orgUnit440 ||
    orgUnit==orgUnit441 || orgUnit==orgUnit442 || orgUnit==orgUnit443 ||
    orgUnit==orgUnit444 || orgUnit==orgUnit445 || orgUnit==orgUnit446 ||
    orgUnit==orgUnit447 || orgUnit==orgUnit448 || orgUnit==orgUnit449 ||
    orgUnit==orgUnit450 || orgUnit==orgUnit451 || orgUnit==orgUnit452 ||
    orgUnit==orgUnit453 || orgUnit==orgUnit454 || orgUnit==orgUnit455 ||
    orgUnit==orgUnit456 || orgUnit==orgUnit457 || orgUnit==orgUnit458 ||
    orgUnit==orgUnit459 || orgUnit==orgUnit460 || orgUnit==orgUnit461 ||
    orgUnit==orgUnit462 || orgUnit==orgUnit463 || orgUnit==orgUnit464 ||
    orgUnit==orgUnit465 || orgUnit==orgUnit466 || orgUnit==orgUnit467 ||
    orgUnit==orgUnit468 || orgUnit==orgUnit469 || orgUnit==orgUnit470 ||
    orgUnit==orgUnit471 || orgUnit==orgUnit472 || orgUnit==orgUnit473 ||
    orgUnit==orgUnit474 || orgUnit==orgUnit475 || orgUnit==orgUnit476 ||
    orgUnit==orgUnit477 || orgUnit==orgUnit478 || orgUnit==orgUnit479 ||
    orgUnit==orgUnit480 || orgUnit==orgUnit481 || orgUnit==orgUnit482 ||
    orgUnit==orgUnit483 || orgUnit==orgUnit484 || orgUnit==orgUnit485 ||
    orgUnit==orgUnit486 || orgUnit==orgUnit487 || orgUnit==orgUnit488 ||
    orgUnit==orgUnit489 || orgUnit==orgUnit490 || orgUnit==orgUnit491 ||
    orgUnit==orgUnit492 || orgUnit==orgUnit493 || orgUnit==orgUnit494 ||
    orgUnit==orgUnit495 || orgUnit==orgUnit496 || orgUnit==orgUnit497 ||
    orgUnit==orgUnit498 || orgUnit==orgUnit499 || orgUnit==orgUnit500 ||
    orgUnit==orgUnit501 || orgUnit==orgUnit502 || orgUnit==orgUnit503 ||
    orgUnit==orgUnit504 || orgUnit==orgUnit505 || orgUnit==orgUnit506 ||
    orgUnit==orgUnit507 || orgUnit==orgUnit508 || orgUnit==orgUnit509 ||
    orgUnit==orgUnit510 || orgUnit==orgUnit511 || orgUnit==orgUnit512 ||
    orgUnit==orgUnit513 || orgUnit==orgUnit514 || orgUnit==orgUnit515 ||
    orgUnit==orgUnit516 || orgUnit==orgUnit517 || orgUnit==orgUnit518 ||
    orgUnit==orgUnit519 || orgUnit==orgUnit520 || orgUnit==orgUnit521 ||
    orgUnit==orgUnit522 || orgUnit==orgUnit523 || orgUnit==orgUnit524 ||
    orgUnit==orgUnit525 || orgUnit==orgUnit526 || orgUnit==orgUnit527 ||
    orgUnit==orgUnit528 || orgUnit==orgUnit529 || orgUnit==orgUnit530 ||
    orgUnit==orgUnit531 || orgUnit==orgUnit532 || orgUnit==orgUnit533 ||
    orgUnit==orgUnit534 || orgUnit==orgUnit535 || orgUnit==orgUnit536 ||
    orgUnit==orgUnit537 || orgUnit==orgUnit538 || orgUnit==orgUnit539 ||
    orgUnit==orgUnit540 || orgUnit==orgUnit541 || orgUnit==orgUnit542 ||
    orgUnit==orgUnit543 || orgUnit==orgUnit544 || orgUnit==orgUnit545 ||
    orgUnit==orgUnit546 || orgUnit==orgUnit547 || orgUnit==orgUnit548 ||
    orgUnit==orgUnit549 || orgUnit==orgUnit550 || orgUnit==orgUnit551 ||
    orgUnit==orgUnit552 || orgUnit==orgUnit553 || orgUnit==orgUnit554 ||
    orgUnit==orgUnit555 || orgUnit==orgUnit556 || orgUnit==orgUnit557 ||
    orgUnit==orgUnit558 || orgUnit==orgUnit559 || orgUnit==orgUnit560 ||
    orgUnit==orgUnit561 || orgUnit==orgUnit562 || orgUnit==orgUnit563 ||
    orgUnit==orgUnit564 || orgUnit==orgUnit565 || orgUnit==orgUnit566 ||
    orgUnit==orgUnit567 || orgUnit==orgUnit568 || orgUnit==orgUnit569 ||
    orgUnit==orgUnit570 || orgUnit==orgUnit571 || orgUnit==orgUnit572 ||
    orgUnit==orgUnit573 || orgUnit==orgUnit574 || orgUnit==orgUnit575 ||
    orgUnit==orgUnit576 || orgUnit==orgUnit577 || orgUnit==orgUnit578 ||
    orgUnit==orgUnit579 || orgUnit==orgUnit580 || orgUnit==orgUnit581 ||
    orgUnit==orgUnit582 || orgUnit==orgUnit583 || orgUnit==orgUnit584 ||
    orgUnit==orgUnit585 || orgUnit==orgUnit586 || orgUnit==orgUnit587 ||
    orgUnit==orgUnit588 || orgUnit==orgUnit589 || orgUnit==orgUnit590 ||
    orgUnit==orgUnit591 || orgUnit==orgUnit592 || orgUnit==orgUnit593 ||
    orgUnit==orgUnit594 || orgUnit==orgUnit595 || orgUnit==orgUnit596 ||
    orgUnit==orgUnit597 || orgUnit==orgUnit598 || orgUnit==orgUnit599 ||
    orgUnit==orgUnit600 || orgUnit==orgUnit601 || orgUnit==orgUnit602 ||
    orgUnit==orgUnit603)

    sql generated -

    SELECT t0.EVENT_ID, t0.ORG_UNIT_ID FROM EVENT t0, ORG_UNIT t1 WHERE
    ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
    ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
    ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
    ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
    ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
    ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
    ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
    (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((t1.OR
    G_UNIT_ID = 2656 AND t0.ORG_UNIT_ID = t1.ORG_UNIT_ID) OR (t1.ORG_UNIT_ID =
    2655 AND t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2149 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2654 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2148 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2653 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2147 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2652 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2146 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2651 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2145 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2650 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2144 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2143 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2142 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2141 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2140 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1879 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1878 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1877 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1876 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1875 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1874 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1873 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1872 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2649 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1871 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2648 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1870 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2647 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2646 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2645 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2139 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2644 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2138 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2643 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2137 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2136 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2135 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2134 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2133 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2132 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2131 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2130 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1869 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1868 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1867 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1866 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1865 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1863 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1862 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1861 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1860 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2129 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2128 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2127 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2126 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2125 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2124 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2123 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2122 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2121 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2120 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1859 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1858 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1857 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1856 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1855 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1854 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1853 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1852 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1851 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1850 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2299 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2625 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2298 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2119 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2624 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2118 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2623 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2117 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2295 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2116 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2115 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2293 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2292 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2291 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2290 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2111 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2110 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1849 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1848 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1847 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1846 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1845 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1844 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1843 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1842 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1841 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1840 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2289 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2288 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2287 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2286 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2285 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2284 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2281 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1839 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1838 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1837 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1836 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1835 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1834 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1833 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2789 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1832 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2788 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1831 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2787 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1830 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2786 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2785 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2279 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2784 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2278 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2783 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2277 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2603 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2276 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2275 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2274 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2273 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2272 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2271 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2270 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1829 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1828 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1827 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1826 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1825 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1824 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1823 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1822 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1821 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1820 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2269 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2268 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2267 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2266 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2265 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2264 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2263 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2262 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2261 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2260 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1819 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1818 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1817 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1816 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1815 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1814 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1813 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1812 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1811 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1810 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2766 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2765 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2259 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2764 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2258 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2763 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2257 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2256 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2255 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2254 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2253 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2252 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2251 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1809 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1808 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1807 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1806 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1805 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1804 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1803 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1802 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1801 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1800 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2249 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2248 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2247 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2246 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2245 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2244 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2240 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2743 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2237 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2236 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2235 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1960 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2223 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2222 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1959 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1958 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1957 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1956 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1950 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2398 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2397 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2723 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2395 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2216 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2394 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2393 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2214 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2392 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2213 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2391 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2212 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2390 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2211 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 2210 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1949 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1946 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1945 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1944 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1943 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1942 AND
    t0.ORG_UNIT_ID = t1.ORG_UNIT_ID)) OR (t1.ORG_UNIT_ID = 1941 AND
    t0.ORG_U

    DB:2.20:Sql Generation (Nested Ors) fm

    On Tue, 08 Apr 2003 14:34:22 -0400, Simon Horne wrote:

    I understand the current beta version of Kodo has the ability to
    generate "in" clauses using Collections and have not experimented with
    this yet but am concerned that this will not reduce the problem.As you noted, the 2.5 beta supports IN clauses. I do not understand why
    you don't think this will address the problem.

    Please try out your queries with 2.5's IN support and let us know if you
    still think that a problem exists.

    -Patrick

    --
    Patrick Linskey
    SolarMetric Inc.

  • RELEVANCY SCORE 2.20

    DB:2.20:Re: Sql Help 9x


    May be this?
    SELECT t1.col2 INTO lv_col2
    FROM t1, t2
    WHERE t1.colA=t2.colA
    OR (lv_col2 IS NULL AND t1.colB=t2.colB)

    DB:2.20:Re: Sql Help 9x

    Undoubtedly a single SQL statement is possible. We just need to understand the specific requirements. Of course, a single SQL statement may well be less maintainable than a bit of procedural logic because it may end up getting rather complicated.So you want to get the value from query1 if and only if query1 returns 1 row. If query1 returns 0 rows or more than 1 row, you want to get the data from query2. Is that right? Is it possible for query1 to return exactly 1 row but for the COL2 value of that row to be NULL? What if query2 returns 0 rows or more than 1 row? Do you want NULL to be returned in that case?Justin

  • RELEVANCY SCORE 2.20

    DB:2.20:Uncharted 2 Redbull j1



    hahaha just got my 12 pack of redbull from the uncharted 2 beta thing

    anyone els got theres yet?

    EDIT:

    oops i dint see the other post about this
    Message Edited by Jakarii on 05-10-2009 01:18 PM



    DB:2.20:Uncharted 2 Redbull j1


    hahaha just got my 12 pack of redbull from the uncharted 2 beta thing

    anyone els got theres yet?

    EDIT:

    oops i dint see the other post about this
    Message Edited by Jakarii on 05-10-2009 01:18 PM



  • RELEVANCY SCORE 2.20

    DB:2.20:Sales By Amt Monthly Query 7d



    Hi All -

    For this query below, any way to modify it so we can select the year, rather than have it provide current year?

    Thanks,

    Mike

    SELECT LEFT(T0.ITEMCODE,4) AS 'Style', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC Amt'

    FROM dbo.OITM T0 LEFT JOIN dbo.INV1 T1 ON LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) WHERE T0.SellItem = 'Y' GROUP BY LEFT(T0.ITEMCODE,4),YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

    ORDER BY LEFT(T0.ITEMCODE,4)

    DB:2.20:Sales By Amt Monthly Query 7d


    Hi Mike,

    This query is too long to add parameter on top of the query. Easy solution would be just replace all YEAR(GETDATE()) with a specific year such as 2009 within query.

    Thanks,

    Gordon

  • RELEVANCY SCORE 2.20

    DB:2.20:Set .Sql File As Variable xz


    I have a powershell script where I set the variable based on a query like this:
    $Query = SELECT
    t1.BSM_NM
    ,t1.D_DTM AS MAXDATETIME
    ,CASE
    WHEN SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) = MAX(t2.MAJOR_VOICE_BLOCK) AND SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) MAX(t2.CRITICAL_VOICE_BLOCK)
    OR
    SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) = MAX(t2.MAJOR_VOICE_DROP) AND SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) MAX(t2.CRITICAL_VOICE_DROP)
    OR
    SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) = MAX(t2.MAJOR_VOICE_AXSFAIL) AND SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) MAX(t2.CRITICAL_VOICE_AXSFAIL)
    THEN 1
    WHEN SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) = MAX(t2.CRITICAL_VOICE_BLOCK)
    OR
    SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) = MAX(t2.CRITICAL_VOICE_DROP)
    OR
    SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) = MAX(t2.CRITICAL_VOICE_AXSFAIL)
    THEN 2
    ELSE 0
    END MAJORCRITICAL
    FROM DMSN.DS3R_FH_1XRTT_BTS_LVL_KPI t1
    INNER JOIN
    ZDMSN.DS3R_1XRTT_TRIGGERS_THRESHOLD t2
    ON
    t1.BSM_NM = t2.BSC_NM
    WHERE t1.BSM_NM = 'ARL1' and t1.D_DTM = (SELECT MAX(D_DTM) FROM DS3R_FH_1XRTT_BTS_LVL_KPI WHERE BSM_NM = 'ARL1')
    GROUP BY
    t1.BSM_NM, t1.D_DTM

    How do I set it to an .sql file instead writing on the query in script itself?

    DB:2.20:Set .Sql File As Variable xz

    That will give you an array. To return the multi-line file as a single string:
    Powershell V3
    get-content ./sqlfile.sql -raw
    Powershell V2 or V3
    (get-content ./sqlfile.sql) -join `n
    or
    [system.io.file]::ReadAllText(c:\sqlfile.sql)

  • RELEVANCY SCORE 2.20

    DB:2.20:Using Parameters In Udos 11



    I'm trying to execute this query, but it says: "Must select a table to select from". First time I tried this without variables but it was the same error. I don't know if its possible, but thanks before hand.

    DECLARE @FechaInicio DATETIME

    DECLARE @FechaFinal DATETIME

    SET @FechaInicio = '[%0]'

    SET @FechaFinal = '[%1]'

    SELECT

    T0.DocEntry,

    T0.DocNum,

    T0.U_Fecha,

    T0.U_Owner,

    T0.U_OwnerName,

    T0.U_Inicio,

    T0.U_Fin,

    T0.U_Activo,

    T1.U_Oportunidad,

    T1.U_Cotizacion,

    T1.U_Codigo,

    T1.U_Cliente,

    T1.U_Articulo,

    T1.U_Grupo,

    T1.U_CtroCosto,

    T1.U_Cantidad,

    T1.U_Pendiente,

    T1.U_CantTotal,

    T1.U_Obra,

    T1.U_Inicio,

    T1.U_Final,

    T1.U_Obs,

    T1.U_ObsInc,

    T1.U_Revisado

    FROM

    [@PRESMENSUAL] T0

    INNER JOIN [@MENSUAL_BLOCK] T1 ON T0.DocEntry = T1.DocEntry

    WHERE

    T0.U_Inicio = @FechaInicio AND T0.U_Inicio = @FechaFinal

  • RELEVANCY SCORE 2.20

    DB:2.20:Probelm With Joins Due To Duplications 7d


    I want To eliminate the duplicate rows_

    select * from
    (select
    t1.emplid
    , t2.first_name
    , t2.middle_name
    , t2.last_name
    , t2.birthdate
    , t1.company
    , t1.jobcode
    , t1.full_part_time
    , t1.empl_status
    , t1.deptid
    , t1.supervisor_id
    , t1.per_org
    , t1.reg_temp
    , t1.action
    , t1.hire_dt
    , t1.termination_dt
    , t1.empl_rcd
    from ps_job t1
    RIGHT outer join ps_personal_data t2
    on (t1.emplid = t2.emplid)
    where t1.rowid (select min(rowid) from ps_job where emplid = t1.emplid));

    Duplicate Rows Table

    select t1.rowid, t1.empl_status, t1.empl_rcd, t1.action, t1.entry_date, t1.effdt from
    ps_job t1
    where t1.rowid (select min(rowid) from ps_job where emplid = t1.emplid);

    DB:2.20:Probelm With Joins Due To Duplications 7d

    Even though there are some more duplicates as per timestamp difference column

    Edited by: Smiley_143 on Apr 4, 2012 5:57 AM

  • RELEVANCY SCORE 2.20

    DB:2.20:Sales Register j3



    hi all,

    how to consolidate two line items in this query

    for item base and service base it show two seperate line item or rows in output

    but i want single row for same customer code

    SELECT MAX (T0.[CardCode]), T0.[CardName], sum(T1.[Quantity]) as Quantity, sum(T1.[LineTotal]) as Value, sum(T1.[VatSum]) as Tax, sum( T1.[LineTotal] + T1.[VatSum]) as Total,
    isnull(sum(T1.Quantity * t2.u_cmmc),'00')as 'Material Value' ,
    isnull (sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc) ,sum(T1.[LineTotal])) as CM ,
    isnull(((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc) )/(sum(T1.[LineTotal]))*100),'100') as 'CM%'
    FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry left outer join oitm t2 on t1.itemcode = t2.itemcode
    where T0.[DocType] = [%0] or t0.docdate = [%1] and t0.docdate = [%2]
    group by T0.CardCode,T0.CardName

    UNION

    SELECT MAX (T0.[CardCode]), T0.[CardName], -sum(T1.[Quantity]) as Quantity, -sum(T1.[LineTotal]) as Value, -sum(T1.[VatSum]) as Tax, -sum( T1.[LineTotal] + T1.[VatSum]) as Total, isnull(-sum(T1.Quantity * t2.u_cmmc),'00') as 'Material Value' ,
    isnull(-sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc) ,-sum(T1.[LineTotal]))as CM ,
    isnull(((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)) / (sum(T1.[LineTotal]))*100),'100') as 'CM%'
    FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry left outer join oitm t2 on t1.itemcode = t2.itemcode
    where T0.[DocType] = [%0] or t0.docdate = [%1] and t0.docdate = [%2]
    group by T0.CardCode,T0.CardName

    DB:2.20:Sales Register j3


    Check this threads,it will give u idea

    Re: Query For Sales Register

    SALES REGISTER QUERY

    Jeyakanthan

  • RELEVANCY SCORE 2.20

    DB:2.20:Suppliers Total To Pay k1



    Hello Experts!

    I have this query that i would like to add it 2 things:

    1. Calculate Total after VAT - If it VAT must so it will be = AA.[Price]AA.[OpenQty]1.16

    Else it will be = AA.[Price]*AA.[OpenQty].

    2. Calculate Total only with US Dollar: (AA.[Price]*AA.[OpenQty] in US dollar)

    IF Currency is $ = AA.[Price]*AA.[OpenQty]

    IF Currency is NIS = (AA.[Price]*AA.[OpenQty]) / AA.[SysRate]

    IF Currency is Euro = Euro Rate \ AA.[SysRate]

    IF Currency is Pound Strlnig = 1.6 * AA.[Price] * AA.[OpenQty]

    SELECT AA.[DocType] AS N'u05E1u05D5u05D2 u05DEu05E1u05DEu05DA',
    AA.[CardCode],
    AA.[CardName],
    AA.[LineStatus],
    AA.[DocNum],
    AA.[Project],
    AA.[OcrCode],
    AA.[GTotal],
    AA.[TaxStatus],
    AA.[TotalSumSy],
    AA.[Price],
    AA.[Quantity],
    AA.[OpenQty],
    AA.[Price]*AA.[OpenQty] 'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',
    AA.[Currency],
    AA.[SysRate] ,
    AA.[ItemCode],
    AA.[Dscription],
    AA.[DocDueDate],
    AA.[DocDate]
    FROM (
    SELECTN'u05D4u05D6u05DEu05E0u05EA u05E8u05DBu05E9' AS DocType,
    T0.[CardCode], T0.[CardName], T1.[LineStatus], T0.[DocNum], T1.[Project], T1.[OcrCode],T1.[GTotal], T1.[TaxStatus], T1.[TotalSumSy], T1.[Price], T1.[Quantity], T1.[OpenQty],T1.[Price]*T1.[OpenQty] as 'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',T0.[SysRate] ,T1.[Currency], T1.[ItemCode], T1.[Dscription], T0.[DocDueDate], T0.[DocDate]
    FROM OPOR T0
    LEFT JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
    WHERE (T0.[DocStatus] = 'O' AND T1.[LineStatus] = 'O')
    UNION ALL
    SELECTN'u05DEu05E9u05DCu05D5u05D7 u05E8u05DBu05E9'AS DocType,
    T0.[CardCode], T0.[CardName], T1.[LineStatus], T0.[DocNum], T1.[Project], T1.[OcrCode],T1.[GTotal], T1.[TaxStatus], T1.[TotalSumSy], T1.[Price], T1.[Quantity], T1.[OpenQty],T1.[Price]*T1.[OpenQty] as 'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',T0.[SysRate] ,T1.[Currency], T1.[ItemCode], T1.[Dscription], T0.[DocDueDate], T0.[DocDate]
    FROM OPDN T0
    LEFT JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
    WHERE (T0.[DocStatus] = 'O' AND T1.[LineStatus] = 'O')
    UNION ALL
    SELECTN'u05EAu05E2u05D5u05D3u05EA u05E8u05DBu05E9'AS DocType,
    T0.[CardCode], T0.[CardName], T1.[LineStatus], T0.[DocNum], T1.[Project], T1.[OcrCode],T1.[GTotal], T1.[TaxStatus], T1.[TotalSumSy], T1.[Price], T1.[Quantity], T1.[OpenQty],T1.[Price]*T1.[OpenQty] as 'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',T0.[SysRate] ,T1.[Currency], T1.[ItemCode], T1.[Dscription], T0.[DocDueDate], T0.[DocDate]
    FROM OPCH T0
    LEFT JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
    WHERE (T0.[DocStatus] = 'O' AND T1.[LineStatus] = 'O')
    UNION ALL
    SELECTN'u05D6u05D9u05DBu05D5u05D9 u05E8u05DBu05E9'AS DocType,
    T0.[CardCode], T0.[CardName], T1.[LineStatus], T0.[DocNum], T1.[Project], T1.[OcrCode],-1*T1.[GTotal], T1.[TaxStatus], -1*T1.[TotalSumSy], T1.[Price], -1*T1.[Quantity], T1.[OpenQty],T1.[Price]*(-1*T1.[Quantity]) as 'u05E1u05DBu05D5u05DD u05E4u05EAu05D5u05D7 u05DCu05EAu05E9u05DCu05D5u05DD',T0.[SysRate] ,T1.[Currency], T1.[ItemCode], T1.[Dscription], T0.[DocDueDate], T0.[DocDate]
    FROM ORPC T0
    LEFT JOIN RPC1 T1 ON T0.DocEntry = T1.DocEntry
    WHERE (T0.[DocStatus] = 'O' AND T1.[LineStatus] = 'O') ) AA

    DB:2.20:Suppliers Total To Pay k1


    I have this query that i would like to add it 2 things:

    1. Calculate Total after VAT - If it VAT must so it will be = AA.[Price]AA.[OpenQty]1.16

    Else it will be = AA.[Price]*AA.[OpenQty].

    2. Calculate Total only with US Dollar: (AA.[Price]AA.[OpenQty] in US dollar) IF Currency is $ = AA.[Price]AA.[OpenQty]

    IF Currency is NIS = (AA.[Price]*AA.[OpenQty]) / AA.[SysRate]

    IF Currency is Euro = Euro Rate \ AA.[SysRate]

    IF Currency is Pound Strlnig = 1.6 * AA.[Price] * AA.[OpenQty]

    MEITAL

  • RELEVANCY SCORE 2.20

    DB:2.20:Inner Join - Oact Into Query s3



    How do I inner join OACT table ( acctcode) into this formula?

    SELECT T0.[DocNum],T0.[CardCode], T0.[CardName], T0.[Address], T0.[DocStatus], T0.[NumAtCard], T1.[Dscription], T1.[VatSumSy], T1.[LineTotal], (T1.[LineTotal] + T1.[VatSumSy]) AS 'Row Total Incl Tax' FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

  • RELEVANCY SCORE 2.20

    DB:2.20:Uso De Fechas 71


    Hola a todos,
    Estoy haciendo un informe de inventario que me muestra por cada mes que cantidad de inventario tenia al finalizar el mes, y cul
    era el costo de este.
    Yo quisiera saber si hay alguna forma que
    yo cree una variable que con solo darle el ao me lo distribuya por cada mes y no tener que cambiar todas las fechas.
    Este es la query que tengo

    SELECT
    'Enero'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='01/31/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Febrero'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='02/28/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Marzo'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='03/31/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Abril'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='04/30/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Mayo'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='05/31/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Junio'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='06/30/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Julio'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='07/31/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Agosto'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='08/31/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Septiembre'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='09/30/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Octubre'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='10/31/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Noviembre'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='11/30/2011'
    AND
    T1.[Warehouse]='PB-PT'
    UNION
    ALL
    SELECT
    'Diciembre'AS Mes,SUM((T1.InQty-T1.OutQty)),SUM(T1.[TransValue])'CalcPr'
    FROM OINM T1

    WHERE
    T1.DocDate='12/31/2011'
    AND
    T1.[Warehouse]='PB-PT')

    Tambien que si el rango de cada mes no tiene movimiento ponga los campos en cero.

    JUAN CAMILO

    DB:2.20:Uso De Fechas 71

    Un amigo me ayudo a terminarla como lo necesitaba
    SELECT
    DISTINCT MONTH(A.DocDate)'Mes',
    (SELECT
    TOP 1 Z.DocDate FROM OINM Z
    WHERE MONTH(Z.DocDate)=MONTH(A.DocDate)
    AND YEAR(Z.DocDate)='2011'
    ORDER BY Z.DocDate
    DESC)'Ultima fecha'
    FROM OINM A
    ORDER
    BY MONTH(A.DocDate)
    --SELECT * FROM
    @t2

    SELECT
    MONTH(T2.FECHA),ISNULL(SUM((T1.InQty-T1.OutQty)),0),ISNULL(SUM(T1.[TransValue]),0)'CalcPr'
    FROM OINM T1
    inner join @t2 t2
    on T1.DocDate=t2.fecha
    AND
    T1.[Warehouse]='PB-PT'
    GROUP
    BY MONTH(T2.FECHA)
    ORDER
    BY MONTH(T2.FECHA)
    Gracias por su valiosa ayuda

  • RELEVANCY SCORE 2.20

    DB:2.20:Tunning Of Below Query zf


    Hi,

    Please help in tunning of the below.

    (SELECT T1.TRANSACTIONID, T1.NARRATION, T1.DEBITCREDITFLAG,
    T1.ACCOUNTPRODUCT_ACCPRODID ACCOUNTID, T1.POSTINGDATE, T1.VALUEDATE,T1.AMOUNT from wasadmin.transaction t1 where t1.transactionid in (
    select t.transactionid
    from wasadmin.transaction t
    where T.POSTINGDATE BETWEEN (SELECT BFLASTFREEZETIME FROM bankfusion.BFTB_PERSISTENTTAG)
    AND(SELECT BFCURRENTFREEZETIME FROM bankfusion.BFTB_PERSISTENTTAG)
    group by t.transactionid
    having sum(t.amount) != 0))

    Transaction table is huge table with lot of records. it taking 30 min fetch the data.

    DB:2.20:Tunning Of Below Query zf

    It looks like the table bankfusion.BFTB_PERSISTENTTAG has only one row. Is this true?

    How about this:
    SELECT t1.TRANSACTIONID, t1.NARRATION, t1.DEBITCREDITFLAG, t1.ACCOUNTPRODUCT_ACCPRODID ACCOUNTID,
    t1.POSTINGDATE, t1.VALUEDATE, t1.AMOUNT
    from wasadmin.transaction t1,
    (select transactionid, sum(amount) amount
    from wasadmin.transaction
    where POSTINGDATE BETWEEN (SELECT BFLASTFREEZETIME FROM bankfusion.BFTB_PERSISTENTTAG)
    AND (SELECT BFCURRENTFREEZETIME FROM bankfusion.BFTB_PERSISTENTTAG)
    group by transactionid
    having sum(amount) != 0) t2
    where t1.transactionid = t2.transactionid
    and t1.POSTINGDATE BETWEEN (SELECT BFLASTFREEZETIME FROM bankfusion.BFTB_PERSISTENTTAG)
    AND (SELECT BFCURRENTFREEZETIME FROM bankfusion.BFTB_PERSISTENTTAG)I would actually prefer to see the BFTB_PERSISTENTTAG values obtained beforehand and then plugged into the main query with bind variables:
    select BFLASTFREEZETIME, BFCURRENTFREEZETIME
    into v_last_freeze_time, v_curr_freeze_time
    from bankfusion.BFTB_PERSISTENTTAG;

    SELECT t1.TRANSACTIONID, t1.NARRATION, t1.DEBITCREDITFLAG, t1.ACCOUNTPRODUCT_ACCPRODID ACCOUNTID,
    t1.POSTINGDATE, t1.VALUEDATE, t1.AMOUNT
    from wasadmin.transaction t1,
    (select transactionid, sum(amount) amount
    from wasadmin.transaction
    where POSTINGDATE BETWEEN v_last_freeze_time AND v_curr_freeze_time
    group by transactionid
    having sum(amount) != 0) t2
    where t1.transactionid = t2.transactionid
    and t1.POSTINGDATE BETWEEN v_last_freeze_time AND v_curr_freeze_time

  • RELEVANCY SCORE 2.20

    DB:2.20:Detail Info sa



    Hi Experts,

    Our technical created query bellows:

    declare @currblnce1 nvarchar(50) declare @currblnce2 nvarchar(50)
    declare @currblnce3 nvarchar(50) declare @currblnce4 nvarchar(50)
    declare @currblnce5 nvarchar(50) declare @currblnce11 nvarchar(50)
    declare @currblnce21 nvarchar(50) declare @currblnce31 nvarchar(50)
    declare @currblnce41 nvarchar(50) declare @currblnce51 nvarchar(50)
    declare @acct1 nvarchar(20) declare @acct2 nvarchar(20)
    declare @acct3 nvarchar(20) declare @acct4 nvarchar(20)
    declare @acct5 nvarchar(20)
    select @acct1 = oact.acctcode from oact where (oact.acctcode = '1310000')
    select @acct2 = oact.acctcode from oact where (oact.acctcode = '1320000')
    select @acct3 = oact.acctcode from oact where (oact.acctcode = '1330000')
    select @acct4 = oact.acctcode from oact where (oact.acctcode = '1340000')
    select @acct5 = oact.acctcode from oact where (oact.acctcode = '1350000')
    set @currblnce1 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
    jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and
    (t1.account = @acct1) and t2.refdate between '01/01/2009'
    and '06/30/2009' group by t1.account) set @currblnce2 = (SELECT sum(-t1.credit+t1.debit)
    FROM OJDT T2 inner join jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and
    (t1.account = @acct2) and t2.refdate between '01/01/2009' and '06/30/2009' group by t1.account)
    set @currblnce3 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
    jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and
    (t1.account = @acct3) and t2.refdate between '01/01/2009' and '06/30/2009' group by t1.account)
    set @currblnce4 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
    jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and
    (t1.account = @acct4) and t2.refdate between '01/01/2009' and '06/30/2009' group by t1.account)
    set @currblnce5 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
    jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and
    (t1.account = @acct5) and t2.refdate between '01/01/2009' and '06/30/2009' group by t1.account)
    set @currblnce11 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
    jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and (t1.account = @acct1) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account) set @currblnce21 = (SELECT sum(-t1.credit+t1.debit)
    FROM OJDT T2 inner join jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and
    (t1.account = @acct2) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account)
    set @currblnce31 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
    jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and (t1.account = @acct3) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account) set @currblnce41 = (SELECT sum(-t1.credit+t1.debit)
    FROM OJDT T2 inner join jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and
    (t1.account = @acct4) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account)
    set @currblnce51 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
    jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit 0 or t1.debit 0) and
    (t1.account = @acct5) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account)
    SELECT t1.account, case
    when t1.account = @acct1 then @currblnce11
    when t1.account = @acct2 then @currblnce21
    when t1.account = @acct3 then @currblnce31
    when t1.account = @acct4 then @currblnce41
    when t1.account = @acct5 then @currblnce51
    else null end as 'O.B. From start of fiscal year',
    case
    when t1.account = @acct1 then @currblnce1
    when t1.account = @acct2 then @currblnce2
    when t1.account = @acct3 then @currblnce3
    when t1.account = @acct4 then @currblnce4
    when t1.account = @acct5 then @currblnce5
    else null end as 'O.B. From start of fiscal year',
    sum(t1.debit)"Debit", sum(t1.credit)"Credit",
    case when t1.account = @acct1 then @currblnce1+(sum(t1.debit-t1.credit))
    when t1.account = @acct2 then @currblnce2+(sum(t1.debit-t1.credit))
    when t1.account = @acct3 then @currblnce3+(sum(t1.debit-t1.credit))
    when t1.account = @acct4 then @currblnce4+(sum(t1.debit-t1.credit))
    when t1.account = @acct5 then @currblnce5+(sum(t1.debit-t1.credit)) else null end as 'Balance'
    FROM OJDT T2 inner join jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit
    0 or t1.debit 0) and (t1.account between '1310000' and '1350000') and t2.refdate
    between '07/01/2009' and '07/31/2009' group by t1.account order by t1.account asc

    DB:2.20:Detail Info sa


    Hi Steve,

    There is no way to tell if this query is Correct or not because we could not test. However, your requirement to make it only one account may not be doable if you need to get the account balance for those 5 accounts.

    Thanks,

    Gordon

  • RELEVANCY SCORE 2.20

    DB:2.20:Rewrite Sql-Help 83


    Is there a way this query can be combined into one simplified
    single select statement instead of with UNION. Appreciate your help.
    Thanks.

    SELECT T1.COL1,
    T1.COL2,
    T2.COL4,
    T1.COL3,
    NVL(T2.COL5, 'FIXED')
    FROM T1 , T2
    WHERE T1.COL2 = T2.COL2 AND
    T1.COL1 = T2.COL1 AND
    NVL(T2.COL7, '0') = NVL(T1.COL6, '0')
    UNION
    SELECT T1.COL1,
    T1.COL2,
    T2.COL4,
    T1.COL3,
    NULL
    FROM T1 , T2
    WHERE T1.COL2 = T2.COL2 AND
    T1.COL1 = T2.COL1 AND
    NVL(T2.COL7, '0') NVL(T1.COL6, '0')

  • RELEVANCY SCORE 2.20

    DB:2.20:Are These Two Statements The Same 13


    update A.BIG_TABLE A set A.COLUMN1 = ( select T1.NEW_VALUE_COL from LOOKUP_TABLE T1 where A.COLUMN1 = T1.CURR_VALUE_COL )
    where
    A.COLUMN1 in ( select T2.CURR_VALUE_COL from LOOKUP_TABLE T2 )

    and

    update A.BIG_TABLE A set A.COLUMN1 = ( select T1.NEW_VALUE_COL from LOOKUP_TABLE T1 where A.COLUMN1 = T1.CURR_VALUE_COL )

    I.E is the where clause in the first statement redundant ?

    Many thanks

    DB:2.20:Are These Two Statements The Same 13

    Nope !

    merge into BIG_TABLE a using (select b.* from LOOKUP_TABLE b)
    on (a.column1 = b.curr_val_col)
    when matched then update set a.column1 = b.newvalue;

    gives, ORA-38104: Columns referenced in the ON Clause cannot be updated:

    Must be a restriction.

  • RELEVANCY SCORE 2.20

    DB:2.20:Query ss



    hai

    SELECT T0.[DocNum],T0.TaxDate,T1.[AcctCode], T1.[AcctName], T1.[Descrip],T1.[SumApplied] FROM [dbo].[OVPM] T0 INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum WHERE T0.[DocType] ='a' and T0.TaxDate ='[%0]' AND T0.TaxDate ='[%1]' and T1.[AcctCode]='OTH10002'

    SELECT T0.[DocNum],t0.taxdate,t1.acctcode,T1.[Dscription],T1.[Price], T0.[DocTotal] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.TaxDate ='[%0]' AND T0.TaxDate ='[%1]' and T1.[AcctCode]='oth10002'

    Above both query i want mingle how do i do ????

    DB:2.20:Query ss


    Hi,

    Check this then :

    Select T0.DocNum,T0.TaxDate,T1.AcctCode, T1.AcctName, T1.Descrip,T1.SumApplied,
    T2.DocNum,t2.taxdate,t3.acctcode,T3.Dscription,T3.Price, T2.DocTotal
    from OVPM t0 left join VPM4 t1 on t0.DocEntry = t1.DocNum
    Left join OPCH t2 on t2.ReceiptNum = t0.DocNum
    Left join PCH1 t3 on t3.DocEntry = t2.DocEntry
    where T0.DocType ='a' and T0.TaxDate ='%0' AND T0.TaxDate ='%1'
    and T1.AcctCode='OTH10002'
    and T2.TaxDate ='%0' AND T2.TaxDate ='%1' and T3.AcctCode='oth10002'

  • RELEVANCY SCORE 2.20

    DB:2.20:Redbull Videos c9


    Redbull videos bowling alley and home square, the game space must be on its way!



    Im a Crunchy Nut!

    DB:2.20:Redbull Videos c9

    Redbull videos bowling alley and home square, the game space must be on its way!



    Im a Crunchy Nut!

  • RELEVANCY SCORE 2.20

    DB:2.20:Were Redbull Space Go? js






    PS3 Trophies

    DB:2.20:Were Redbull Space Go? js

    yeeehhhhh there it is got scared for a sec there lol



    PS3 Trophies

  • RELEVANCY SCORE 2.20

    DB:2.20:Boolean Error fp


    Hi -
    I am getting this error

    An expression of non-boolean type specified in a context where a condition is expected, near ';'.
    Code is below:

    UPDATE Guy_Working..OL_HB_110310_110509

    SET Guy_Working..OL_HB_110310_110509.PN_ID = (SELECT T5.PN_ID, T1.Forename, T1.Surname, T1.Postcode

    FROM Guy_Working..OL_HB_110310_110509 T1, CFOUR..vLandmarkPeople T5

    WHERE T1.Forename = T5.PN_FIRST_NAME

    AND T1.Surname = T5.PN_SURNAME

    AND T1.Postcode = T5.ADD_POSTCODE)

    WHERE EXISTS

    (SELECT T5.PN_ID, T1.Forename, T1.Surname, T1.Postcode

    FROM Guy_Working..OL_HB_110310_110509 T1, CFOUR..vLandmarkPeople T5

    WHERE T1.Forename = T5.PN_FIRST_NAME

    AND T1.Surname = T5.PN_SURNAME

    AND T1.Postcode = T5.ADD_POSTCODE);

    DB:2.20:Boolean Error fp

    No problem. You should mark this question as answered by UWEJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

  • RELEVANCY SCORE 2.20

    DB:2.20:Re: Ouputting Refcursor Results Using Utl_File.Put_Line? zz


    For more information the procedure is:

    PROCEDURE p_get_group_list(v_group_id IN v_group.group_id%type,
    v_recordset OUT SYS_REFCURSOR)
    IS
    BEGIN
    Open v_recordset for
    select t1.group_id as group_id,
    t1.group_name as group_name,
    t1.group_desc as group_desc
    from v_group t1
    where t1.group_id = v_group_id
    and t1.cancelled_ind = 'C'
    order by upper(t1.group_name);

    END p_get_group_list;

    DB:2.20:Re: Ouputting Refcursor Results Using Utl_File.Put_Line? zz

    I am using the following code and got the error:Data type inconsistent.
    Getting Error at line

    FETCH custodian_extract_header
    INTO ce_line;

    since i have many columns with different date types and i have defined ce_line as Varchar 2 it
    giving me inconsistent data type.

    If i select only VARCHAR columns for my V_CHR_SQL, the below code runs perfectly fine.

    I need to know what data type has to be defined for ce_line so that it accepts any datatype.

    Decleare:
    ce_line varchar2(20000);
    V_DB_DIR_NAME varchar2 (34) := ' ';
    ce_file UTL_FILE.file_type;

    Body:

    open custodian_extract_header for V_CHR_SQL;

    IF UTL_FILE.is_open (ce_file)
    THEN
    LOOP
    FETCH custodian_extract_header
    INTO ce_line;

    EXIT WHEN custodian_extract_header%NOTFOUND;

    UTL_FILE.put_line (ce_file, ce_line, FALSE);
    END LOOP;
    END IF;

    CLOSE custodian_extract_header;
    UTL_FILE.fclose (ce_file);

  • RELEVANCY SCORE 2.19

    DB:2.19:Without Linked Server Can We Fetch Record From 2 Different Data Base Using Ssis a8


    Hi ,
    i have query which basically join but from 2 different server and data base also which i am using SSIS OLE DB source , i mention query,
    my infrasturcure team said they dont want to create linked server because locking issuse , so how i cna achive this thing without creating linked server, can we do with 2 differnt connection string, query below: i marked as bold , this query working when
    using one Database.
    with cte
    as (
    SELECT t1.FTPStation_ID,
    t1.year,t1.jDay,t1.Time,t1.WindSpeed,t1.WindDirection,

    t1.Maxwindgust,t1.Dirwindgust,t1.Temp,t1.Dewpoint,t1.RH,t1.Maxtemp,

    t1.Mintemp,t1.Maxwind,t1.Timeofgust,t1.Dirofgust,
    t2y.Outdoor_TemperatureConf,

    t2y.Wind_SpeedConf,
    t2y.Wind_DirectionConf,

    t2y.DewPointConf,
    t2y.Confidence_date, t1.observationDateTime,

    --[dbo].[GetEasternTimeOffset_fn] (t1.zipCode, getdate(), getUTCDate(),t1.observationDateTime) as UTCTime,

    row_number()
    over (partition
    by [FTPStation_ID], t1.observationDateTime

    ORDER BY t2y.Confidence_date
    desc )as row
    FROM
    [dc4-nmpp-db1].[SDGE].dbo.[SDGEData]
    as t1 INNER
    JOIN

    [awsmain].[BugMain].dbo.[LastStationConfidenceHistory] t2y
    ON t1.AWSStation_ID
    = t2y.Station_ID

    Where t2y.Confidence_date
    =[dc4-nmpp-db1].[SDGE].[dbo].[GetEasternTimeOffset_fn]
    (t1.zipCode,
    getdate(),
    getUTCDate(),t1.observationDateTime))

    SELECT cte.FTPStation_ID,cte.year,cte.jDay,cte.Time,cte.WindSpeed,cte.WindDirection,

    cte.Maxwindgust,cte.Dirwindgust,cte.Temp,cte.Dewpoint,cte.RH,cte.Maxtemp,

    cte.Mintemp,cte.Maxwind,cte.Timeofgust,cte.Dirofgust,
    cte.Outdoor_TemperatureConf,

    cte.Wind_SpeedConf,
    cte.Wind_DirectionConf,

    -- cte.UTCtime,
    cte.DewPointConf,
    cte.Confidence_date, cte.observationDateTime
    from CTE where row=1

    --order by cte.FTPStation_ID

    Union
    select t1.FTPStation_ID, t1.year,t1.jDay,t1.Time,t1.WindSpeed,t1.WindDirection,

    t1.Maxwindgust,t1.Dirwindgust,t1.Temp,t1.Dewpoint,t1.RH,t1.Maxtemp,

    t1.Mintemp,t1.Maxwind,t1.Timeofgust,t1.Dirofgust,
    null,

    null, null,

    null, null, t1.observationDateTime
    FROM dbo.SDGEData
    as t1 where t1.AWSStation_ID
    not in
    (Select
    distinct(t2y.Station_ID)
    from
    dbo.LastStationConfidenceHistory
    t2y)

    DB:2.19:Without Linked Server Can We Fetch Record From 2 Different Data Base Using Ssis a8

    Hi ETL,
    yes and I meant this exactly in my statement, with ORDER BY he needs to set IsSorted to true too.
    Regards,http://www.rad.pasfu.com

  • RELEVANCY SCORE 2.19

    DB:2.19:How To Add Parameter Into Query 3p



    select t0.cardcode, t1.itemcode, t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP] from ocrd t0 inner join [DEV].[dbo].[2yearbycarditem] t1 on t0.cardcode = t1.cardcode

    WHERE t1.ItemCode like '12V%'

    group by t0.cardcode, t1.itemcode, t1.[year], t1.[month]

    order by t0.cardcode, t1.[year],t1.[month]

    how do i change this so the itemcode is a parameter. They need to enter an itemcode that starts with e.g. 12V

    I have tried

    WHERE t1.ItemCode like '%' + @itemcode + '%'

    this does not work.

    please help

    thanks in advance- Janice

    DB:2.19:How To Add Parameter Into Query 3p


    Hi Janice Middleton....

    Try This, If you want to pull all items the use % in the selection

    /* select * from oitm x1 */ DECLARE @d1 AS nvarchar(100)

    SET @d1=/* x1.ItemCode */ '[%0]'

    SELECT t0.cardcode,

    t1.itemcode,

    t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP]

    FROM ocrd t0

    INNER JOIN [DBO].[2yearbycarditem] t1 ON t0.cardcode = t1.cardcode

    INNER JOIN oitm oi ON oi.ItemCode=t1.itemcode

    WHERE oi.ItemCode LIKE @d1

    GROUP BY t0.cardcode,

    t1.itemcode,

    t1.[year], t1.[month]

    ORDER BY t0.cardcode,

    t1.[year],t1.[month]

    Regards

    Kennedy

  • RELEVANCY SCORE 2.19

    DB:2.19:Ap Invoice s9



    hai

    SELECT T0.DocNum,T0.TaxDate,T1.AcctCode, T1.AcctName, T1.Descrip,T1.SumApplied FROM dbo.OVPM T0 INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum WHERE T0.DocType ='a' and T0.TaxDate ='%0' AND T0.TaxDate ='%1' and T1.AcctCode='OTH10002'

    SELECT T0.DocNum,t0.taxdate,t1.acctcode,T1.Dscription,T1.Price, T0.DocTotal FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.TaxDate ='%0' AND T0.TaxDate ='%1' and T1.AcctCode='oth10002'

    Above both query i want mingle how do i do ???? i changed my query inner join and left join but some column data is empty.

    DB:2.19:Ap Invoice s9


    Hi,

    Try:
    SELECT T0.DocNum,T0.TaxDate,T1.AcctCode, T1.AcctName, T1.Descrip,T1.GrossAmnt,T1.SumApplied

    FROM dbo.OVPM T0
    INNER JOIN VPM4 T1 ON T0.DocNum = T1.DocNum
    WHERE T0.DocType ='a' and T0.TaxDate ='[%0]' AND T0.TaxDate ='[%1]' and T1.AcctCode='[%2]'

    UNION ALL

    SELECT T0.DocNum,t0.taxdate,t1.acctcode,T2.Acctname,T1.Dscription,T1.Price, T0.DocTotal
    FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OACT T2 ON T2.AcctCode=T1.Account
    WHERE T0.TaxDate ='[%0]' AND T0.TaxDate ='[%1]' and T2.AcctCode='[%2]'

  • RELEVANCY SCORE 2.19

    DB:2.19:Pending Quantity ax



    Hi Guys...

    We need a report for pending Orders and pending Quantities for Sales.

    And for this i used a query .

    SELECT T0.DocNum as 'SO No', T0.DocDate as 'SO Date',
    T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription,
    T1.Quantity AS 'SO Qty', (T1.Quantity-T1.OpenQty) AS 'Delivered Qty',
    T1.OpenQty AS 'Balance Qty'
    FROM ORDR T0
    INNER JOIN RDR1 T1 ON T1.DocEntry = T0.DocEntry
    INNER JOIN ITM1 T2 ON T2.ItemCode = T1.ItemCode
    WHERE
    T1.LineStatus = 'O'
    AND
    T0.DocDate BETWEEN '[%0]' AND '[%1]' and ((T1.Quantity-T1.OpenQty) != 0 )

    DB:2.19:Pending Quantity ax


    Hi Gordon,,

    Really its solves the problem... thanks..

    I will not be regularly using forum so not replied.

  • RELEVANCY SCORE 2.19

    DB:2.19:How To Use Execute Immediate Command For Below Query jk



    Hi,

    How to use 'Execute Immediate'
    commandfor below query. I am working on oracle.

    SELECT T1.PROD_LINE_CD,T1.BUS_GROUP_CD,T1.BUS_UNIT_CD,T1.COUNTRY_CD
    FROM Table1 T1,Table2 T2
    WHERE T1.AUTH_PROFILE_CD=T2.AUTH_PROFILE_CD
    AND T2.ACTIVE_FL='Y';
    AND T1.PROD_LINE_CD = :v_PROD_LINE_CD
    AND T1.BUS_GROUP_CD= :v_BUS_GROUP_CD
    AND T1.BUS_UNIT_CD=:v_BUS_UNIT_CD
    AND T1.COUNTRY_CD=:v_COUNTRY_CD

    Thanks in advance.

    DB:2.19:How To Use Execute Immediate Command For Below Query jk

    Hi,

    How to useExecute Immediatefor below query. I am working on oracle.

    SELECT T1.PROD_LINE_CD,T1.BUS_GROUP_CD,T1.BUS_UNIT_CD,T1.COUNTRY_CD
    FROM Table1 T1,Table2 T2
    WHERE T1.AUTH_PROFILE_CD=T2.AUTH_PROFILE_CD
    AND T2.ACTIVE_FL='Y';
    AND T1.PROD_LINE_CD = :v_PROD_LINE_CD
    AND T1.BUS_GROUP_CD= :v_BUS_GROUP_CD
    AND T1.BUS_UNIT_CD=:v_BUS_UNIT_CD
    AND T1.COUNTRY_CD=:v_COUNTRY_CD

    Thanks in advance.

    I agree with samuelare you asking howto executeabove qwery in SQl server by converting it compatible to SQL server.You can use SSMA for converting queries infact if you have lots of Oracle queries you can take help of this.
    http://blogs.msdn.com/b/ssma/archive/2012/01/31/microsoft-sql-server-migration-assistant-ssma-5-2-is-now-available.aspxPlease mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

  • RELEVANCY SCORE 2.19

    DB:2.19: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:2.19: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 2.19

    DB:2.19:Re: Repeated Data In Database Table f3


    try like this
    select
    t1.ID,t1.date_received,t1.account_number,t1.category, t2.id, t2.date_received
    from
    complaint_table t1, complaint_table t2
    where
    t1.account_number = t2.account_number
    and t1.category = t2.category
    and t1.date_received = t2.date_received - 90
    and t1.id t2.id

    DB:2.19:Re: Repeated Data In Database Table f3

    Maybewithtbl(account_number,category) as(select '200','pqr' from dual union all select '201','abc' from dual union all select '200','pqr' from dual union all select '201','abc' from dual union all select '201','abc' from dual union all select '300','xyz' from dual)select account_number,category,count(distinct account_number||'~'||category) the_count from tbl group by account_number,categoryACCOUNT_NUMBERCATEGORYTHE_COUNT300xyz1200pqr1201abc1RegardsEtbin

  • RELEVANCY SCORE 2.19

    DB:2.19:Running Sumsaverages Access 2010 38


    Need help very new at this...... Ihave created query with a running sum and running average column. The query evaluates all the data in table. am looking to have on each row/record a running sum/average of the previous 12 records? Any suggestions...alm
    wt

    SELECT T1.id, T1.failures, (select Sum([well count].[failures]) as Total
    From [well count]
    Where [well count].[id] =T1.[id]) AS Total, (select avg([well count].[wellcount]) as average
    From [well count]
    Where [well count].[id] =T1.[id]) AS average, T1.Year, T1.wellcount, T1.[Rental Wellcount], T1.Permwellcount, T1.[Perm Failures], T1.[Rental Failures], T1.[Main Field], T1.[Total Wellcount], T1.FFR
    FROM [well count] AS T1
    WHERE (((T1.id)53) AND ((T1.Year)#1/1/2014#));

  • RELEVANCY SCORE 2.19

    DB:2.19:Zona Redbull cf


    Scusate ma come faccio a volare ? dove si deve andare ? grazie.......

    DB:2.19:Zona Redbull cf


    Dai un'occhiata a questo thread.

    Chiudiamo.

  • RELEVANCY SCORE 2.19

    DB:2.19:How To Use One Word To Replace A Lot Of Same Word To Avoid Clerical Error 8z



    Hi,

    I have a specific project which needs a lot of union sqls and most field in the sqls are the same,

    say,

    select t1.a1,t1.a2,t1.a3,t1.a4 from have as t1

    union

    select t1.a1,t1.a2,t1.a3,t1.a5 from have as t1

    union

    select t1.a1,t1.a2,t1.a3,t1.a6 from have as t1

    because i 'm debuging the program, lots of fields is confusing ,

    I want the following

    select var1 ,t1.a4 from have as t1

    union

    select var1,t1.a5 from have as t1

    union

    select var1,t1.a6 from have as t1

    which means use var1 to replace t1.a1,t1.a2,t1.a3

    can it be achieved?

    Thanks.

    DB:2.19:How To Use One Word To Replace A Lot Of Same Word To Avoid Clerical Error 8z


    Art,Hima,

    First thank you for your help.

    Second ,I have to apologize.I have thought of the %let method, but instinctly thought it won't work.

    I should at least have a try, cause it is so easy to resolve if I had a try..

    Thank you both for your patience to give a hand to a newbie especially on a so silly question.

  • RELEVANCY SCORE 2.19

    DB:2.19:Batch Numbers In Credit Note Layout z3



    Hi Guys,

    I have the following query that was based on a query I was using for Invoices to bring n the Batch Numbers and Expiry Dates, I need this to function for Credit Notes, sadly everything appears to be working except it is not pulling in the Batch Number and Expiry date from the Invoice, can anyone point me in the right direction ?

    Select T2.CardCode, T2.CardName, T3.PymntGroup, T0.DocDate, T0.DocDueDate,T0.DocCur, T0.PayToCode, T0.Address, T0.ShipToCode, T0.Address2, T0.DocNum, T0.DocEntry AS 'Doc Entry', T0.NumatCard, T0.Comments,T0.VatSum,T0.VatSumFC, T0.DocTotalFC, T0.Comments,T1.ItemCode, T1.Dscription, T1.FreeTxt, T1.Quantity, T1.Price, T1.PriceBefDi AS 'Price Before Discount', T1.LineTotal, T1.VatPrcnt, T1.VatGroup, T1.TotalFrgn, T0.DocTotal, T8.ItemCode, T8.DistNumber, T8.ExpDate, T1.BaseatCard, I1.BatchNum, T1.Height1, T1.Hght1Unit, T1.Height2, T1.Hght2Unit, T1.Width1, T1.Wdth1Unit, T1.Width2, T1.Wdth2Unit, T1.Length1, T1.Len1Unit, T1.Len1Unit, T1.Length2, T1.Len2Unit, T1.Volume, T1.VolUnit, T1.Weight1, T1.Wght1Unit, T1.Weight2, T1.Wght2Unit, T1.Factor1, T1.Factor2, T7.Linetotal AS 'INV3 Linetotal', T7.TotalFrgn, I1.Quantity AS 'Batch Qty', T1.BaseRef

    FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode=T0.CardCode INNER JOIN OCTG T3 ON T3.GroupNum=T2.GroupNum LEFT Join RIN1 T6 On T6.DocEntry=T1.BaseEntry and T6.LineNum=T1.BaseLine LEFT OUTER JOIN RIN3 T7 ON T0.DocEntry = T7.DocEntry

    left join IBT1 I1 on T1.ItemCode=I1.ItemCode and T6.DocEntry=I1.BaseEntry and T6.ObjType=I1.BaseType

    left join OBTN T8 on T8.ItemCode=I1.ItemCode and I1.BatchNum=T8.DistNumber

    Any help appreciated

    Kind regards

    Sean Martin

    DB:2.19:Batch Numbers In Credit Note Layout z3


    Hi Sean.......

    If you have done Delivery also then it may not be the query.

    You have to make slight change in the query......

    Select T0.CardCode, T0.CardName, T2.PymntGroup, T0.DocDate, T0.DocDueDate,T0.DocCur, T0.PayToCode, T0.Address, T0.ShipToCode, T0.Address2, T0.DocNum, T0.DocEntry AS 'Doc Entry', T0.NumatCard, T0.Comments,T0.VatSum,T0.VatSumFC, T0.DocTotalFC, T0.Comments,T1.ItemCode, T1.Dscription, T1.FreeTxt, T1.Quantity, T1.Price, T1.PriceBefDi AS 'Price Before Discount', T1.LineTotal, T1.VatPrcnt, T1.VatGroup, T1.TotalFrgn, T0.DocTotal, T8.ItemCode, T8.DistNumber, T8.ExpDate, I1.BatchNum, T1.Height1, T1.Hght1Unit, T1.Height2, T1.Hght2Unit, T1.Width1, T1.Wdth1Unit, T1.Width2, T1.Wdth2Unit, T1.Length1, T1.Len1Unit, T1.Len1Unit, T1.Length2, T1.Len2Unit, T1.Volume, T1.VolUnit, T1.Weight1, T1.Wght1Unit, T1.Weight2, T1.Wght2Unit, T1.Factor1, T1.Factor2, T7.Linetotal AS 'RIN3 Linetotal', T7.TotalFrgn, I1.Quantity AS 'Batch Qty', T1.BaseRef

    FROM ORIN T0 Inner Join RIN1 T1 On T0.DocEntry=T1.DocEntry INNER JOIN INV1 T20 ON T1.BaseEntry = T20.DocEntry Inner Join OINV T21 On T20.DocEntry=T21.DocEntry INNER JOIN OCTG T2 ON
    T0.GroupNum = T2.GroupNum LEFT Join RIN12 T3 On T3.DocEntry=T0.DocEntry LEFT JOIN OITM T4 On T4.ItemCode=T1.ItemCode LEFT JOIN OCHP T5 On T4.ChapterID=T5.AbsEntry
    Inner Join DLN1 T6 On T6.DocEntry=T20.BaseEntry and T6.LineNum=T20.BaseLine
    left outer join IBT1 I1 on T1.ItemCode=I1.ItemCode and (T6.DocEntry=I1.BaseEntry and T6.ObjType=I1.BaseType)
    left outer join OBTN T8 on T8.ItemCode=I1.ItemCode and I1.BatchNum=T8.DistNumber

  • RELEVANCY SCORE 2.19

    DB:2.19:Request Help For Below Query 19


    Hi All, need humble help. Please guide why do i get duplicate rows in here.SELECT -- T1.FEED_ID,T1.FEED_RECORD,EX.EXCEPTION_LOG_ID REGEXP_SUBSTR(REGEXP_SUBSTR(t1.EXCEPTION_RECORD, '([^^])+', 1, t2.column_value),'[^|]+',1,1) EXCEPTION_CODE, REGEXP_SUBSTR(REGEXP_SUBSTR(t1.EXCEPTION_RECORD, '([^^])+', 1, t2.column_value),'[^|]+',1,2) ATTRIBUTE_NAME, REGEXP_SUBSTR(REGEXP_SUBSTR(t1.EXCEPTION_RECORD, '([^^])+', 1, t2.column_value),'[^|]+',1,3) ATTRIBUTE_VALUE, T1.FEED_RECORD,T1.FEED_ID,T2.COLUMN_VALUE FROM STG_EXCEPTION_GLOBAL_ETL t1 CROSS JOIN TABLE ( CAST ( MULTISET ( SELECT LEVEL FROM DUAL CONNECT BY LEVEL = REGEXP_COUNT(t1.EXCEPTION_RECORD, '([^^])+') ) AS SYS.odciNumberList ) ) t2,EXCEPTION_LOG EX WHERE T1.FEED_RECORD = EX.EXCEPTION_RECORD AND T1.PROCESS_ID = EX.PROCESS_ID AND T1.FEED_ID = EX.FEED_IDMany thanksArpit Agrawal

    DB:2.19:Request Help For Below Query 19

    We don't have access to your database. So without knowing the table structure and the data in it, its highly difficult to come to conclusion. So I would suggest you read Re: 2. How do I ask a question on the forums? from FAQ and post the necessary details.

  • RELEVANCY SCORE 2.19

    DB:2.19:Oracle Sql Left Outer Join p1


    SELECT A, B, C, D from T1 where T1.loc=5 AND T1.Prod is NULL and T1.CAL=1

    SELECT A1, B1, C1, D1 from T1 where T1.loc=5 AND T1.Prod is NULL and T1.CAL=2

    Finally I need result like that A, A1, B, B1, C, C1, D, D1 colums
    Need single query with the using of left outer join.

    Can any body help me with proper Query

    DB:2.19:Oracle Sql Left Outer Join p1

    You post in wrong forum.
    Pleas post at SQL and PL/SQL and you will get quick and exact solutions..

    Hope this helps..

    Hamid

  • RELEVANCY SCORE 2.19

    DB:2.19:Problema Con Parametros jz



    Hola que tal,

    Tengo un query que sin parametros funciona correctamente, pero a la hora de ponerselos, me marca errores sin sentido, qu podra modificarle para que SAP lo reconozca? ya que directamente en SQL si funciona. Les dejo el query:

    SELECT T0.U_CHOFERORIG, T1.Code, T1.U_INICIO, T1.U_TERM, T1.U_DIAS, SUM(T0.U_SUELDO) [Sueldo], T1.U_NOCOMP,

    (ISNULL(T0.U_SUELDO,0)-ISNULL(T1.U_NOCOMP,0)) [Neto],

    (ISNULL(T1.U_DIAS,0) * 9.61) [IMSS],

    (SUM(ISNULL(T0.U_SUELDO,0))*0.075) [ISPT],

    ISNULL(U_INFO,0) [Infonavit],

    ((ISNULL(T0.U_SUELDO,0)-ISNULL(T1.U_NOCOMP,0))-(ISNULL(T1.U_DIAS,0) * 9.61)-(SUM(ISNULL(T0.U_SUELDO,0))*0.075) - ISNULL(U_INFO,0)) [Total a pagar],

    ISNULL(T1.U_ANTIC,0) [Anticipo], ISNULL(T1.U_ADEUDO,0) [Adeudo], ISNULL(T1.U_NEXTEL,0) [Nextel],

    (((ISNULL(T0.U_SUELDO,0)-ISNULL(T1.U_NOCOMP,0))-(ISNULL(T1.U_DIAS,0) * 9.61)-(SUM(ISNULL(T0.U_SUELDO,0))*0.075) - ISNULL(U_INFO,0))

    -ISNULL(T1.U_ANTIC,0) -ISNULL(T1.U_ADEUDO,0) - ISNULL(T1.U_NEXTEL,0)) [Saldo]

    FROM

    ORDR AS T0 INNER JOIN dbo.[@LIQ] AS T1 ON T0.U_LIQNUM = T1.Code

    WHERE

    t0.U_CHOFERORIG = [%0] AND T1.Code = [%1]

    GROUP BY

    T0.U_CHOFERORIG, T1.U_INICIO, T1.U_TERM, T1.U_DIAS, T1.U_NOCOMP, T0.U_SUELDO, T1.U_INFO,

    T1.U_ANTIC, T1.U_ADEUDO, T1.U_NEXTEL, t1.Code

    Saludos!

  • RELEVANCY SCORE 2.19

    DB:2.19:Query Help - Using Alias In Column Equation 8m



    Hi All,

    I have selected some data as an alias and would like to refer to the alias in another equation column. I cannot figure out how to do this correctly.

    Here is my full query below, with a NULL called '3 Mnth Average' column because I am unsure how to reference the Alias and data type correctly for the required equation.

    For example, I want to return the '3 Mnth Average' result being the '3 Mnth Total' Alias divided by 3. ('3 Mnth Total' / 3).... Every time I try... i fail.

    Any help would be most appreciated.

    Best regards,

    John

    SELECT T0.ITEMCODE, T0.ITEMNAME,
    Cast((T0.ONHAND - T0.IsCommited) As INT) As 'Available',
    Cast(T0.ONHAND As INT) As 'In Stock', Cast(T0.IsCommited As INT) As 'Committed',
    Cast((SELECT SUM(T1.[Quantity]) FROM INV1 T1 WHERE T1.ITEMCODE = T0.ITEMCODE
    AND DATEPART(mm,T1.[DocDate]) = DATEPART(mm,getdate())-3
    AND DATEPART(mm,T1.[DocDate]) = DATEPART(mm,getdate())) As INT) As '3 Mnth Total',
    (NULL) As '3 Mnth Average',
    CAST ((SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) As INT) AS 'JAN QTY',
    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB QTY',
    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR QTY',
    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR QTY',
    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY QTY',
    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN QTY',
    CAST ((SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) As INT) AS 'JUL QTY',
    CAST ((SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS INT) AS 'AUG QTY',
    CAST ((SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) As INT) AS 'SEP QTY',
    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT QTY',
    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV QTY',
    (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
    WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE =
    T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC QTY'
    FROM dbo.OITM T0
    LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode
    WHERE T0.SellItem = 'Y'
    GROUP BY T0.ItemCode, T0.ItemName, T0.OnHand, T0.IsCommited, YEAR(T1.DOCDATE)
    HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())
    ORDER BY T0. ITEMCODE

    DB:2.19:Query Help - Using Alias In Column Equation 8m


    Hi John,

    If you are not using temporary table, you have to rewrite complete formula to get 3 month average. There is no way to reference within the same query.

    Thanks,

    Gordon

  • RELEVANCY SCORE 2.19

    DB:2.19:Query Help Required To Link Two Fields 98


    Hi all,I have made an query as under :SELECT T2.Period, T0.dept, T0.empID, T0.firstName, T0.lastName, T1.U_ebasic, T1.U_ehra, T1.U_etrspt, T1.U_ecola, T1.U_emeins, T1.U_education, T1.U_grosssal,T1.U_totduc, T1.U_oadd, T1.U_netsal, T1.U_bank, T1.U_branch, T1.U_accno FROM OHEM T0 , [dbo].[@PAYROLL_PR_DETAIL] T1, [dbo].[@PAYROLL_PROLL_HEAD] T2 where t2.docentry=t1.docentry and t1.U_ecode=t0.empidNow I want that the Department Full NAME as mentioned in the Human Resource screen for that employee should come. Apart from it I also want to link a new field in this query user_code from OUSR table for each employee.Can you please help me to make this query.Regards,kamlesh

    DB:2.19:Query Help Required To Link Two Fields 98

    Try this one to see:SELECT T2.Period, T0.dept, T3.Name, T0.empID, T0.firstName, T0.lastName, T4.USER_CODE,T1.U_ebasic, T1.U_ehra, T1.U_etrspt, T1.U_ecola, T1.U_emeins, T1.U_education, T1.U_grosssal,T1.U_totduc, T1.U_oadd, T1.U_netsal, T1.U_bank, T1.U_branch, T1.U_accnoFROM dbo.OHEM T0 INNER JOIN dbo.OUDP T3 ON T3.Code = T0.dept INNER JOIN dbo.OUSR T4 ON T4.INTERNAL_K = T0.userId,dbo.@PAYROLL_PR_DETAIL T1, dbo.@PAYROLL_PROLL_HEAD T2where t2.docentry=t1.docentry and t1.U_ecode=t0.empidThanks,Gordon

  • RELEVANCY SCORE 2.19

    DB:2.19:Sales By Style 19



    Hello -

    We currently use the query below that shows us sales by stocking unit per month.

    However, we would like to have a summary view that just shows us the sales by Style # each month. The Style # is the first four characters in our nine digit stocking number. The two characters after that is the color and the three after that is for the size. We are a clothing company.

    How can we modify or create new query to do this?

    Thanks!

    Mike

    SELECT T0.ITEMCODE,

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV Amt',

    (SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

    (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE

    = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC Amt'

    FROM dbo.OITM T0

    LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode

    WHERE T0.SellItem = 'Y'

    GROUP BY T0.ItemCode,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

    ORDER BY T0. ITEMCODE

    DB:2.19:Sales By Style 19


    Hi Zal -

    Returns are a small percentage of our total sales and a ballpark analysis is sufficient for us on this query - at least for now.

    Thanks,

    Mike

  • RELEVANCY SCORE 2.19

    DB:2.19:Rejected Records Ora-35570 Message d7


    We are having records/rows rejected during a cube maint load. The error message is ORA-35570: (XSRWLD06) A column value is not a valid member of its target dimension. We are not sure how exactly to interpret this error message. The load SQL is:

    SQL
    ![CDATA[
    SELECT /*+ bypass_recursive_check cursor_sharing_exact no_expand no_rewrite */
    T13_MONTH_MEM ALIAS_26,
    T16_RAT_DIM_ID ALIAS_27,
    T10_BEG_END_ID ALIAS_28,
    T16_SIGN_CD_DIM_ID ALIAS_29,
    T16_SUS_DIM_ID ALIAS_30,
    T7_TOTAL ALIAS_31,
    T4_ORG10_MEM ALIAS_32,
    T1_FILE_ID ALIAS_33,
    SUM(T16_DLR_AM) ALIAS_34,
    COUNT(T16_DLR_AM) ALIAS_34_COUNT,
    COUNT(*) SYS_COUNT,
    1 SYS_COUNT_COUNT
    FROM
    (
    SELECT /*+ no_rewrite */
    T1."PRD_MEM" T16_PRD_MEM,
    T1."LOA_DIM_ID" T16_LOA_DIM_ID,
    T1."RAT_DIM_ID" T16_RAT_DIM_ID,
    T1."SUS_DIM_ID" T16_SUS_DIM_ID,
    T1."TB_FEEDER_DIM_ID" T16_TB_FEEDER_DIM_ID,
    T1."ORG_DIM_ID" T16_ORG_DIM_ID,
    T1."DLR_AM" T16_DLR_AM,
    T1."GLAC_ID" T16_GLAC_ID,
    T1."SIGN_CD_DIM_ID" T16_SIGN_CD_DIM_ID
    FROM
    DDRS_ICE."DR_BI_FACT" T1 )
    T16,
    (
    SELECT /*+ no_rewrite */
    T1."MONTH_MEM" T13_MONTH_MEM
    FROM
    DDRS_ICE."DR_BI_TIME_DIM" T1 )
    T13,
    (
    SELECT /*+ no_rewrite */
    T1."ID" T10_ID,
    T1."BEG_END_ID" T10_BEG_END_ID
    FROM
    DDRS_ICE."DR_BI_LOA_DIM" T1 )
    T10,
    (
    SELECT /*+ no_rewrite */
    T1."ID" T7_ID,
    T1."TOTAL" T7_TOTAL
    FROM
    DDRS_ICE."DR_BI_GLAC_DIM" T1 )
    T7,
    (
    SELECT /*+ no_rewrite */
    T1."ID" T4_ID,
    T1."ORG10_MEM" T4_ORG10_MEM
    FROM
    DDRS_ICE."DR_BI_ORG_LEVELS_DIM" T1 )
    T4,
    (
    SELECT /*+ no_rewrite */
    T1."ID" T1_ID,
    T1."FILE_ID" T1_FILE_ID
    FROM
    DDRS_ICE."DR_BI_FDR_TB_DIM" T1 )
    T1
    WHERE
    ((T16_PRD_MEM = T13_MONTH_MEM)
    AND (T16_LOA_DIM_ID = T10_ID)
    AND (T16_GLAC_ID = T7_ID)
    AND (T16_ORG_DIM_ID = T4_ID)
    AND (T16_TB_FEEDER_DIM_ID = T1_ID)
    AND ((T16_PRD_MEM) = (200812.000000) ) )
    GROUP BY
    (T1_FILE_ID, T4_ORG10_MEM, T7_TOTAL, T16_SUS_DIM_ID, T16_SIGN_CD_DIM_ID, T10_BEG_END_ID, T16_RAT_DIM_ID, T13_MONTH_MEM)
    ORDER BY
    T1_FILE_ID ASC NULLS LAST ,
    T4_ORG10_MEM ASC NULLS LAST ,
    T7_TOTAL ASC NULLS LAST ,
    T16_SUS_DIM_ID ASC NULLS LAST ,
    T16_SIGN_CD_DIM_ID ASC NULLS LAST ,
    T10_BEG_END_ID ASC NULLS LAST ,
    T16_RAT_DIM_ID ASC NULLS LAST ,
    T13_MONTH_MEM ASC NULLS LAST ]]
    /SQL

    Any help would be appreciated.

  • RELEVANCY SCORE 2.19

    DB:2.19:Where Clause And On Clause fk


    when we join two tables
    1) select t1.id,t2.id from t1 join t2 on t1.id=t2.id where t1.id5
    which condition is checked first? where t1.id5 ? or join tables first then filter

    2)select t1.id,t2.id from t1 join t2 on t1.id=t2.id andt1.id5
    which is better in the above 2?

    DB:2.19:Where Clause And On Clause fk

    In addition to the above replies, you may find this article http://pratchev.blogspot.com/2007/05/anatomy-of-query.htmlvery helpful.Premature optimization is the root of all evil in programming. (c) by Donald Knuth
    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

  • RELEVANCY SCORE 2.18

    DB:2.18:Hi Update The Whole Record At One Time 3x


    i want to update the whole record but error said 'single-row subquery returns more than one row'..here i attach my query

    update acct_to_print_bak T1 set (T1.bp_partn_num) =
    (
    select floor((ROWNUM-1)/N)+101 BP_PARTN_NUM
    FROM
    (select T1.BILL_DATE,T1.ACCT_NO,T1.STATUS_CODE,T1.BP_PARTN_NUM,T3.N
    from acct_to_print_bak T1,inv_acct_bill_addr T2,
    (select ceil(count(*)/20) N from inv_acct_bill_addr) T3 where
    t2.bill_date='19-FEB-07'
    and t2.bill_date=t1.bill_date
    and t1.ACCT_NO=t2.ACCT_NO
    order by T2.postal_code
    )T4
    )
    WHERE EXISTS
    (select 1 FROM
    (select T1.BILL_DATE,T1.ACCT_NO,T1.STATUS_CODE,T1.BP_PARTN_NUM,T3.N
    from acct_to_print_bak t1,inv_acct_bill_addr T2,(select ceil(count(*)/20) N from inv_acct_bill_addr) T3
    where t2.bill_date='19-FEB-07'
    and t2.bill_date=t1.bill_date
    and t1.ACCT_NO=t2.ACCT_NO
    order by T2.postal_code
    ));

    please help me..

    regards,
    erica

    DB:2.18:Hi Update The Whole Record At One Time 3x

    yeah, its replied more than one values..but nw i dont know how to update the whole record with unique id is acct_no...do u hv any suggestion???

    before this i only use single update

    please help me

  • RELEVANCY SCORE 2.18

    DB:2.18:F1 2010 For X2010 Redbull z1



    Any one whant to swap there X2010 REDBULL for my F1 2010 CAR

    DB:2.18:F1 2010 For X2010 Redbull z1


    Ask here:

    http://community.eu.playstation.com/t5/Gran-Turism​o/OPC-Swap-Shop/td-p/11940311




  • RELEVANCY SCORE 2.18

    DB:2.18:Redbull Series m7


    Can anybody tell me where the redbull sport programs have gone? I watched the first two and now can't find them again.

    DB:2.18:Redbull Series m7

    Can anybody tell me where the redbull sport programs have gone? I watched the first two and now can't find them again.

  • RELEVANCY SCORE 2.18

    DB:2.18:Help With A Complicated Query kj



    Hello to all!

    i have created the following query

    SELECT J0.*,J1.* FROM

    (

    SELECT T0.OBJTYPE,T0.DocNum AS ORD_DocNum, T0.DocDate, T0.DocEntry, T1.LineNum, T2.DocNum AS POR_DocNum FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

    LEFT JOIN OPOR T2 LEFT JOIN POR1 T3 ON T2.DocEntry = T3.DocEntry ON T1.DocEntry = T3.BaseEntry AND T1.LineNum = T3.BaseLine

    ) AS J0

    INNER JOIN

    (

    SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry,T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

    UNION ALL

    SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry, T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

    UNION ALL

    SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry,T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

    ) AS J1

    ON J0.DOCENTRY = J1.BASEENTRY AND J0.LINENUM = J1.BASELINE AND J0.OBJTYPE = J1.BASETYPE

    LEFT JOIN

    (

    SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry,T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

    UNION ALL

    SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry,T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

    ) AS J2

    ON J1.TARGETTYPE = J2.OBJTYPE AND J1.TRGETENTRY = J2.DOCENTRY AND J1.LINENUM = J2.BASELINE

    WHERE J1.ITEMCODE = '77777'

    now i am trying to make it more human understandable....

    what i mean..

    i am interested in columns with Header ObjType to write what kind of document is assigned for example

    if the line is objtype 13 then instead of 13 to be displayed A/R Invoice etc.

    do you have any idea how to do it?

    i tried with case...but it does not work

    DB:2.18:Help With A Complicated Query kj


    Hi Priya!

    it works perfect..

    i have to check it again!

    till then i relaly thank you for your instant reply

  • RELEVANCY SCORE 2.18

    DB:2.18:Display Parameters In Query Results. 3d



    I would like to display the Parameters in the Query results. Is it possible?

    Here is the Query, it gives errors.

    SELECT 'Searched for' as 'Search', '[%0' as 'Input', T1.ItemCode, T1.Dscription, T1.Quantity, T1.OpenQty, T1.ShipDate, T0.DocNum, T1.LineNum,T0.CardCode, T0.CardName,T1.WhsCode, T1.LineStatus, T1.PickStatus, T1.PickIdNo FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE (T1.Dscription Like '%[%0] %'

    OR T1.Dscription LIKE '[%0]%'

    OR T1.Dscription LIKE '%[%0]') and '[%0]' != '' and T1.LineStatus='[%1]'

    Edited by: M. Jenkins on Jul 30, 2010 6:47 PM

    DB:2.18:Display Parameters In Query Results. 3d


    Try this one:
    declare @c nvarchar(100)
    declare @s char(1)
    /*select T.Item from rdr1 t
    where T.Dscription Like '%[%0]%' and T.LineStatus='[%1]'*/
    set @c='[%0]'
    set @s='[%1]'
    SELECT 'Searched for' as 'Search',@c as Input, T1.ItemCode, T1.Dscription,
    T1.Quantity, T1.OpenQty, T1.ShipDate,
    T0.DocNum, T1.LineNum,T0.CardCode, T0.CardName,
    T1.WhsCode, T1.LineStatus, T1.PickStatus, T1.PickIdNo
    FROM ORDR T0
    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
    WHERE T1.Dscription Like '%'+@c+'%' and @c != '' and T1.LineStatus= @s

  • RELEVANCY SCORE 2.18

    DB:2.18:Qual Destas Consultas Obtm Um Melhor Desempenho? 98


    Consulta 1:
    Dim tab = From t1 In bd.Tabela1 Join t2 In bd.Tabela2 On t1.idTabela1 Equals t2.idTabela2 _
    Select t1.idTabela1, t1.campo2, t2.campo2

    ou

    Consulta 2:
    Dim tab = From t1 In bd.Tabela1 Select t1.idTabela1, t1.campo2, t1.Tabela2.campo2

    DB:2.18:Qual Destas Consultas Obtm Um Melhor Desempenho? 98


    Voc j deu uma olhada no SQL que est sendo gerado nos dois casos? Muito provavelmenteele ser o mesmopara ambas as consultas...

    A dvida era se, por exemplo, eu usasse uma Grid para retornar os dados gerados na consulta usando
    Join, a Grid seria carregada mais rpido.

    Amigo, a sua consulta 2 est tentando retornar tabela2 como se fosse um campo de t1, isto est correto ?

    Tentarei explicar com o exemplo abaixo:
    Suponhamos que tabela1 - Cliente e tabela2 - Profissao;
    Cliente:
    Profissao:
    idCliente
    idProfissao
    Nome Descricao
    idProfissao
    Relacionamento:
    FK_Cliente_Profissao
    idProfissao --- idProfissao
    Ento a consulta ficaria assim:
    Dim consulta = From c In bd.Clientes Select c.idCliente, c.Nome, c.Profissao.Descricao

  • RELEVANCY SCORE 2.18

    DB:2.18:Query Help For Journal - Addtional Fileds Required zf



    Dear All,

    Due to Audit requirement I need a report in which all journal entries which are based into SAP Business One should come and for that I am using the following query :

    SELECT T0.[Number], T0.[Memo],T1.[Line_ID], T1.[LineMemo],T1.[ShortName], T2.[ActType], T2.[AcctName],T2.[Levels], T2.[GroupMask], T1.[FCDebit], T1.[FCCredit], T1.[Debit], T1.[Credit], T1.[RefDate], T1.[CreatedBy], T0.[BaseRef] 'Base Reference',T0.[FinncPriod] FROM [dbo].[OJDT] T0 INNER JOIN [dbo].[JDT1] T1 ON T0.TransId = T1.TransId INNER JOIN [dbo].[OACT] T2 ON T1.Account = T2.AcctCode

    DB:2.18:Query Help For Journal - Addtional Fileds Required zf


    hello deepika dev find the query below

    SELECT distinct (T0.[Number]),

    T0.[Memo],

    T1.[Line_ID],

    T1.[LineMemo],

    T1.[ShortName],

    T2.[ActType],

    T2.[AcctName],

    T2.[Levels],

    T2.[GroupMask],

    T1.[FCDebit],

    T1.[FCCredit],

    T1.[Debit],

    T1.[Credit],

    T1.[RefDate],

    T1.[CreatedBy],

    T0.[BaseRef] 'Base Reference',

    T0.[FinncPriod],

    T3.u_name [User Name],

    T4.seriesname

    FROM [OJDT] T0

    INNER JOIN [JDT1] T1 ON T0.TransId = T1.TransId

    INNER JOIN [OACT] T2 ON T1.Account = T2.AcctCode

    inner join [OUSR] T3 on T0.usersign = T3.userid

    inner join NNM1 T4 on T4.series = T0.series and T4.objectcode='30'

    now this will solve ur query i think so if s pls do close the thread

  • RELEVANCY SCORE 2.18

    DB:2.18:Query Error: sa



    Hi,

    Below is the standard query.In this query I added one fllter filed 'Status'.Based on status(Draft,Executed,approved,Cancelled ..) I want to get my master agreement list. In the query I added this below line.%AND_OP("T4.DISPLAY_NAME_ID",%?(T4.DISPLAY_NAME_ID)%)%

    Then when I try to execute the query I am getting the following error.

    SQL Exception: ORA-01722: invalid number , ANSI-92 SQL State: 42000, Vendor Error Code: 1722.

    Can some body throw light on this issue,please?

    SELECT %RESULTS%

    FROM

    %SCHEMA%.FCI_MAS_VALUE_LIST_VALUE

    T4,

    %SCHEMA%.FCI_CONTRACT T1

    LEFT OUTER JOIN (SELECT

    PARENT_OBJECT_ID, COUNT(*) AS AGR_COUNT FROM %SCHEMA%.FCI_AGREEMENT

    GROUP BY PARENT_OBJECT_ID) T2 ON T1.OBJECTID = T2.PARENT_OBJECT_ID

    LEFT OUTER

    JOIN %SCHEMA%.FCI_MAS_INTERNAL_CAT T3 ON T1.INT_CAT_OBJECT_ID =

    T3.OBJECTID

    LEFT OUTER JOIN %EXT_TABLE(contracts.Contract)% T5 ON

    T1.OBJECTID = T5.PARENT_OBJECT_ID

    LEFT OUTER JOIN

    %SCHEMA%.FCI_MAS_VALUE_LIST_VALUE T6 ON T5.SPND_CAT_PROC_CONT_OBJECT_ID

    = T6.OBJECTID

    LEFT OUTER JOIN %SCHEMA%.FCI_CONTRACT T1 ON

    T5.TOT_CONTRACT_VAL_IN_USD = T1.OBJECTID

    LEFT OUTER JOIN

    %SCHEMA%.FCI_CONTRACT T1 ON T5.COMP_CODE_OBJECT_ID =

    T1.OBJECTID

    %DYN_JOIN%

    WHERE

    T1.CONTEXTID

    =%CONTEXT(contracts.Contract)%

    AND T1.IS_TEMPLATE =0

    AND

    T1.STATUS_OBJECT_ID = T4.OBJECTID

    AND T1.UNIQUE_DOC_NAME LIKE

    'PMA%'

    %AND_OP("UPPER(T1.DISPLAY_NAME)",%?(T1.DISPLAY_NAME)%)%

    %AND_OP("UPPER(T1.VENDOR_OBJECT_NAME)",%?(T1.VENDOR_OBJECT_NAME)%)%

    %AND_OP("T1.EXPIRATION_DATE_DATE",%?(T1.EXPIRATION_DATE_DATE)%)%

    %AND_OP("T1.INT_CAT_OBJECT_ID",%?(T1.INT_CAT_OBJECT_ID)%)%

    %AND_OP("UPPER(T1.DOC_OWNER_USER_OBJECT_NAME)",%?(T1.DOC_OWNER_USER_OBJECT_NAME)%)%

    %AND_OP("T1.INACTIVE",%?(T1.INACTIVE)%)%

    %AND_OP("T1.DOC_TYPE_OBJECT_ID",%?(T1.DOC_TYPE_OBJECT_ID)%)%

    %AND_OP("T4.DISPLAY_NAME_ID",%?(T4.DISPLAY_NAME_ID)%)%

    %DYN_FILTER%

    With Regards,

    Jaheer

    DB:2.18:Query Error: sa


    Hi Jaheer,

    T is master Agreement Table FCI_CONTRACT

    LEFT OUTER JOIN %SCHEMA%.FCI_MAS_PORG T1ON T1.OBJECTID= T.PURCHASING_ORG_OBJECT_ID

    LEFT OUTER JOIN %SCHEMA%.FCI_MAS_PGROUP T2ON T1.OBJECTID= T.PURCHASING_GRP_OBJECT_ID

    For Purchasing Group, result field you can give T2.DISPLAY_NAME also you can get from MA Table as below

    T.PURCHASING_GRP_OBJECT_NAME

    For Purchasing Organization, result field you can give T1.DISPLAY_NAME also you can get from MA Table as T.PURCHASING_ORG_OBJECT_NAME

    Hope this will help you.

    Let me know if you need any assistance.

    Thanks,

    Raj.

  • RELEVANCY SCORE 2.18

    DB:2.18:Correlated Subqueries With Rownum And Order By x1


    Hi ALL,

    Can anyone show me what is problem for my query below:

    select T1.COL1, T2.COL2
    from T1, T2, T3
    where T1.COL1=T2.COL2
    and T1.COL1=T3.COL3 (+)
    and T1.COL1
    (select * from
    (select COL1 from T1
    where COL1T1.COL1
    and COL2=T1.COl2
    order by COl1 desc)
    WHERE rownum = 1)
    order by T1.COl1 asc

    The error is "ORA-00904: "T1"."COL1": invalid identifier"

    Cheers

    DB:2.18:Correlated Subqueries With Rownum And Order By x1

    We cannot refer the alias of main-query in from-subquery. Or "correlated inline views are disallowed" in Oracle.

    It might be able to do it with newest version, but it is not stable.Attempt was made but failed. [url https://metalink.oracle.com/metalink/plsql/f?p=130:14:2283483500069284936::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,374632.1,1,1,1,helvetica#FIX]Note:374632.1 ORA-904 when using correlated variables in a FROM clause

  • RELEVANCY SCORE 2.18

    DB:2.18:Redbull xp



    Para quando a abertura do evento Red Bull?

    DB:2.18:Redbull xp


    Finais de Janeiro.

    About The Release of the 2nd Part of the “Red Bull X Challenge”

    “The in-game message displayed when someone attempts o enter the 2nd part of the “Red Bull X Challenge” is incorrect. The next event will be available at the end of January and not on the 1st as currently suggested.

    The message will be amended with the next game update. We deeply apologize for any inconvenience this may have caused.”

    Fonte: GTPlanet

    Da próxima vez que tiveres uma duvida escreve no topico do GT6, para se evitar de abrir constantemente novos topicos. Visto que é uma duvida sobre o jogo porque não colocar lá?

    Cpts.




  • RELEVANCY SCORE 2.18

    DB:2.18:Why Locking Not Allow In Joing For Sql Query Isolation Level Read Committed k1


    Hi i want to apply Isolation level Read Commited but not getting error like with incorrect, where my 1st query is working but not second query not working where i made join:
    =============Working===========================

    SET

    TRANSACTION
    ISOLATION
    LEVEL
    READ
    UNCOMMITTED

    Select

    *
    from SDGEData

    SET

    TRANSACTION
    ISOLATION
    LEVEL
    READ
    COMMITTED
    ==============2nd query=============================not working

    ----------

    SET
    TRANSACTION
    ISOLATION
    LEVEL
    READ
    UNCOMMITTED

    with

    cte as
    (

    SELECT

    t1.FTPStation_ID,
    t1.year,t1.jDay,t1.Time,t1.WindSpeed,t1.WindDirection,

    t1

    .Maxwindgust,t1.Dirwindgust,t1.Temp,t1.Dewpoint,t1.RH,t1.Maxtemp,

    t1

    .Mintemp,t1.Maxwind,t1.Timeofgust,t1.Dirofgust,
    t2y.Outdoor_TemperatureConf,

    t2y

    .Wind_SpeedConf,
    t2y.Wind_DirectionConf,

    t2y

    .DewPointConf,
    t2y.Confidence_date,
    t1.observationDateTime,

    row_number()
    over
    (partition
    by [FTPStation_ID],
    t1.observationDateTime

    ORDER
    BY t2y.Confidence_date
    desc
    )as
    row

    FROM

    dbo.SDGEData
    as t1
    INNER
    JOIN

    dbo

    .LastStationConfidenceHistory t2y
    ON t1.AWSStation_ID
    = t2y.Station_ID

    Where

    t2y.Confidence_date
    =t1.observationDateTime)

    SELECT cte.FTPStation_ID,cte.year,cte.jDay,cte.Time,cte.WindSpeed,cte.WindDirection,

    cte

    .Maxwindgust,cte.Dirwindgust,cte.Temp,cte.Dewpoint,cte.RH,cte.Maxtemp,

    cte

    .Mintemp,cte.Maxwind,cte.Timeofgust,cte.Dirofgust,
    cte.Outdoor_TemperatureConf,

    cte

    .Wind_SpeedConf,
    cte.Wind_DirectionConf,

    cte

    .DewPointConf,
    cte.Confidence_date,
    cte.observationDateTime
    from CTE
    where row=1

    --order by cte.FTPStation_ID

    Union

    select

    t1.FTPStation_ID,
    t1.year,t1.jDay,t1.Time,t1.WindSpeed,t1.WindDirection,

    t1

    .Maxwindgust,t1.Dirwindgust,t1.Temp,t1.Dewpoint,t1.RH,t1.Maxtemp,

    t1

    .Mintemp,t1.Maxwind,t1.Timeofgust,t1.Dirofgust,
    null,

    null,
    null,

    null,
    null, t1.observationDateTime

    FROM

    dbo.SDGEData
    as t1
    where t1.AWSStation_ID
    not
    in
    (Select
    distinct(t2y.Station_ID)
    from

    dbo

    .LastStationConfidenceHistory t2y)

    SET

    TRANSACTION
    ISOLATION
    LEVEL
    READ
    COMMITTED

    =====================error========================

    Msg 319, Level 15, State 1, Line 6
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

  • RELEVANCY SCORE 2.18

    DB:2.18:Need Help dc



    How To Insert BP Name in this Query?

    SELECT T1.[RcptNum] AS 'Receipt Number', T1.[RcptDate] AS 'Receipt Date', T1.[CardCode] AS 'Customer Code', T1.[CheckNum] AS 'Check Number', T1.[CheckSum] AS 'Check Amount', T1.[Deposited] AS 'Deposited', T1.[DpstAbs] AS 'Payment Internal ID', T0.[DeposNum] AS 'Payment No.', T0.[DeposDate] AS 'Payment Date' FROM [dbo].[ODPS] T0 right JOIN [dbo].[OCHH] T1 ON T1.[DpstAbs] = T0.[DeposId]

    DB:2.18:Need Help dc


    Hi Nabil,

    The following query should work:

    SELECT

    T1.[RcptNum] AS 'Receipt Number',

    T1.[RcptDate] AS 'Receipt Date',

    T1.[CardCode] AS 'Customer Code',

    T2.[CardName] AS 'Customer Name',

    T1.[CheckNum] AS 'Check Number',

    T1.[CheckSum] AS 'Check Amount',

    T1.[Deposited] AS 'Deposited',

    T1.[DpstAbs] AS 'Payment Internal ID',

    T0.[DeposNum] AS 'Payment No.',

    T0.[DeposDate] AS 'Payment Date'

    FROM

    [dbo].[ODPS] T0

    right join [dbo].[OCHH] T1 ON T1.[DpstAbs] = T0.[DeposId]

    inner join [dbo].[OCRD] T2 ON T1.[CardCode] = T2.[CardCode]

    Kind Regards,

    Owen

  • RELEVANCY SCORE 2.18

    DB:2.18:How To Merge Two Querries px



    hi experts,,

    i have two querries in only one field is different.

    querries are:

    1.SELECT T0.[WhsCode] AS 'Warehouse Code', T1.[DocDate] AS 'Posting Date', T0.[ItemCode] AS 'Item Code', T0.[Dscription] AS 'Item/Service Description', T0.[Quantity] AS 'Quantity1', T1.[U_No4_5] FROM [dbo].[WTR1] T0 INNER JOIN [dbo].[OWTR] T1 ON T1.[DocEntry] = T0.[DocEntry] WHERE (T1.[Filler] = (N'01' ) OR T1.[Filler] = (N'CI' ) OR T1.[Filler] = (N'FG-Noida' ) OR T1.[Filler] = (N'REJ' ) OR T1.[Filler] = (N'RW' ) OR T1.[Filler] = (N'RW_NonEX' ) OR T1.[Filler] = (N'Scrap' )) AND T1.DocDate = [%1] AND T1.DocDate = [%2]

    2.SELECT T0.[Whscode] AS 'Warehouse Code', T1.[DocDate] AS 'Posting Date', T0.[ItemCode] AS 'Item Code', T0.[Dscription] AS 'Item/Service Description', T0.[Quantity] AS 'Quantity2', T1.[U_No4_5] AS '4/5NO' FROM [dbo].[WTR1] T0 INNER JOIN [dbo].[OWTR] T1 ON T1.[DocEntry] = T0.[DocEntry] WHERE (T1.[Filler] = (N'JW_AJIT' ) OR T1.[Filler] = (N'JW_ANG' ) OR T1.[Filler] = (N'JW_BWR' ) OR T1.[Filler] = (N'JW_GAD' ) OR T1.[Filler] = (N'JW_KARTI' ) OR T1.[Filler] = (N'JW_PASHU' ) OR T1.[Filler] = (N'JW_RIV' ) OR T1.[Filler] = (N'JW_S.V.K' ) OR T1.[Filler] = (N'JW_SAV' ) OR T1.[Filler] = (N'JW_VICT' ) OR T1.[Filler] = (N'JW_VIMAL' )) AND T1.DocDate = [%1] AND T1.DocDate = [%2]

    now i want this in single report with quantity2 and conditions are different for both quantities.

    Thanks regards

    Manoj S

    DB:2.18:How To Merge Two Querries px


    I personally like Istvn's query. It is more clear with simple logic Have you tried the query yet?

    Thanks,

    Gordon

  • RELEVANCY SCORE 2.18

    DB:2.18:What Problem This Query Need To Get Today Data Only da


    Hi I have query which working fine, but i want today data , which can compare with last mordified date but if i set getdate it doestnt return what wrong in my query, it return all data

    SET

    TRANSACTION
    ISOLATION
    LEVEL
    READ
    UNCOMMITTED;

    with

    cte as
    (

    SELECT

    t1.FTPStation_ID,
    t1.year,t1.jDay,t1.Time,t1.WindSpeed,t1.WindDirection,

    t1

    .Maxwindgust,t1.Dirwindgust,t1.Temp,t1.Dewpoint,t1.RH,t1.Maxtemp,

    t1

    .Mintemp,t1.Maxwind,t1.Timeofgust,t1.Dirofgust,
    t2y.Outdoor_TemperatureConf,

    t2y

    .Wind_SpeedConf,
    t2y.Wind_DirectionConf,
    t1.LastModifiedDate,

    t2y

    .DewPointConf,
    t2y.Confidence_date,
    t1.observationDateTime,

    row_number()
    over
    (partition
    by [FTPStation_ID],
    t1.observationDateTime

    ORDER
    BY t2y.Confidence_date
    desc
    )as
    row

    FROM

    dbo.SDGEData
    as t1
    INNER
    JOIN

    dbo

    .LastStationConfidenceHistory t2y
    ON t1.AWSStation_ID
    = t2y.Station_ID

    Where

    t2y.Confidence_date
    =t1.observationDateTime
    )

    SELECT

    cte.FTPStation_ID,cte.year,cte.jDay,cte.Time,cte.WindSpeed,cte.WindDirection,

    cte

    .Maxwindgust,cte.Dirwindgust,cte.Temp,cte.Dewpoint,cte.RH,cte.Maxtemp,

    cte

    .Mintemp,cte.Maxwind,cte.Timeofgust,cte.Dirofgust,
    cte.Outdoor_TemperatureConf,

    cte

    .Wind_SpeedConf,
    cte.Wind_DirectionConf,

    cte

    .DewPointConf,
    cte.Confidence_date,
    cte.observationDateTime,cte.LastModifiedDate
    from CTE
    where row=1

    and cte.LastModifiedDate
    =GETDATE
    ()

    --order by cte.FTPStation_ID

    Union

    select

    t1.FTPStation_ID,
    t1.year,t1.jDay,t1.Time,t1.WindSpeed,t1.WindDirection,

    t1

    .Maxwindgust,t1.Dirwindgust,t1.Temp,t1.Dewpoint,t1.RH,t1.Maxtemp,

    t1

    .Mintemp,t1.Maxwind,t1.Timeofgust,t1.Dirofgust,
    null,

    null,
    null,

    null,
    null, t1.observationDateTime,t1.LastModifiedDate

    FROM

    dbo.SDGEData
    as t1
    where t1.AWSStation_ID
    not
    in
    (Select
    distinct(t2y.Station_ID)
    from

    dbo

    .LastStationConfidenceHistory t2y

    where t2y.LastModifiedDate
    =GETDATE
    ()

    )

    SET
    TRANSACTION
    ISOLATION
    LEVEL
    READ
    COMMITTED;

    DB:2.18:What Problem This Query Need To Get Today Data Only da

    I don't know what do you mean by not work. Please also note, that you select t1.LastModifiedDate but you're filtering by
    t2y.LastModifiedDate - in other words, if you add to your result t2y.LastModifiedDate as [Date I was Checking]
    then that date is going to be correct. Also, in your UNION part you don't join with the t2y table at all, so I'm even not sure how that code is working and not complaining.

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

  • RELEVANCY SCORE 2.18

    DB:2.18:Missing Redbull Bought Furniture - Not Recived!??? s1



    I just bought these:

    Redbull Furniture

    Red bull Surfboard - Sunstripe

    Red bull Surfboard - Vitamin Sea

    Red bull Air Race - Kirby Chambliss Plane

    I recently bought these three items from the Featured item store. And it's not showing in storage or the furniture. So i checked Purchased items, and its only showing the three other items that i bought.

    So i tried to re-buy them again, and i saw that the money was taken + no item recived. And i couldn't buy it, it said i couldnt select these items at this time?? Can i please get refund on this account, if it.. sony are scamming me *****?!




    Playstation Home Glitches .

    DB:2.18:Missing Redbull Bought Furniture - Not Recived!??? s1


    I just bought these:

    Redbull Furniture

    Red bull Surfboard - Sunstripe

    Red bull Surfboard - Vitamin Sea

    Red bull Air Race - Kirby Chambliss Plane

    I recently bought these three items from the Featured item store. And it's not showing in storage or the furniture. So i checked Purchased items, and its only showing the three other items that i bought.

    So i tried to re-buy them again, and i saw that the money was taken + no item recived. And i couldn't buy it, it said i couldnt select these items at this time?? Can i please get refund on this account, if it.. sony are scamming me *****?!




    Playstation Home Glitches .

  • RELEVANCY SCORE 2.18

    DB:2.18:How To Call Oracle Function Which Has If Else Condition In Data Template zs


    Hi,

    currently I am working on creating Data Template which uses a Oracle Function which I need to make use in my data template. But I have some confusions on using the same. Could anybody please help me in this regard.

    I have a function like this,

    *************************************************************************************************************
    function invoice_query (p_facility_id facility.facility_id%TYPE,
    p_wave_nbr pick_directive.wave_nbr%TYPE,
    p_container_id unit_pick_group_detail.container_id%TYPE,
    p_distro_nbr unit_pick_group_detail.distro_nbr%TYPE) return invoice_refcur IS
    refcur invoice_refcur;
    begin

    IF p_wave_nbr IS NOT NULL THEN

    OPEN refcur FOR SELECT t1.distro_nbr,
    t1.cust_order_nbr,
    t1.pick_not_before_date,
    SYSDATE,
    t1.ship_address_description,
    t1.ship_address1,
    t1.ship_address2,
    t1.ship_address3,
    t1.ship_address4,
    t1.ship_address5,
    t1.ship_city || ', ' || t1.ship_state || ' ' || t1.ship_zip,
    t1.ship_country_code,
    t1.bill_address_description,
    t1.bill_address1,
    t1.bill_address2,
    t1.bill_address3,
    t1.bill_address4,
    t1.bill_address5,
    t1.bill_city || ', ' || t1.bill_state || ' ' || t1.bill_zip,
    t1.bill_country_code,
    min(t2.pick_order),
    NULL,
    t2.wave_nbr
    FROM stock_order t1,
    pick_directive t2,
    unit_pick_group_detail t3
    WHERE t1.facility_id = t2.facility_id
    AND t1.facility_id = t3.facility_id
    AND t2.facility_id = t3.facility_id
    AND t1.distro_nbr = t2.distro_nbr
    AND t1.distro_nbr = t3.distro_nbr
    AND t2.distro_nbr = t3.distro_nbr
    AND t1.facility_id = p_facility_id
    AND t2.wave_nbr = p_wave_nbr
    AND g_scp(p_facility_id, 'interface_tcp_flag') = 'N'
    AND t2.pick_type = 'U'
    AND t3.group_id in (SELECT t4.group_id
    FROM unit_pick_group t4
    WHERE t4.facility_id = p_facility_id
    AND t4.wave_nbr = p_wave_nbr)
    GROUP BY t1.distro_nbr,
    t1.cust_order_nbr,
    t1.pick_not_before_date,
    t1.ship_address_description,
    t1.ship_address1,
    t1.ship_address2,
    t1.ship_address3,
    t1.ship_address4,
    t1.ship_address5,
    t1.ship_city || ', ' || t1.ship_state || ' ' || t1.ship_zip,
    t1.ship_country_code,
    t1.bill_address_description,
    t1.bill_address1,
    t1.bill_address2,
    t1.bill_address3,
    t1.bill_address4,
    t1.bill_address5,
    t1.bill_city || ', ' || t1.bill_state || ' ' || t1.bill_zip,
    t1.bill_country_code,
    t2.wave_nbr
    ORDER BY MIN(t3.group_id), MAX(t3.slot);
    elsif p_container_id is not null then
    OPEN refcur FOR SELECT distinct t1.distro_nbr,
    t1.cust_order_nbr,
    t1.pick_not_before_date,
    SYSDATE,
    t1.ship_address_description,
    t1.ship_address1,
    t1.ship_address2,
    t1.ship_address3,
    t1.ship_address4,
    t1.ship_address5,
    t1.ship_city || ', ' || t1.ship_state || ' ' || t1.ship_zip,
    t1.ship_country_code,
    t1.bill_address_description,
    t1.bill_address1,
    t1.bill_address2,
    t1.bill_address3,
    t1.bill_address4,
    t1.bill_address5,
    t1.bill_city || ', ' || t1.bill_state || ' ' || t1.bill_zip,
    t1.bill_country_code,
    NULL,
    t2.dest_id,
    null
    FROM stock_order t1,
    unit_pick_group_detail t2
    WHERE t1.facility_id = t2.facility_id
    and t1.distro_nbr = t2.distro_nbr
    and t1.facility_id = p_facility_id
    AND g_scp(p_facility_id, 'interface_tcp_flag') = 'N'
    AND t2.container_id = p_container_id;
    else
    open refcur for SELECT distinct t1.distro_nbr,
    t1.cust_order_nbr,
    t1.pick_not_before_date,
    SYSDATE,
    t1.ship_address_description,
    t1.ship_address1,
    t1.ship_address2,
    t1.ship_address3,
    t1.ship_address4,
    t1.ship_address5,
    t1.ship_city || ', ' || t1.ship_state || ' ' || t1.ship_zip,
    t1.ship_country_code,
    t1.bill_address_description,
    t1.bill_address1,
    t1.bill_address2,
    t1.bill_address3,
    t1.bill_address4,
    t1.bill_address5,
    t1.bill_city || ', ' || t1.bill_state || ' ' || t1.bill_zip,
    t1.bill_country_code,
    NULL,
    NULL,
    t3.wave_nbr
    FROM stock_order t1,
    unit_pick_group_detail t2,
    unit_pick_group t3
    WHERE t1.facility_id = t2.facility_id
    and t2.facility_id = t3.facility_id
    and t1.distro_nbr = t2.distro_nbr
    and t2.group_id = t3.group_id
    and t1.facility_id = p_facility_id
    AND g_scp(p_facility_id, 'interface_tcp_flag') = 'N'
    AND t2.distro_nbr = p_distro_nbr;
    END IF;

    return refcur;
    end;
    ************************************************************************************************************************

    I have created data template like following,

    sqlStatement name="Q_INVOICE"
    ![CDATA[
    SELECT Pack_Slip_R.invoice_query(:P_FACILITY_ID,:P_WAVE_NBR,:P_CONTAINER_ID,:P_DISTRO_NBR) from dual
    ]]
    /sqlStatement

    But how does I create a element for the "t1.ship_city || ', ' || t1.ship_state || ' ' || t1.ship_zip" column in the oracle function. I normally create an element like following,

    group name="G_INVOICE" source="Q_INVOICE"
    element name="CUST_ORDER_NBR" value="cust_order_nbr"/
    element name=":dest_id" value="dest_id"/
    /Group

    But how do i create element if a column name is kind of dynamic. Please help. I cannot Rename/change the Column in SQL Query. Please let me know If I could handle this whole logic in BI Publsiher.

    Regards,
    Ashoka BL

    DB:2.18:How To Call Oracle Function Which Has If Else Condition In Data Template zs

    You mean in the query I can alias it, yeah that's fine, But I am not sure whether I can change the SQL Query (Because this package is kind of old one)

  • RELEVANCY SCORE 2.18

    DB:2.18:Query Correction sc



    hi friends,

    SELECT T0.[DocNum],T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity],

    Sum(T1.Quantity)- Sum(T1.OpenQty ) [Received Qty], T1.[OpenQty] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[CardCode] =[%0]

    kindly correct above query what i mention bold

    DB:2.18:Query Correction sc


    Hi Ramesh,

    You can also use below query.

    SELECT T0.[DocNum],T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity],

    T1.Quantity- T1.OpenQty [Received Qty], T1.[OpenQty] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

    Thanks,

    Tushar