Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.oracle.misc > #812

Query help

Show all headers | View raw

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;

Back to comp.databases.oracle.misc | Previous | Next — Next in thread | Find similar

Thread

Query help Steffen Kother <steffen.kother@gmx.net> - 2014-03-07 14:16 +0100
  Re: Query help ddf <oratune@msn.com> - 2014-03-07 07:04 -0800
    Re: Query help Steffen Kother <steffen.kother@gmx.net> - 2014-03-10 09:12 +0100
      Re: Query help Steffen Kother <steffen.kother@gmx.net> - 2014-03-10 13:30 +0100

csiph-web