May 7, 2015 at 10:53 am
Hello, I would like to replace the value in the Select query of CO OWNER with a space ' ' or NULL if the ORDINAL value = 0.
The CO OWNER name is stored in the CARDNAME table, and the OWNER name is stored in the NAME table. I can not change the db structure.
JEAN is the ACCOUNT owner and BILL is CO OWNER of two cards.
Can someone point me to an example or show the required code.
Thank you!
Current Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 BILL JEAN
Desired Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 NULL JEAN
Current SQL Select statement:
SELECT DISTINCT
CARD.PARENTACCOUNT AS ACCOUNT,
CARD.NUMBER AS CARD,
CARD.ORDINAL,
CARD.STATUS,
CARDNAME.FIRST AS CO_OWNER,
NAME.FIRST AS OWNER
FROM CARD INNER JOIN
CARDNAME ON CARD.PARENTACCOUNT = CARDNAME.PARENTACCOUNT INNER JOIN
NAME ON CARD.PARENTACCOUNT = NAME.PARENTACCOUNT
GROUP BY CARD.PARENTACCOUNT,
CARD.NUMBER,
CARD.ORDINAL,
CARD.STATUS,
CARDNAME.FIRST,
NAME.FIRST
ORDER BY CARD.PARENTACCOUNT
May 7, 2015 at 11:09 am
select CO_Owner = iif(Ordinal = 0,NULL,CO_Owner)
May 7, 2015 at 11:32 am
Oracle NVL2() function would be great for this.
But since we don't have that functionality in SQL Server, IIF() presented by Phil or a classic CASE statement would do the job.
select CO_Owner = CASE WHEN Ordinal = 0 THEN NULL ELSE CO_Owner END
May 7, 2015 at 12:13 pm
Thanks!
May 7, 2015 at 12:15 pm
Thanks! I got an error saying the function requires 3 arguments?
May 7, 2015 at 12:17 pm
SKY986 (5/7/2015)
Thanks! I got an error saying the function requires 3 arguments?
Post your SQL please.
May 7, 2015 at 12:27 pm
SELECT
CARD.PARENTACCOUNT AS ACCOUNT,
CARD.NUMBER AS CARD,
CARD.ORDINAL,
CARD.STATUS,
NAME.FIRST AS OWNER,
CO_OWNER = IIF(CARD.ORDINAL=0,NULL,CARDNAME.FIRST)
--CASE WHEN CARD.ORDINAL=0 THEN '' ELSE CARDNAME.FIRST
--END AS [CO_OWNER]
FROM CARD INNER JOIN
CARDNAME ON CARD.PARENTACCOUNT = CARDNAME.PARENTACCOUNT INNER JOIN
NAME ON CARD.PARENTACCOUNT = NAME.PARENTACCOUNT
GROUP BY CARD.PARENTACCOUNT,
CARD.NUMBER,
CARD.ORDINAL,
CARD.STATUS,
CARDNAME.FIRST,
NAME.FIRST
ORDER BY CARD.PARENTACCOUNT
May 7, 2015 at 12:31 pm
Also, said multipart identifier could not be bound... the field CARD.ORDINAL
Placing the IIF first, don't get the multi-part identifier could not be bound error? It looks to be in the correct format!
SELECT
CO_OWNER = IIF(CARD.ORDINAL=0,NULL,CARDNAME.FIRST),
CARD.PARENTACCOUNT AS ACCOUNT,
CARD.NUMBER AS CARD,
CARD.ORDINAL,
CARD.STATUS,
NAME.FIRST AS OWNER
May 7, 2015 at 12:58 pm
Here's a code snippet proving that the iif() method should be fine.
declare @Ordinal int = 0;
declare @First varchar(30) = 'Jimbo';
select CO_OWNER = iif(@Ordinal = 0, null, @First);
set @Ordinal = 1
select CO_OWNER = iif(@Ordinal = 0, null, @First);
Regarding the 'could not be bound' message, that suggests that there is no column called Ordinal on the card table.
May 7, 2015 at 1:09 pm
ahh... I was connected to a SQL 2008R2 server. When I connected to a 2014 Server it works just fine!
Different syntax for 2008?
Thanks
May 7, 2015 at 1:11 pm
SKY986 (5/7/2015)
ahh... I was connected to a SQL 2008R2 server. When I connected to a 2014 Server it works just fine!Different syntax for 2008?
Thanks
IIF() is from 2012 only ... and this is a 2012 forum, so I took it as read 🙂
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply