We help IT Professionals succeed at work.

How to make this query work

team2005
team2005 used Ask the Experts™
on
Hi!

Tryed this query:

select
    REG.Lopenummer 'Løpenummer',
    REG.Markedsforingnavn 'Butikknavn',
    REG.Juridisknavn 'Firmanavn',
    REG.Organisasjonsnr 'Orgnr',
    REG.Besoksadresse 'Besoksadresse',
    REG.postnrbesok 'Postnummer',
    REG.Poststedbesok 'Poststed 1',
    REG.Fakturaadresse 'Fakturadresse',
    REG.Postnrfaktura 'Postnummer',
    REG.Poststedfaktura 'Poststed',
    REG.Telefon 'Telefon',
    REG.Telefax 'Fax',
    REG.Okonomiskregion 'Okonomisk region',
    REG.Modul 'Modul',
    REG.Fylke 'Fylke',
    REG.Produl 'Produl',
    REG.Grossistkundenr 'Grossist',
    REG.Butikktypeprofil 'Kundetype',
    REG.Kontonr 'Bankkonto',
    REG.Datovedlikehold 'Sist oppdatert',
    REG.Datooprettet 'Førstegang registrert',
    REG.Gammeltlopenr 'Gammelt/nytt kundenummer',
    REG.Kjedekundenr 'Kjedekundenummer',
    REG.Grossistkundenr 'Grossistkundenummer',
    REG.Lokasjonsnr 'Lokasjonsnummer',
    (SELECT dbo.KJE_GROSSISTER.Navn
FROM dbo.KJE_GROSSISTER JOIN dbo.KJE_KJEDEREG
ON dbo.KJE_GROSSISTER.Nummer=dbo.KJE_KJEDEREG.Hovedgossist),
      FT.MKjede1,
    MK1.Kjedenavn 'Navn1',
    MK1.Kjedenummer,
    FT.RKjede1,
    RK1.Kjedenavn 'Navn2',
    FT.LoepeNr,
    FT.RKjede2,
    RK2.Kjedenavn 'Navn3'
FROM dbo.KJE_FLINKTOTAL FT
INNER JOIN dbo.KJE_KJEDE MK1 on MK1.Kjedenummer = FT.MKjede1
INNER JOIN dbo.KJE_KJEDE RK1 on RK1.Kjedenummer = FT.RKjede1
INNER JOIN dbo.KJE_KJEDE RK2 on RK2.Kjedenummer = FT.RKjede2
INNER JOIN dbo.KJE_KJEDEREG REG ON FT.LoepeNr = REG.Lopenummer

Open in new window


But get this error message ?'
11:25:50  [SELECT - 0 row(s), 0.000 secs]  [Error Code: 512, SQL State: 21000]  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

How to make this query work ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
That  subselect in your selectlist

  (SELECT dbo.KJE_GROSSISTER.Navn
FROM dbo.KJE_GROSSISTER JOIN dbo.KJE_KJEDEREG
ON dbo.KJE_GROSSISTER.Nummer=dbo.KJE_KJEDEREG.Hovedgossist)

will return different rows.  I don't see it filtered to be relevant for the records it will be combined with.

I think it's the best to move that as an extra joined table ..... but still there must be found that same join-condition(filter)

Author

Commented:
Hi!

How do i do this ?
(SELECT dbo.KJE_GROSSISTER.Navn
FROM dbo.KJE_GROSSISTER JOIN dbo.KJE_KJEDEREG
ON dbo.KJE_GROSSISTER.Nummer=dbo.KJE_KJEDEREG.Hovedgossist WHERE {somethingFrom TheseTable = Something from outer tables})

Author

Commented:
Hi!

Must be combain like

WHERE FT.LoepeNr = REG.Lopenummer

Like:

SELECT dbo.KJE_GROSSISTER.Navn
FROM dbo.KJE_GROSSISTER JOIN dbo.KJE_KJEDEREG
ON dbo.KJE_GROSSISTER.Nummer=dbo.KJE_KJEDEREG.Hovedgossist WHERE FT.LoepeNr = REG.Lopenummer

Open in new window


But this give me error message:

[SELECT - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42000]  Incorrect syntax near ','.

Commented:
<<Must be combain like

WHERE FT.LoepeNr = REG.Lopenummer
>>
Yes something like that , only with relevant columns to filter to only get one result from the join  with talbes
dbo.KJE_GROSSISTER
 dbo.KJE_KJEDEREG

Maybe something like
    (SELECT dbo.KJE_GROSSISTER.Navn
FROM dbo.KJE_GROSSISTER JOIN dbo.KJE_KJEDEREG
ON dbo.KJE_GROSSISTER.Nummer=dbo.KJE_KJEDEREG.Hovedgossist
where dbo.KJE_GROSSISTER.Nummer = REG.Grossistkundenr ),

If you do
select * from
FROM dbo.KJE_GROSSISTER JOIN dbo.KJE_KJEDEREG
ON dbo.KJE_GROSSISTER.Nummer=dbo.KJE_KJEDEREG.Hovedgossist
you get more than one result, check the columns of that result (or table-definition) and see how there is a link with all the rest of your query.


<<[SELECT - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42000]  Incorrect syntax near ','.>>
For that you must post whole your statement, but probably just forgot a ')'
Commented:
If I understand your purpose correctly, change the subquery by removing the join, and change to a where clause so it becomes :

SELECT top 1 dbo.KJE_GROSSISTER.Navn
FROM dbo.KJE_GROSSISTER
WHERE dbo.KJE_GROSSISTER.Nummer=REG.Hovedgossist

The complete code will be

select
    REG.Lopenummer 'Løpenummer',
    REG.Markedsforingnavn 'Butikknavn',
    REG.Juridisknavn 'Firmanavn',
    REG.Organisasjonsnr 'Orgnr',
    REG.Besoksadresse 'Besoksadresse',
    REG.postnrbesok 'Postnummer',
    REG.Poststedbesok 'Poststed 1',
    REG.Fakturaadresse 'Fakturadresse',
    REG.Postnrfaktura 'Postnummer',
    REG.Poststedfaktura 'Poststed',
    REG.Telefon 'Telefon',
    REG.Telefax 'Fax',
    REG.Okonomiskregion 'Okonomisk region',
    REG.Modul 'Modul',
    REG.Fylke 'Fylke',
    REG.Produl 'Produl',
    REG.Grossistkundenr 'Grossist',
    REG.Butikktypeprofil 'Kundetype',
    REG.Kontonr 'Bankkonto',
    REG.Datovedlikehold 'Sist oppdatert',
    REG.Datooprettet 'Førstegang registrert',
    REG.Gammeltlopenr 'Gammelt/nytt kundenummer',
    REG.Kjedekundenr 'Kjedekundenummer',
    REG.Grossistkundenr 'Grossistkundenummer',
    REG.Lokasjonsnr 'Lokasjonsnummer',
    (SELECT top 1 dbo.KJE_GROSSISTER.Navn
FROM dbo.KJE_GROSSISTER
WHERE dbo.KJE_GROSSISTER.Nummer=REG.Hovedgossist),
      FT.MKjede1,
    MK1.Kjedenavn 'Navn1',
    MK1.Kjedenummer,
    FT.RKjede1,
    RK1.Kjedenavn 'Navn2',
    FT.LoepeNr,
    FT.RKjede2,
    RK2.Kjedenavn 'Navn3'
FROM dbo.KJE_FLINKTOTAL FT
INNER JOIN dbo.KJE_KJEDE MK1 on MK1.Kjedenummer = FT.MKjede1
INNER JOIN dbo.KJE_KJEDE RK1 on RK1.Kjedenummer = FT.RKjede1
INNER JOIN dbo.KJE_KJEDE RK2 on RK2.Kjedenummer = FT.RKjede2
INNER JOIN dbo.KJE_KJEDEREG REG ON FT.LoepeNr = REG.Lopenummer

Open in new window

Author

Commented:
Hi!

Thanks, that works fine.

But one thing, name of colum is -> 26 N/A ???

Tryed This:

 (SELECT top 1 dbo.KJE_GROSSISTER.Navn 'Grossist'
FROM dbo.KJE_GROSSISTER
WHERE dbo.KJE_GROSSISTER.Nummer=REG.Hovedgossist),

But dosent display Grossist ?

Author

Commented:
Thanks alot

Commented:
So the join in the subselect was not necessary.

Is the TOP1 necessary? No then I would not write it as a subquery but as a join, inner or left depending on the occurance

FROM dbo.KJE_FLINKTOTAL FT
INNER JOIN dbo.KJE_KJEDE MK1 on MK1.Kjedenummer = FT.MKjede1
INNER JOIN dbo.KJE_KJEDE RK1 on RK1.Kjedenummer = FT.RKjede1
INNER JOIN dbo.KJE_KJEDE RK2 on RK2.Kjedenummer = FT.RKjede2
INNER JOIN dbo.KJE_KJEDEREG REG ON FT.LoepeNr = REG.Lopenummer
left join dbo.KJE_GROSSISTER as gr  on gr.Nummer=REG.Hovedgossist
                                            

Open in new window

Commented:
Thanks Team2005

To change the column name, you should change to   :

(SELECT top 1 dbo.KJE_GROSSISTER.Navn
FROM dbo.KJE_GROSSISTER
WHERE dbo.KJE_GROSSISTER.Nummer=REG.Hovedgossist) 'Grossist'

Instead of

(SELECT top 1 dbo.KJE_GROSSISTER.Navn 'Grossist'
FROM dbo.KJE_GROSSISTER
WHERE dbo.KJE_GROSSISTER.Nummer=REG.Hovedgossist)

The Top 1 is not necessary, as long as the table KJE_GROSSISTER is unique by Nummer field. It is added to make sure the query won't fail if the subquery results in more than one record for each outer select.

Commented:
<<The Top 1 is not necessary, as long as the table KJE_GROSSISTER is unique by Nummer field.>>
then don't write it and change it from a subselect to a joined table as shown above

Commented:
You're right, it would work either using subquery or left outer join.

Author

Commented:
Hi!

Thanks for big help here. Realy learn somthing today.

Thanks you

Commented:
<<You're right, it would work either using subquery or left outer join.>>
Execept when you need a second column from that table ... it will work with the join not with the subselect.