I have the following piece of code:

Select tdsls045.orno,tdsls045.ssls
from tdsls045
where tdsls045.ssls = 1
group by tdsls045.orno,tdsls045.ssls
order by tdsls045.orno,tdsls045.ssls

The code works when I run it as a Query
but not in the program script.
What am I doing wrong?

Does your code have a selectdo and endselect in the script?

Select tdsls045.orno,tdsls045.ssls
from tdsls045
where tdsls045.ssls = 1
group by tdsls045.orno,tdsls045.ssls
order by tdsls045.orno,tdsls045.ssls
selectdo
endselect

If it does is there another error message from compiling? Can you post the whole script?

The whole script is as follows:

select tdsls045.orno,tdsls045.ssls
from tdsls045
where tdsls045.ssls = 1
group by tdsls045.orno,tdsls045.ssls
order by tdsls045.orno,tdsls045.ssls
selectdo
select tdsls040.*,tccom010.*
from tdsls040,tccom010
where tdsls040._index1 = {:tdsls045.orno}
and tdsls040.cuno refers to tccom010
selectdo
endselect
endselect

The program returns all orders regardless of
the value of tdsls045.ssls

Hi,

The field tdsls045.orno will be having an unique value. There wont be any repeatation to the value, so Group by for that field is not need.

And for tdsls045.ssls gives the value '1' as you mentioned as condition, so it will return only the value '1'. so Group by for that field too not need.

You can give tdsls045.orno in Order by, order by for ssls is also not need.

I guess SQL can manage all these things and gives some value which baansql doesnt.

Check by giving the following query in script,

select tdsls045.orno,tdsls045.ssls
from tdsls045
where tdsls045.ssls = 1
order by tdsls045.orno


Regards,
Thomson S

It still returns all Sales Orders, ignoring the
where condition. tdsls045.orno is not a unique value
as there are multiple records for the same orno
with different position numbers.

This is weird. Though I have seen such a thing before. In that case there was a "|" in the line of the FROM clause and no new line character behind in. In that case the WHERE clause was interpreted as comment.

It still returns all Sales Orders, ignoring the
where condition. tdsls045.orno is not a unique value
as there are multiple records for the same orno
with different position numbers.

Hello,
i've done something like yours in BIV :


select tdsls045.*, tdsls040.refb, tdsls041.*, tccom010.nama,
tcmcs023.dsca, tdsls040.refa, tdsls040.eono
from tdsls045, tdsls040, tdsls041, tccom010
Where tdsls045._index2 inrange {:ssls.f, :cuno.f, :orno.f} and
{:ssls.t, :cuno.t, :orno.t} and
tdsls045.item between :item.f and :item.t and
tdsls045.ddat between :ddat.f and :ddat.t and
tdsls041.citg between :citg.f and :citg.t and
tdsls041.ddta between :ddta.f and :ddta.t and
tdsls040.refb between :emno.f and :emno.t and
tdsls045.orno refers to tdsls040 and
tdsls045.cmba refers to tdsls041 and
tdsls045.cuno refers to tccom010 and
tdsls041.citg refers to tcmcs023 |and
order by tdsls045.ssls,tdsls041.citg,tdsls041.ddta,
tdsls045.cuno,tdsls045.item,tdsls045.orno
selectdo


Regards

I agree with jaapjd - this is weird. What kind of session and script is this? Can you attach the complete script. There could be other queries involved or something in the report causing the issue. Assuming this is a report the first thing I would do is comment out everything, but this and run it in debug mode until I got this piece working:

select tdsls045.orno,tdsls045.ssls
from tdsls045
where tdsls045.ssls = 1

because until you get this working the rest will not work. Unless there is something wrong with the data, which you will have to confirm.

I got the script working fine and selecting only
records with tdsls045.ssls = 1. The problem is
the type of session - Display Session. The form
type is 2 (multi-occurence) and main table is
tdsls040. I tried display.curr.occ() but that didn't
seem to help. It returns all records in the main table.

In a type-2 session you should not use queries to filter the records being displayed. Use query.extension.