Query with multiple outputs

[JAMES L REID]

Can anyone tell me how to write a query that does the following:

1. source file: Account Number numeric
Select 1 numeric
Select 2 numeric

2. output file: Account number
select if present.

The result output could have no output account if neither select has a value
or
the result output could have a single output record for the account if only
1 select value
or
the result output could have two output records one for account and select 1
and one for account and select 2.

So one input record could create 0, 1, or 2 output records.

Any help will be appreicated.

Jim Reid
Ja*******@Verizon.net

[Larry Linson]

What, exactly do you mean by "select 1 numeric", "select 2 numeric", and
"select if present" -- is each of these supposed to represent a field name,
or does each represent a "select" action? I am having some difficulty
understanding your question. If we can understand what you have and what you
are asking, perhaps someone can offer a worthwhile suggestion.

"JAMES L REID" <ja*******@verizon.net> wrote in message
news:R07ud.1812$sU4.1405@trndny01...

Can anyone tell me how to write a query that does the following:

1. source file: Account Number numeric
Select 1 numeric
Select 2 numeric

2. output file: Account number
select if present.

The result output could have no output account if neither select has a value or
the result output could have a single output record for the account if only 1 select value
or
the result output could have two output records one for account and select 1 and one for account and select 2.

So one input record could create 0, 1, or 2 output records.

Any help will be appreicated.

Jim Reid
Ja*******@Verizon.net

[jimfortune]

JAMES L REID wrote:

Can anyone tell me how to write a query that does the following:

If I understand what you're saying:

tblAccountOptions
Account Number Double (Primary Key?)
Select 1 Double
Select 2 Double
1234.2 Null Null
1234.3 83.1 54.2
1234.4 Null 51.0

should give:

1234.3 83.1
1234.3 54.2
1234.4 51.0

SELECT A.[Account Number], A.[Select 1] AS Selected
FROM tblAccountOptions AS A INNER JOIN tblAccountOptions ON A.[Account
Number] = tblAccountOptions.[Account Number]
WHERE (((A.[Select 1]) Is Not Null)) UNION SELECT A.[Account Number],
A.[Select 2] AS Selected
FROM tblAccountOptions AS A INNER JOIN tblAccountOptions ON A.[Account
Number] = tblAccountOptions.[Account Number]
WHERE (((A.[Select 2]) Is Not Null));

gave me:

Account Number Selected
1234.3 54.2
1234.3 83.1
1234.4 51

Note that the word 'select' by itself is a reserved word. Yes, I tried
using it first. Duh! :-)

James A. Fortune

[JAMES L REID]

Well folks I found out how to do what I wanted. I don't think I explained
it well so here goes again.

Select a, b, iff(b>0,Null,Null) as c from file1
Union Select a,iff(c>0,null,null) as b,c from file1;

what this gives me is the following:

File1:
a b c
1 2 3
2 4 5
3 6

Output:
a b c
1 2
1 3
2 4
2 5
3 6

For every input record I'm getting at least 1 maybe 2 output records
depending on what is in B & C. Hopefully this will help others trying to do
the same thing in the future.

Thanks for your help folks.

Jim

<ji********@compumarc.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

JAMES L REID wrote:

Can anyone tell me how to write a query that does the following:

If I understand what you're saying:

tblAccountOptions
Account Number Double (Primary Key?)
Select 1 Double
Select 2 Double
1234.2 Null Null
1234.3 83.1 54.2
1234.4 Null 51.0

should give:

1234.3 83.1
1234.3 54.2
1234.4 51.0

SELECT A.[Account Number], A.[Select 1] AS Selected
FROM tblAccountOptions AS A INNER JOIN tblAccountOptions ON A.[Account
Number] = tblAccountOptions.[Account Number]
WHERE (((A.[Select 1]) Is Not Null)) UNION SELECT A.[Account Number],
A.[Select 2] AS Selected
FROM tblAccountOptions AS A INNER JOIN tblAccountOptions ON A.[Account
Number] = tblAccountOptions.[Account Number]
WHERE (((A.[Select 2]) Is Not Null));

gave me:

Account Number Selected
1234.3 54.2
1234.3 83.1
1234.4 51

Note that the word 'select' by itself is a reserved word. Yes, I tried
using it first. Duh! :-)

James A. Fortune

[JAMES L REID]

Well folks I found out how to do what I wanted. I don't think I explained
it well so here goes again.

Select a, b, iff(b>0,Null,Null) as c from file1
Union Select a,iff(c>0,null,null) as b,c from file1;

what this gives me is the following:

File1:
a b c
1 2 3
2 4 5
3 6

Output:
a b c
1 2
1 3
2 4
2 5
3 6

For every input record I'm getting at least 1 maybe 2 output records
depending on what is in B & C. Hopefully this will help others trying to do
the same thing in the future.

Thanks for your help folks.

Jim

<ji********@compumarc.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

JAMES L REID wrote:

Can anyone tell me how to write a query that does the following:

If I understand what you're saying:

tblAccountOptions
Account Number Double (Primary Key?)
Select 1 Double
Select 2 Double
1234.2 Null Null
1234.3 83.1 54.2
1234.4 Null 51.0

should give:

1234.3 83.1
1234.3 54.2
1234.4 51.0

SELECT A.[Account Number], A.[Select 1] AS Selected
FROM tblAccountOptions AS A INNER JOIN tblAccountOptions ON A.[Account
Number] = tblAccountOptions.[Account Number]
WHERE (((A.[Select 1]) Is Not Null)) UNION SELECT A.[Account Number],
A.[Select 2] AS Selected
FROM tblAccountOptions AS A INNER JOIN tblAccountOptions ON A.[Account
Number] = tblAccountOptions.[Account Number]
WHERE (((A.[Select 2]) Is Not Null));

gave me:

Account Number Selected
1234.3 54.2
1234.3 83.1
1234.4 51

Note that the word 'select' by itself is a reserved word. Yes, I tried
using it first. Duh! :-)

James A. Fortune