Select query with blank date

[Former Member]

Hi All,

I have issue with my select query, this query should execute when the date is empty in the table. Fields for the date is (trrecv and trpay)

code:

SELECT * FROM zcat3_mov

INTO CORRESPONDING FIELDS OF

TABLE lt_invmov

WHERE bukrs EQ zcat3_mov-bukrs

AND trans EQ zcat3_mov-trans

AND status EQ 'CD'

AND clear EQ ' '

AND trrecv EQ ' '

AND trpay EQ ' '.

Thanks,

FED

[Former Member]

Hi,

Write the condition as :

SELECT * FROM zcat3_mov

INTO CORRESPONDING FIELDS OF

TABLE lt_invmov

WHERE bukrs EQ zcat3_mov-bukrs

AND trans EQ zcat3_mov-trans

AND status EQ 'CD'

AND clear EQ ' '

AND trrecv EQ '00000000'

AND trpay EQ '00000000 '.

Reagrds,

Srini.

[Former Member]

Hi Srinivas,

I tried but didnt worked. any ideas?

I added these two new fields in the table will there be any effect?

I used databased utility, adjusted and activated the table, do I need to anything If there is a table maintenance for this table?

Thanks,

Fed

[Sandeep_Kumar]

your select is on table or view ?

you have to regenerate the view if you want to use these newly added fields .

[Former Member]

Hi Sandeep,

This is a table.

I am new to table maintenance., can you tell me how to generate it.

thanks,

FED

[Former Member]

Hi,

goto Utilities--> Table maintenance generator

click on change

click on New field/sec. table in structure checkbox.

click ok.

Your view will be regenerated.

Regards,

Srini.

[Former Member]

Hi Federer,

I tested with empty date field and it work as Srini Vas did.

SELECT * FROM zcat3_mov
INTO CORRESPONDING FIELDS OF
TABLE lt_invmov
WHERE bukrs EQ zcat3_mov-bukrs
AND trans EQ zcat3_mov-trans
AND status EQ 'CD'
AND clear EQ ' '
AND trrecv EQ '00000000' "eight zero
AND trpay EQ '00000000'. "eight zero

but make sure your field trrecv and trpay have data element type DATS (Field of type DATS with length 8).

please check and reply.

regards,

[Sandeep_Kumar]

there is nice document in the forums , searchw ith "table maintenance generatot".

[Former Member]

Hi,

I tried to do as you said but here there is one more screen 'Change generation elements' what I need to select in that screen.

I have options here

Maintenance screens

overview screen

single screen

field type/Sec. type

key field

normal field

I suppose I need to select the key field if I am not wrong?

Thanks,

FED

[Former Member]

Hi,

Click on the Key Field if U have added Key field else click Normal field.

U can click both the screens

overview screen

single screen

Regards,

Srini.

[Sandeep_Kumar]

AND trrecv EQ ' '

AND trpay EQ ' '.

You code should work :

check the follwoing if you have checked the initial flag for these 2 fields in teh table ->if yes , comapre where AND trrecv EQ '000000'.

[Subhankar]

Hi,

Initial value of date format is '00000000'. Instead of hard coding I will suggest first declare a local variable of field type. Here you need to declare of type sy-datum. For other types also you can used this technique. Its easy and robust.

data: l_ini_date type sy-datum.

SELECT * FROM zcat3_mov

INTO CORRESPONDING FIELDS OF

TABLE lt_invmov

WHERE bukrs EQ zcat3_mov-bukrs

AND trans EQ zcat3_mov-trans

AND status EQ 'CD'

AND clear EQ ' '

AND trrecv EQ l_ini_date

AND trpay EQ l_ini_date.

Thanks

Subhankar

[Former Member]

Hi,

Do u have the data in table zcat3_mov?

Chek wheather u have all the valid data as per ur where condtion.

I think u have not maintained the data in table as per ur where condtion so only u r not getting data in it.

Regards,

Pravin

[Former Member]

Hi,

to check for blank date fields in database tables always use :

date1 = '00000000'

so your query will be -

SELECT * FROM zcat3_mov INTO CORRESPONDING FIELDS OF TABLE lt_invmov
                        WHERE bukrs  EQ zcat3_mov-bukrs  AND 
                              trans  EQ zcat3_mov-trans  AND 
                              status EQ 'CD'             AND 
                              clear  EQ ' '              AND 
                              trrecv EQ '00000000'       AND 
                              trpay  EQ '00000000 '.

regards,

Shaurabh Pandey