Skip to Content
author's profile photo Former Member
Former Member

problem with a query

hello there

i try to make a query but the behavior of my sbo is a little bit strange

i have the following tsql code made

SELECT ign1.itemcode,ign1.dscription,oign.docdate,

(select oitm.U_INTCODE from oitm where oitm.itemcode = '71107') ,

oitm.CSTGRPCODE,ign1.baseentry,sum(IGN1.QUANTITY) as 'u03A0u03BFu03C3όu03C4u03B7u03C4u03B1'

FROM IGN1

inner join OIGN on ign1.docentry=oign.docentry

full outer join oitm on oitm.docentry=ign1.docentry

cross join oitg

WHERE

OIGN.DOCDATE='2011-01-19'

and IGN1.ITEMCODE='71107'

and oitg.itmstypcod='63'

GROUP BY ign1.itemcode,oign.docdate,ign1.dscription,oitm.U_INTCODE,oitm.CSTGRPCODE,ign1.baseentry

the above query works fine in sql server but trying to change the values from static to dynamic, lets consider that i want prompt the user to type the itemcode and the docdate, the above query will be like

SELECT ign1.itemcode,ign1.dscription,oign.docdate,

(select oitm.U_INTCODE from oitm where oitm.itemcode = '[%0]') ,

oitm.CSTGRPCODE,ign1.baseentry,sum(IGN1.QUANTITY) as 'u03A0u03BFu03C3όu03C4u03B7u03C4u03B1'

FROM IGN1

inner join OIGN on ign1.docentry=oign.docentry

full outer join oitm on oitm.docentry=ign1.docentry

cross join oitg

WHERE

OIGN.DOCDATE='[%1]''

and IGN1.ITEMCODE='[%0]'

and oitg.itmstypcod='63'

GROUP BY ign1.itemcode,oign.docdate,ign1.dscription,oitm.U_INTCODE,oitm.CSTGRPCODE,ign1.baseentry

the strange thing is that by trying to execute the above query, the sap comes up with the following error

column ign1.itemcode is invalid in the select list because it is not contained in either an aggregate function or in the GROUP BY clause. 2)microsoft sql native client sql server statement "SEWSY

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 14, 2011 at 08:39 AM

    Hi Vasilis,

    Try This.

    SELECT ign1.itemcode,ign1.dscription,oign.docdate,
    (select oitm.U_INTCODE from oitm where oitm.itemcode = [%0]) ,
    oitm.CSTGRPCODE,ign1.baseentry,sum(IGN1.QUANTITY) as 'u03A0u03BFu03C3u03CCu03C4u03B7u03C4u03B1'
    FROM IGN1
    inner join OIGN on ign1.docentry=oign.docentry
    full outer join oitm on oitm.docentry=ign1.docentry
    cross join oitg
    WHERE
    OIGN.DOCDATE=[%1]
    and IGN1.ITEMCODE=[%0]
    and oitg.itmstypcod='63'
    GROUP BY ign1.itemcode,oign.docdate,ign1.dscription,oitm.U_INTCODE,oitm.CSTGRPCODE,ign1.baseentry
    

    Regards,

    Darius Gragasin

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      the simple one it does work but while i try to add more conditions in where clause it crashes...

      could you please help me how can i bring the data of th oarg table(CSTGRPname values) to the following query, because it does not bring me any data

      the working query without the data from the oarg table is the

      SELECT

      ign1.itemcode,

      ign1.dscription,

      oign.docdate,

      --oitm.u_intcode,

      --oitm.itemcode,

      --(select oitm.U_INTCODE from oitm where oitm.itemcode='71107') ,

      --oarg.CSTGRPname,

      ign1.baseentry,sum(IGN1.QUANTITY) as 'u03A0u03BFu03C3όu03C4u03B7u03C4u03B1'

      FROM IGN1

      inner join OIGN on ign1.docentry=oign.docentry

      full join oitm on oitm.docentry=ign1.docentry

      cross join oitg

      --full outer join oarg on oarg.cstgrpcode=oitm.cstgrpcode

      WHERE

      OIGN.DOCDATE>='2010-01-01'

      --and IGN1.ITEMCODE='71107'

      and oitg.itmstypcod='63'

      GROUP BY ign1.itemcode,oign.docdate,ign1.dscription,oitm.U_INTCODE,oitm.CSTGRPCODE,ign1.baseentry,oitm.itemcode

      by modifying it to

      SELECT

      ign1.itemcode,

      ign1.dscription,

      oign.docdate,

      --oitm.u_intcode,

      --oitm.itemcode,

      --(select oitm.U_INTCODE from oitm where oitm.itemcode='71107') ,

      oarg.CSTGRPname,

      ign1.baseentry,sum(IGN1.QUANTITY) as 'u03A0u03BFu03C3όu03C4u03B7u03C4u03B1'

      FROM IGN1

      inner join OIGN on ign1.docentry=oign.docentry

      full join oitm on oitm.docentry=ign1.docentry

      cross join oitg

      full outer join oarg on oarg.cstgrpcode=oitm.cstgrpcode

      WHERE

      OIGN.DOCDATE>='2010-01-01'

      --and IGN1.ITEMCODE='71107'

      and oitg.itmstypcod='63'

      GROUP BY ign1.itemcode,oign.docdate,ign1.dscription,oitm.U_INTCODE,oitm.CSTGRPCODE,ign1.baseentry,oitm.itemcode,oarg.CSTGRPname

      it does not bring me the data of the oarg

      i have tried it with all the joins but still nothing

  • Posted on Sep 14, 2011 at 08:58 AM

    Hi,

    Try this

    SELECT T1.itemcode,T1.dscription,oign.docdate,
    (select T5.U_INTCODE from oitm T5 where T5.itemcode =T1.ItemCode) ,
    oitm.CSTGRPCODE,T1.baseentry,sum(T1.QUANTITY) as 'u03A0u03BFu03C3u03CCu03C4u03B7u03C4u03B1'
    FROM IGN1 T1
    inner join OIGN on T1.docentry=oign.docentry
    full outer join oitm on oitm.docentry=T1.docentry
    cross join oitg
    WHERE
    OIGN.DOCDATE=[%1]
    and T1.ITEMCODE=[%0]
    and oitg.itmstypcod='63'
    GROUP BY T1.itemcode,oign.docdate,T1.dscription,oitm.U_INTCODE,oitm.CSTGRPCODE,T1.baseentry

    Regards,

    Bala

    Edited by: Balakumar Viswanathan on Sep 14, 2011 2:28 PM

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.