SQL Query

keepitsimple
01-15-2004, 08:46 AM
Is it possible that can we use if condition inside an SQL query?
i.e
sql= SELECT 'if a=1 Then field1 else field2 End if' FROM table1

tahnks

KnooKie
01-15-2004, 09:19 AM
what database are you using ?

You can do conditions in SQL Server SELECT statements

keepitsimple
01-15-2004, 09:24 AM
I am using MS SQL 2000 and VB6.
Thanks for your respond if it's posible to use conditions in SQL Server, can you give me some hints.

Thanks

KnooKie
01-15-2004, 09:32 AM
I am using MS SQL 2000 and VB6.
Thanks for your respond if it's posible to use conditions in SQL Server, can you give me some hints.

Thanks

Bit of a long stored procedure but the bit in bold further down will hopefully help you understand the use of IF statements in SELECT statements. I don't think you can do it in the way in which your example implies but this may help you think slightly differently on the problem you have !?



CREATE PROCEDURE rsp_AdvSearch_Fmg_Analyst_Nested
@UID varchar(7)
AS

DECLARE
@SectorCode int,
@BrokerCode int,
@EmployeeCode int,
@Rank int,
@SectorCode2 int,
@BrokerCode2 int,
@EmployeeCode2 int,
@Rank2 int,
@Count int,
@Survey varchar (7),
@ChartType int


DECLARE @orderNo Cursor

SET @orderNo = Cursor for

/* SELECT statement for populating cursor */
SELECT TOP 100 PERCENT dbo.FMG_Bemco_Ranking.fi_sectorCode,
dbo.Broker_Employees_in_UID.fl_brokerCode,
dbo.FMG_Bemco_Ranking.fi_bemcoCode,
SUM(dbo.FMG_Bemco_Ranking.fd_score * dbo.FMG_Qaire.fl_weighting
* dbo.Sectors_in_UID.fd_weighting),
dbo.UID.fs_Universal_ID

FROM dbo.FMG_Qaire
INNER JOIN dbo.FMG_Bemco_Ranking
ON dbo.FMG_Qaire.fa_qID = dbo.FMG_Bemco_Ranking.fi_fmgQaireID
INNER JOIN dbo.Chart_Name_in_UID
ON dbo.FMG_Qaire.fs_UID = dbo.Chart_Name_in_UID.fs_UID
INNER JOIN dbo.Broker_Employee
INNER JOIN dbo.Broker_Employees_in_UID
ON dbo.Broker_Employee.fa_brokerEmployeeCode = dbo.Broker_Employees_in_UID.f_brokerEmployeeCode
INNER JOIN dbo.Brokers_in_UID
ON dbo.Broker_Employees_in_UID.fs_UID = dbo.Brokers_in_UID.fs_UID AND
dbo.Broker_Employees_in_UID.fl_brokerCode = dbo.Brokers_in_UID.fl_brokerCode
ON dbo.FMG_Bemco_Ranking.fi_bemcoCode = dbo.Broker_Employee.fa_brokerEmployeeCode AND
dbo.FMG_Qaire.fs_UID = dbo.Broker_Employees_in_UID.fs_UID
INNER JOIN dbo.Sectors_in_UID
ON dbo.FMG_Qaire.fs_UID = dbo.Sectors_in_UID.fs_UID AND
dbo.FMG_Bemco_Ranking.fi_sectorCode = dbo.Sectors_in_UID.fl_sectorCode
INNER JOIN dbo.UID
ON dbo.FMG_Qaire.fs_UID = dbo.UID.fs_Universal_ID
WHERE (dbo.FMG_Qaire.fs_UID = @UID)

GROUP BY dbo.FMG_Bemco_Ranking.fi_sectorCode,
dbo.Broker_Employees_in_UID.fl_brokerCode,
dbo.FMG_Bemco_Ranking.fi_bemcoCode,
dbo.UID.fs_Universal_ID


ORDER BY dbo.FMG_Bemco_Ranking.fi_sectorCode DESC, dbo.Broker_Employees_in_UID.fl_brokerCode DESC,
SUM(dbo.FMG_Bemco_Ranking.fd_score * dbo.FMG_Qaire.fl_weighting * dbo.Sectors_in_UID.fd_weighting) DESC

Open @OrderNo

FETCH NEXT FROM @OrderNo
-- get first record
INTO @sectorCode, @brokerCode, @employeeCode, @Rank, @Survey

SET @Count = 1
SET @SectorCode2 = @SectorCode
SET @brokerCode2 = @brokerCode
SET @employeeCode2 = @employeeCode
SET @Rank2 = @Rank
SET @ChartType = 1



WHILE (@@FETCH_STATUS = 0) -- loop through recordset
Begin
-- new broker or sector ?

If (@sectorCode <> @sectorCode2) OR (@brokerCode <> @brokerCode2) -- ...YES
Begin
Set @count = 1 -- so reset the order number
End
Else -- ...NO so still part of same ranking group so...
Begin

-- equal score ?
IF @Rank <> @Rank2 -- NO so add one to the order number i.e. @count
SET @count = @Count + 1
End

/* store all chart results table */
INSERT into
PreComp_EmployeeRank
(
fi_sectorCode,
fi_InstCode,
fi_EmpCode,
fi_OrderNo,
fs_UID,
fi_chartCode



)
VALUES
(
@sectorCode2,
@brokerCode2,
@employeeCode2,
@Count,
@Survey,
@ChartType
)

-- store current record
SET @sectorCode = @SectorCode2
SET @brokerCode = @brokerCode2
SET @employeeCode = @employeeCode2
SET @Rank = @Rank2

-- grab next record
FETCH NEXT FROM @ORDERNO
INTO @sectorCode2, @brokerCode2, @employeeCode2, @Rank2, @Survey
End

CLOSE @OrderNo

DEALLOCATE @OrderNo


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum