Small doubt in SQL query!

Question

Post reply

Small doubt in SQL query!

Supriya Jadhav

SSC Enthusiast

Points: 199
More actions
November 21, 2004 at 11:06 pm

#182950

Hi,
In sql server we can have a select query like
Select [var1] = ‘Hello’,[var2] = t.col1
from table t
Now what is this [var1] or [var2] exactly? Can I use them as column names?
Below is my select query. Here I want to only fetch the rows for which Error1 != ‘’. How do I achieve it? Right now I have put the entire string in error1 in the where clause to check the comdition. Is there any better way of doing it?
select [Fund Code],
[Unitholder Social Code],
AccountNumber = ([Unitholder Account Number]+'-'+ [Unitholder Sub-Account Number]),
[From Date] = @FromDate ,
[To Date] = @ToDate ,
[Name] = case
            WHEN @identifier = 'Client' THEN (select account_desc from dbo.refACCOUNT where account_id = @identifyingid )
            WHEN @identifier = 'ABN' THEN (select [Registered name of investment body] from dbo.refFundManager where [Investment body Australian Business Number] = @identifyingid)
            END,
[LoadCheckSum] =
            case
            WHEN @identifier= 'Client' THEN dbo.f_CalculateLoadChecksum (@identifyingid,NULL)
            WHEN @identifier= 'ABN'THEN dbo.f_CalculateLoadChecksum (NULL,@identifyingid)
            END,
[RecCount] = dbo.f_getValidationReportCount(@identifyingid,@identifier,@FromDate,@ToDate),
[Identifier]= @identifier,
[IdentifierId] = @identifyingid,
            Error1 =
            case
             WHEN cast([Fund Code] as varchar(10))=''
            THEN 'Fund Code is not valid in UHR'+ char(13)
            ELSE ''--Test1 error in fund code'
            END +
            case
            WHEN ([Unitholder Social Code]is NULL OR LTRIM([Unitholder Social Code])= '' OR (dbo.ShareHolderTaxData.[Unitholder Social Code]<> dbo.ShareHolderTaxData.[Australian Tax Social Code]))
            THEN 'The social codes are not the same'+ char(13)
            ELSE ''--Testing for blank error in Rule 2 '
            end +
            case
            WHEN ([Unitholder Account Number]is NULL OR LTRIM([Unitholder Account Number])= '')
            THEN 'The Unit holder account number is blank'+ char(13)
            ELSE ''--No Error in Rule 3'
            END +
            case
            WHEN ([Unitholder Sub-Account Number] is NULL OR LTRIM([Unitholder Sub-Account Number])= '')
            THEN 'The Unit holder sub account number is blank '+ char(13)
            ELSE ''--No Error in Rule 4'
            END +
            case
            WHEN ([Unitholder Social Code]is NULL OR UPPER([Unitholder Social Code]) not in ('I','J','D','C','P','T','S','G','O') )
            THEN 'The Investor Social Code is not D,C,P,T,S,G or O'+ char(13)
            ELSE ''--No error in Rule 5 '
END
            from dbo.ShareHolderTaxData
            where [Fund Code] in (
            select [UHR Fund Code] from refFMtoFundCode
            where [Investment body Australian Business Number] = @identifyingid
            and @identifier = 'ABN' union ( select fund_number from dbo.refFUNDCODE where [account_id] = @identifyingid
            and @identifier='Client')
            )
            and [Australian Tax Last Change Date CYMD] > = @FromDate
            and [Australian Tax Last Change Date CYMD] < = @ToDate
            and upper([Australian Tax Reportable]) = 'Y'
Thanks & Regards,
Supriya

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Where did you get this from?

Select [var1] = ‘Hello’,[var2] = t.col1

from table t

I have never seen anything like that in SQL Server documents. The square brackets [] are used for column names that are 1) Reserved words or 2) have spaces in them.

Variables begin with the @ character. For example: @variable1.

So the example you used would actually be:

SELECT @var1 = 'Hello', @var2 = t.col1

FROM table t

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 2

I should have added that you are better off doing:

SET @var1 = 'Hello'

SET @var2 = SELECT col1 FROM table

SELECT @var1, @var2

And don't forget to DECLARE the variables at the beginning.

-SQLBill

Supriya Jadhav SSC Enthusiast Points: 199 More actions · Answer 3

I have a constraint that i need to return a single resultset to crystal report so i can use only single select. The second select statement in the previous post is the actualy select where i am facing a problem.

There i actually need the rows for which Error1 != ''. Could you help me with that?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Okay, I hope I didn't get lost inside your queries, but to me it seems that you need to include the test for Error1 !='' in the WHERE clause. And if I see this right you need to repeat the expression there rather than just refer to the variable.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

Another thing to keep in mind is IF you are writing this inside Crystal Reports it will be a pain to maintain (know from experience). Why not write this as a stored-procedure that accepts parameters and have the datasource for the Crystal Report the EXEC sp?

This will make maintenance a lot easier and will avoid a lot of the nasties with Crystal when you start changing the source

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Supriya Jadhav SSC Enthusiast Points: 199 More actions · Answer 6

i have actually written a stored proc which returns the resultset. The crystal report is able to access only one resultset from the stored proc too.

My problem here is the where cluase. Right now i have repeated the entire concatenation (the value of Error1) in the where clause. i am looking for a better approach.

Thanks for your responses

Bob Monahon Ten Centuries Points: 1282 More actions · Answer 7

Would this do it?

SELECT * FROM (

-- Your whole select statement goes here

)

WHERE Error1 !=''

Regards,

Bob Monahon