Problem with query and date ranges

I'm trying to create a query that will tell me which requests
took longer than 10 days to move one from particular state to another
state. The query I've created returns the correct requests,
but not always the correct 'NextActionDate'/'NextStatus'/'NextState'.

I'm sure I'm missing something easy, but I can't figure out what it
might be. Any help is appreciated! Thanks,
Myron
-- remove SPAM-KILL from address to reply by email --

DDL for table creation and data population:

CREATE TABLE [dbo].[ReqHistory] (
[Id] [int] NOT NULL ,
[ReqId] [int] NOT NULL ,
[ReqIDStateId] [tinyint] NOT NULL ,
[ActionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RequestStates] (
[ID] [tinyint] NOT NULL ,
[StateText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Requests] (
[ID] [int] NOT NULL ,
[ShortDescription] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StatusChangeDate] [datetime] NULL ,
[Status] [tinyint] NULL
) ON [PRIMARY]
GO

insert into Requests values(361, 'Test ID: 361', cast('2004-06-03 08:52:03.013' as datetime),98)
insert into Requests values(1400, 'Test ID: 1400', cast('2004-05-13 04:01:55.250' as datetime),97)
insert into Requests values(30051,'Test ID: 30051', cast('2004-09-15 10:10:25.093' as datetime), 96)

insert into ReqHistory values(904,361,1,cast('2004-05-03 00:20:55.983' as datetime))
insert into ReqHistory values(931,361,2,cast('2004-05-03 01:07:14.157' as datetime))
insert into ReqHistory values(959,361,20,cast('2004-05-03 01:29:20.157' as datetime))
insert into ReqHistory values(20250,361,31,cast('2004-06-03 08:51:58.950' as datetime))
insert into ReqHistory values(20251,361,98,cast('2004-06-03 08:52:03.013' as datetime))
insert into ReqHistory values(20249,361,30,cast('2004-06-03 08:51:51.107' as datetime))
insert into ReqHistory values(939,361,10,cast('2004-05-03 01:10:36.093' as datetime))
insert into ReqHistory values(7318,1400,1,cast('2004-05-13 03:48:01.420' as datetime))
insert into ReqHistory values(7346,1400,2,cast('2004-05-13 03:56:37.857' as datetime))
insert into ReqHistory values(7347,1400,12,cast('2004-05-13 03:57:03.293' as datetime))
insert into ReqHistory values(7356,1400,22,cast('2004-05-13 04:00:58.497' as datetime))
insert into ReqHistory values(7357,1400,97,cast('2004-05-13 04:01:55.250' as datetime))
insert into ReqHistory values(53218,30051,1,cast('2004-08-06 10:12:33.050' as datetime))
insert into ReqHistory values(53223,30051,2,cast('2004-08-06 10:15:32.500' as datetime))
insert into ReqHistory values(53246,30051,13,cast('2004-08-06 10:26:34.850' as datetime))
insert into ReqHistory values(53264,30051,23,cast('2004-08-06 10:47:38.993' as datetime))
insert into ReqHistory values(70138,30051,3,cast('2004-09-15 09:21:18.230' as datetime))
insert into ReqHistory values(70257,30051,96,cast('2004-09-15 10:10:25.093' as datetime))

insert into RequestStates values(1,'Awaiting CSMC')
insert into RequestStates values(2,'CSMC Review')
insert into RequestStates values(3,'Reject Awaiting CSMC')
insert into RequestStates values(10,'Awaiting MA Review')
insert into RequestStates values(12,'Awaiting FO Review')
insert into RequestStates values(13,'Awaiting IS Review')
insert into RequestStates values(20,'MA Review')
insert into RequestStates values(22,'FO Review')
insert into RequestStates values(23,'IS Review')
insert into RequestStates values(30,'Func Approval')
insert into RequestStates values(31,'Func Approval Complete')
insert into RequestStates values(96,'Resolved')
insert into RequestStates values(97,'Planning')
insert into RequestStates values(98,'Open')
insert into RequestStates values(99,'Closed')

The query that almost works:

select irh.ReqID, irh.MAactiondate, irh.reviewstate,
irh2.Nextactiondate, irh2.irh2state as NextStatus, irh2.statetext as NextState
from (select distinct irh.ReqID, max(irh.actiondate) as MAactiondate,
irh.ReqIDStateID As IRHState, irs.statetext as ReviewState
from ReqHistory IRH
join requeststates irs on irs.id = irh.ReqIDStateID
where irh.ReqIDStateID in (20, 23)
group by irh.ReqID, irs.statetext, irh.ReqIDStateID) as irh
join (select irh2.actiondate as NextActiondate, irh2.ReqID, irh2.IRH2State, irs.statetext
from (select min(actiondate) as actiondate, ReqID,
min(ReqIDStateID) as IRH2State
from ReqHistory
--the WHERE is wrong, and I believe should be irh2.Nextactiondate > irh.maactiondate,
--but I couldn't make it work
where ReqIDStateID > 23
group by ReqID) as irh2
join RequestStates irs on irs.id = irh2.irh2state ) as irh2 on irh.ReqID = irh2.ReqID
join requests ir on ir.id = irh.ReqID
where irh.MAactiondate + 10 < irh2.Nextactiondate
order by irh.ReqID

The data being returned is:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 96 Resolved

The data that should have been returned:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 3 Reject Awaiting CSMCHoly subqueries batman!

Thanks for including the DDL... it made finding an answer to this much
easier. A couple quick notes though... you didn't include and foreign
or primary keys. That combined with the inconsistent naming standards
made it a little difficult to understand the code.

The following code returned what you were expecting. The code assumes
that no two actions can occur at the same exact time. If that
assumption is incorrect then the code will not work correctly in those
situations.

Also, instead of the NOT EXISTS you could of course opt to use a LEFT
OUTER JOIN along with checking for IS NULL on one of the PK columns for
the table (RH). That often gives better performance than NOT EXISTS in
my experience.

HTH,
-Tom.

SELECT MAH.ReqID, MAH.ActionDate, RS.StateText, NA.ActionDate,
NA.ReqIDStateID, NS.StateText
FROM ReqHistory MAH
INNER JOIN ReqHistory NA ON NA.ReqID = MAH.ReqID
AND NA.ActionDate > DATEADD(dy, 10,
MAH.ActionDate)
INNER JOIN RequestStates RS ON RS.ID = MAH.ReqIDStateID
INNER JOIN RequestStates NS ON NS.ID = NA.ReqIDStateID
WHERE MAH.ReqIDStateID IN (20, 23)
AND NOT EXISTS (SELECT *
FROM ReqHistory RH
WHERE RH.ReqID = MAH.ReqID
AND RH.ActionDate > MAH.ActionDate
AND RH.ActionDate < NA.ActionDate)|||"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote:
>Holy subqueries batman!
>Thanks for including the DDL... it made finding an answer to this much
>easier. A couple quick notes though... you didn't include and foreign
>or primary keys. That combined with the inconsistent naming standards
>made it a little difficult to understand the code.
<some snippage
Thanks for the speedy and accurate answer, Tom! Your query looks a
lot more elegant than my monster, and it found rows that my original
was dropping. :)
Myron|||Your basic design is wrong. Time comes in durations and not points --
look at everyone from Zeno to the ISO standards. Also, your data
element names make no sense -- "ReqIDStateId"? if something is a
state, then it is a value and not an indentifier. What is the vague
"ID": floating around? Surely you do not blindly use sequentail
numbering in an RDBMS to mimic a sequential file physical record
number!

CREATE TABLE RequestHistory
( request_nbr INTEGER NOT NULL ,
request_status INTEGER NOT NULL
REFERENCES RequestStatusCodes(request_status)
ON UPDATE CASCADE,
start_date DATETIME DEFAULT CURRENT TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
PRIMARY KEY (request_nbr, start_date ));

>>I'm trying to create a query that will tell me which requests took
longer than 10 days to move one from particular state to another state.
<<

Trival with the right design, isn't it?

problem with query

Hi

I have the following bit of code

string test0001 = "Select Max(activity_order) from roomactivitylk";

int max;

SqlCommand cmd15 = new SqlCommand();
cmd15.Connection = con;
cmd15.CommandText = test0001;
max = (int)cmd15.ExecuteScalar();


max = max + 1;

what it does is add 1 to the value max which is taken from the database

however it seems to be set to 0 as everytime it brings back 0 even though the next incrment value should be 2

any suggestions?

cheerts

Hi

I am not sure which parts of the code is in a loop. But i guess, the "int max" declaration should be outside of the loop (if it is already not).

Hope this helps.

VJ

|||

My guess is u want to perform autoincrement . If i m right i think u may not get it because when there r no records exist check the return value.u may get null if u get null make it as 1 or increment with the max value. Plz reply to me am i rt or wrong

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Hi

thanks for the replys manmaged to sort it

this is the soltuion

string maxquery = "Select Max(activity_order) from roomactivitylk ";
//cmd14.ExecuteScalar();

int max;

SqlCommand cmd15 = new SqlCommand();
//SqlCommand cmd15 = new SqlCommand(test0001, con);
cmd15.Connection = con;
cmd15.CommandText = maxquery;
max = (int)cmd15.ExecuteNonQuery();


max = max + 2;
++max;
max++;

ps yes i was tring to increment:-)

cheers!!

Problem with Query

Hi,

Here is the part of a stored procedure

declare @.startDate datetime
declare @.endDate datetime
set @.startDate = '12/1/2007 12:00:00 AM'
set @.endDate = '12/20/2007 12:00:00 AM'

--case1:
--The below query executes fine and displays records (count 50)
select * from Employee
where dtsubmittimestamp BETWEEN @.startDate and @.endDate

--case2:
--The below query executes fine and displays records (count 37)
select * from Employee
where iclientaccid = 51

--case3:
--The below query executes but no records are displayed (0 records)
select * from Employee
where iclientaccid = 51
and dtsubmittimestamp BETWEEN @.startDate and @.endDate

--case4:
--The below query executes but no records are displayed (0 records)
select * from Employee
where dtsubmittimestamp BETWEEN @.startDate and @.endDate
and iclientaccid = 51

I am unable to find out why it doesn't display any records in cases 3 and 4

Please help me. Thanks in advance.

Try...

where (iclientaccid = 51)and (dtsubmittimestampBETWEEN @.startDateand @.endDate)

|||

Your iclientaccid = 51 is not in the range of hardcoded statrtdate and endDate

Satalaj.

Problem with Query

I need to replace the text (III|xII) with (1II|xII)'. For that i created the following query.

SELECT (REPLACE((STUFF(Col1,1,1,'1')),'I','1')) FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

The problem with this query is that it replaces all '|' with '1'. ie, it gives (111|x11).

Could anyone ple show me what's wrong with the query.

All help appreciated.

Thanks,

The following code will give you the flexibility to change any char in the string, at any position, at any length. This function returns max 15 chars (but that’s easy to change)…

CREATE FUNCTION dbo.FixString
(
@.DataString varchar(15),
@.StartPosition smallint,
@.StartLength smallint,
@.SeacrhString varchar(15),
@.ReplaceString varchar(15)
)
RETURNS varchar(15)
AS
BEGIN

DECLARE @.Part1 varchar(15),
@.Part2 varchar(15),
@.Part3 varchar(15)

SET @.Part1 = SUBSTRING(@.DataString, 1, @.StartPosition - 1)
SET @.Part2 = REPLACE(SUBSTRING(@.DataString, @.StartPosition, @.StartLength), @.SeacrhString, @.ReplaceString)
SET @.Part3 = SUBSTRING( @.DataString, @.StartPosition + @.StartLength, LEN(@.DataString) - (@.StartPosition + @.StartLength) + 1 )

RETURN @.Part1 + @.Part2 + @.Part3
END
GO

SELECT dbo.FixString(Col1,1,1,'B','1') FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

Happy SQL'n,
Kent Howerter

|||You don't need the REPLACE since it will replace all occurrences of the specified string. STUFF is enough in this case since you are using the length parameter to delete the character at specified position and insert the new one.

Problem with query

Hi All!
i want to update a column based on another column and using following
quey but its giving me error any help
update hospital1 set hospital1.father_name = temp.father_name
join temp on temp.id = temp.id
and hospital1.father_name <> 'Not Available'
thanx!
Farid
*** Sent via Developersdex http://www.examnotes.net ***update h
set h.father_name = t.father_name
from hospital1 h
join temp t on t.id = h.id
and h.father_name <> 'Not Available'
I assumed you can join temp and hospital1 on id
http://sqlservercode.blogspot.com/|||Ghulam Farid wrote:
> Hi All!
> i want to update a column based on another column and using following
> quey but its giving me error any help
> update hospital1 set hospital1.father_name = temp.father_name
> join temp on temp.id = temp.id
> and hospital1.father_name <> 'Not Available'
> thanx!
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
I'm guessing you want something like this:
UPDATE hospital1
SET father_name =
(SELECT father_name
FROM temp
WHERE id = hospital1.id
AND father_name <> 'Not Available') ;
WHERE ... ?
Note that this statement assumes id is unique in Temp for each hospital
row to be updated. If that assumption is incorrect then please explain
how you want to handle the duplicates (i.e. more than one father_name).
David Portas
SQL Server MVP
--|||Your proprietary answer makes no sense in terms of the SQL language
model. A FROM clause is always suppose effectively materialize a
working table that disappears at the end of the statement. Likewise,
an alias is supposed to act as it materializes a new working table with
the data from the original table expression in it. To be consistent,
this syntax says that you have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).

Problem with query

I have the following sql:
SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastNm,
e.Processcatalog,
b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm,
k.caseid, k.comments
FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
INNER JOIN Processcatalog AS E ON A.process = E.processcatalogid
INNER JOIN Atts AS J ON A.id = J.caseid
INNER JOIN Users AS I ON J.userid = I.Userid
LEFT JOIN Users AS F ON A.empid = F.userid
LEFT JOIN CaseComments AS K ON a.id = k.Caseid
WHERE A.firmid = 2
ORDER BY i.userid
As soon as I added k.comments
I get this error:
Server: Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.
Any ideas ?
AleksTry making the query without the Text field a subquery, aliased as, say Z,
and join it (Z) to the CaseComments table
Select Z.*, K.CaseID, K.comments
From (SELECT distinct a.id,a.caseid, b.userid AlienUserId,
b.FirstNm, b.LastNm, e.Processcatalog,
b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName,
a.approvaldate, i.userid,i.MailStr, i.MaidenNm
From Cases A
Join Users B On A.alienid = B.userid
Join Processcatalog E On A.process = E.processcatalogid
Join Atts J On A.id = J.caseid
Join Users I On J.userid = I.Userid
Left Join Users F On A.empid = F.userid) As Z
Join CaseComments K On k.Caseid = Z.id
"Aleks" wrote:

> I have the following sql:
> SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastN
m,
> e.Processcatalog,
> b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm
,
> k.caseid, k.comments
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
> INNER JOIN Processcatalog AS E ON A.process = E.processcatalogid
> INNER JOIN Atts AS J ON A.id = J.caseid
> INNER JOIN Users AS I ON J.userid = I.Userid
> LEFT JOIN Users AS F ON A.empid = F.userid
> LEFT JOIN CaseComments AS K ON a.id = k.Caseid
> WHERE A.firmid = 2
> ORDER BY i.userid
> As soon as I added k.comments
> I get this error:
> Server: Msg 8163, Level 16, State 3, Line 1
> The text, ntext, or image data type cannot be selected as DISTINCT.
>
> Any ideas ?
> Aleks
>
>|||uh uh, forgot your Where Clause, and Order By...
Select Z.*, K.CaseID, K.comments
From (SELECT distinct a.id,a.caseid, b.userid AlienUserId,
b.FirstNm, b.LastNm, e.Processcatalog,
b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName,
a.approvaldate, i.userid,i.MailStr, i.MaidenNm
From Cases A
Join Users B On A.alienid = B.userid
Join Processcatalog E On A.process = E.processcatalogid
Join Atts J On A.id = J.caseid
Join Users I On J.userid = I.Userid
Left Join Users F On A.empid = F.userid
Where A.firmid = 2) As Z
Join CaseComments K On K.Caseid = Z.id
Order By Z.ID
"Aleks" wrote:

> I have the following sql:
> SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastN
m,
> e.Processcatalog,
> b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm
,
> k.caseid, k.comments
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
> INNER JOIN Processcatalog AS E ON A.process = E.processcatalogid
> INNER JOIN Atts AS J ON A.id = J.caseid
> INNER JOIN Users AS I ON J.userid = I.Userid
> LEFT JOIN Users AS F ON A.empid = F.userid
> LEFT JOIN CaseComments AS K ON a.id = k.Caseid
> WHERE A.firmid = 2
> ORDER BY i.userid
> As soon as I added k.comments
> I get this error:
> Server: Msg 8163, Level 16, State 3, Line 1
> The text, ntext, or image data type cannot be selected as DISTINCT.
>
> Any ideas ?
> Aleks
>
>

Problem with query

Hi,
I have a query that is supposed to return records and make a left join where
one field is not null, but for some reason is not working properly and
returns the records even though they are null.
SELECT *
FROM cases a
left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
MAX(y.Dateinitiated)
From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
Any help is greately appreciated.
AleksAleks,
The reason the query seems to be returnning records from Activities,
where the Dateinitiated column is null, is because you have specified an
Outer Join.
When you specify An Outer Join, Al records from the Outer table are
returned, even when there is no match on the other side. You actually are
NOT returning any data from Activities Table where Dateinitiated column is
null. If you look at those rows, you'll probably notice that all the field
s
from Activities table are null there...
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>

problem with query

hello and good day!

...this works fine

select a.invce_no, a.invce_slsamt_yen, a.invce_slsamt_php, a.invce_slsamt_dlr, a.crnc_code, a.date_prn,
a.fob_charge,b.fx_amt,b.invce_amt,b.crnc_code,b.ba l_amt,b.amt_php,b.rate_php from delivery_header a left join delivery_trans_detail b on
a.invce_no = b.invce_no where cust_code='200105000011

here is the problem...

i need additional columns,
trans_date and ref_date on another table which is delivery_trans_header
where delivery_trans_header.trans_no = delivery_trans_detail.trans_no

could anyone pls tell me what to do? how could i connect the third table? do i have to use another join? if so, how?Use that, new rows are upper:

select
a.invce_no,
a.invce_slsamt_yen,
a.invce_slsamt_php,
a.invce_slsamt_dlr,
a.crnc_code, a.date_prn,
a.fob_charge,
b.fx_amt,
b.invce_amt,
b.crnc_code,
b.bal_amt,
b.amt_php,
b.rate_php,
C.TRANS_DATE,
C.REF_DATE
from
delivery_header a
left join
delivery_trans_detail b
on a.invce_no = b.invce_no
INNER JOIN -- is join type correct, check
DELIVERY_TRANS_HEADER C
ON C.TRANS_NO = B.TRANS_NO
where
cust_code='200105000011'|||thanks for the quick reply!

it did display the additional columns but there's still one problem, it didnt display the colums with no matches, ive tried inner, left and right but to no avail, all of it has the same output...

wish you could still help me with this|||try with
"full outer join"|||i tried it, but it still has the same output, it still didnt display the columns with no matches...|||Originally posted by BuTcHoK
i tried it, but it still has the same output, it still didnt display the columns with no matches...

TRY DOING A LEFT OUTER JOIN ON A SUBQUERY, SOMETHING LIKE...

select
a.invce_no,
a.invce_slsamt_yen,
a.invce_slsamt_php,
a.invce_slsamt_dlr,
a.crnc_code, a.date_prn,
a.fob_charge,
b_d.fx_amt,
b_d.invce_amt,
b_d.crnc_code,
b_d.bal_amt,
b_d.amt_php,
b_d.rate_php,
b_d.TRANS_DATE,
b_d.REF_DATE
from
delivery_header a
left outer join

(select
b.invce_no,
b.fx_amt,
b.invce_amt,
b.crnc_code,
b.bal_amt,
b.amt_php,
b.rate_php,
c.TRANS_DATE,
c.REF_DATE
FROM delivery_trans_detail b
INNER JOIN DELIVERY_TRANS_HEADER C
ON C.TRANS_NO = B.TRANS_NO) B_D

on b_d.invce_no = a.invce_no
where
a.cust_code='200105000011'|||hi,

it tried it, but it says token unknown "select" ...|||Give an example what exactly want to be matched, pls ..
May be it is miss understanding ...|||Originally posted by BuTcHoK
hi,

it tried it, but it says token unknown "select" ...

Try to remove the blank lines between
a) left outer join AND (select
and
b) TRANS_NO) B_D AND on b_d.invce_no = a.invce_no|||please see attached file for example... thanks so much|||I make test - everything is OK. Use it.

declare @.a table(pk int, pk1 int)
declare @.b table(pk1 int, pk2 int)
declare @.c table(pk2 int, qq int)

insert into @.a values(1,10)
insert into @.a values(2,20)
insert into @.a values(3,30)
insert into @.a values(4,40)

insert into @.b values(10,100)
insert into @.b values(20,200)
insert into @.b values(50,500)

insert into @.c values(100,1000)
insert into @.c values(300,3000)
insert into @.c values(500,5000)

select * from @.a
select * from @.b

select * from @.a a left outer join @.b b on a.pk1 = b.pk1
left outer join @.c c on b.pk2 = c.pk2

Problem with Query

Hi people, I need your help:

I made a query which should return the values:

Customer's Name (table CUSTOMERS)
Customer's ID (table CUSTOMERS)
Customer's City (table CITY)
Date of Visits to Customer (table VISITS)
Visit's Status (table STATUSVIS) = returns value '0' - if the customer was visited or returns '1' if the customer wasn't at his office.

My problem is: to get the value of the field City I have 2 tables for Customers Addresses, they are CUSTADD1 and CUSTADD2, and my customer can only have one address in one of those tables.

The query I made isn't working and I have no clue how to fix it up.

--------------------------SELECT distinct(VISITS.visdate), CUSTOMERS.customername, CUSTOMERS.customerID, CITY.cityname

FROM
CUSTOMERS, VISITS, STATUSVIS, CUSTADD1, CUSTADD2, CITY
WHERE
CUSTOMERS.customerID in (CUSTADD1.customerID, CUSTADD2.customerID) and
CUSTADD1.cityname = CITY.cityname or
CUSTADD2.cityname = CITY.cityname and
CUSTOMERS.customerID = VISITS.customerID and
STATUSVIS.status = 0
--------------------------

Does someone have an idea about what isn't working in this query?

Thanks in advance,
GiseleYour query is failing because you are mixing AND and OR comparisons in your WHERE clause without defining their order of precedence with parentheses, and becuase you have an unjoined table (STATUSVIS) in your statement.

The query would be a lot clearer if you would establish table relationships with JOIN statements rather than in your WHERE clause. Joining tables in the WHERE clause is a bad habit. Very bad.

Here is your query rewritten using JOINs. Notice how the CITY table isn't necessary. You aren't pulling and data from it that you can't get from the CUSTADD tables. Also, the STATUSVIS table has been dropped because it was not joined in the original query anyway. If you want to add its criteria to the query, join it to one of the existing tables (CUSTOMER?).

The isnull function returns the cityname from CUSTADD1 if it exists, otherwise it returns the value from CUSTADD2. Modify it if you want different selection logic.

Also, I'm not sure if the DISTINCT clause is necessary. I doubt it, but it depends on your data.

Please read up on the SELECT statement in Books Online and familiarize yourself with good coding practices.

SELECT distinct(VISITS.visdate),
CUSTOMERS.customername,
CUSTOMERS.customerID,
isnull(CUSTADD1.cityname, CUSTADD2.cityname) cityname
FROM CUSTOMERS
inner join VISITS on CUSTOMERS.customerID = VISITS.customerID
left outer join CUSTADD1 on CUSTOMERS.customerID = CUSTADD1.customerID
left outer join CUSTADD2 on CUSTOMERS.customerID = CUSTADD2.customerID|||blindman, you gotta start using COALESCE instead of ISNULL, it will go further

gisele, your problems with ANDs and ORs is quite common, i have seen many people write them linearly and expect them to be evaluated linearly

however, they are evaluated as though there were parentheses around any terms connect with AND

for example

... WHERE x=1 AND y=4 OR z=7

will be evaluated as

... WHERE (x=1 AND y=4) OR z=7

ANDs always take precedence over ORs

this type of expression is called a boolean expression, named after Jim Boolean, inventor of the word AND

:cool:|||Now it worked, thanks a lot guys!|||COALESCE vs ISNULL?

What's the diff for two values?

..and the word OR was first coined by George Orwell, who also, by the way, invented the OReo cookie.|||there is no difference in sql server

my advice was meant for sql in general

COALESCE is standard

ISNULL isn't (i think)

besides, then you will never get it mixed up with the NULLIF function

:cool:|||Yes, ISNULL it not ANSI standard, while COALESCE is, so I suppose COALESCE is preferable.

Problem with query

I have two tables. One for invoice detail and one for inventory per warehouse. Let's say the one for invoice detail has the following data:

PRODUCT | AMT_SOLD
shirt | 3
pant | 1
shirt | 4
pant | 5

and the inventory per warehouse has the following data:

PRODUCT | WAREHOUSE | QUANTITY
shirt | A1 | 50
shirt | A3 | 30
shirt | A4 | 10
pant | A2 | 25
pant | A3 | 10

I'm trying to make a query that will show me the name of the product, the sum of the quantity sold (from the invoice table) and the sum of the quantity in all of the warehouses, like this:

PRODUCT | SUM1 | SUM2
shirt | 7 | 90
pant | 6 | 35

If I do:
SELECT a.product, SUM(a.amt_sold) as SUM1, SUM(b.quantity) as SUM2
FROM invoice a, inventory b
WHERE a.product = b.product
GROUP BY a.product

I get:

PRODUCT | SUM1 | SUM2
shirt | 21 | 180
pant | 12 | 70

which is obviously not right

any ideas?this..

with a subquery..

select T.product,T.sum1, sum(quantity) as sum2 from
(select product, sum(amt_sold) as sum1 from invoice group by product) as T
inner join inventory as B
on T.product = B.product
group by T.product,T.sum1

try this =)
Ale.

Problem with query

Hello everyone.
Does anybody have an idea how to optimize following?

I have a table - containing primary key (not identity) wich - on a new entry- should always be the lowest possible number (e.g. 1-234 and 236-400 are occupied - it should select 235, not 401).

Currently I'm doing it trough a dlookup loop searching for a free number (lowest possible)...

Is there any sql query (select top 1 or something like that) to speed this up?

Thanks for any replies!
Greetz - Marcselect min(key) from yourtable

ask yourself why you want to fill in the gaps

a primary key should have no meaning

a surrogate key (an assigned number, for instance) should not even be visible to users of the application

rudy
http://rudy.ca/|||Dear Rudy,
Select min(key) would give me back the lowest existing number - i need the lowest free number.
Primary Key was a bit wrong told - it has a identity besides this number, but i need this number to be unique, and given each time as low as possible (not taken)...

I'm using the system to make reservations in the local database - e.g. article ... gets reserved for a customer under reservation number 5, 1-4 and 6-10 are taken...|||doh!! (smacks self on head)

sorry, i answered too quickly, of course min(key) doesn't get the lowest available number

i could give you some sql, but it involves a left outer join with a temp table containing every integer smaller than max(key) -- it would be ugly and slow

as i said, you should re-think why you want the number to "fill in the gaps"

rudy|||Dear Rudy,
I need it to be the lowest possible number - else it would fill up my whole office :).
I've thought about this reservation system because you always have a low number (max) and so you can sort in the articles by reservation number - wich wouldn't exceed 1000 (except if really more than one thousand would be reserved)...
You can imagine it like this:
I have a wall full of articles with numbers, reserved for customers...
If I would have a ongoing number, the wall would have to get bigger and bigger (if I sort them in ascending by number)...

Do you think it would be faster if I do a SELECT * on a recordset object and loop trough than dlookup?|||consider this --create table reservations
(id integer primary key
, title varchar(50) not null
);
insert into reservations (id, title)
values (1, 'the first one');
insert into reservations (id, title)
values (937, 'the second one');
insert into reservations (id, title)
values (2, 'the third one');how big is your table? three rows

trust me, you do not have to re-use numbers to prevent your table from growing

the database does not reserve space for missing entries

;)|||Dear Robert,
It doesn't consider me if the table grows - the wall with the reservations would have to grow if i reserve by number...

e.g. pos 1 is number 1 - until pos 600 reserved... wall is full sorted by reservation number... now if I don't re-use the numbers wich go out (e.g. 50 gets sent)... my numbers will go to 1200 sometime... and i cannot store that reservation number in my office :)...|||let me explain otherwise...
i have a wall - with places for articles (reservations) - this wall is numbered from 1 to 1000 - so i need the reservations go from 1 to max 1000 - i need to reuse those numbers, elsewhere i would have to make the wall bigger ...
so what I do currently, is loop trough the table with a counter looking for a free position...|||create a new table for your reservation numbers, 1 through 600 or whatever (you can even use auto_number for this)

create table reservationnumbers
( resno integer not null
, reservationid integer null
)

reservationid will point to a reservation your existing table of reservations, the ones that can get deleted -- go ahead and assign them with an autonumber

when you want to assign a new reservation, use

select min(resno) from reservationnumbers
where reservationid is null

when you delete a reservation, make sure you set the corresponding reservationid in the the reservationnumbers table to null

rudy|||You could create a simple table called 'ALLVALUES' with only a numeric field (MYVALUE) containing all possible values (Ex. 1 - 5000).
Obviously this field must be the primary key.

then you can create a query extracting the lowest value in your table not matching with values in ALLVALUES.

Select min(ALLVALUES.MYVALUE) from ALLVALUES
LEFT OUTER JOIN YOURTABLE on (
ALLVALUES.MYVALUE = YOURTABLE.Key )
WHERE YOURTABLE.Key IS NULL

I think this will work

Bye
movendra@.yahoo.com|||That solution didn't get in my mind at all - thanks guys! I'll try it this way...

thanks for the help!|||Let us say your table is called XXX and the column that you are seek to find the lowest unused value is COL_ID

select min(x1.COL_ID + 1)
from XXX x1 (nolock)
where not exists
(
select *
from XXX x2 (nolock)
where x2.COL_ID = (x1.COL_ID + 1)
)|||That solution didn't get in my mind at all - thanks guys! I'll try it this way...

thanks for the help!|||May this will be more understandable for you...

create table test(id int, name varchar(10))

insert test values(1,'1')
insert test values(3,'3')
insert test values(4,'4')
insert test values(5,'5')

select min(id+1) newid from test
where (id+1) not in (select id from test)

newid
----
2|||hey guys (snail and achorozy), that's pretty slick

unfortunately it doesn't find the gap at the front of the table, but i suppose that's a minor quibble, eh

:cool:

problem with query

Hello ,

Actor

ANO	ANAME
A1	L Tree
A2	D Pond
A3	M Lake
A4	J Worthy
A5	T Gross
A6	L Leaf

Movie

MNO	MNAME
M1	Work a lot
M2	Sleep too little
M3	Too much fun
M4	Laugh and Cry

PLAY

ANO	MNO	Pay
A1	M1	$77.00
A1	M2	$80.00
A2	M2	$93.00
A2	M3	$88.00
A2	M4	$91.00
A3	M2	$74.00
A3	M3	$89.00
A4	M2	$75.00
A4	M3	$85.00
A4	M4	$55.00
A5	M1	$50.00
A5	M4	$61.00
A6	M2	$77.00
A6	M3	$88.00
A6	M4	$54.00

Here are my table,

I am trying to write a few queries with them,

1. The names of the actors who did not play in any movie that 'Jworthy" Played.

2. Find the names of movies that both 'J worthy' and "T Gross " played it

3 Find the names of actors who played in all movies that 'J worthy ' played

4. Find tyhe names of actors who got better pays than 'j worthy' in all movies that 'j worthy' played in

5. For each movie, list the movie number, the average pay of all actors and the total number of actors in the movie

6. extend question 5 by also including the number of actors whose pay is below average pay for the movie

7. for each actor , list the actor name and his/her average pay of all time recorded in the database

8. Find the names of the actors who have the highest average pay. This cannot be done using "order By"

IF any one could please gelp me with all or few of these queries would be greatly appreaciated.

Thank You,

gsk_phili

Are we supposed to do your homework?

--
SvenC

|||

Is it not what people do here. Bring their work problems and school problems.?

I figured out 5 of them, i only need help with query 4 , 6 and 8.

|||

Well, pasting the questions one to one and not even trying to solve one itself is not what makes many people here try to help. The homework actually should help you understand the topics. Try some solutions and ask questions what is wrong with your approach if it does not work.

But as you tried, here is a solution for 4:

SELECT Play.ANO , SUM(Play.Pay) As PaySum FROM play where Play.MNO in

( SELECT Play.MNO from Actor inner join Play ON Actor.ANO = Play.ANO where Actor.ANO = 'A4' )

GROUP BY Play.Ano

HAVING SUM(Play.Pay) > (SELECT SUM(Play.Pay) As PaySum FROM play where Play.MNO in

( SELECT Play.MNO from Actor inner join Play ON Actor.ANO = Play.ANO where Actor.ANO = 'A4' )

AND Play.ANO = 'A4')

How far have you got with 6 and 8

--
SvenC

|||

6 is a bit tricky. I give you a start by showing you the innermost select. You will need to put two more selects around it - at least my solution needs that, don't know if an easier one exists.

Here is my innermost select:

SELECT MNO as mno1, AVG(Pay) as AvgPay, count(ANO) As ActNum from play group by MNO

--
SvenC

|||

Hello svenc,

Thanks for the response. As to reply of '6'. This is the exact query that works for question 5.

i have it written as

SELECT MNO, Avg(PLAY.Pay) AS AvgOfPay, Count(PLAY.ANO) AS CountOfANO
FROM PLAY
GROUP BY MNO;

And as for query 4, the result of the query is A2 and A6 but A6 has lesser pay than j worthy in M4.

Thanks

gsk_phili

|||

HELLO svenc,

I got the 4th one.

The resulting table should only list D.pond

NOw i am struck at 6 and 8

Problem with query

I have the table persons with fields
lastname - varchar
id - numeric
i want to update last name to null if id and lastname are equal. i
tried the following query, but it not works
update persons set lastname=null where lastname like id;
I really appreciate you if you could help me.
Thanks in AdvanceTry this
update persons set lastname=null
where lastname = convert(varchar(100),id)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||No it doesn't work!
Please give someother solution|||please post DDL, sample data, and an explanation of what you mean by "it
doesn't work".
Why doesn't it work? Are you getting an error message? Are any rows
updated?
See this link. It explains what we need.
http://www.aspfaq.com/etiquette.asp?id=5006
"meendar" <askjavaprogrammers@.gmail.com> wrote in message
news:1148067610.329895.147730@.u72g2000cwu.googlegroups.com...
> No it doesn't work!
> Please give someother solution
>|||DDL
CREATE TABLE [PERSONS] (
[HONORIFIC] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIRSTNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MIDDLE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NICKNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPOUSE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS_1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ADDRESS_2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CITY] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STAFF_REP] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SOURCE] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATE_DATE] [datetime] NULL ,
[UPDATE_DATE] [datetime] NULL ,
[NEXT_DATE] [datetime] NULL ,
[REQ_SALARY] [float] NOT NULL ,
[RELOCATE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TRAVEL] [float] NULL ,
[PURGEDATE] [datetime] NULL ,
[SSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GNRL_COMMENTS] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PROF_SUMMARY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIRSTAVAILABLE] [datetime] NULL ,
[LASTAVAILABLE] [datetime] NULL ,
[CITIZENSHIP] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[LOCPREF] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FEETO] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FEETERMS] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TITLE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMADDRESS_1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COMADDRESS_2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COMCITY] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMSTATE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMZIP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMCOUNTRY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SIC] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ASSISTNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[REPNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[START_SAL] [float] NULL ,
[END_SAL] [float] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[RESP_DESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEXCODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RACECODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MARITALSTATUS] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EEOCODEID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EEOGROUPID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[EEOCODE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EEOGROUP] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EEOCAT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMAILADDR] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STDBILLRATE] [float] NULL ,
[STDPAYRATE] [float] NULL ,
[CURBILLRATE] [float] NULL ,
[CURPAYRATE] [float] NULL ,
[TYPE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLASS] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STAFF_NEXT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CATEGORY] [int] NOT NULL ,
[OVERTIMEAFTER] [decimal](11, 4) NULL ,
[OVERTIMERATE] [decimal](11, 4) NULL ,
[CURGROSSMARGIN] [decimal](11, 4) NULL ,
[STDGROSSMARGIN] [decimal](11, 4) NULL ,
[OVERTIMEEXEMPT] [int] NULL ,
[DEDUCTIONS] [int] NULL ,
[PAYTYPE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SALUTATION] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[RESUMEUPDATED] [datetime] NULL ,
[SICCODEDESC] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ID] [decimal](12, 3) NOT NULL ,
[COMID] [decimal](12, 3) NULL ,
[REPID] [decimal](12, 3) NULL ,
[CONTACTOK] [int] NULL ,
[VERIFIED] [int] NULL ,
[HOMEPRIMARY] [int] NULL ,
[XDEDUCTIONS] [float] NULL ,
[ST_DEDUCTIONS] [int] NULL ,
[ST_XDEDUCTIONS] [float] NULL ,
[SUI] [int] NULL ,
[FEDTAX] [int] NULL ,
[FUTA] [int] NULL ,
[MCARE] [int] NULL ,
[SSEC] [int] NULL ,
[PAYPERIOD] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEDBY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATEDBY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSROWID] [timestamp] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I don't think sample data will be useful. I am not getting any error
messages but the field is not updated.

problem with query

Hello group,
I have the following issue:
select convert(varchar, GETDATE(), 105)
--
30-10-2007
(1 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda
--
28-10-2007
30-10-2007
31-10-2007
01-11-2007
(4 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda where
convert(varchar,ini_fecha_actividad,105) between convert(varchar,
GETDATE(), 105) AND convert(varchar, dateadd(day,1,GETDATE()), 105)
--
30-10-2007
31-10-2007
(2 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda where
convert(varchar,ini_fecha_actividad,105) between convert(varchar,
GETDATE(), 105) and convert(varchar, dateadd(day,5,GETDATE()), 105)
--
(0 row(s) affected)
What is the problem with my last query ? It should return:
30-10-2007
31-10-2007
01-11-2007
Any help will be REALLY appreciated.
greetings,
hansHi Hans,
The problem is that in your WHERE clause you are comparing the dates as
character strings. As such the end period in your BETWEEN becomes
'05-11-2007'. That sorts alphabetically before any of the other dates and
your range does not return any results.
One of your queries returns the desired results but that is just by
coincidence (because in that case the character representation of the end
date is higher in order).
The correct way to return a range of dates is to compare dates in their
native format, not to convert them. To trim the time portion you can use
different techniques, one is via using the DATEADD and DATEDIFF functions.
Here is a query that will return the desired results:
SELECT CONVERT(VARCHAR, ini_fecha_actividad, 105)
FROM Agenda
WHERE ini_fecha_actividad >= DATEADD(day, DATEDIFF(day, '20010101',
CURRENT_TIMESTAMP), '20010101')
AND ini_fecha_actividad < DATEADD(day, DATEDIFF(day, '20010101',
CURRENT_TIMESTAMP) + 6, '20010101');
Note that I changed the math and added 6 days to the upper boundary to
include the results dates for the next 5 days.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hans, you should try this approach which compares dates with datetypes.
declare @.d1 smalldatetime
set @.d1 = '11-01-2007' -- Nov 1st
select convert(varchar, @.d1, 105)
where @.d1 between GETDATE() and dateadd(day,5,GETDATE())
answer: 01-11-2007
I removed the references to a table but you get the format - works the same.
--
James Chacata
jameschac@.hotmail.com
"Hans" wrote:
> Hello group,
> I have the following issue:
> select convert(varchar, GETDATE(), 105)
> --
> 30-10-2007
> (1 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda
> --
> 28-10-2007
> 30-10-2007
> 31-10-2007
> 01-11-2007
> (4 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda where
> convert(varchar,ini_fecha_actividad,105) between convert(varchar,
> GETDATE(), 105) AND convert(varchar, dateadd(day,1,GETDATE()), 105)
> --
> 30-10-2007
> 31-10-2007
> (2 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda where
> convert(varchar,ini_fecha_actividad,105) between convert(varchar,
> GETDATE(), 105) and convert(varchar, dateadd(day,5,GETDATE()), 105)
> --
> (0 row(s) affected)
>
> What is the problem with my last query ? It should return:
> 30-10-2007
> 31-10-2007
> 01-11-2007
>
> Any help will be REALLY appreciated.
> greetings,
> hans
>

Problem with querry performance locally vs. network

Hi,

I work on speed-up our company SQL Server 2000. I reconstructed one querry and test it on client computer (other than server). There was no profit. I was surprised :(. I supposed several times speed-up. Next I run old and new querry directly on our SQL Server machine. The result was proper - new (after modifications) querry takes 2,5 times less time than previous.

Ok, question: why there wasn't profit of modification from client computer?

I thougth that reason is slow network. So I copy from client computer file of weight 80MB to server. This takes 10seconds. Result of my querry is 35MB and it takes 17 seconds on client computer and 6 seconds directly from server.

Best regards,
Walter Luszczyk

If the result of your query is 35MB in size then it's not particularly surprising that it takes 6 seconds for the client computer to get the results. Likely the reason it's taking longer when you copy the file to the client is that the client computer does not perform as well as the server. After your tuning, the server processed the query faster but it still had to transfer 35MB of data over the LAN.

Consider your 80MB data file. How long did it take for you do copy it from the server to the client?

With this in mind, you should consider whether you need 35MB worth of results. This is 43% of your database.

Can you work with less columns?|||

Unfortunately I cannot select smaller result - it's report ... :(

I try bit this problem from another side and my observations are:

I use SQL Profiler to solve the problem. Sending old querry yields performance: CPU:8641, Reads: 10688, Writes: 0, Duration: 12513. But new querry yields: CPU: 627, Reads: 10627, Writes: 0, Duration: 11956.

So why duration doesn't change (12513 : 11956) while CPU changes a lot (8641 : 627)?

This time result's size is only 7MB - it's really small in out network.

Regards,

Walter

|||

Duration in Profiler is not necessarily a measure of time to execute the query. It actually a measure of the time it takes to execute the query, get the result to the client, and the client to close the recordset.

For example, if you're transferring megabytes of data and your client is on a gigabit ethernet connection, your duration will be lower than if your client is on a 10 megabit ethernet connection. If you can set up a VPN connection between your client and server (connect from home) then you would see an even longer duration.

I've actually seen Microsoft Access tables opened on SQL Server that showed durations of 20-30 minutes but showed very low reads and CPU.

In your case, you need to either reduce the size of the data being returned or get a higher bandwidth connection to your server.

Some ideas:

Reduce the size of the data types. For example: Are you using nvarchar where you could use VarChar? Can you convert the output of your DateTime columns to SmallDateType?

Problem with push subscription where subscriber identified by IP not name

I have set up six push subscriptions of the same publication on a wide area
network successfully, but am having a problem with the seventh one. I think
the problem is due to the fact that the subscription was set up using the
subscribing server's IP address rather than network name (there is currently
a problem on the network so the server's name can't be resolved)
The error seen in the merge agent for the subscription is:
The subscription to publication 'PersonNameAddrUpdate' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
------
The remote server is not defined as a subscription server.
(Source: xxx.xxx.xxx.xx (Data source); Error number: 14010)
------
(xxx.xxx.xxx.xx is the subscribing server's IP address)
Is there anything that can be done while we have this problem with the
network?
Try using the client network utility to map the subscriber's IP address to
its netbios name and then register that in Enterprise Manager and as a
subscriber.
HTH,
Paul Ibison
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%237O3GIeTFHA.2812@.TK2MSFTNGP09.phx.gbl...
>I have set up six push subscriptions of the same publication on a wide area
>network successfully, but am having a problem with the seventh one. I think
>the problem is due to the fact that the subscription was set up using the
>subscribing server's IP address rather than network name (there is
>currently a problem on the network so the server's name can't be resolved)
> The error seen in the merge agent for the subscription is:
> The subscription to publication 'PersonNameAddrUpdate' is invalid.
> (Source: Merge Replication Provider (Agent); Error number: -2147201019)
> ------
> The remote server is not defined as a subscription server.
> (Source: xxx.xxx.xxx.xx (Data source); Error number: 14010)
> ------
> (xxx.xxx.xxx.xx is the subscribing server's IP address)
> Is there anything that can be done while we have this problem with the
> network?
>
|||I figured it out with help from this page:
http://www.adminlife.com/247referenc...36/184432.aspx
The answer was:
The error can also occur if the subscriber is registered using a TCP/IP
address instead of a Name.(see 321822). To work around, use the client
utilities to create an ALIAS name to the subscribers TCP/IP address and then
use that ALIAS name when registering the subscriber instead of the TCP/IP
address.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%237O3GIeTFHA.2812@.TK2MSFTNGP09.phx.gbl...
>I have set up six push subscriptions of the same publication on a wide area
>network successfully, but am having a problem with the seventh one. I think
>the problem is due to the fact that the subscription was set up using the
>subscribing server's IP address rather than network name (there is
>currently a problem on the network so the server's name can't be resolved)
> The error seen in the merge agent for the subscription is:
> The subscription to publication 'PersonNameAddrUpdate' is invalid.
> (Source: Merge Replication Provider (Agent); Error number: -2147201019)
> ------
> The remote server is not defined as a subscription server.
> (Source: xxx.xxx.xxx.xx (Data source); Error number: 14010)
> ------
> (xxx.xxx.xxx.xx is the subscribing server's IP address)
> Is there anything that can be done while we have this problem with the
> network?
>

Problem with Publication Articles being deleted

I wonder if anyone can advise, I currently have a replication set to 5 offices. Which has been working fine for some 18 months+, then the replication dropped out. When I looked in the publisher it had lost all its articles and would not allow me to add back in. I deleted the publication and recreated it and then after creating a new snapshot starting rolling back out to the various locations, and one of the locations failed and again all the articles where lost. I deleted and rebuilt the database at that office and went through the same routine again, only for it to happen again, the issue being that now errors where produced!!!.

So at present I have the publication and all the subscriptions running apart from this one office.

Has anyone had this before, or any ideas. I have run a full virus scan just in case and it was fine.

Can you provide more information?

Are you using push transactional replication?

Where does the replication fail? What error messages are you getting?

When you say one of the location failed, does it mean other locations worked fine?

Gary

|||

Hi Gary,

I found the problem, they were two dns records for the server I was trying to replicate to, and one of the records pointed back to the publication server. Now just need to find why all of a sudden I have 2 DNS records.

Thanks

Tony

Problem with Provider=SQLXMLOLEDB.3.0

Hi!

When I make a connection to the sqlserver database, everything work
fine.
Recently I connected to a huge db 1.4 GB.
The querys are executed with the same time however making a connection
is taking a long time.

I am using the following connection string.

My_CONNECTION = "Provider=SQLXMLOLEDB.3.0;" & _
"Data Provider=SQLOLEDB;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=" & MY_SERVER & ";" & _
"Initial Catalog=BigDB;" & _
"User Id=sa;" & _
"Password=sa"

Thanks is advance,
JaiJai (jaijai_kumar@.hotmail.com) writes:
> When I make a connection to the sqlserver database, everything work
> fine.
> Recently I connected to a huge db 1.4 GB.
> The querys are executed with the same time however making a connection
> is taking a long time.
> I am using the following connection string.
> My_CONNECTION = "Provider=SQLXMLOLEDB.3.0;" & _
> "Data Provider=SQLOLEDB;" & _
> "Network Library=DBMSSOCN;" & _
> "Data Source=" & MY_SERVER & ";" & _
> "Initial Catalog=BigDB;" & _
> "User Id=sa;" & _
> "Password=sa"

Take out Network Library from the connection string. Not sure if it
helps, but I don't think that DBMSOCN is one of the preferred onces.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks a lot for ur message...
It did not help?
Any other idea...?

Thanks,
Jai

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94934E9BA02EYazorman@.127.0.0.1>...
> Jai (jaijai_kumar@.hotmail.com) writes:
> > When I make a connection to the sqlserver database, everything work
> > fine.
> > Recently I connected to a huge db 1.4 GB.
> > The querys are executed with the same time however making a connection
> > is taking a long time.
> > I am using the following connection string.
> > My_CONNECTION = "Provider=SQLXMLOLEDB.3.0;" & _
> > "Data Provider=SQLOLEDB;" & _
> > "Network Library=DBMSSOCN;" & _
> > "Data Source=" & MY_SERVER & ";" & _
> > "Initial Catalog=BigDB;" & _
> > "User Id=sa;" & _
> > "Password=sa"
> Take out Network Library from the connection string. Not sure if it
> helps, but I don't think that DBMSOCN is one of the preferred onces.|||Jai (jaijai_kumar@.hotmail.com) writes:
> Thanks a lot for ur message...
> It did not help?
> Any other idea...?

There was not an overflow of information in your post, so it is
difficult to suggest anything. But let's look at it again.

>> > When I make a connection to the sqlserver database, everything work
>> > fine.
>> > Recently I connected to a huge db 1.4 GB.
>> > The querys are executed with the same time however making a connection
>> > is taking a long time.

Is the database that is slow to connect to on the same server as
the one that is fast to connect to? (My assumption is that is not.)

If you connect to the slow server from Query Analyzer, does that
also take time? If that is fast, what if you connect with SQLOLEDB
only; that is "Provider=SQLOLEDB"?

Assuming that you connect slow no matter the method, the problem may
be in the network somewhere. Not really my field of expertise to
say what that may be. Did you try connecting by IP-addrress directly?

Finally, most people would find a 1.4GB a moderately size database,
or even smallish...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Problem with profiling datetime

Hi,
SQL Server 2005 x64
I have a weird problem when analysing a trace log with DTA.
The problem is that the trace log include syntax error for datetime!!!
Ex. A date time parameter would appear like this in the trace window.
@.ClaimDate=''2006-03-27 00:00:00:000''
-- Note the two single quotes at the end and start...
Whe analysing, DTA give me this error
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
I can't figue out how to change this so I can't profile any statement that
include date.
Please help!!!!
I will add a sample...since my initial post is not that clear...
That simple .NET code
SqlCommand cmd = new SqlCommand("select @.TestDate");
cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
cmd.Connection = conn;
DateTime dt = (DateTime) cmd.ExecuteScalar();
The resulting trace log item is as follow
exec sp_executesql N'select @.TestDate',N'@.TestDate
datetime',@.TestDate=''2006-04-02 18:44:22:687''
Wich will fail when analyzed since there is a syntax error in the command
e.g. two single quote at the end and start of the date.
Please help!
"Martin Masse" wrote:

> Hi,
> SQL Server 2005 x64
> I have a weird problem when analysing a trace log with DTA.
> The problem is that the trace log include syntax error for datetime!!!
> Ex. A date time parameter would appear like this in the trace window.
> @.ClaimDate=''2006-03-27 00:00:00:000''
> -- Note the two single quotes at the end and start...
> Whe analysing, DTA give me this error
> [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
> I can't figue out how to change this so I can't profile any statement that
> include date.
> Please help!!!!
>
>
>
|||Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I will add a sample...since my initial post is not that clear...
> That simple .NET code
> SqlCommand cmd = new SqlCommand("select @.TestDate");
> cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
> cmd.Connection = conn;
> DateTime dt = (DateTime) cmd.ExecuteScalar();
> The resulting trace log item is as follow
> exec sp_executesql N'select @.TestDate',N'@.TestDate
> datetime',@.TestDate=''2006-04-02 18:44:22:687''
> Wich will fail when analyzed since there is a syntax error in the command
> e.g. two single quote at the end and start of the date.
> Please help!
I don't really have a suggestion how to work around this, as I have not
worked much with the DTA.
However, it is obviously a bug somewhere, although I can't tell whether
it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
DBK48153
What I noticed was that if I changed DateTime.Now to "DateTime.Now",
that is a string, I got the correct output in Profiler.
Erland Sommarskog, SQL Server MVP, esquel@.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
|||Thanks Erland.
"Erland Sommarskog" wrote:

> Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I don't really have a suggestion how to work around this, as I have not
> worked much with the DTA.
> However, it is obviously a bug somewhere, although I can't tell whether
> it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
> the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
> http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
> DBK48153
> What I noticed was that if I changed DateTime.Now to "DateTime.Now",
> that is a string, I got the correct output in Profiler.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.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
>

Problem with ProClarity report 'Export' options

Hi, all here,

Thank you very much for your kind attention.

I encountered a problem with export ProClarity reports. The problem is: under the 'Export' option, there is only 'Export to business reporter'. No other options like: 'Export to reporting service', 'Export to PowerPoint', 'Export to outlook' which actually should be available for ProClarity report 'Export' options tho.

Would please any expert here give me any guidance and advices for that.

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Hi Helen:

The ProClarity "export to" options are usually established on installation of the ProClarity Professional. In the installation process you can choose to install the "Integration with Office" components which provide the Export to Excel, Outlook, and Powerpoint. You can verify that this functionality was NOT installed by looking in the subfolder:

C:\Program Files\Common Files\ProClarity\Client\Office

I have three folders here: "Excel", "Outlook", and "PPoint". Each folder contains a .DLL and supporting files for each flavor of export. If you don't have the folders then re-installing the ProClarity Professional with the correct components selected should suffice to fix the problem. If the folders DO exist then something else is wrong. Your shortest route to fixing the situation may be to uninstall the ProClarity Professional and reinstall with the correct components selected.

Hope this helps.

PGoldy

|||

Hi, Pgoldy,

Thank you very much for your guidance and help.

Yes, I have had a look at the folder(C:\Program Files\ProClarity\Client\), but no 'Office' existed there. So yes would try to resinstall the software as you recommended. Thank you very much.

With best regards,

Yours sincerely,

|||

Sorry, please ignore this post.

With best regards,

Yours sincerely,

Problem with ProClarity analystics 6 for SQL Server 2005 reporting services

Hi, all here,

Thank you very much for your kind attention.

I'v got a problem with Microsoft ProClarity for SQL Server 2005 reporting services as below:

Created a 3-D effect reports (which are views in ProClarity professional ) and exported it into SQL Server 2005 reporting services, but the result in SQL Server 2005 reporting serivces is totally different, no 3-D result, and also the color of the report has totally changed, for better result in SQL Server 2005, I have to manually modify the reporting file in reporting services.

Another problem is: the views created in ProClarity always change colors (the colors I think are aweful) after exported into SQL Server 2005 reporting services.

Can any expert for that give me any adives for that? What can we try to solve this problem?

Thanks a lot in advance for any guidance and advices for that.

With best regards,

Yours sincerely,

I recommend http://www.proclarity.com/services/support.asp for these problems.

Regards

Thomas Ivarsson

|||

Thanks a lot.

With best regards,

Problem with ProClarity analystics 6 for SQL Server 2005 reporting services

Hi, all here,

Thank you very much for your kind attention.

I'v got a problem with Microsoft ProClarity for SQL Server 2005 reporting services as below:

Created a 3-D effect reports (which are views in ProClarity professional ) and exported it into SQL Server 2005 reporting services, but the result in SQL Server 2005 reporting serivces is totally different, no 3-D result, and also the color of the report has totally changed, for better result in SQL Server 2005, I have to manually modify the reporting file in reporting services.

Another problem is: the views created in ProClarity always change colors (the colors I think are aweful) after exported into SQL Server 2005 reporting services.

Can any expert for that give me any adives for that? What can we try to solve this problem?

Thanks a lot in advance for any guidance and advices for that.

With best regards,

Yours sincerely,

I recommend http://www.proclarity.com/services/support.asp for these problems.

Regards

Thomas Ivarsson

|||

Thanks a lot.

With best regards,

Problem with procedure call style

I try to execute code:
...
CallableStatement prep = conn.prepareCall("{call sp_xml_preparedocument
(?, ?)}");
prep.registerOutParameter(1, java.sql.Types.INTEGER);
prep.setString(2, "<root/>");
prep.executeUpdate();
System.out.println(prep.getInt(1));
CallableStatement rem = conn.prepareCall("{call sp_xml_removedocument
(?)}");
rem.setInt(1, prep.getInt(1));
rem.executeUpdate();
...
Java prints Exception Could not find prepared statement with handle 1.
Possible this problem is in using sp_execsql by MSSQL JDBC Driver.
How can I fix it?
Thank You!
| From: "Yuri Shustrov" <yuri_shustrov@.mail.ru>
| Subject: Problem with procedure call style
| Date: Fri, 10 Dec 2004 16:03:46 +0300
| Lines: 19
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OBfhHir3EHA.2404@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: ns.escort-center.ru 212.176.17.195
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP14
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6541
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I try to execute code:
| ...
| CallableStatement prep = conn.prepareCall("{call
sp_xml_preparedocument
| (?, ?)}");
| prep.registerOutParameter(1, java.sql.Types.INTEGER);
| prep.setString(2, "<root/>");
| prep.executeUpdate();
| System.out.println(prep.getInt(1));
| CallableStatement rem = conn.prepareCall("{call sp_xml_removedocument
| (?)}");
| rem.setInt(1, prep.getInt(1));
| rem.executeUpdate();
| ...
| Java prints Exception Could not find prepared statement with handle 1.
| Possible this problem is in using sp_execsql by MSSQL JDBC Driver.
| How can I fix it?
| Thank You!
|
|
|
Hello Yuri,
The SQL Server 2000 Books Online topic "sp_xml_preparedocument" states the
following:
"sp_xml_preparedocument returns a handle that can be used to access the
newly created internal representation of the XML document. This handle is
valid for the duration of the connection to Microsoft SQL Server 2000,
until the connection is reset, or until the handle is invalidated by
executing sp_xml_removedocument."
Your code shows two CallableStatements being prepared and executed on the
same connection variable "conn" without closing the first
CallableStatement. If you are using "SelectMethod=direct", then this will
result in a new cloned connection. Since the handle is only valid on the
first connection, the call to sp_xml_removedocument will fail. You can
resolve this by specifying "SelectMethod=cursor" in your connection string.
Otherwise, you can still use "SelectMethod=direct" and simply modify your
code so that the first CallableStatement is closed before the second
CallableStatement is prepared. This will maintain the same underlying
connection, and so the document handle will still be valid:
CallableStatement prep = conn.prepareCall("{call
sp_xml_preparedocument(?, ?)}");
prep.registerOutParameter(1, java.sql.Types.INTEGER);
prep.setString(2, "<root/>");
prep.executeUpdate();
System.out.println(prep.getInt(1));
int handle = prep.getInt(1);
prep.close();
prep = null;
CallableStatement rem = conn.prepareCall("{call
sp_xml_removedocument(?)}");
rem.setInt(1, handle);
rem.executeUpdate();
rem.close();
rem = null;
You can verify this behavior using SQL Profiler. In the failing scenario,
you will observe two different SPIDs (two different connections).
Hope that helps!
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

problem with procedure 2

Hi, Please with procedure not funcion:

--PROCEDIMIENTO CARGAR DM_CURSOS

create procedure dbo.sp_dm_cursos @.db varchar(50) as

INSERT INTO [DW_MMQ].[dbo].[dm_cursos]
([cu_codigo], [cu_descripcion], [cu_cod_nivel],
[cu_des_nivel], [cu_cod_paralelo], [cu_des_paralelo],
[cu_ao_lectivo], [cu_cod_unidad])

select
convert(varchar,a.courseid) + 'Sec' as codigo,
case b.name
when 'Basica' then 'Bsica'
else b.name end as nombre ,
d.levelid as cod_nivel,
d.name as nom_nivel,
c.parallelid as cod_paralelo,
c.name as nom_paralelo,
convert(varchar,startrange)+ '-'+ convert(varchar,endrange) as cod_ao_lectivo,
1 as cod_unidad
from
[@.db].[dbo].[Course] a,
[@.db].[dbo].[Parallel] c,
[@.db].[dbo].[mLevel] d,
[@.db].[dbo].[Specialization] b,
[@.db].[dbo].[SchoolYear] e

where a.parallelid = c.parallelid
and a.levelid = d.levelid
and b.SpecializationID = d.SpecializationID
and e.schoolyearid = c.schoolyearid
and b.schoolyearid = e.schoolyearid
and convert(varchar,a.courseid) + 'Sec' not in (select cu_codigo from dm_cursos)

RESULT
-- execute sp_dm_cursos2 'Quitumbe'
--this is the problem?, please

Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Course'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Parallel'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.mLevel'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Specialization'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.SchoolYear'.change

[@.db].[dbo].[Course] a,
[@.db].[dbo].[Parallel] c,
[@.db].[dbo].[mLevel] d,
[@.db].[dbo].[Specialization] b,
[@.db].[dbo].[SchoolYear] e

to

[db].[dbo].[Course] a,
[db].[dbo].[Parallel] c,
[db].[dbo].[mLevel] d,
[db].[dbo].[Specialization] b,
[db].[dbo].[SchoolYear] e|||create procedure dbo.sp_dm_cursos @.db varchar(50) as

DECLARE @.sql varchar(8000)

SELECT @.sql = 'INSERT INTO [DW_MMQ].[dbo].[dm_cursos]'
+'([cu_codigo], [cu_descripcion], [cu_cod_nivel], '
+'[cu_des_nivel], [cu_cod_paralelo], [cu_des_paralelo], '
+'[cu_ao_lectivo], [cu_cod_unidad]) '
+''
+'select '
+'convert(varchar,a.courseid) + ''Sec'' as codigo,'
+'case b.name '
+'when ''Basica'' then ''Bsica'' '
+'else b.name end as nombre ,'
+'d.levelid as cod_nivel,'
+'d.name as nom_nivel,'
+'c.parallelid as cod_paralelo, '
+'c.name as nom_paralelo, '
+'convert(varchar,startrange)+ ''-''+ convert(varchar,endrange) as cod_ao_lectivo, '
+'1 as cod_unidad '
+'from '
+'['+@.db+'].[dbo].[Course] a,'
+'['+@.db+'].[dbo].[Parallel] c,'
+'['+@.db+'].[dbo].[mLevel] d,'
+'['+@.db+'].[dbo].[Specialization] b,'
+'['+@.db+'].[dbo].[SchoolYear] e'
+''
+'where a.parallelid = c.parallelid '
+'and a.levelid = d.levelid '
+'and b.SpecializationID = d.SpecializationID '
+'and e.schoolyearid = c.schoolyearid '
+'and b.schoolyearid = e.schoolyearid '
+'and convert(varchar,a.courseid) + ''Sec'' not in (select cu_codigo from dm_cursos)'

SELECT @.sql

EXEC(@.sql)|||Nope..the db is dynamic

change

[@.db].[dbo].[Course] a,
[@.db].[dbo].[Parallel] c,
[@.db].[dbo].[mLevel] d,
[@.db].[dbo].[Specialization] b,
[@.db].[dbo].[SchoolYear] e

to

[db].[dbo].[Course] a,
[db].[dbo].[Parallel] c,
[db].[dbo].[mLevel] d,
[db].[dbo].[Specialization] b,
[db].[dbo].[SchoolYear] e|||thank brett|||No worries...but why do you have to do it that way?

How many databases do you have?

problem with procedure

Hi, Please with procedure:

go

--PROCEDIMIENTO CARGAR DM_CURSOS

create procedure dbo.sp_dm_cursos @.db varchar(50) as

INSERT INTO [DW_MMQ].[dbo].[dm_cursos]
([cu_codigo], [cu_descripcion], [cu_cod_nivel],
[cu_des_nivel], [cu_cod_paralelo], [cu_des_paralelo],
[cu_ao_lectivo], [cu_cod_unidad])

select
convert(varchar,a.courseid) + 'Sec' as codigo,
case b.name
when 'Basica' then 'Bsica'
else b.name end as nombre ,
d.levelid as cod_nivel,
d.name as nom_nivel,
c.parallelid as cod_paralelo,
c.name as nom_paralelo,
convert(varchar,startrange)+ '-'+ convert(varchar,endrange) as cod_ao_lectivo,
1 as cod_unidad
from
[@.db].[dbo].[Course] a,
[@.db].[dbo].[Parallel] c,
[@.db].[dbo].[mLevel] d,
[@.db].[dbo].[Specialization] b,
[@.db].[dbo].[SchoolYear] e

where a.parallelid = c.parallelid
and a.levelid = d.levelid
and b.SpecializationID = d.SpecializationID
and e.schoolyearid = c.schoolyearid
and b.schoolyearid = e.schoolyearid
and convert(varchar,a.courseid) + 'Sec' not in (select cu_codigo from dm_cursos)

-- execute sp_dm_cursos2 'Quitumbe'
--this is the problem?, please
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Course'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Parallel'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.mLevel'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Specialization'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.SchoolYear'.mi espanol is muy mal. que es el problemo? necessito mas informacion. Inglis por favor?|||Start by converting your WHERE syntax into the more acceptable JOIN syntax:

from
[Quitumbe_Secundaria].[dbo].[Course] a
inner join [Quitumbe_Secundaria].[dbo].[Parallel] c on a.parallelid = c.parallelid
inner join [Quitumbe_Secundaria].[dbo].[mLevel] d on a.levelid = d.levelid
inner join [Quitumbe_Secundaria].[dbo].[Specialization] b on b.SpecializationID = d.SpecializationID
inner join [Quitumbe_Secundaria].[dbo].[SchoolYear] e
on e.schoolyearid = c.schoolyearid
and e.schoolyearid = b.schoolyearid
where convert(varchar,a.courseid) + 'Sec' not in (select cu_codigo from dm_cursos)

Now, if you map out your table relationships, you can see that you have exclusive inner joins for five tables the form a relational loop:

A - C \
| E
D - B /

For a record to appear in your dataset, all five of these joins must be satisfied. It is very possible that you have no records that pass this test, plus the criteria left in the WHERE clause above.

You may be able to drop [SchoolYear] from your query entirely, as it does not seem to appear in the SELECT clause, and tables [Parallel] and [Specialization] can be joined directly on the shared schoolyearid key.

problem with procedure

Hi,
I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,
even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."
code:
CREATE PROCEDURE x1 (@.y1 AS numeric=NULL )AS
declare @.z1 Varchar(200)
begin
set @.z1= 'and a1.id='
print @.y1
print @.z1
end
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....
I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.
Thanks in Advance!You cannot execute the statement dynamically in this fashion.
Rather, try this...
-- For storing Unicode SQL statements to be executed on the fly.
DECLARE @.sql_statement_string nvarchar(1024)
-- Construct SQL statement to select
SET @.sql_statement_string =3D 'select z1 from employee a1 where z2=3D'
+ @.z1
+ ' + 45 ...'
-- Execute the SQL & insert activity details.
EXECUTE sp_executesql @.sql_statement_string
--Seenu
On May 2, 1:04=C2=A0am, meendar <askjavaprogramm...@.gmail.com> wrote:
> Hi,
> I have selected a field name and declared it as varchar, since it is
> varchar in table and performed some numeric operation with numbers,
> even after i cast the sql in below code, it throws an exception as
> "Error converting data type varchar to numeric."
> code:
> CREATEPROCEDUREx1 (@.y1 AS numeric=3DNULL )AS
> declare @.z1 Varchar(200)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 begin
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 set @.z1=3D 'and a1.id=3D'
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.y1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.z1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 end
> Declare r1 cursor
> local Scroll Keyset Optimistic
> For
> select z1 =C2=A0from =C2=A0employee a1 where =C2=A0z2=3D @.z1 + 45 ....
> I want to clear that how can we cast the field with varchar for
> numeric operations, i have also tried cast and convert to change it
> but all in vain.
> Thanks in Advance!|||On May 2, 7:42=C2=A0pm, =E0=AE=9A=E0=AF=80=E0=AE=A9=E0=AF=81 <srinivasan...=@.gmail.com> wrote:
> You cannot execute the statement dynamically in this fashion.
> Rather, try this...
> =C2=A0 -- For storing Unicode SQL statements to be executed on the fly.
> =C2=A0 DECLARE @.sql_statement_string nvarchar(1024)
> =C2=A0 =C2=A0 -- Construct SQL statement to select
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 SET @.sql_statement_string =3D 'select z1 =C2==A0from =C2=A0employee a1 where =C2=A0z2=3D'
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2==A0 =C2=A0 =C2=A0+ @.z1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2==A0 =C2=A0 =C2=A0+ ' + 45 ...'
> =C2=A0 =C2=A0 -- Execute the SQL & insert activity details.
> =C2=A0 =C2=A0 EXECUTE sp_executesql @.sql_statement_string
> --Seenu
> On May 2, 1:04=C2=A0am, meendar <askjavaprogramm...@.gmail.com> wrote:
>
> > Hi,
> > I have selected a field name and declared it as varchar, since it is
> > varchar in table and performed some numeric operation with numbers,
> > even after i cast the sql in below code, it throws an exception as
> > "Error converting data type varchar to numeric."
> > code:
> > CREATEPROCEDUREx1 (@.y1 AS numeric=3DNULL )AS
> > declare @.z1 Varchar(200)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 begin
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 set @.z1=3D 'and a1.id=3D'
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.y1
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.z1
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 end
> > Declare r1 cursor
> > local Scroll Keyset Optimistic
> > For
> > select z1 =C2=A0from =C2=A0employee a1 where =C2=A0z2=3D @.z1 + 45 ....
> > I want to clear that how can we cast the field with varchar for
> > numeric operations, i have also tried cast and convert to change it
> > but all in vain.
> > Thanks in Advance!- Hide quoted text -
> - Show quoted text -
Thanks to All

Problem with procedure

Hi,

I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,

even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."

code:

CREATE PROCEDURE x1 (@.y1 AS numeric=NULL )AS
declare @.z1 Varchar(200)

begin
set @.z1= 'and a1.id='
print @.y1
print @.z1
end

Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....

I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.

Thanks in Advance!meendar (askjavaprogrammers@.gmail.com) writes:

Quote:

Originally Posted by

I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,
>
even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."
>
>...
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....
>
>
I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.

SQL Server tries to convert all values in employee.z2 to numeric, and
when this fails for some value, the query fails.

You need to use the CASE expression:

WHERE CASE WHEN ltrim(rtrim(z2)) NOT LIKE '%[^0-9]%'
THEN convert(numeric, z2)
END = @.z1 + 45

Now it will only attempt to convert z2 which it consists of digits only.

--
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|||On May 2, 12:10 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

meendar (askjavaprogramm...@.gmail.com) writes:

Quote:

Originally Posted by

I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,

>

Quote:

Originally Posted by

even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."

>

Quote:

Originally Posted by

...
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....

>

Quote:

Originally Posted by

I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.

>
SQL Server tries to convert all values in employee.z2 to numeric, and
when this fails for some value, the query fails.
>
You need to use the CASE expression:
>
WHERE CASE WHEN ltrim(rtrim(z2)) NOT LIKE '%[^0-9]%'
THEN convert(numeric, z2)
END = @.z1 + 45
>
Now it will only attempt to convert z2 which it consists of digits only.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

Thanks to All

problem with procedure

Hi,
I have selected a field name and declared it as varchar, since it is
varchar in table and performed some numeric operation with numbers,
even after i cast the sql in below code, it throws an exception as
"Error converting data type varchar to numeric."
code:
CREATE PROCEDURE x1 (@.y1 AS numeric=NULL )AS
declare @.z1 Varchar(200)
begin
set @.z1= 'and a1.id='
print @.y1
print @.z1
end
Declare r1 cursor
local Scroll Keyset Optimistic
For
select z1 from employee a1 where z2= @.z1 + 45 ....
I want to clear that how can we cast the field with varchar for
numeric operations, i have also tried cast and convert to change it
but all in vain.
Thanks in Advance!You cannot execute the statement dynamically in this fashion.
Rather, try this...
-- For storing Unicode SQL statements to be executed on the fly.
DECLARE @.sql_statement_string nvarchar(1024)
-- Construct SQL statement to select
SET @.sql_statement_string =3D 'select z1 from employee a1 where z2=3D'
+ @.z1
+ ' + 45 ...'
-- Execute the SQL & insert activity details.
EXECUTE sp_executesql @.sql_statement_string
--Seenu
On May 2, 1:04=C2=A0am, meendar <askjavaprogramm...@.gmail.com> wrote:
> Hi,
> I have selected a field name and declared it as varchar, since it is
> varchar in table and performed some numeric operation with numbers,
> even after i cast the sql in below code, it throws an exception as
> "Error converting data type varchar to numeric."
> code:
> CREATEPROCEDUREx1 (@.y1 AS numeric=3DNULL )AS
> declare @.z1 Varchar(200)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 begin
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 set @.z1=3D 'and a1.id=3D'
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.y1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 print @.z1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 end
> Declare r1 cursor
> local Scroll Keyset Optimistic
> For
> select z1 =C2=A0from =C2=A0employee a1 where =C2=A0z2=3D @.z1 + 45 ....
> I want to clear that how can we cast the field with varchar for
> numeric operations, i have also tried cast and convert to change it
> but all in vain.
> Thanks in Advance!|||On May 2, 7:42=C2=A0pm, =E0=AE=9A=E0=AF=80=E0=AE=A9=E0=AF=81 <srinivasan...=
@.gmail.com> wrote:
> You cannot execute the statement dynamically in this fashion.
> Rather, try this...
> =C2=A0 -- For storing Unicode SQL statements to be executed on the fly.
> =C2=A0 DECLARE @.sql_statement_string nvarchar(1024)
> =C2=A0 =C2=A0 -- Construct SQL statement to select
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 SET @.sql_statement_string =3D 'select z1 =C2=
=A0from =C2=A0employee a1 where =C2=A0z2=3D'
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0+ @.z1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0+ ' + 45 ...'
> =C2=A0 =C2=A0 -- Execute the SQL & insert activity details.
> =C2=A0 =C2=A0 EXECUTE sp_executesql @.sql_statement_string
> --Seenu
> On May 2, 1:04=C2=A0am, meendar <askjavaprogramm...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Thanks to All