flow of events while sending message from one serivce two another

Hello,

I want to know the exact flow of events when I use this statement:

BEGIN DIALOG CONVERSATION @.dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract]

Assuming that I have defined SERVICE1 for Queue1 in the initiator ,

and I have defined SERVICE2 for queue2 on the target.

Is this the flow:

1.Message first goes to the queue1
or
it directly goes to the SERVICE2 on target end point which in turn puts this message in Queue2 on the target?

2. target queue then activates the stored procedure which is connected to queue2 (let's say the procedure name is 'Processqueue2')

3.I noticed that even when none of the item comes in the queue, still when u use "ALTER QUEUE queue2 WITH STATUS = ON", that time also the 'processqueue2' is called.

4.'processqueue2' then fetch message from queue2 and process this. optionally it can send the acknowledgement message to the initiator or just send the end dialog message.

5.if i dont want 'processqueue2' to send the acknowledgement then
can I can directly send the End dialog from the inititor it self i.e. the end dialog just after sending the message.

BEGIN DIALOG CONVERSATION @.dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract]

SEND ON CONVERSATION @.dialog_handle
MESSAGE TYPE SendMessageType ('hello from intiator')

END CONVERSATION @.dialog

In this case why do i need queue1 at all?

my assumption here is that my communication is one way and i don't need the ACK from the target.

Thanks,

BEGIN DIALOG alone does not actualy send any message. It just create the initiator endpoint in sys.conversation_endpoints.

When you SEND a message, the message goes at first into sys.transmission_queue. After the SEND is commited, the message is picked up from sys.transmission_queue and delivered to he machines where SERVICE2 is hosted and is enqueued into Queue2. After the enqueue into Queue2 is commited, an ACK is automatically sent back to the host of SERVICE1 and this allows the message to be deleted from sys.transmission_queue.
In the case when SERVICE1 and SERVICE2 are within the same SQL Server instance, we might try to optimize the SEND by directly enqueueing the message into Queue2 (skip the intermediate step of sys.transmission_queue). If this optimization attempt fails for whatever reason (e.g. Queue2 is diasbled), then the normal path of sys.transmission_queue is used even within the same SQL instance (in fcat, even within the same database).

You application cannot send ACK replies, it can only send real message replies. These are ordinary messages sent from target to initiator, and they would follow the exact sequence as above.

Procedure activation (Processqueue2) happens whenever there are available (i.e. unlocked) messages in the queue. It is not a trigger, the procedure does not get activated once for each message. The algorithm that determines when to activate a new instance of the procedure (up to the max of MAX_QUEUE_READERS setting) monitors the activity of the procedure (RECEIVE statements) vs. the incomming rate of messages and determines when the procedure cannot keep up and launches a new instance of it. When you enable a queue, if there are messages in the queue, it will activate the procedure. Same goes for server start-up, database going online etc (if there are messages in the queue, it will activate the procedure).
It is not guaranteed that the activated procedure will actually find messages in the queue. The code of the procedure should always be prepared with being activated but finding the queue empty (altough we do try hard not to activate in such situations).

A one way message flow that does BEGIN DIALOG/SEND/END is at risk of running into problems if the target service suddenly starts erroring dialogs (e.g. permissions change, or service contract changes etc). Because the initiator has already ended the conversation, when the error comes back from the target it will be droped. The initiator has no way of evem knowing the error occured. A much better message exchange patttern is to BEGIN DIALOG/SEND from the initiator, RECEIVE/END from the target, then RECEIVE/END from the initiator (i.e. the target ENDs first). The initiator does not have to sit there waiting for the target to reply, the EndDialog message sent tby the target can be processed by a procedure attached to queue1. This issue is also discussed in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273523&SiteID=1.

HTH,
~ Remus

sql

Flat file to QUASI-Relational ?

I have a flat file table that describes crash data in SQL Server.
It contains vehicle information.

I would like to know if anyone knows a SQL statement that could go from
this
table= events

CRASHID | VEH1_TYPE | VEH2_TYPE | VEH_3TYPE
--------------
555555 | CAR | TRUCK | VAN

TO

CRASHID | VEH_TYPE | VEH_NUMBER
----------
555555 CAR 1
555555 Truck 2
555555 VAN 3

Any Ideas? I am relitively new at this and can only see how it could be
done by creating multiple tables and appending them.
Any help that could create the end selection in one query would be
great.

Thanks,
ChuckINSERT INTO NewTable (crashid, veh_type, veh_number)
SELECT crashid, veh1_type, 1
FROM OldTable
WHERE veh1_type IS NOT NULL
UNION ALL
SELECT crashid, veh2_type, 2
FROM OldTable
WHERE veh2_type IS NOT NULL
UNION ALL
SELECT crashid, veh3_type, 3
FROM OldTable
WHERE veh3_type IS NOT NULL ;

--
David Portas
SQL Server MVP
--|||Great.. Just what I was lookin for... Thanks for the help it is really
nice to have ppl who can/will help out..sql

Flag Random Record

I am using this select statement to radomly display a record

SelectCommand="SELECT TOP 1 * FROM [TBL_Example] ORDER BY NEWID()

I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.

I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement. I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.

Any ideas?I probably need a statement like this in my code behind:

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
SqlDataSource1.UpdateParameters("Flag").DefaultValue = "True"
End Sub

and then I probably need some insert parameters, here is what I am working with right now with my sqldatasource

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT TOP 1 * FROM [CIT_ContactInfo] ORDER BY NEWID()"
updatecommand="update [CIT_ContactInfo] set [Flag] = @.Flag where [UserName] = @.UserName">
<UpdateParameters>
<asp:Parameter Name="Flag" Type="boolean" />
</UpdateParameters>
</asp:SqlDataSource
Does any of this look remotely correct?

Fix my SQL "WHERE" Statement!

I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.

Basically, I want to select records using criteria of Race, Gender and
Crime Code. But the Crime Code field in the table is text, and I
cannot change it. I want to use a range of crime codes, so need to
convert it to an integer on-the-fly. Here's what I have in my code so
far:

varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "

varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
PrisonRelease.PID = Defendant.PID_Code "

varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "

varSQL = varSQL & "AND
(IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
Between 1800 And 1899) "

When I try to execute this code on my Web page, I get an error. But it
works fine in Access, with some minor syntax changes. What am I
missing?!

Thanks,
Rachel WeedenRachelWeeden@.hotmail.com (Rachel Weeden) wrote in message news:<f5066a28.0408230526.3f881906@.posting.google.com>...
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden

I think it is because you are using [] brackets which is a access
syntax and not asp.|||RachelWeeden@.hotmail.com (Rachel Weeden) wrote in message news:<f5066a28.0408230526.3f881906@.posting.google.com>...
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden

I think it is because you are using [] brackets which is a access
syntax and not asp.|||CInt is not supported in SQL-Server. You can use CAST or CONVERT
instead.

IIf it not supported in SQL-Server. You can use the CASE expression,
although it works slightly different, so you will need to rewrite that
part.

Have a look at SQL-Server Books Online for more information and
examples.

Hope this helps,
Gert-Jan

Rachel Weeden wrote:
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden

--
(Please reply only to the newsgroup)|||"Rachel Weeden" wrote:

> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden

Rachel,

[Note: I typed some of the T-SQL code in my newsreader, so formatting and
syntax may be a little goofy, but it should get you started in the right
direction.]

The square brackets are OK in T-SQL. The problem you're having is that your
WHERE clause is using VBA functions. While this is a cool feature in the
JET database engine, you can't use it in T-SQL (or any other DB environment
that I'm aware of). As others have mentioned:

- Use CAST or CONVERT instead of CInt (or any of the VB casting functions
e.g. CStr, CDbl, etc)

- Use CASE instead of IIf

Also,

- In VB, IsNull is a boolean function that returns true if the single
argument is NULL. In SQL Server T-SQL, ISNULL is a function that takes 2
parameters; if the first argument is NULL it returns the second else it
returns the first. For example:

ISNULL(NULL, 1) returns 1
...and...
ISNULL(2, 1) returns 2

A rough translation of your code would go something like (watch out for word
wrap and funny formatting)...

AND (
CASE
WHEN ISNULL(Defendant.[CRIME_CLASSIFICATION_CODE], '') = ''
THEN 9999

WHEN Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9]' AND
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9][0-9]'
THEN 9999

ELSE
CASE WHEN CONVERT(int, Defendant.[CRIME_CLASSIFICATION_CODE]) BETWEEN
1800 AND 1899
THEN 1
ELSE 0
END
END
)

However, it appears you want something akin to "WHERE
Defendant.[CRIME_CLASSIFICATION_CODE] isn't an appropriate numeric
representation or it is numeric and is inclusively in the range 1800-1899".
If I'm correct, you could use something like this (tested in Query Analyzer
with SQL Server 2000)...

DECLARE @.tab TABLE (
d varchar(32),
ccc varchar(20)
)

INSERT @.tab VALUES ('Num outside range', '1750')
INSERT @.tab VALUES ('Num in range', '1800')
INSERT @.tab VALUES ('Not a num', 'aaa')
INSERT @.tab VALUES ('NULL', NULL)
INSERT @.tab VALUES ('Empty string', '')

SELECT *
FROM @.tab
WHERE CASE WHEN ISNUMERIC(ccc) = 1
THEN
CASE WHEN CONVERT(int, ccc) BETWEEN 1800 AND 1899
THEN 1
ELSE 0
END
ELSE 1
END = 1

This returns everything in the test table except the 'Num outside range'
row.

Craig|||Thanks for all the input, Craig - I have taken some time to look over
your code, and I understand the basics about replacing some of my VB
functions with T-SQL ones. Problem is, I am very inexperienced with
SQL (this page is my first project, really!), so the details are a
little confusing.

For example, I've never heard of T-SQL before. I assumed I was writing
a SQL statement in a VB script on an ASP page...but that's a new
acronym for me! Also, the code you included looks totally different
than anything else on my page, so I am having trouble figuring out
where it all fits in, etc.

But I will look into this a bit more, and I'm sure your suggestions
about CAST, CONVERT, CASE, etc. will come in handy.

Thanks again,
Rachel

"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message news:<v5tWc.504132$Gx4.393231@.bgtnsc04-news.ops.worldnet.att.net>...
> "Rachel Weeden" wrote:
> > I'm working on an ASP Web application, and am having syntax issues in
> > a WHERE statement I'm trying to write that uses the CInt Function on a
> > field.
> > Basically, I want to select records using criteria of Race, Gender and
> > Crime Code. But the Crime Code field in the table is text, and I
> > cannot change it. I want to use a range of crime codes, so need to
> > convert it to an integer on-the-fly. Here's what I have in my code so
> > far:
> > varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> > varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> > ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> > PrisonRelease.PID = Defendant.PID_Code "
> > varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> > varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> > varSQL = varSQL & "AND
> > (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> > Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> > (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> > Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> > "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> > Between 1800 And 1899) "
> > When I try to execute this code on my Web page, I get an error. But it
> > works fine in Access, with some minor syntax changes. What am I
> > missing?!
> > Thanks,
> > Rachel Weeden
> Rachel,
> [Note: I typed some of the T-SQL code in my newsreader, so formatting and
> syntax may be a little goofy, but it should get you started in the right
> direction.]
> The square brackets are OK in T-SQL. The problem you're having is that your
> WHERE clause is using VBA functions. While this is a cool feature in the
> JET database engine, you can't use it in T-SQL (or any other DB environment
> that I'm aware of). As others have mentioned:
> - Use CAST or CONVERT instead of CInt (or any of the VB casting functions
> e.g. CStr, CDbl, etc)
> - Use CASE instead of IIf
> Also,
> - In VB, IsNull is a boolean function that returns true if the single
> argument is NULL. In SQL Server T-SQL, ISNULL is a function that takes 2
> parameters; if the first argument is NULL it returns the second else it
> returns the first. For example:
> ISNULL(NULL, 1) returns 1
> ...and...
> ISNULL(2, 1) returns 2
> A rough translation of your code would go something like (watch out for word
> wrap and funny formatting)...
> AND (
> CASE
> WHEN ISNULL(Defendant.[CRIME_CLASSIFICATION_CODE], '') = ''
> THEN 9999
> WHEN Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9]' AND
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9][0-9]'
> THEN 9999
> ELSE
> CASE WHEN CONVERT(int, Defendant.[CRIME_CLASSIFICATION_CODE]) BETWEEN
> 1800 AND 1899
> THEN 1
> ELSE 0
> END
> END
> )
> However, it appears you want something akin to "WHERE
> Defendant.[CRIME_CLASSIFICATION_CODE] isn't an appropriate numeric
> representation or it is numeric and is inclusively in the range 1800-1899".
> If I'm correct, you could use something like this (tested in Query Analyzer
> with SQL Server 2000)...
> DECLARE @.tab TABLE (
> d varchar(32),
> ccc varchar(20)
> )
> INSERT @.tab VALUES ('Num outside range', '1750')
> INSERT @.tab VALUES ('Num in range', '1800')
> INSERT @.tab VALUES ('Not a num', 'aaa')
> INSERT @.tab VALUES ('NULL', NULL)
> INSERT @.tab VALUES ('Empty string', '')
> SELECT *
> FROM @.tab
> WHERE CASE WHEN ISNUMERIC(ccc) = 1
> THEN
> CASE WHEN CONVERT(int, ccc) BETWEEN 1800 AND 1899
> THEN 1
> ELSE 0
> END
> ELSE 1
> END = 1
> This returns everything in the test table except the 'Num outside range'
> row.
> Craig

Fix matrix column and IF-ELSE statement

Hello.
I have to questions -
1) I have a matrix in my report and I want The group column to be fix so if I have a lot of columns and I scroll left the Header column will stay in the left of the screen and only the result column will be scrolled.
How can I do it?
2) How can I use a IF-ELSE statment or a CASE in the expression?
I want one of my column to be red if the value is "1", green if the value is "2" and blue if the value is "3".
Is there a way to do it?

Thanks in advance.Just to let you know that I found a replacment for the if.

I found that there is a switch-case statment in SSRS :)

I still be happy to hear if there is a way to make the first column of a matrix or table fix (like in excel) so they will stay visible even if I scroll left or right.

Thanks.|||

You can do the fix column using the fixedHeader property set to true on the cell of the table you are trying to lock.

Hope this helps

Andy

|||Hi Andy.

Thanks for your reply.

The FixedHeader is good only for table but I'm more intresting in Matrix.
In matrix you also have column that you define and I want those column to stay fixed on the left while I'm scrolling to the right with the rest of the data.

I didn't found any attribute like the "fixheader" for the matrix column.

Thank again.

Roy.|||

I would assume the field you want to remain visible is on a header as this is usually the case.

For this if you Edit the Column Group (or add the group!) then you will have an option asking if "Group header should remain visible when scrolling"

|||Andy - you are the man :)

Thanks a lot pal, I looked for ages for this solution!

Its exactly what I needed and now I can sleep well again :)

Thanks and take care,
Roy.

Fix matrix column and IF-ELSE statement

Hello.
I have to questions -
1) I have a matrix in my report and I want The group column to be fix
so if I have a lot of columns and I scroll left the Header column will
stay in the left of the screen and only the result column will be
scrolled.
How can I do it?
2) How can I use a IF-ELSE statment or a CASE in the expression?
I want one of my column to be red if the value is "1", green if the
value is "2" and blue if the value is "3".
Is there a way to do it?
Thanks in advance.Just to let you know that I found a replacment for the if.
I found that there is a switch-case statment in SSRS :)
I still be happy to hear if there is a way to make the first column of
a matrix or table fix (like in excel) so they will stay visible even if
I scroll left or right.
Thanks.
nicknack =EB=FA=E1:
> Hello.
> I have to questions -
> 1) I have a matrix in my report and I want The group column to be fix
> so if I have a lot of columns and I scroll left the Header column will
> stay in the left of the screen and only the result column will be
> scrolled.
> How can I do it?
> 2) How can I use a IF-ELSE statment or a CASE in the expression?
> I want one of my column to be red if the value is "1", green if the
> value is "2" and blue if the value is "3".
> Is there a way to do it?
> > Thanks in advance.|||O=2Ek so I got the answer to this matrix thing too.
After creating a column group you can use (in the "edit group" option)
the "Group header should remain visible when scrolling" and thats what
make the magic happen.
Thanks to andy for the solution :)
take care,
Roy
nicknack =D7=9B=D7=AA=D7=91:
> Just to let you know that I found a replacment for the if.
> I found that there is a switch-case statment in SSRS :)
> I still be happy to hear if there is a way to make the first column of
> a matrix or table fix (like in excel) so they will stay visible even if
> I scroll left or right.
> Thanks.
> nicknack =C3=AB=C3=BA=C3=A1:
> > Hello.
> > I have to questions -
> > 1) I have a matrix in my report and I want The group column to be fix
> > so if I have a lot of columns and I scroll left the Header column will
> > stay in the left of the screen and only the result column will be
> > scrolled.
> > How can I do it?
> > 2) How can I use a IF-ELSE statment or a CASE in the expression?
> > I want one of my column to be red if the value is "1", green if the
> > value is "2" and blue if the value is "3".
> > Is there a way to do it?
> > > > Thanks in advance.

Fix matrix column and IF-ELSE statement

Hello.
I have to questions -
1) I have a matrix in my report and I want The group column to be fixso if I have a lot of columns and I scroll left the Header column willstay in the left of the screen and only the result column will bescrolled.
How can I do it?
2) How can I use a IF-ELSE statment or a CASE in the expression?
I want one of my column to be red if the value is "1", green if the value is "2" and blue if the value is "3".
Is there a way to do it?

Thanks in advance.Just to let you know that I found a replacment for the if.

I found that there is a switch-case statment in SSRS :)

I still be happy to hear if there is a way to make the first column ofa matrix or table fix (like in excel) so they will stay visible even ifI scroll left or right.

Thanks.|||

O.k so I got the answer to this matrix thing too.

After creating a column group you can use (in the "edit group" option) the "Group header should remain visible when scrolling" and thats what make the magic happen.

Thanks to andy for the solution :)

take care,

Roy z.

First statement from Access to SQL

I'm trying to convert this from Access to SQL:

SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC
FROM ORDER_DETAIL

But it won't accept the First statement, specifically "First" is not recognized.

First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC

Does anyone know a way around?I'd test this in the SQL Anaylyser:

SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, Top(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC
FROM ORDER_DETAIL|||Let a brother know if it works

And remember

The RedSkins are back and Championship will follow.|||Understand that the concept of first and last doesn't have any meaning

Don't know how access doesn't...but you can do

SELECT ORDER_NUMBER
, SUBSTRING(ITEM_NUMBER,2,7) AS ITEMNO
, MAX(ITEM_DESC) AS MAX_ITEM_DESC
FROM ORDER_DETAIL
GROUP BY ORDER_NUMBER
, SUBSTRING(ITEM_NUMBER,2,7)

Or

SELECT TOP 1 ORDER_NUMBER
, SUBSTRING(ITEM_NUMBER,2,7) AS ITEMNO
, MAX(ITEM_DESC) AS MAX_ITEM_DESC
FROM ORDER_DETAIL
ORDER BY SUBSTRING(ITEM_NUMBER,2,7)

You need to know if you want the MAX or MIN, or to order by something

The order of data in a database has no meaning...

Go Ellie!

PS> Another fluf post on my part...damn...|||well, i wouldn't exactly call it a fluf post, but your second code example is wrong, it has a non-aggregate expression in the SELECT list along with the MAX aggregate, but it's lacking the GROUP BY clause

:cool:

First in sequence

A java group put this out. Is there a way to make it work in SQL 2000 (works
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
--
Regards,
JamieTry:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:
> A java group put this out. Is there a way to make it work in SQL 2000 (works
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie

First in sequence

A java group put this out. Is there a way to make it work in SQL 2000 (works
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
Regards,
Jamie
Try:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:

> A java group put this out. Is there a way to make it work in SQL 2000 (works
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie

First in sequence

A Java group put this out. Is there a way to make it work in SQL 2000 (work
s
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
Regards,
JamieTry:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:

> A Java group put this out. Is there a way to make it work in SQL 2000 (wo
rks
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie

First day of month of 3 months ago

How can I get the date of the first day of the month of
3 months ago in one statement ?
(And with no time in it ??)

Arno de Jong, The Netherlands.SELECT DATEADD(MONTH,-3,
DATEADD(DAY,1-DAY(CURRENT_TIMESTAMP),
CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)))

--
David Portas
----
Please reply only to the newsgroup
--

"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bpvelg$ii7$1@.reader10.wxs.nl...
> How can I get the date of the first day of the month of
> 3 months ago in one statement ?
> (And with no time in it ??)
> Arno de Jong, The Netherlands.

First & last record retrieve

Is there a simple SQL statement to get the
last and first record of a table under
Access and SQL Server ?
Thanks,
Pierre.Tables have no concept of "first" and "last". You need to create a column by which you can order the data. Then you can do something like:

SELECT * FROM t WHERE col IN (SELECT MIN(col) FROM t
UNION SELECT MAX(col) FROM t);