How to make this join select statement works

Here is a select statement of table A:

select user01_id, user02_id from A where ...

I also need to have the names of users which is in the table user. How to join the table user to have the name field and yeild two fields with the same name?

select a.user01_id,u.name, a.user02_id, u.name from a join user u on (?)

table A

user01_id int,
user02_id int,
...
primary (user01_id, user02_id)

table user

userid int primary key,
name varchar(80),
...

Thans for your advise.select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/|||Originally posted by r937
select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/

Hi, Rudy,

The query works well. I didn't know the alias also can be appled on a field in addition of a table. I guess I can use inner join instead when the both user01_id and user02_id of table A refer to the userid in the table user.

Thanks very much for your help.

Vernon|||yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy|||Originally posted by r937
yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy

In fact, the two IDs of table A are foreign keys of the tabe user primary key, userid. So existence of the userid is guaranteed.

A good usage of "defensive". That is the difference between a master and a regular craftsman.

How to make sure in an insert statement that the vendor being inserted does not exist?

I am using INSERT into Vendors (....) VALUES (...) form of insert statement. How can I make sure that the insert fails if thie new vendor's vendorname exists?

I cannot create a unique index on 'VendorName' column since it is more than 900 bytes and SQL Server will not allow to create index on a column bigger than 900 bytes.

There was a readers challenge article in sqlmag that asked that question.
The winning solution was a trigger that easily be adapted for your table.

http://www.sqlmag.com/articles/index.cfm?articleid=93824

I did a quick test of their posted code and it appears to do exactly what you need.

let me know if you have any trouble adapting what they published.

|||

I have got bad vibes from DBA's about triggers and that has made me apprehensive about them.

Is there a non-trigger solution to this problem?

I was thinking of the following logic, but need help to implement it:

1. start a transaction that locks the entire Vendors table for updates like inserts, deletes and update statements so no one else can insert a new Vendor row.
2. then check count of vendors with vendorname = @.vendorname and if count >0 then exit the transaction
else
3. insert the new row for new vendor and commit the transaction in case of no errors.|||

bad vibes is no reason to avoid triggers. they are a very powerful feature of sql server.

if you read through the trigger i referenced, you'll see that it is basically doing what you are suggesting with the exception that it doesnt lock the entire table.

|||

Triggers are usually a maintenance headache. A better way to handle this would be from the stored proc where you are doing the insert.

IF NOT EXISTS (SELECT * FROm yourTable WHERE <condition>)

BEGIN

--do your insert here.

END

checking for records using EXISTS works fastest since SQL Server will only check for existance using indexes and not scan the entire table or return a huge result set. EXISTS just returns TRUE or FALSE.

|||

The downside of not using the trigger is that

a) every stored proc that can insert or update must be modified similarly.

b) there is still no guarantee that the table would contain unique data. date can be entered directly by a dba bypassing the checks in the procs.

also note that the size of the column precludes the use of an index, so a table scan will occur whichever way you choose.

|||

Dinakar,

There is one problem with your suggested solution.

Between the if (select...) and the actual insert statement, there will be a time gap (most likely of the order of milliseconds or less), and if in that time gap, another user inserts a vendor with same name, then the current stored procedure will end up inserting a duplicate vendor name.

Do you have a solution for the above described problem, because then your sp would be perfect?

Thanks

|||

mbanavige:

a) every stored proc that can insert or update must be modified similarly.

I agree. If having a unique value was a requirement it should have been coded that way from the beginning. Generally triggers are a big NO-NO. They may have their advantages but their disadvantages outweight the little benefit they bring.

mbanavige:

b) there is still no guarantee that the table would contain unique data. date can be entered directly by a dba bypassing the checks in the procs.

Generally, in a proper production environment, there should not be direct INSERTs into a table. ALL operations should be done through stored procs. Again its easier in the long run maintenance. If there is a schema change on the table a developer has to dig through his entire application to find where all the INSERTs were and modify.

mbanavige:

also note that the size of the column precludes the use of an index, so a table scan will occur whichever way you choose.

I agree, again. The enforcement can be made from the stored proc. To avoid the table scan, the additional column approach as mentioned in the article can still be used. Instead of querying for the combined columns from the trigger, it can be done in a stored proc.

|||

sun21170:

Between the if (select...) and the actual insert statement, there will be a time gap (most likely of the order of milliseconds or less), and if in that time gap, another user inserts a vendor with same name, then the current stored procedure will end up inserting a duplicate vendor name.

You could use TRANsactions to control the access. If you need total granularity on the locks, check out BOL for the different locking mechanisms and the different ISOLATION LEVELs.

sql

How to make computed field out of conditional

Short Form of Question.
How to do IF statement in computed field. Wanting to concat on to
resulting field based on simple conditional.
made up syntax of what I want:
STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
Long Form:
I want to make a computer field like the following. I'm going to make
up the syntax which of course doesn't work. Wondering if someone
could suggest some syntax that does.
Thanks
Database Columns:
ActiveEmail bit,
users_id Int
id INT
STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
that is, say users_id=10
id=99
ActiveEmail=1
I want for my computer column 10A
users_id=10
if id=99
activeemail=0
I get 1099
THanks,
Peter Kellner
http://peterkellner.net
Peter Kellner
http://peterkellner.netHi Peter
Try something like:
SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
ELSE CAST(id AS VARCHAR(10)) END AS Result
FROM MyTable
John
"Peter Kellner" <pkellnernews@.73rdstreet.com> wrote in message
news:uu084259i3cclbbc61uhhju9c834f7c2mg@.
4ax.com...
> Short Form of Question.
> How to do IF statement in computed field. Wanting to concat on to
> resulting field based on simple conditional.
> made up syntax of what I want:
> STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
> --
> Long Form:
> I want to make a computer field like the following. I'm going to make
> up the syntax which of course doesn't work. Wondering if someone
> could suggest some syntax that does.
> Thanks
> Database Columns:
> ActiveEmail bit,
> users_id Int
> id INT
> STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
> that is, say users_id=10
> id=99
> ActiveEmail=1
> I want for my computer column 10A
> users_id=10
> if id=99
> activeemail=0
> I get 1099
> THanks,
> Peter Kellner
> http://peterkellner.net
> Peter Kellner
> http://peterkellner.net|||On Mon, 17 Apr 2006 22:33:32 +0100, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi Peter
>Try something like:
>SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
>ELSE CAST(id AS VARCHAR(10)) END AS Result
>FROM MyTable
>John
>
Perfect! Thank you John.
Peter Kellner
http://peterkellner.net|||On Mon, 17 Apr 2006 22:33:32 +0100, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi Peter
>Try something like:
>SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
>ELSE CAST(id AS VARCHAR(10)) END AS Result
>FROM MyTable
>John
>
Perfect! Thank you John.
Peter Kellner
http://peterkellner.net

How to make computed field out of conditional

Short Form of Question.
How to do IF statement in computed field. Wanting to concat on to
resulting field based on simple conditional.
made up syntax of what I want:
STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
--
Long Form:
I want to make a computer field like the following. I'm going to make
up the syntax which of course doesn't work. Wondering if someone
could suggest some syntax that does.
Thanks
Database Columns:
ActiveEmail bit,
users_id Int
id INT
STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
that is, say users_id=10
id=99
ActiveEmail=1
I want for my computer column 10A
users_id=10
if id=99
activeemail=0
I get 1099
THanks,
Peter Kellner
http://peterkellner.net
Peter Kellner
http://peterkellner.netHi Peter
Try something like:
SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
ELSE CAST(id AS VARCHAR(10)) END AS Result
FROM MyTable
John
"Peter Kellner" <pkellnernews@.73rdstreet.com> wrote in message
news:uu084259i3cclbbc61uhhju9c834f7c2mg@.4ax.com...
> Short Form of Question.
> How to do IF statement in computed field. Wanting to concat on to
> resulting field based on simple conditional.
> made up syntax of what I want:
> STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
> --
> Long Form:
> I want to make a computer field like the following. I'm going to make
> up the syntax which of course doesn't work. Wondering if someone
> could suggest some syntax that does.
> Thanks
> Database Columns:
> ActiveEmail bit,
> users_id Int
> id INT
> STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
> that is, say users_id=10
> id=99
> ActiveEmail=1
> I want for my computer column 10A
> users_id=10
> if id=99
> activeemail=0
> I get 1099
> THanks,
> Peter Kellner
> http://peterkellner.net
> Peter Kellner
> http://peterkellner.net|||On Mon, 17 Apr 2006 22:33:32 +0100, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:
>Hi Peter
>Try something like:
>SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
>ELSE CAST(id AS VARCHAR(10)) END AS Result
>FROM MyTable
>John
>
Perfect! Thank you John.
Peter Kellner
http://peterkellner.net|||On Mon, 17 Apr 2006 22:33:32 +0100, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:
>Hi Peter
>Try something like:
>SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
>ELSE CAST(id AS VARCHAR(10)) END AS Result
>FROM MyTable
>John
>
Perfect! Thank you John.
Peter Kellner
http://peterkellner.netsql

How to make a correct select

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/ThanksOn 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|||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|||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|||jazpar (jannoergaard@.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, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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|||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

how to lock a row when perform SELECT statement

Hi,
i would like to know how to lock a table from other to view when select statement..can anyone help me with that?
i tried

SELECT * FROM IMIS_FRAMEWORK_RUNNING_NUMBER with (HOLDLOCK,ROWLOCK)
WHERE running_type=@.type;

but i m still able to see my row when i perform my SELECT STATEMENT from other query analyser..

Please help me with this..thank youCheck out this page, it shows you how to do what you're asking:

http://www.sql-server-performance.com/at_sql_locking.asp|||What you are asking to do is a sort of pessimistic lock, and this can be very dangerous in a web application. What happens e.g. when a user SELECTs the record and then their browser crashes?

You can read about concurrency control here:ASP.NET Optimistic Concurrency Control

Maybe if you explain more about what you are trying to do we can offer some suggestions.

Terri|||Thanks for helping ,

First of all i have a table to stores running number

tblRunningNumber
ID Desc LatestNumber
1 ModuleA 01
2 ModuleB 12

Then i wanna gat the latest runningnumber from tblRunningNumber n stores it in to my other table. For example i wanan get the latestNumber for ModuleA and stores it into ModuleA table.

To i need to have a stored procedure to lock the ModuleA rows n then update the latestNumber from 01 to 02 and at the same time update the 01 in my ModuleA database Table.

That y i need to lock the row..my issues is that when i do the select * statement it just hang there...until i close the connection or transactin is commited...

that the whole senarios that i m doing..can u give me any good suggestion on that coz i cannot use asp.net to control the running number so i decided to use storedprocedure .

Thanks alot.|||"can be very dangerous in a web application"

That's an understatement! Especially with a relatively unguarded select statement!|||But to ensure consistency, 'sometimes' you will have to enforce locking for the duration of a transcation. Can you tell us what would happen when a user SELECTs the record and then their browser crashes? The SELECT statement uses a HOLDLOCK. Isn't there a way to handle this crash scenario?|||

Holdlocks works on tables, pages, or rows it won't lock an entire database. If the commit and/or rollback is in the stored procedure, or contained in the batch sent to the SQL Server, then no. The batch and/or stored procedure will run to completion.

If you do something like issue this to the SQL Server: BEGIN TRANSACTION (Or start a transaction using the transaction object), then issue a SELECT ... (WITH HOLDLOCK) then crash before your ASP.NET application rollsback or commits the transaction, and the database server is remote, then yes, it'll be locked until the SQL Server realizes the connection is dead, and I'm not sure how long that would take. If it's a local SQL Server, then it realizes it immediate, and rollsback the transaction.

Also, the client crashing, disconnecting, going off-line, getting hit by lightning, etc means nothing. The server will continue executing it's page to completion no matter if the client is there or not, unless of course the page crashes itself, in which case the connection will be closed, and SQL Server will rollback it's transactions for those connections. Of course if the web server machine takes a complete dump (power loss, blue screen of death, etc), then SQL Server (if remote from the web server), will hold the lock until it realizes the connection is dead, and I'm not sure how quickly SQL Server detects a dead connection, but I'm guessing it'll be longer than you'd want in a web farm type environment.

How to limit the search like this?

there is a column called "date" with the data format like this:
2003-11-12 10:59:22.997
I can limit the search by using the sql statement like this:
select id from logtable where date like '%2003%'
but how to limit the search to be able to display 2003-11-12 only?
thanks very much!!
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!
|||http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!
|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue as MVP Tibor's
link is excellent! We appreciate your patience and look forward to hearing
from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||This link might be helpful also
http://www.aspfaq.com/2312
http://www.aspfaq.com/
(Reverse address to reply.)
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!

How to limit the search like this?

there is a column called "date" with the data format like this:
2003-11-12 10:59:22.997
I can limit the search by using the sql statement like this:
select id from logtable where date like '%2003%'
but how to limit the search to be able to display 2003-11-12 only?
thanks very much!!http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue as MVP Tibor's
link is excellent! We appreciate your patience and look forward to hearing
from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||This link might be helpful also
http://www.aspfaq.com/2312
http://www.aspfaq.com/
(Reverse address to reply.)
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!

How to limit the search like this?

there is a column called "date" with the data format like this:
2003-11-12 10:59:22.997
I can limit the search by using the sql statement like this:
select id from logtable where date like '%2003%'
but how to limit the search to be able to display 2003-11-12 only?
thanks very much!!http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue as MVP Tibor's
link is excellent! We appreciate your patience and look forward to hearing
from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||This link might be helpful also
http://www.aspfaq.com/2312
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!

How to know What Query Statement that consume the Server resourcesmost ?

Hi all,
We have problem with SQL Server 2000 : there something in the
application that cause the server out of memory. I suspect this is
because an inefficient and heavy query.
In Oracle we have TOP SQL from Enterprise Manager to see what query
that consume the server resources most, How can I get that information
in SQL Server 2000 ?
Thank you for your help,
Krist
Hi
It is possible that long running queries and /or queries that performs lots
READs are consumed resources. SQL Server Profiler tool is your friend
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>
|||To really get a look at the internals of SQL executions and resource usage
you need to be on SQL 2005+. Not much info available in 2000.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>

How to know What Query Statement that consume the Server resources

Hi all,
We have problem with SQL Server 2000 : there something in the
application that cause the server out of memory. I suspect this is
because an inefficient and heavy query.
In Oracle we have TOP SQL from Enterprise Manager to see what query
that consume the server resources most, How can I get that information
in SQL Server 2000 ?
Thank you for your help,
KristHi
It is possible that long running queries and /or queries that performs lots
READs are consumed resources. SQL Server Profiler tool is your friend
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>|||To really get a look at the internals of SQL executions and resource usage
you need to be on SQL 2005+. Not much info available in 2000.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>