Select in Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select in Select

Author

Topic

luc_chivas
Starting Member

3 Posts

Posted - 2009-04-19 : 04:36:57

'morning all
I'am trying to write a query with conditional information.
I've write the following query :

SELECT GLYEAR.ACCOUNT + GLYEAR.ENTITY As ACCOUNTENTITY,
TFR_REKENING.BP,
GLYEAR.ACCOUNT,
TFR_REKENING.Description As DESCRIPTION,
CASE
WHEN LEN(GLYEAR.ENTITY) >3 THEN
SUBSTRING(GLYEAR.ENTITY,1,3) ELSE
'TF-'
END AS REGION,
CASE
WHEN LEN(GLYEAR.ENTITY) >3 THEN
SUBSTRING(GLYEAR.ENTITY,4,LEN(GLYEAR.ENTITY)-3) ELSE
''
END AS DEPT,
TFR_REKENING.Fscode As FSCODE,
TFR_REKENING.FSDesc As FSDESC,
CONSO.RAP1 As FRAP1,
FSRAP1.[FSRap1 explanation] As FRAP1_DESCRIPTION,
CONSO.RAP2 As FRAP2,
FSRAP2.[FSRap2 explanation] As FRAP2_DESCRIPTION,
CONSO.RAP3 As FRAP3,
FSRAP3.[FSRap3 explanation] As FRAP3_DESCRIPTION,
TFR_DEPARTM.Ccons1 As CONS1,
TFR_DEPARTM.Ccons2 As CONS2,
TFR_DEPARTM.CRap1 As DRAP1,
TFR_DEPARTM.CRap2 As DRAP2,
TFR_DEPARTM.CRap3 As DRAP3,
TFR_DEPARTM.CRap4 As DRAP4,
CASE
WHEN CONSO.RAP1 IN ('60','65','67','70','75') THEN
(SELECT EBIT
FROM TFR_REKENING
WHERE TFR_REKENING.RAP1 = FRAP1
AND TFR_REKENING.Fscode = FSCode) ELSE
(SELECT EBIT
FROM DEPT
WHERE DEPT.DEPT = DEPT)
END As EBIT,
glyear.[Period 1],
glyear.[Period 2],
glyear.[Period 3],
glyear.[Period 4],
glyear.[Period 5],
glyear.[Period 6],
glyear.[Period 7],
glyear.[Period 8],
glyear.[Period 9],
glyear.[Period 10],
glyear.[Period 11],
glyear.[Period 12],
glyear.[Period 0]

FROM GLYEAR AS glyear LEFT OUTER JOIN
TFR_REKENING AS TFR_REKENING ON glyear.Account = TFR_REKENING.Compte_france LEFT OUTER JOIN
TFR_DEPARTM AS TFR_DEPARTM ON glyear.Entity = TFR_DEPARTM.[Analytique France] LEFT OUTER JOIN
CONSO AS CONSO ON TFR_REKENING.Fscode = CONSO.Code LEFT OUTER JOIN
FSRAP1 AS FSRAP1 ON CONSO.Rap1 = FSRAP1.FSRap1 LEFT OUTER JOIN
FSRAP2 AS FSRAP2 ON CONSO.Rap2 = FSRAP2.FSRap2 LEFT OUTER JOIN
FSRAP3 AS FSRAP3 ON CONSO.Rap3 = FSRAP3.FSRap3
ORDER BY TFR_REKENING.B

In the Select in Select part , I want the where clause to take care of the previous FRAP1 and FSCODE..
is that the right way ??

Luc

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-19 : 08:18:40

[code]SELECT glyear.ACCOUNT + glyear.entity AS accountentity,
tfr_rekening.bp,
glyear.ACCOUNT,
tfr_rekening.DESCRIPTION AS DESCRIPTION,
CASE
WHEN Len(glyear.entity) > 3 THEN Substring(glyear.entity,1,3)
ELSE 'TF-'
END AS region,
CASE
WHEN Len(glyear.entity) > 3 THEN Substring(glyear.entity,4,Len(glyear.entity) - 3)
ELSE ''
END AS dept,
tfr_rekening.fscode AS fscode,
tfr_rekening.fsdesc AS fsdesc,
conso.rap1 AS frap1,
fsrap1.[fsrap1 explanation] AS frap1_description,
conso.rap2 AS frap2,
fsrap2.[fsrap2 explanation] AS frap2_description,
conso.rap3 AS frap3,
fsrap3.[fsrap3 explanation] AS frap3_description,
tfr_departm.ccons1 AS cons1,
tfr_departm.ccons2 AS cons2,
tfr_departm.crap1 AS drap1,
tfr_departm.crap2 AS drap2,
tfr_departm.crap3 AS drap3,
tfr_departm.crap4 AS drap4,
CASE
WHEN conso.rap1 IN ('60','65','67','70',
'75') THEN (SELECT ebit
FROM tfr_rekening
WHERE tfr_rekening.rap1 = frap1
AND tfr_rekening.fscode = fscode)
ELSE (SELECT ebit
FROM dept
WHERE dept.dept = dept)
END AS ebit,
glyear.[period 1],
glyear.[period 2],
glyear.[period 3],
glyear.[period 4],
glyear.[period 5],
glyear.[period 6],
glyear.[period 7],
glyear.[period 8],
glyear.[period 9],
glyear.[period 10],
glyear.[period 11],
glyear.[period 12],
glyear.[period 0]
FROM glyear AS glyear
LEFT OUTER JOIN tfr_rekening AS tfr_rekening
ON glyear.ACCOUNT = tfr_rekening.compte_france
LEFT OUTER JOIN tfr_departm AS tfr_departm
ON glyear.entity = tfr_departm.[analytique france]
LEFT OUTER JOIN conso AS conso
ON tfr_rekening.fscode = conso.code
LEFT OUTER JOIN fsrap1 AS fsrap1
ON conso.rap1 = fsrap1.fsrap1
LEFT OUTER JOIN fsrap2 AS fsrap2
ON conso.rap2 = fsrap2.fsrap2
LEFT OUTER JOIN fsrap3 AS fsrap3
ON conso.rap3 = fsrap3.fsrap3
ORDER BY tfr_rekening.b [/code]

Now the code is a bit more readable.

No, you're never too old to Yak'n'Roll if you're too young to die.