Semplify a select ... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Semplify a select ...

Author

Topic

masterx81
Starting Member

12 Posts

Posted - 2003-06-24 : 07:07:51

Is possible to semplificate this sql select????
It is good, but too long :p
Thanks to all!!!

select id,(cast (dAsede as varchar(2)) + '/' + cast(danumero as varchar(8)) + (case when daserie is null then '' else daserie end)) as da, case when (cast (dAsede as varchar(2)) + '/' + cast(danumero as varchar(8)) + (case when daserie is null then '' else daserie end)) = (cast (dAsede as varchar(2)) + '/' + cast(danumero as varchar(8)) + (case when daserie is null then '' else daserie end)) then '' else (cast (dAsede as varchar(2)) + '/' + cast(danumero as varchar(8)) + (case when daserie is null then '' else daserie end)) end as a,case when escludi=1 then 'Si' else 'No' end as Escludi,ultimamodifica from cartelle WHERE contenitore = 10 order by da

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-24 : 07:32:01

Hi there,

CASE WHEN DASERIE IS NULL THEN '' ELSE DASERIE END

There is already a function is SQL which does the above for you.
Instead of the above you could replace it with :

ISNULL(DASERIE, '')

-- see Books on Line for more info.

and here is a more readable version of your code :

SELECT ID,
CAST (DASEDE AS VARCHAR(2)) + '/' + CAST(DANUMERO AS VARCHAR(8)) + (ISNULL(DASERIE, '') AS DA,
CASE
WHEN (CAST (DASEDE AS VARCHAR(2)) + '/' + CAST(DANUMERO AS VARCHAR(8)) + (ISNULL(DASERIE, '')) = (CAST (DASEDE AS VARCHAR(2)) + '/' + CAST(DANUMERO AS VARCHAR(8)) + (ISNULL(DASERIE, '')) THEN ''
ELSE (CAST (DASEDE AS VARCHAR(2)) + '/' + CAST(DANUMERO AS VARCHAR(8)) + (ISNULL(DASERIE, '')) END AS A,
CASE
WHEN ESCLUDI=1 THEN 'SI' ELSE 'NO' END AS ESCLUDI,ULTIMAMODIFICA
FROM CARTELLE
WHERE CONTENITORE = 10
ORDER BY DA

----------------
Have a good day!

Edited by - Amethystium on 06/24/2003 07:37:05

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-24 : 07:52:26

what is the point of this?

CASE
WHEN

(CAST (DASEDE AS VARCHAR(2)) + '/' + CAST(DANUMERO AS VARCHAR(8)) + (ISNULL(DASERIE, '')) =

(CAST (DASEDE AS VARCHAR(2)) + '/' + CAST(DANUMERO AS VARCHAR(8)) + (ISNULL(DASERIE, ''))

THEN ''

ELSE

(CAST (DASEDE AS VARCHAR(2)) + '/' + CAST(DANUMERO AS VARCHAR(8)) + (ISNULL(DASERIE, ''))

END AS A

that says "when something equals ITSELF, return ''. Otherwise, return that something."

needless to say, that always returns ''.

What are you trying to do here? why do you have this in there at all?

- Jeff

masterx81
Starting Member

12 Posts

Posted - 2003-06-24 : 09:13:41

Ops...
:-P

select id,
(cast (dAsede as varchar(2)) + '/' + cast(danumero as varchar(8)) + (case when daserie is null then '' else daserie end)) as da,
case when (cast (dAsede as varchar(2)) + '/' + cast(danumero as varchar(8)) + (case when daserie is null then '' else daserie end)) = (cast (Asede as varchar(2)) + '/' + cast(anumero as varchar(8)) + (case when aserie is null then '' else aserie end))
then
''
else (cast (Asede as varchar(2)) + '/' + cast(anumero as varchar(8)) + (case when aserie is null then '' else aserie end))
end as a,
case when escludi=1 then 'Si' else 'No' end as Escludi,ultimamodifica from cartelle WHERE contenitore = 8 order by da

Sorry for the mistake!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources