topic Re: Select query with blank date in Application Development and Automation Discussions

Select query with blank date

Former Member — Mon, 30 Aug 2010 06:35:34 GMT

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

Re: Select query with blank date

Former Member — Mon, 30 Aug 2010 06:38:07 GMT

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.

Re: Select query with blank date

Sandeep_Panghal — Mon, 30 Aug 2010 06:39:38 GMT

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'.

Re: Select query with blank date

Former Member — Mon, 30 Aug 2010 06:56:52 GMT

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

Re: Select query with blank date

Sandeep_Panghal — Mon, 30 Aug 2010 07:00:30 GMT

your select is on table or view ?

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

Re: Select query with blank date

Former Member — Mon, 30 Aug 2010 07:03:28 GMT

Hi Sandeep,

This is a table.

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

thanks,

FED

Re: Select query with blank date

Former Member — Mon, 30 Aug 2010 07:09:15 GMT

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.

Re: Select query with blank date

Former Member — Mon, 30 Aug 2010 07:11:30 GMT

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,

Re: Select query with blank date

Sandeep_Panghal — Mon, 30 Aug 2010 07:12:12 GMT

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

Re: Select query with blank date

Subhankar — Mon, 30 Aug 2010 07:15:28 GMT

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

Re: Select query with blank date

Former Member — Mon, 30 Aug 2010 07:21:12 GMT

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

Re: Select query with blank date

Former Member — Mon, 30 Aug 2010 07:26:25 GMT

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

Re: Select query with blank date

Former Member — Mon, 30 Aug 2010 07:37:35 GMT

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.

Re: Select query with blank date

Former Member — Mon, 30 Aug 2010 08:55:59 GMT

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