Nested

[igloo]

Hi all,
I have a query that looks like so:
SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID AS
Person_Name
FROM F0911
WHERE (GLAID = '00181913')

However by stipulating that GLAID = GLAID I cannot get the person_name
as not all the GLALID fields are filled in. from my reading of the
helpdesk I have a felling that a nested query might be the way to go
or a self-join but beyond this I am lost!?
Many thanks for any pointers in advance.

Sam

[Simon Hayes]

Re: Nested


"igloo" <igloo@spamhole .com> wrote in message
news:eed8672e.0 401080527.78d5b a30@posting.goo gle.com...[color=blue]
> Hi all,
> I have a query that looks like so:
> SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID AS
> Person_Name
> FROM F0911
> WHERE (GLAID = '00181913')
>
> However by stipulating that GLAID = GLAID I cannot get the person_name
> as not all the GLALID fields are filled in. from my reading of the
> helpdesk I have a felling that a nested query might be the way to go
> or a self-join but beyond this I am lost!?
> Many thanks for any pointers in advance.
>
> Sam[/color]

It's not completely clear from your post what you mean - are there NULLs in
the GLALID column, or the GLAID column, or both? I've made a couple of
complete guesses below, but if they don't help then you should post some
more details, preferably including your table structure and some sample
data.

SELECT
GLDCT AS [Doc Type],
GLDOC AS DocNumber,
GLALID AS Person_Name
FROM F0911
WHERE GLAID = '00181913' OR
GLAID IS NULL

SELECT
GLDCT AS [Doc Type],
GLDOC AS DocNumber,
ISNULL(GLAID, GLALID) AS Person_Name
FROM F0911
WHERE GLAID = '00181913'

Simon

[igloo]

Re: Nested

Sorry I realise that this is somewhat esoteric I'll try and explain it
better: If I had:

Doc_Type Doc_Number Person_Name GLAID

F 300 00181913
F 300 John 00265898


There are many more fields but by filtering on 00181913 I could never
see the name john I need to put his name in if it has the same
Doc_Type and Doc_Number.
In an ideal world I'd like to populate the Person_Name field with all
‘john' but this is not practical at the present.

Hope that's a bit less muddy now?

Thanks again.
IL

[Simon Hayes]

Re: Nested


"igloo" <igloo@spamhole .com> wrote in message
news:eed8672e.0 401090717.5c9ea b9c@posting.goo gle.com...[color=blue]
> Sorry I realise that this is somewhat esoteric I'll try and explain it
> better: If I had:
>
> Doc_Type Doc_Number Person_Name GLAID
>
> F 300 00181913
> F 300 John 00265898
>
>
> There are many more fields but by filtering on 00181913 I could never
> see the name john I need to put his name in if it has the same
> Doc_Type and Doc_Number.
> In an ideal world I'd like to populate the Person_Name field with all
> 'john' but this is not practical at the present.
>
> Hope that's a bit less muddy now?
>
> Thanks again.
> IL[/color]

That's a little clearer, although I'm still not sure I understand
completely. But I guess you may want something like this:

select f.doc_type, f.doc_number, coalesce(f.pers on_name, dt.person_name) ,
f.GLAID
from
foo f
join
(
select distinct doc_type, doc_number, person_name
from foo
where person_name is not null) dt
on f.doc_type = dt.doc_type and
f.doc_number = dt.doc_number
where f.GLAID = '00181913'

Without knowing more about the table structure (ie the CREATE TABLE
statement), and which columns are NULLable, which are keys etc.this is just
a guess, and may not work correctly in all cases.

Simon