JDBC-RFC Query Error

[vijay_kumar133]

Hi friends,

I have 2 tables at sender JDBC side . one is header table and second one is Line items.

Now i have to pass to RFC in such a way that onley once header and respective line items to the RFC.

rite now i am using this Query in JDBC sender side

select * from table_Header,table_Line_Item where flag = '1' and counter1 = counter2;

where counter1 and counter2 represents the header and line items correspondence.

now i need to change in query or in mapping or in integration any condition....

please help me its urgent

Regards

Vijay Kumar

[Former Member · Accepted solution]

You can use a JOIN staement to query multiple tables based on a condition.

*******************************************************************

Query SQL Statement

You have the following options:

· Specify a valid SQL SELECT statement to select the data to be sent from the specified database.

· Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement.

The expression must correspond to the SQL variant supported by the relevant JDBC driver. It can also contain table JOINs.

[vijay_kumar133 · Accepted solution]

Hi friends,

I have to use 2 sql statements to complete my issue..can you please say me how u can we use to nesting sql statement in MSSQL2005.

the statements are

SELECT * FROM table_Header INNER JOIN table_Line_Item ON table_Header.counter1 = table_Line_Item.counter2 WHERE table_Line_Item.newbs = '40'

SELECT * FROM table_Line_Item INNER JOIN table_Header ON table_Header.counter1 = table_Line_Item.counter2 WHERE table_Line_Item.newbs = '50'

i need to make this as stored procedure..

can i combine this 2 sql in stored procedure.. i am very new to this SQl please help me..

Thanks and Regards

Vijay

[GabrielSagaya · Accepted solution]

CREATE PROCEDURE StoredProcedureName as

@counter varchar(10)

As

SELECT * FROM table_Header INNER JOIN table_Line_Item ON table_Header.counter1 = table_Line_Item.counter2 WHERE table_Line_Item.newbs = counter

EXECUTE StoredProcedureName '40'

EXECUTE StoredProcedureName '50'

[vijay_kumar133 · Accepted solution]

hi fine thanks a lot for this now how to use this in my iterface..

i mean where should i write this storedprocedure and how should i call in adapter..

i can call two EXECUTE StoredProcedureName '40'

EXECUTE StoredProcedureName '50'

in the adapter..

please more clear please

Regards

Vijay

[vijay_kumar133 · Accepted solution]

Hi in the storedProcedure there is mistake...

I need to execute first SQL query if newbs=40;

and i need to execute second sql query if newbs = 50;

so please make change accordingly in the stored procedure..

i need to make nesting procedure.. or kind of thing please look into this..

thanks and Regards

Vijay

[GabrielSagaya · Accepted solution]

CREATE PROCEDURE StoredProcedureName as

@counter1 varchar(10), @counter2 varchar2(10)

As

SELECT * FROM table_Header INNER JOIN table_Line_Item ON table_Header.counter1 = table_Line_Item.counter2 WHERE table_Line_Item.newbs = counter1

SELECT * FROM table_Header INNER JOIN table_Line_Item ON table_Header.counter1 = table_Line_Item.counter2 WHERE table_Line_Item.newbs = counter2

EXECUTE StoredProcedureName '40','50'

http://databases.about.com/od/sqlserver/a/storedprocedure.htm

http://www.4guysfromrolla.com/webtech/111499-1.shtml

[GabrielSagaya]

Inorder to select records from 2 table prefer Storedprocedure

Just Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement.

This will have to be done for your SELECT statement in JDBC adapter. ALso, make the UPDATE as either TEST, if you dont want any updations or give an UPDATE statement for the UPDATE query.

Set the polling interval of your JDBC adapter to 1 hour and it will work like u want.

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm