topic Re: Problem in making SQL Query in Application Development and Automation Discussions

Problem in making SQL Query

Former Member — Thu, 21 Jul 2005 05:47:34 GMT

Hello All,

I have one problem regarding sql query.

I have one internal table which contains equnr and bis as fields. There are two database tables egerr and eastl. The structure for tables are as follows:

Fields for egerr:

equnr, bis, logiknr in which first two fields form key.

Field for eastl:

anlage, bis, logiknr in which all fields form primary key.

I want to select records from internal table which does not have record in eastl.

For the reference we can extract logiknr from egerr by using intarnal table and then use this logiknr to check entry in table eastl. but i want those equnr which are in internal table but not mapped in eastl.

I want the most efficient solution for this as there are many records.

Thanks..... and if you have any queries then let me know.

Jignesh.

Re: Problem in making SQL Query

Former Member — Thu, 21 Jul 2005 06:00:56 GMT

hi,

as per ur statement, u want the field equnr which exists in the internal table but not in eastl. now for comparing with eastl u will need to check for all the three fields as they form the key...

get data from egerr for matching equnr and bis in your internal table

i.e. assuming ur table is itab and itab_logiknr contains a single field logiknr

select logiknr from egerr

into table itab_logiknr

for all entries in itab

where equnr eq itab-equnr

and bis eq itab-bis.

now from this data (itab_egerr), compare the data with that in eastl for matching (or non matching) values of logiknr

assuming data from eastl lies in itab_eastl

select anlage bis logiknr from eastl into itab_eastl

for all entries in itab_logiknr

where logiknr eq itab_logiknr-logiknr.

for non matching entries u can select data from eastl which is not present in itab_eastl now....

(but mind you....since all fields of eastl form the key, u might not be getting the correct data) so if possible study ur scenario again and see if u can search the eastl table comparing all fields in the primary key)

try this....get back in case of any clarifications

hope it gives u some pointers...

regards,

Re: Problem in making SQL Query

Former Member — Thu, 21 Jul 2005 06:15:05 GMT

Hi Priyank,

"for non matching entries u can select data from eastl which is not present in itab_eastl now...."

I want to extract those equnr which are in internal table itab but they dont have entry(corresponding logiknr) in eastl.

Can you give me the exact query i should go for? I dont want the records which have corresponding entry in eastl but i want those records who dont have entries in eastl.

Thanks for your help buddy !!!!!!!

Jignesh.

Re: Problem in making SQL Query

Former Member — Thu, 21 Jul 2005 06:22:41 GMT

once u have the data in itab_logiknr, these are only for existing equnr in egerr and matching entries in your internal table....

now u can use the following select

select anlage bis logiknr from eastl into table itab_eastl

for all entries in itab_logiknr

where logiknr ne itab_logiknr-logiknr.

see if this solves ur purpose.....but keep in mind the caution i mentioned earlier

regards,

Re: Problem in making SQL Query

andreas_mann3 — Thu, 21 Jul 2005 06:42:38 GMT

hi,

look f1 to subquery and try that:

SELECT f1 f2 from egerr
   where 
  ...                    
*subquery
                   
                    AND anlage NOT IN
                     ( SELECT  f1  FROM  eastl
                            WHERE  ... )

regards Andreas