Hi,

I am creating a report to compare BOM between two companies and the following SQL is working fine. But I want the company number to be taken from the form which the user inputs. Can someone help me modify the query?

Thanks,
srikarthy

select tibom010.*
from tibom010
where tibom010.mitm between :mitm.f and :mitm.t
and tibom010._compnr = "100,905"
order by tibom010._index1
selectdo
rprt_send()
endselect

Use the domain tcncmp for the form field where the user inputs the company, then change the line in your SQL to:

and tibom010._compnr = :ncmp.i

Assuming the input field on form is called ncmp.i.

Rob

You can always use dynamic SQL to build your select statement as you have it. Try searching on dynamic sql - you should find several threads.

Hi Rob,

This cannot be written as an embedded sql or possibly using the wherebind it can be achieved but not sure. As Mark pointed out I have to use dynamic sql. Mark, I do not know dynamic sql and thought someone will write for me and I can use ctrl C ctrl V technology. I will wait for a few more days for any patrons and if there is no other way, I'll take a plunge into dynamic sql.

Regards,
srikarthy

See this link for a (http://www.baanboard.com/baanboard/showthread.php?t=29844&highlight=dynamic+sql) sample I posted. See this link (http://www.baanboard.com/baanboard/showthread.php?t=34787&highlight=dynamic+sql) for some links to help posted by George. Give it a try yourself and come back if you have problems.

Hi Rob,

This cannot be written as an embedded sql
srikarthy

I think this is wrong . Have u tested this .

I tried but I couldn't write it in normal embedded SQL. When I tried to
maintain the structure tibom010._compnr = "100,905" with
tibom010._compnr = :company2, :company3 I was getting error. Then I tried with
tibom010._compnr = :company2 or tibom010._compnr = :company3 it compiled but while running, I got Error 304 (general SQL error) on select.
If I use just where tibom010._compnr = :company2 it was working.

Then I tried using wherebind but here again I could use only one company but I think I have the option to change the company variable.

select tibom010.*
from tibom010
where tibom010._compnr = :1
and tibom010.mitm between :mitm.f and :mitm.t
wherebind(1, company2)
| order by tibom010._index1
selectdo
rprt_send()
endselect

Again, here if I use the order by, I am getting error.

Then I tried with dynamic sql based on Mark's links as below.

sql = sql.parse("select tibom010.* from tibom010 where tibom010.mitm between :mitm.f and :mitm.t and " &
"tibom010._compnr = :1 order by tibom010._index1")
sql.where.bind(sql, 1, company2)

sql.exec(sql)
while ( true )
message(str$(sql))
sql.fetch(sql)
if eendfile = true then
sql.break(sql)
endif
rprt_send()
endwhile
sql.close(sql)

This was working too but was indefintely looping and I didn't explore further. Again here I can change the variable company2.

Regards,
srikarthy

AFAIK order by clause will not work when compnr is used . This is due to the way the indices stored and the way the database drivers work .

So to answer ur query , merging records of 2 companies in 1 SQL in merged order is not possible . If u just printing , why dont u sort the same in report only .

I think the same problem with dynamic sql.

Here is another example - should help on breaking out of the query:

sql.code = ""
found = true
sql.code = sql.code & "select tipgc001_1.item:grp.item, tipgc001_1.ccot:prj.grp, tiitm001_1.dsca:item.dsca "
sql.code = sql.code & "from tipgc001 tipgc001_1, tiitm001 tiitm001_1 "
sql.code = sql.code & "where tipgc001_1._index1 inrange {" & chr$(34) & prjgrp.from & chr$(34) & "," & chr$(34) & item.from & chr$(34) &"} "
sql.code = sql.code & "and {" & chr$(34) & prjgrp.to & chr$(34) & "," & chr$(34) & item.to & chr$(34) & "} "
sql.code = sql.code & "and tipgc001_1.buyr = " & str$(current.buyer) & " "
sql.code = sql.code & "and tiitm001_1.item = tipgc001_1.item "
sql.code = sql.code & "and tiitm001_1.dsca like " & chr$(34) & strip$(itemdesc) & chr$(34)
sql_id = sql.parse(sql.code)
sql.exec(sql_id)
while found
on case sql.fetch(sql_id)
case eendfile:
found = false
break
case 0:
found = true
tipgc001.item = grp.item
tipgc001.ccot = prj.grp
tiitm001.dsca = item.dsca
rprt_send()
if update.grp.item = tcyesno.yes then
update.group.item()
endif
if update.std.item = tcyesno.yes then
update.standard.item()
endif
break
default:
found = false
endcase
endwhile


I would expect the very first code you posted to look something like this:

sql.code = sql.code & "select tibom010.* "
sql.code = sql.code & "from tibom010 "
sql.code = sql.code & "where tibom010._index1 inrange {" & chr$(34) & mitm.f & chr$(34) & "}" &
" and {"& chr$(34) & mitm.t & chr$(34) & "} "
sql.code = sql.code & "and tibom010._compnr = " & chr$(34) &"100" & "," & "905" & chr$(34) & " "
sql.code = sql.code & "order by tibom010._index1"


You could always replace "100" and "905" with string variables from your user. Also note I do not use wherebind - based off the first query. Mainly because your user input mitm.f and mitm.t. So use the actual input data. So if your first query works for you then something like the above should also work and allow you to have the company numbers as input fields.

Yes Hitesh, I have come to know now that merging records of 2 companies is not possible. But when I didn't use variables, the following query gave me more or less what I wanted. I can say it was giving a bit of merged one. This type of query is in the Baan document as an example and may be it works only that way.

select tibom010.*
from tibom010
where tibom010.mitm between :mitm.f and :mitm.t
and tibom010._compnr = "100,905"
order by tibom010._index1
selectdo
rprt_send()
endselect

The result in the report detail section was

100 manuf1:
component1
component2
905 manuf1:
component1
component2

100 manuf2:
component3
component4

905 manuf2:
component3
component6

I brought in the after layout they are side by side and then suppressed the identical ones so that only the difference appears in the report. This was after considerable effort. Now I will try in the dynamic sql to change the company after a change in manufactured item and see whether somehow I can fit the program script for my report script. Otherwise I'll just use fixed companies. Once again, thanks for the quick response.

Regards,
srikarthy

PS: while typing this, mark has posted a message and I'll read and reply separately.

Otherwise I'll just use fixed companies. .

U can still use variables for company numbers (not usiing "100,905" or in specification) using OR condition like

select tibom010.*
from tibom010
where tibom010.mitm between :mitm.f and :mitm.t
and (tibom010._compnr = :company1 OR
tibom010._compnr = :company2)
order by tibom010._index1
selectdo
rprt_send()
endselect

where tibom010.mitm between :mitm.f and :mitm.t
and (tibom010._compnr = :company1 OR
tibom010._compnr = :company2)

Hi Hitesh,

Brilliant. It is working. I was missing the brackets. It compiled without brackets and thought Baan was wrong when I got the SQL error while running. So it seems a successful compilation doesn't mean the code is correct. I must not forget to use brackets whenever there is "and" "or" mix up. Thanks very much.

Regards,
srikarthy

Hi there,

If you want to select records from more than one company, then it depends on the version whether the driver allows those constructions I see here.

In LN it is no longer allowed to join on the ._compnr variable or to use the "or" construction with it. A table can only be read in one company at a time.

In LN you can however use the UNION ALL command. This command does not work with a query extend, so you can not use it to show data of multiple companies in one session, however it can perfectly work as a query to get data on a report. Here a short part from the programmer's manual:
The following example joins the names of all employees in companies 000 and 001 having a salary bigger than 40000. The result is sorted by company.

SELECT _compnr, firstnme, lastname
FROM dbtst120
WHERE salary > 40000
AND _compnr = 000

UNION ALL

SELECT _compnr, firstnme, lastname
FROM dbtst120
WHERE salary > 40000
AND _compnr = 001

ORDER BY _compnr
Note that in this case it is possible to sort by name, since both SELECT statements share the column name for the first column.

Regards,
Michiel