Combined queries

system · January 19, 2000, 1:13pm

Hello all,

We want the following but do not know how to achieve it:

In a table data is stored about operations. Some operations are stored twice in this table, with some small changes in the data. Look at the following example:

Name Y_Column

1 A
2 B1
3 B2 ‘Yes’
4 C
5 D

So, B1 and B2 are almost the same. We want to select (in one query) only these rows which are unique (1,4,5) and from the duplicate rows only the ones which have the Y_Column set to ‘Yes’ (3). I know about the combined queries, but do not know how to solve this problem. I hope someone can help me.

Thanks, Tim Lauret
Tecnomatix Machining Automation
Enschede, the Netherlands

Listserv Archives (BOB member since 2002-06-25)

system · January 19, 2000, 1:38pm

How about this:

You need to do a group on field1 ( the a, b, c) + a decode on the second field that yields either a null (?) or the rowid… That might work…

Brent

Hello all,

We want the following but do not know how to achieve it:

In a table data is stored about operations. Some operations are stored twice in this table, with some small changes in the data. Look at the following example:

Name Y_Column

1 A
2 B1
3 B2 ‘Yes’
4 C
5 D

So, B1 and B2 are almost the same. We want to select (in one query) only these rows which are unique (1,4,5) and from the duplicate rows only the ones which have the Y_Column set to ‘Yes’ (3). I know about the combined queries, but do not know how to solve this problem. I hope someone can help me.
Thanks, Tim Lauret
Tecnomatix Machining Automation
Enschede, the Netherlands

Listserv Archives (BOB member since 2002-06-25)

system · January 19, 2000, 1:39pm

Have you tried using the max function on both the name column and the Y_column in your query? You say B1 and B2 are duplicate rows so I’m assuming that the name would actually be the same or that there is some identifier that you can put a max value on to suggest that the two rows are the same? i.e. in this example you’d do a max(substring(name,1)), and then a max(Y_column), and I think you’d just get the 1 row, I haven’t tested that, just brainstorming!

Julie Neuman

We want the following but do not know how to achieve it:

In a table data is stored about operations. Some operations are stored twice in this table, with some small changes in the data. Look at the following example:

Name Y_Column

1 A
2 B1
3 B2 ‘Yes’
4 C
5 D

So, B1 and B2 are almost the same. We want to select (in one query) only these rows which are unique (1,4,5) and from the duplicate rows only the ones which have the Y_Column set to ‘Yes’ (3). I know about the combined queries, but do not know how to solve this problem. I hope someone can help
me.

Listserv Archives (BOB member since 2002-06-25)

system · September 24, 2001, 8:25pm

Hi BO experts,

I have 2 queries in the same report as follows:

Query1
EMP_Num Department Placed in Department

Listserv Archives (BOB member since 2002-06-25)

system · September 25, 2001, 4:08pm

Hi BO experts,

I have 2 queries in the same report as follows:

Query1
EMP_Num Department Placed in Department

Listserv Archives (BOB member since 2002-06-25)

system · September 25, 2001, 5:31pm

In a message dated Tue, 25 Sep 2001 1:17:04 PM Eastern Daylight Time, Yuval yuval@KRSW.CO.IL writes:

Hi BO experts,

I have 2 queries in the same report as follows:

Query1
EMP_Num Department Placed in Department
1 Sales 18/1/2000
1 R&D 18/5/2000
1 QA 18/2/2001
2 R&D 18/3/2000
2 QA 31/9/2001

Query2
EMP_Num Child Name Child Birthday
1 Joe 17/1/1969
1 Tami 14/5/1985
2 Yoni 19/8/1974
2 Tom 16/7/1972
2 Fred 25/6/1989

What I am trying to get is a report that looks like this:

EMP_Num Department Placed in Department Child Name Child
BDay
1 Sales 18/1/2000
Joe 17/1/1969
1 R&D 18/5/2000
Tami 14/5/1985
1 QA 18/2/2001
2 R&D 18/3/2000
Yoni 19/8/1974
2 QA 31/9/2001
Tom 16/7/1972
2
Fred 25/6/1989

Is it possible to do that in BO ?

What you are asking for does not make sense, actually. If you try to put everything into one block, you are forcing a relationship between “child” and “department”, and the number of children an employee has will have nothing to do with the department that he/she is in. It’s meaningless.

What you can do is use the “Set as Master” option, and create two blocks. Your data would then look like this:

Employee Number ###

Block 1 Block 2
Department Assigned to Dept Child Name Birthday

Some employees may have 5 children and only one department. Others may have 5 departments and no children. By separating the two pieces of information into separate blocks, combining with a Master / Detail (section) structure, you will get an appropriate picture of your data.

You can simulate this by creating a query using Island Resorts. Select Customer, Invoice Date, Number of Guests, Reservation Date, and Future Guests. BusObj will automatically generate the correct output format.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)