www.webdeveloper.com
Results 1 to 5 of 5

Thread: Join with group by question

  1. #1
    Join Date
    May 2010
    Posts
    136

    Join with group by question

    I am wondering why if I put a group by in the query below, it will not work?No output

    Code:
        $query1 = "select ". $SCHEMAx ."VBNN.VKZ, FAN11, MATNR, AUART, ORDNUM, ". $SCHEMAx ."PPP_SALES.ERDAT, ". $SCHEMAx ."PPP_SALES.KDGRP, ". $SCHEMAx ."PPP_SALES.KUNAG, NAME1,FKIMG, ZZMAXDISC, MVGR1, PR01 / FKIMG AS BASEPRICE, ". $SCHEMAx ."PPP_SALES.NETWR / FKIMG AS NETPRICE "; 
        		$query1 = $query1 . "from ". $SCHEMAx ."PPP_SALES ";
        		$query1 = $query1 . "left join ". $SCHEMAx ."VBRK on ". $SCHEMAx ."PPP_SALES.ORDNUM = ". $SCHEMAx ."VBRK.BSTNK_VF and ". $SCHEMAx ."PPP_SALES.MANDT = ". $SCHEMAx ."VBRK.MANDT ";
        		$query1 = $query1 . "where ". $SCHEMAx ."PPP_SALES.MANDT = '". $MANDTx ."' and ". $SCHEMAx ."PPP_SALES.FKIMG > 0 and ". $SCHEMAx ."PPP_SALES.ERDAT>= '". $startdate ."' and ". $SCHEMAx ."PPP_SALES.ERDAT <= '" . $enddate . "' ";
        		$query1 = $query1 . "AND PPP_SALES.KDGRP <> '21' ";
    
          //group by here does not work 
        // $query1 = $query1 . "group by ERDAT";
    
        $query1 = $query1 . "order by ". $SCHEMAx ."PPP_SALES.FAN11, ". $SCHEMAx ."PPP_SALES.AUART, ". $SCHEMAx ."PPP_SALES.ERDAT ";

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,384
    I'm going to guess it's because there's no white-space between "ERDAT" and "order by".
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,384
    PS: I'd highly recommend writing your query in PHP something along these lines to increase readability while helping to avoid such errors:
    PHP Code:
    $query1 = <<<EOD
    select
        
    {$SCHEMAx}VBNN.VKZ,
        FAN11,
        MATNR,
        AUART,
        ORDNUM,
        
    {$SCHEMAx}PPP_SALES.ERDAT,
        
    {$SCHEMAx}PPP_SALES.KDGRP,
        
    {$SCHEMAx}PPP_SALES.KUNAG,
        NAME1,
        FKIMG,
        ZZMAXDISC,
        MVGR1,
        PR01 / FKIMG AS BASEPRICE,
        
    {$SCHEMAx}PPP_SALES.NETWR / FKIMG AS NETPRICE
    from 
    {$SCHEMAx}PPP_SALES
    left join 
    {$SCHEMAx}VBRK on
        
    {$SCHEMAx}PPP_SALES.ORDNUM = {$SCHEMAx}VBRK.BSTNK_VF and
        
    {$SCHEMAx}PPP_SALES.MANDT = {$SCHEMAx}VBRK.MANDT
    where 
    {$SCHEMAx}PPP_SALES.MANDT = '$MANDTx'
      and 
    {$SCHEMAx}PPP_SALES.FKIMG > 0
      and 
    {$SCHEMAx}PPP_SALES.ERDAT >= '$startdate'
      and 
    {$SCHEMAx}PPP_SALES.ERDAT <= '$enddate'
      AND PPP_SALES.KDGRP <> '21'
    group by ERDAT
    order by
        
    {$SCHEMAx}PPP_SALES.FAN11,
        
    {$SCHEMAx}PPP_SALES.AUART,
        
    {$SCHEMAx}PPP_SALES.ERDAT
    EOD;

    // must not be any spaces/tabs before that "EOD;" line! 
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    May 2010
    Posts
    136
    Thanks for coding tips. I already put white space with these

    white-space between "ERDAT" and "order by" with the code below.

    // $query1 = $query1 . " group by ERDAT ";

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,384
    This line appends text to the query string ending in the letter "T" with no space after it:
    PHP Code:
    $query1 $query1 "group by ERDAT"
    This line append a string beginning with the letter "o" with no space before it:
    PHP Code:
    $query1 $query1 "order by "$SCHEMAx ."PPP_SALES.FAN11, "$SCHEMAx ."PPP_SALES.AUART, "$SCHEMAx ."PPP_SALES.ERDAT "
    Therefore, that part of the query string will end up as:
    Code:
    ... group by ERDATorder by ...
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles