How to make a correct select

[jazpar]

Could anyone help med with a select statement with a join between to
tables. It is to be used in a OLAP cube.
I Havde table LedgerBudget and Table Admin. In table admin I can setup
a from and to date and also a budgetmodel.
The admin have ONE record per OLAP cube.

The statement below works fine if I have stated a budgetmodel in table
Admin.
But if no budetmodel stated in table Admin, I want the statement to
select every ledgerbudget with active = 1 and allocatemethod = 0
Could anyone help me with this.
SELECT LTRIM(dbo.LEDGERBUDGET.ACCOUNTNUM) AS ACCOUNT_ID,
dbo.LEDGERBUDGET.STARTDATE AS TRANSDATE, - dbo.LEDGERBUDGET.AMOUNT AS
BUDGET
FROM dbo.LEDGERBUDGET INNER JOIN
dbo.ADMIN ON dbo.LEDGERBUDGET.STARTDATE >=
dbo.ADMIN.FROMDATE AND
dbo.LEDGERBUDGET.STARTDATE <= dbo.ADMIN.TODATE
AND
dbo.LEDGERBUDGET.MODELNUM =
dbo.ADMIN.BUDGETMODELID
WHERE (dbo.LEDGERBUDGET.ACTIVE = 1) AND
(dbo.LEDGERBUDGET.ALLOCATEMETHOD = 0)
BR/Thanks

[Hugo Kornelis]

On 24 May 2006 06:23:58 -0700, jazpar wrote:

(snip)

The statement below works fine if I have stated a budgetmodel in table
Admin.
But if no budetmodel stated in table Admin, I want the statement to
select every ledgerbudget with active = 1 and allocatemethod = 0
Could anyone help me with this.

(snip)

Hi jazpar,

Try changing the join from INNER JOIN to LEFT OUTER JOIN.

If that doesn't do what you need, post table structure (as CREATE TABLE
statements, including constraints, properties and indexes), sample data
(as INSERT statements) and expected results. See www.aspfaq.com/5006

--
Hugo Kornelis, SQL Server MVP

[jazpar]

Hi Hugo, and thanks

Here is table ADMIN (actual name GURU_ADMIN)
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GURU_ADMIN]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[GURU_ADMIN]
GO

CREATE TABLE [dbo].[GURU_ADMIN] (
[OLAPFROMDATE] [datetime] NOT NULL ,
[OLAPCUBENAME] [varchar] (30) COLLATE Danish_Norwegian_CI_AS NOT NULL
,
[OLAPTODATE] [datetime] NOT NULL ,
[BUDGETMODELID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL
,
[GURUDESCRIPTION] [varchar] (250) COLLATE Danish_Norwegian_CI_AS NOT
NULL ,
[DATAAREAID] [varchar] (3) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[RECID] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GURU_ADMIN] ADD
CONSTRAINT [DF__GURU_ADMI__OLAPF__5C482906] DEFAULT ('1900-01-01
00:00:00.000') FOR [OLAPFROMDATE],
CONSTRAINT [DF__GURU_ADMI__OLAPC__5D3C4D3F] DEFAULT ('') FOR
[OLAPCUBENAME],
CONSTRAINT [DF__GURU_ADMI__OLAPT__5E307178] DEFAULT ('1900-01-01
00:00:00.000') FOR [OLAPTODATE],
CONSTRAINT [DF__GURU_ADMI__BUDGE__5F2495B1] DEFAULT ('') FOR
[BUDGETMODELID],
CONSTRAINT [DF__GURU_ADMI__GURUD__6018B9EA] DEFAULT ('') FOR
[GURUDESCRIPTION],
CONSTRAINT [DF__GURU_ADMI__DATAA__610CDE23] DEFAULT ('dat') FOR
[DATAAREAID],
CHECK ([RECID] <> 0)
GO

CREATE UNIQUE INDEX [I_50001RECID] ON
[dbo].[GURU_ADMIN]([DATAAREAID], [RECID]) ON [PRIMARY]
GO

Table Ledgerbudget
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[LEDGERBUDGET]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[LEDGERBUDGET]
GO

CREATE TABLE [dbo].[LEDGERBUDGET] (
[ACCOUNTNUM] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[STARTDATE] [datetime] NOT NULL ,
[ENDDATE] [datetime] NOT NULL ,
[FREQCODE] [int] NOT NULL ,
[ACTIVE] [int] NOT NULL ,
[AMOUNT] [numeric](28, 12) NOT NULL ,
[COMMENT_] [varchar] (30) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DIMENSION] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DIMENSION2_] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DIMENSION3_] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[AUTOTRANS] [int] NOT NULL ,
[CURRENCY] [varchar] (3) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[QTY] [numeric](28, 12) NOT NULL ,
[PRICE] [numeric](28, 12) NOT NULL ,
[STOP] [int] NOT NULL ,
[KEY_] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[EXPANDID] [int] NOT NULL ,
[REPORT] [int] NOT NULL ,
[COV] [int] NOT NULL ,
[COVSTATUS] [int] NOT NULL ,
[CREDITING] [int] NOT NULL ,
[FREQ] [int] NOT NULL ,
[TAXGROUP] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[MODELNUM] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[INVENTRECID] [int] NOT NULL ,
[INVENTTABLEID] [int] NOT NULL ,
[ALLOCATEMETHOD] [int] NOT NULL ,
[FORECASTMODELID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT
NULL ,
[ASSETID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[ASSETTRANSTYPE] [int] NOT NULL ,
[ASSETBOOKID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[MODIFIEDBY] [varchar] (5) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DATAAREAID] [varchar] (3) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[RECID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [I_196MODELIDX] ON
[dbo].[LEDGERBUDGET]([DATAAREAID], [MODELNUM], [ACCOUNTNUM],
[STARTDATE]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LEDGERBUDGET] ADD
CONSTRAINT [DF__LEDGERBUD__ACCOU__2B9540A9] DEFAULT ('') FOR
[ACCOUNTNUM],
CONSTRAINT [DF__LEDGERBUD__START__2C8964E2] DEFAULT ('1900-01-01
00:00:00.000') FOR [STARTDATE],
CONSTRAINT [DF__LEDGERBUD__ENDDA__2D7D891B] DEFAULT ('1900-01-01
00:00:00.000') FOR [ENDDATE],
CONSTRAINT [DF__LEDGERBUD__FREQC__2E71AD54] DEFAULT (0) FOR
[FREQCODE],
CONSTRAINT [DF__LEDGERBUD__ACTIV__2F65D18D] DEFAULT (0) FOR [ACTIVE],
CONSTRAINT [DF__LEDGERBUD__AMOUN__3059F5C6] DEFAULT (0) FOR [AMOUNT],
CONSTRAINT [DF__LEDGERBUD__COMME__314E19FF] DEFAULT ('') FOR
[COMMENT_],
CONSTRAINT [DF__LEDGERBUD__DIMEN__32423E38] DEFAULT ('') FOR
[DIMENSION],
CONSTRAINT [DF__LEDGERBUD__DIMEN__33366271] DEFAULT ('') FOR
[DIMENSION2_],
CONSTRAINT [DF__LEDGERBUD__DIMEN__342A86AA] DEFAULT ('') FOR
[DIMENSION3_],
CONSTRAINT [DF__LEDGERBUD__AUTOT__351EAAE3] DEFAULT (0) FOR
[AUTOTRANS],
CONSTRAINT [DF__LEDGERBUD__CURRE__3612CF1C] DEFAULT ('') FOR
[CURRENCY],
CONSTRAINT [DF__LEDGERBUDGE__QTY__3706F355] DEFAULT (0) FOR [QTY],
CONSTRAINT [DF__LEDGERBUD__PRICE__37FB178E] DEFAULT (0) FOR [PRICE],
CONSTRAINT [DF__LEDGERBUDG__STOP__38EF3BC7] DEFAULT (0) FOR [STOP],
CONSTRAINT [DF__LEDGERBUDG__KEY___39E36000] DEFAULT ('') FOR [KEY_],
CONSTRAINT [DF__LEDGERBUD__EXPAN__3AD78439] DEFAULT (0) FOR
[EXPANDID],
CONSTRAINT [DF__LEDGERBUD__REPOR__3BCBA872] DEFAULT (0) FOR [REPORT],
CONSTRAINT [DF__LEDGERBUDGE__COV__3CBFCCAB] DEFAULT (0) FOR [COV],
CONSTRAINT [DF__LEDGERBUD__COVST__3DB3F0E4] DEFAULT (0) FOR
[COVSTATUS],
CONSTRAINT [DF__LEDGERBUD__CREDI__3EA8151D] DEFAULT (0) FOR
[CREDITING],
CONSTRAINT [DF__LEDGERBUDG__FREQ__3F9C3956] DEFAULT (0) FOR [FREQ],
CONSTRAINT [DF__LEDGERBUD__TAXGR__40905D8F] DEFAULT ('') FOR
[TAXGROUP],
CONSTRAINT [DF__LEDGERBUD__MODEL__418481C8] DEFAULT ('') FOR
[MODELNUM],
CONSTRAINT [DF__LEDGERBUD__INVEN__4278A601] DEFAULT (0) FOR
[INVENTRECID],
CONSTRAINT [DF__LEDGERBUD__INVEN__436CCA3A] DEFAULT (0) FOR
[INVENTTABLEID],
CONSTRAINT [DF__LEDGERBUD__ALLOC__4460EE73] DEFAULT (0) FOR
[ALLOCATEMETHOD],
CONSTRAINT [DF__LEDGERBUD__FOREC__455512AC] DEFAULT ('') FOR
[FORECASTMODELID],
CONSTRAINT [DF__LEDGERBUD__ASSET__464936E5] DEFAULT ('') FOR
[ASSETID],
CONSTRAINT [DF__LEDGERBUD__ASSET__473D5B1E] DEFAULT (0) FOR
[ASSETTRANSTYPE],
CONSTRAINT [DF__LEDGERBUD__ASSET__48317F57] DEFAULT ('') FOR
[ASSETBOOKID],
CONSTRAINT [DF__LEDGERBUD__MODIF__4925A390] DEFAULT ('?') FOR
[MODIFIEDBY],
CONSTRAINT [DF__LEDGERBUD__DATAA__4A19C7C9] DEFAULT ('dat') FOR
[DATAAREAID],
CHECK ([RECID] <> 0)
GO

CREATE INDEX [I_196ACCOUNTIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[ACCOUNTNUM], [MODELNUM], [STARTDATE]) ON [PRIMARY]
GO

CREATE INDEX [I_196EXPANDIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[EXPANDID]) ON [PRIMARY]
GO

CREATE INDEX [I_196REPIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[REPORT], [ACCOUNTNUM], [MODELNUM], [STARTDATE]) ON [PRIMARY]
GO

CREATE INDEX [I_196COVIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[COVSTATUS]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [I_196RECID] ON
[dbo].[LEDGERBUDGET]([DATAAREAID], [RECID]) ON [PRIMARY]
GO

Sample data could be (I dont know how to make these in a file)

Sample data Ledgerbudget:
Accountnum, Startdate, Modelnum,Amount,Active,AllocateMethod
1, 01012006,Test1,100,1,0
1, 01012006,Test2,100,1,0

Sample data Guru_Admin (record with no BudgetModelId):
OLAPFromDate,OLAPToDate,BudgetModelId
01012006,31012006,''

Sample data Guru_Admin (record with BudgetModelId):
OLAPFromDate,OLAPToDate,BudgetModelId
01012006,31012006,'Test1'

So the first case should return both records from table LedgerBudget,
and the latter case should only return the first record from
LedgerBudget.

I hope you can be able to help with this matter.
Thanks /BR
Jan

[Hugo Kornelis]

On 26 May 2006 00:34:23 -0700, jazpar wrote:

Hi Hugo, and thanks

Here is table ADMIN (actual name GURU_ADMIN) (snip)

Hi Jan,

Thanks for the CREATE TABLE statements.
Sample data could be (I dont know how to make these in a file)

Sample data Ledgerbudget:
Accountnum, Startdate, Modelnum,Amount,Active,AllocateMethod
1, 01012006,Test1,100,1,0
1, 01012006,Test2,100,1,0
Converting these rows to INSERT statements yields

INSERT INTO LEDGERBUDGET (ACCOUNTNUM, STARTDATE, MODELNUM, AMOUNT,
ACTIVE, ALLOCATEMETHOD)
SELECT 1, '20060101', 'Test1', 100, 1, 0
UNION ALL
SELECT 1, '20060101', 'Test2', 100, 1, 0

Howver, this gives me an error because several required columns are not
specified. Please either trim irrelevant columns from the CREATE TABLE
statement, or post INSERT statements that include all columns.

(snip)So the first case should return both records from table LedgerBudget,
and the latter case should only return the first record from
LedgerBudget.

Untested (for the reasons stated above), but maybe this works:

SELECT LTRIM(dbo.LEDGERBUDGET.ACCOUNTNUM) AS ACCOUNT_ID,
dbo.LEDGERBUDGET.STARTDATE AS TRANSDATE,
- dbo.LEDGERBUDGET.AMOUNT AS BUDGET
FROM dbo.LEDGERBUDGET
INNER JOIN dbo.ADMIN
ON dbo.LEDGERBUDGET.STARTDATE >= dbo.ADMIN.FROMDATE
AND dbo.LEDGERBUDGET.STARTDATE <= dbo.ADMIN.TODATE
AND (dbo.LEDGERBUDGET.MODELNUM = dbo.ADMIN.BUDGETMODELID
OR dbo.ADMIN.BUDGETMODELID = '')
WHERE dbo.LEDGERBUDGET.ACTIVE = 1
AND dbo.LEDGERBUDGET.ALLOCATEMETHOD = 0

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

jazpar (ja**********@hotmail.com) writes:

Sample data could be (I dont know how to make these in a file)

You don't know how to type INSERT statements?:

INSERT LEDGERBUDGET(ACCOUNTNUM, STARTDATE, MODELNUM, AMOUNT, ACTIVE,
ALLOCATEMETHOD, RECID)
VALUES(1, '20060101','Test1',100,1,0, 1)
INSERT LEDGERBUDGET(ACCOUNTNUM, STARTDATE, MODELNUM, AMOUNT, ACTIVE,
ALLOCATEMETHOD, RECID)
VALUES(1, '20060101', 'Test1', 100,1,0, 2)
go
INSERT GURU_ADMIN(OLAPFROMDATE, OLAPTODATE, BUDGETMODELID, RECID)
VALUES('20060101', '31010206','', 1)
INSERT GURU_ADMIN(OLAPFROMDATE, OLAPTODATE, BUDGETMODELID, RECID)
VALUES('20060101','31010206','Test1', 2)

If you had made the effort to do this, and actually tested the
script, it would have saved me the time from changing all the
column names, adding quotes, and fixing the bad dates.

I also like to remind you that part of the recommendation is that you
post the desired output from the query. This makes it possible to
test and validate the query.

Anyway, after having read your requirements, I think what you need is
to change the query to:

SELECT LTRIM(l.ACCOUNTNUM) AS ACCOUNT_ID,
l.STARTDATE AS TRANSDATE, - l.AMOUNT AS BUDGET
FROM dbo.LEDGERBUDGET l
LEFT JOIN dbo.GURU_ADMIN g ON
l.STARTDATE >= g.OLAPFROMDATE
AND l.STARTDATE <= g.OLAPTODATE
AND l.MODELNUM = coalesce(nullif(g.BUDGETMODELID, ''), l.MODELNUM)
WHERE l.ACTIVE = 1
AND l.ALLOCATEMETHOD = 0

The important line is:

AND l.MODELNUM = coalesce(nullif(g.BUDGETMODELID, ''), l.MODELNUM)

nullif says that space should be interpreted as NULL. (Your default
values appear excessive to me.) coalesce returns the first non-NULL
value of its argument.

The problem with this solution is that it may not perform well. But
without knowing sizes of the tables, I don't feel like considering
alternate solutions.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jazpar]

Hi Hugo

Thank you very much. This did the trick very well. I hadn't thought of
doing it this way. As you might expect I'm not an expert, but more into
ERP systems. But thanks againg.

BR/Jan

[jazpar]

Hi Erland

No actually I didn't know how to make insert statements. But thanks now
I do, and will remember this for the next time. I will try out your
sugestion and see how it works.
Thanks
BR/ Jan