Dynamic query help..

[anandknr]

I have a dynamic query for table named table1 .

CREATE QUERY hQueryData.
CREATE BUFFER hBufferData FOR TABLE Table1.
hQueryData:SET-BUFFERS(hBufferData).
hQueryData:QUERY-PREPARE(sqlquery).
hQueryData:QUERY-OPEN.

hQueryData:GET-FIRST ().


REPEAT:
IF hQueryData:QUERY-OFF-END THEN LEAVE.
DO i=1 TO hBufferData:NUM-FIELDS :
h4 = hBufferData:BUFFER-FIELD(i) .

MESSAGE h4:NAME h4:BUFFER-VALUE() VIEW-AS ALERT-BOX .

END.

hQueryData:GET-NEXT ().
END.








How to rewrite the above query for two table named table1 and table2 . Please help for the syntax .

 

[MaximMonin]

Something like

IF RecID-user <> ? THEN
  sQuery = sQuery + " AND document2.close-doc = " + STRING (RecID-user).
sQuery = sQuery + ", EACH applicat2 OF document2 NO-LOCK, " +
     " EACH ctg-division2 OF document2 NO-LOCK, " + 
     " EACH typedoc2 OF document2 NO-LOCK, " +
     " EACH vis-divisions2 WHERE document2.rid-division = vis-divisions2.rid-div".

CREATE QUERY hQueryData.
hQueryData:FORWARD-ONLY = true.
hQueryData:SET-BUFFERS (BUFFER document2:HANDLE,
                        BUFFER applicat2:HANDLE,
                        BUFFER ctg-division2:HANDLE,
                        BUFFER typedoc2:HANDLE,
                        BUFFER vis-divisions2:HANDLE).
hQueryData:QUERY-PREPARE(sQuery).
hQueryData:QUERY-OPEN. 
hQueryData:GET-FIRST ().

 

[anandknr]

ok i will try

 

[anandknr]

But i am getting the error "QUERY-PREPARE text must have 1 FOR EACH/PRESELECT for each query
buffer. (7325)
"
And here is my code

define variable sqlquery as character.
define variable tablename as character initial "paxsegmt".
define variable tablename1 as character initial "paxinfo".
define variable sortby as character initial "ship-cd".
DEF VAR hQueryData AS HANDLE NO-UNDO .

DEF VAR hBufferData AS HANDLE NO-UNDO .
DEF VAR hBufferData1 AS HANDLE NO-UNDO .
DEF VAR h4 AS HANDLE NO-UNDO .
DEF VAR i AS INT NO-UNDO .

sqlquery = "FOR EACH " + TableName + " where paxinfo.billing-no = paxsegmt.billing-no AND
paxinfo.pass-no=paxsegmt.pass-no NO-LOCK".
/*
if trim(sortby) <> "" then
sqlquery = sqlquery + " by " + TableName + "." + sortby.
*/
MESSAGE sqlquery VIEW-AS ALERT-BOX .

CREATE QUERY hQueryData.

CREATE BUFFER hBufferData FOR TABLE TableName.
CREATE BUFFER hBufferData1 FOR TABLE TableName1 .
hQueryData:FORWARD-ONLY = true.
hQueryData:SET-BUFFERS(BUFFER paxinfo:HANDLE,BUFFER paxsegmt:HANDLE).
hQueryData:QUERY-PREPARE(sqlquery).
hQueryData:QUERY-OPEN.

hQueryData:GET-FIRST ().


repeat:
if hQueryData:QUERY-OFF-END then leave.
DO i=1 TO hBufferData:NUM-FIELDS :
h4 = hBufferData:BUFFER-FIELD(i) .
MESSAGE h4:NAME h4:BUFFER-VALUE() VIEW-AS ALERT-BOX .

END.

hQueryData:GET-NEXT ().
end.

 

[MaximMonin]

you have to link paxsegmn and paxinfo in query

for each table1 where...,
each table2 where table2.field = table1.field.

 

[anandknr]

Hi >...
Every thing went fine except for the statement h4:BUFFER-VALUE() in the below code ...



define variable sqlquery as character.
define variable tablename as character initial "paxsegmt".
define variable tablename1 as character initial "paxinfo".
define variable sortby as character initial "ship-cd".
DEF VAR hQueryData AS HANDLE NO-UNDO .

DEF VAR hBufferData AS HANDLE NO-UNDO .
DEF VAR hBufferData1 AS HANDLE NO-UNDO .
DEF VAR h4 AS HANDLE NO-UNDO .
DEF VAR i AS INT NO-UNDO .

sqlquery = "for each paxsegmt, each paxinfo where paxinfo.billing-no = paxsegmt.billing-no ".
/*
if trim(sortby) <> "" then
sqlquery = sqlquery + " by " + TableName + "." + sortby.
*/
MESSAGE sqlquery VIEW-AS ALERT-BOX .

CREATE QUERY hQueryData.

hQueryData:FORWARD-ONLY = true.
CREATE BUFFER hBufferData FOR TABLE tableName.
CREATE BUFFER hBufferData1 FOR TABLE tableName1.

hQueryData:SET-BUFFERS(BUFFER paxsegmt:HANDLE , BUFFER paxinfo:HANDLE).
hQueryData:QUERY-PREPARE(sqlquery).
hQueryData:QUERY-OPEN.

hQueryData:GET-FIRST ().


repeat:
if hQueryData:QUERY-OFF-END then leave.
DO i=1 TO hBufferData:NUM-FIELDS :

h4 = hBufferData:BUFFER-FIELD(i) .
MESSAGE h4:NAME h4:BUFFER-VALUE() VIEW-AS ALERT-BOX .


END.

hQueryData:GET-NEXT ().
end.




When i tried with MESSAGE h4:NAME VIEW-AS ALERT-BOX . it is outputting the field names .. but when tried with h4:BUFFER-VALUE() it is showing an error as ** No record is available. (91) **

What could be the reason ???Please help ....

 

[MaximMonin]

use just
message table.fieldname table2.fieldname2.

 

[medu]

hQueryData:SET-BUFFERS(BUFFER paxsegmt:HANDLE , BUFFER paxinfo:HANDLE).

you are mixing buffers there, first you create two buffers for the two tables but then you don't assign those handles to the query but use the default table buffer handle... try this instead:

hQueryData:SET-BUFFERS(hBufferData , hBufferData1).