SQL syntax problem

Hanney

Hi there,In Navision, they have
global dimension 1 code which table related to dimension value.code with condition global dimension = 1 and global dimension 2 code which table related to dimension value.code with condition global dimension = 2

I want to create report with query
Select posting date, global dimension 1 as department code, global dimension 2 as project code from G\L Entry

i get record with empty data for both global dimension code
right now i dont know how retrieve data for both global dimension code which same condition.
i had try to do (who knows it work)

Select [Posting Date], [Global Dimension 1 Code]
[select Code as [Department name] from [CRONUS Malaysia Sdn_ Bhd_$Dimension Value] where ([Global Dimension No_]=1)],[Global Dimension 2 Code] [SELECT Code AS [Department name] FROM [CRONUS Malaysia Sdn_ Bhd_$Dimension Value]
where ([Global Dimension No_]= 2)]
from [CRONUS Malaysia Sdn_ Bhd_$G_L Entry]

error msg:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ']'.
any idea..?
Thank you

garak

The Global dimensions 1 and 2 also stored in the G/L Entry.
Do you need also the names of the Dinension codes?
Your query is more then bad ;-)

Here the solution with the names.

Select [Posting Date], [Global Dimension 1 Code], 
(select Name as [Department name] from [Cronus$Dimension Value] 
	where ([Code]=[Global Dimension 1 Code])) as DIMCODE1NAME,
[Global Dimension 2 Code],
(SELECT Name AS [Department name] FROM [Cronus$Dimension Value]
	where ([Code]= [Global Dimension 2 Code])) as DIMCODE2NAME
from [Cronus$G_L Entry]
	where [Global Dimension 1 Code] = '1'
	and [Global Dimension 2 Code] = '2'

The result set is:

Posting Date Global Dimension 1 Code DIMCODE1NAME Global Dimension 2 Code DIMCODE2NAME

---

---

---

2007-11-02 00:00:00.000 1 Sales 1 2 Project
2007-11-02 00:00:00.000 1 Sales 1 2 Project
2007-11-02 00:00:00.000 1 Sales 1 2 Project
2007-11-02 00:00:00.000 1 Sales 1 2 Project

Do you need this?

stryk

Hanney wrote:

Select [Posting Date], [Global Dimension 1 Code]
[select Code as [Department name] from [CRONUS Malaysia Sdn_ Bhd_$Dimension Value] where ([Global Dimension No_]=1)],[Global Dimension 2 Code] [SELECT Code AS [Department name] FROM [CRONUS Malaysia Sdn_ Bhd_$Dimension Value]
where ([Global Dimension No_]= 2)]
from [CRONUS Malaysia Sdn_ Bhd_$G_L Entry]

I guess it should be ...

Select [Posting Date]
, [Global Dimension 1 Code] 
, (select Code as [Department name] from [CRONUS Malaysia Sdn_ Bhd_$Dimension Value] where ([Global Dimension No_]=1))
,[Global Dimension 2 Code]
, (SELECT Code AS [Department name] FROM [CRONUS Malaysia Sdn_ Bhd_$Dimension Value] where ([Global Dimension No_]= 2))
from [CRONUS Malaysia Sdn_ Bhd_$G_L Entry]

... then it should be syntactically correct (haven't tested). But I'm not sure if this is really what you want; if I understood you right, you want something like this:

SELECT gle.[Posting Date], gle.[Global Dimension 1 Code], dv1.[Code] as [Department Name 1],
,gle.[Global Dimension 2 Code], dv2.[Code] as [Department Name 2]
FROM [CRONUS Malaysia Sdn_ Bhd_$G_L Entry] gle
LEFT JOIN [CRONUS Malaysia Sdn_ Bhd_$Dimension Value] dv1 ON dv1.[Dimension Code] = gle.[Global Dimension 1 Code]
LEFT JOIN [CRONUS Malaysia Sdn_ Bhd_$Dimension Value] dv2 ON dv2.[Dimension Code] = gle.[Global Dimension 2 Code]

Does this work?

Hanney

sory for the late reply.. :oops:
i had try both stryk and garak code but i prefer to use stryk code.
Thanks to garak too, your code also give me an idea to solve another prob...

but i still thinking what wrong with my syntax?

garak

one is the '' behind it

Hanney

But when i change it like this:

Select [Posting Date],
(select Code as [Department name] from [CRONUS Malaysia Sdn_ Bhd_$Dimension Value] where ([Global Dimension No_]=1)),
(SELECT Code AS [Project name] FROM [CRONUS Malaysia Sdn_ Bhd_$Dimension Value] where ([Global Dimension No_]= 2))
from [CRONUS Malaysia Sdn_ Bhd_$G_L Entry]

I got error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What wrong with that? :-k

davmac1

Your subqueries are selecting all the values for each Dimension Value.

If you want to see what they are returning, run the subqueries as separate queries and see what results they are returning.

The left joins in the earlier examples return just the matching value for that entry if it exists.

Hanney

I already run separately the subqueries..the result return what i want..
I also search sql tutorial in the net, I found the syntax like this:
SELECT pno, qty, (SELECT city FROM s WHERE s.sno = sp.sno)
FROM sp
that why I relly want to try..

but i guess i better to use left join..its easy..