Showing posts with label order. Show all posts
Showing posts with label order. Show all posts

Wednesday, March 28, 2012

IF ELSE statement in WHERE clause

actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it.

i have try to use:

if @.IssueUserID = ' ' Select...(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services... :confused: is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help....

================================================== ==
SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount,
tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation
FROM tbl_Receipt WITH (nolock) INNER JOIN
tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN
tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN
tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID

WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @.DateFrom, 103) AND CONVERT(datetime, @.DateTo, 103)) AND
(tbl_Receipt.IssueUserID = @.IssueUserID)
ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeIDHi,

A trick I have used in this case would be to code:

...
AND (tbl_Receipt.IssueUserID =
ISNULL(@.IssueUserID, tbl_Receipt.IssueUserID )
)
...

In other words, if @.IssueUserId is not present, you join the column to itself, which gets all the data...

I've done this in stored procedure reports where I can default the passed parameters, e.g. :

create proc myproc (... , @.IssueUserID int = NULL, ...)

HTH,

Bill|||thank you for ur help... :rolleyes:

but when i try to edit the queries, i won't get all the data when the IssueUserID is blank. :(|||thank you for ur help... :rolleyes:

but when i try to edit the queries, i won't get all the data when the IssueUserID is blank. :(

so @.IssueUserID is a character data type, and when it's "blank" you don't want it to be considered in the where clause...?

try

AND ( (@.IssueUserID = ' ') OR (@.IssueUserID != ' ' AND table.column = @.IssueUserID))

HTH

Bill|||;) actually i really appreciate your help. the way u teach is working when i try in SQL Query Analyzer. When comes to the MS.NET reporting, the query is not working..so sad. :cool:|||Very confusing here whether you are checking for NULLs, zero-length strings ('') or values with just a single space (' '). Your MS.NET reporting layer may be interpreting things differently than Query Analyzer.

Your code indicates a single space, but that is an unusual value to be submitted by an interface to indicate the absence of data.

This code handles both NULL values and zero-length strings:

AND (nullif(@.IssueUserID, '') is null OR (nullif(@.IssueUserID, '') is not null AND table.column = @.IssueUserID))

Friday, March 23, 2012

IDIOT NEEDS HELP comparing a variable against a list

I have a variable called @.ORComm which has been selected using a cursor from each line on an order.

DECLARE TC2 CURSOR FOR
SELECT [Commodity],[Total] FROM [CSITSS].[dbo].[Ordrate] WHERE [OrderNumber]= @.OrdNum AND [Companydiv] = 'GLPC-TRANS'
OPEN TC2
FETCH NEXT FROM TC2 INTO @.ORComm, @.ORTotal

I need to compare the resulting @.ORComm against a list of valid commodity types selectable by

SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0

What's the easiest way to do this?SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0 and CommodityClass=@.ORComm

??|||I feel stupid I didn't think of that. I was thinking of storing the selected values in an array but that is much simpler.

Monday, March 12, 2012

Identity order

Hi,

I have some tables in a database with a identity (autoincrement) column (PK).

After several operations (INSERT, UPDATE and DELETE), some holes appeared in the identity column, like this:

ContactId Contact

1 John

2 Mary

5 Sam

9 David

where ContactId is the identity column.

Can I order the ContactId column, by removing the empty spaces, in order to the table appears like this?:

ContactId Contact

1 John

2 Mary

3 Sam

4 David

(I'm using SQL Server 2005.)

Thank you in advance.

Identity values are meant to be unique (but not technically guaranteed) but they won't necessary be sequential or without any gaps. An identity is probably not going to work well for this scenario. You can create another table to manage this. And this may give you another option:

http://blogs.msdn.com/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

However, you will still have issues with deletes - those won't be easy to manage if this is your requirement.

-Sue

|||

Sue:

Thank you for your answer.

I understand what you mean. But, after the table is filled with data, can I apply any command in order to put the identity values in sequential order? (Maybe 'ALTER INDEX' or 'DBCC' commands.)

I ask this, because I think identity column value must have a limit (maybe integers maximum limit in C language), and after long time with too much database operations, perhaps that limit be reached and several empty spaces (talking of auto generated values) remains in the table.

I beg your pardon for my silly question, but I'm newbie with SQL Server.

Anyway, thanks a lot.

--

Adrián

|||

You can change the number it's seeded at - for example if the next contact id number will be 100 but your last contact id in the table is 50 - using DBCC CHECKIDENT but it won't do anything about the gaps. For that, you would need to do something along the lines of creating a new table, populate the exiting data in the old table into the new table, drop the old table and rename the new table.

If the issue with gaps is that you want to use the number and are afraid you will run out of numbers, if the data type is an int, you can go up to 2,147,483,647 and then after exhausting positive values it will start using negatives through the value -2,147,483,648. So you have over 4 billion to work with there. If you double your storage space and use bigints the range is larger. It would handle thousands of ids generated per second over the course of over 100 years. I can remember the details but I really doubt that you would run out of numbers. You would of course want to use an appropriate data type due to the difference in storage required.

-Sue

|||

Thank you very much.

Your answer was very clear.

Identity order

Hi,

I have some tables in a database with a identity (autoincrement) column (PK).

After several operations (INSERT, UPDATE and DELETE), some holes appeared in the identity column, like this:

ContactId Contact

1 John

2 Mary

5 Sam

9 David

where ContactId is the identity column.

Can I order the ContactId column, by removing the empty spaces, in order to the table appears like this?:

ContactId Contact

1 John

2 Mary

3 Sam

4 David

(I'm using SQL Server 2005.)

Thank you in advance.

Identity values are meant to be unique (but not technically guaranteed) but they won't necessary be sequential or without any gaps. An identity is probably not going to work well for this scenario. You can create another table to manage this. And this may give you another option:

http://blogs.msdn.com/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

However, you will still have issues with deletes - those won't be easy to manage if this is your requirement.

-Sue

|||

Sue:

Thank you for your answer.

I understand what you mean. But, after the table is filled with data, can I apply any command in order to put the identity values in sequential order? (Maybe 'ALTER INDEX' or 'DBCC' commands.)

I ask this, because I think identity column value must have a limit (maybe integers maximum limit in C language), and after long time with too much database operations, perhaps that limit be reached and several empty spaces (talking of auto generated values) remains in the table.

I beg your pardon for my silly question, but I'm newbie with SQL Server.

Anyway, thanks a lot.

--

Adrián

|||

You can change the number it's seeded at - for example if the next contact id number will be 100 but your last contact id in the table is 50 - using DBCC CHECKIDENT but it won't do anything about the gaps. For that, you would need to do something along the lines of creating a new table, populate the exiting data in the old table into the new table, drop the old table and rename the new table.

If the issue with gaps is that you want to use the number and are afraid you will run out of numbers, if the data type is an int, you can go up to 2,147,483,647 and then after exhausting positive values it will start using negatives through the value -2,147,483,648. So you have over 4 billion to work with there. If you double your storage space and use bigints the range is larger. It would handle thousands of ids generated per second over the course of over 100 years. I can remember the details but I really doubt that you would run out of numbers. You would of course want to use an appropriate data type due to the difference in storage required.

-Sue

|||

Thank you very much.

Your answer was very clear.