Re: Query help

[Quoted] On Friday, March 7, 2014 6:16:33 AM UTC-7, Steffen Kother wrote:
> Hi there,
>
>
>
> I'm sitting here trying to rewrite query for using with PHP. At first I
>
> built it with Access 2010 and there it works like expected. But I need
>
> access query in oracle...
>
>
>
> It should look for GSPROGRP in table GSDAT and link it to the right name
>
> in SDDATEN in table SDAT. The problem is GSPROGRP contains at least 1 up
>
> to 4 characters, eg. 05, 16, DR, RGW, but SDKEY in SDDATEN stores it as
>
> 0005, 0016, 00DR or 0RGW.
>
>
>
> My oracle query doesn't show any result. Can anybody help me with some
>
> hints? Maybe sub select or something else? Queries are under my sig.
>
> --
>
> Kind regards,
>
>
>
> Steffen
>
>
>
>
>
>
>
> Oracle query:
>
> SELECT
>
> trim(GSPROGRP) AS PrdGrp,
>
> trim(substr(sddaten, 2, 29)) AS PrdGrpBez
>
> FROM
>
> GSDAT,
>
> sddat
>
> where
>
> GSDAT.GSMANDANT = '00'
>
> AND (case length(trim(gsprogrp))
>
> when 1 then '000' || gsprogrp
>
> when 2 then '00' || gsprogrp
>
> when 3 then '0' || gsprogrp
>
> else gsprogrp
>
> end) = sddat.sdkey
>
> AND SDDAT.SDSA = '0196'
>
> ORDER BY
>
> GSDAT.GSPROGRP;
>
>
>
>
>
> Access query:
>
> SELECT
>
> GSDAT.GSPROGRP AS PrdGrp,
>
> Trim(Mid(sddaten,2,29)) AS PrdGrpBez
>
> FROM
>
> GSDAT,
>
> SDDAT
>
> GROUP BY
>
> GSDAT.GSPROGRP,
>
> Trim(Mid(sddaten,2,29)),
>
> GSDAT.GSMANDANT,
>
> SDDAT.SDKEY,
>
> SDDAT.SDSA
>
> HAVING
>
> (((GSDAT.GSMANDANT)='00')
>
> AND ((SDDAT.SDKEY)=IIf(Len(Trim(gsprogrp))=1,'000' &
>
> gsprogrp,IIf(Len(Trim(gsprogrp))=2,'00' &
>
> gsprogrp,IIf(Len(Trim(gsprogrp))=3,'0 ' &
>
> gsprogrp,gsprogrp))))
>
> AND ((SDDAT.SDSA)='0196'))
>
> ORDER BY
>
> GSDAT.GSPROGRP;
First off I'd be using lpad() instead of a case statement to format the values:

SELECT

        trim(GSPROGRP)                           AS PrdGrp, 
        trim(substr(sddaten, 2, 29))      AS PrdGrpBez 
FROM 
        GSDAT, 
        sddat 
where 
        GSDAT.GSMANDANT = '00' 
        AND lpad(gsprogrp, 4, '0') = sddat.sdkey 
        AND SDDAT.SDSA = '0196' 
ORDER BY 
        GSDAT.GSPROGRP; 

[Quoted] What I don't understand is you have a GROUP BY query for Access yet this is not the case for the Oracle version. Why? We need a bit more information on what the result is supposed to be.

David Fitzjarrell Received on Fri Mar 07 2014 - 16:04:25 CET