July 24, 2012 at 8:48 am
Hi,
Am stuck here, writing a view.
I have a column generated with a SQl Function, which am using in the next column.
[Unique Record Identifier] is dependent upon [ROW ID]
[CFR ID] is dependent upon [ROW ID]
[Adjustment Type] is dependent upon [Unique Record Identifier]
How to get these parsed and use the dependet column values on the fly using this select query?
Any help is much appreciated, as this fix would help me in many other similar queries.
My Sample query looks like below.
SELECT
(SELECT ROW_NUMBER() OVER(ORDER BY [ID]) AS [Row ID] From [tbl_FFIEC101]),
tbl_FFIEC101.[Reporting Period End Date],
[Data Source] + '-' + [Row ID] AS [Unique Record Identifier],
tbl_FFIEC101.[Data Source],
tbl_FFIEC101.[FDW Interface Name],
(SELECT
CASE
WHEN [Data Source] Like 'WC-AFS' THEN 'AIR' + [Row Id]
ELSE [CFR ID] END) AS [CFR ID],
(SELECT
CASE
WHEN [Adjustment Type] Like '%POST%' OR [Adjustment Type] Like '%ELIM%' Or [Adjustment Type] Like '%ADD%' Or [Adjustment Type] Like '%REV%' Or [Adjustment Type] Like '%DEL%' THEN [Adjustment Type] + '-' + [Unique Record Identifier]
ELSE [Adjustment Type] END) AS [Adjustment Type], *
From tbl_FFIEC101
July 24, 2012 at 9:28 am
Hi mate,
You can't reference a column by it's alias in the same select in that way.
You can by selecting from a subquery though - think that's what you're trying to do?
Does this work?
SELECT [Row ID],
[Reporting Period End Date],
[Unique Record Identifier],
[Data Source],
[FDW Interface Name],
CASE
WHEN [Data Source] Like 'WC-AFS' THEN 'AIR' + [Row Id]
ELSE [CFR ID] END) AS [CFR ID],
CASE
WHEN [Adjustment Type] Like '%POST%' OR [Adjustment Type] Like '%ELIM%' Or [Adjustment Type] Like '%ADD%' Or [Adjustment Type] Like '%REV%' Or [Adjustment Type] Like '%DEL%' THEN [Adjustment Type] + '-' + [Data Source] + '-' + [Row ID]
ELSE [Adjustment Type] END) AS [Adjustment Type],
*
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY [ID]) AS [Row ID],
tbl_FFIEC101.[Reporting Period End Date],
[Data Source] + '-' + [Row ID] AS [Unique Record Identifier],
tbl_FFIEC101.[Data Source],
tbl_FFIEC101.[FDW Interface Name],
[Adjustment Type],
*
From tbl_FFIEC101
) z
Cheers
Gaz
Edit: Updated query to use a subquery.
July 24, 2012 at 9:35 am
How about this:
IF OBJECT_ID('tbl_FFIEC101') IS NOT NULL
DROP TABLE tbl_FFIEC101;
CREATE TABLE tbl_FFIEC101
(
[ID] Int,
[CFR ID] Varchar(20),
[Reporting Period End Date] DateTime,
[Data Source] Varchar(30),
[FDW Interface Name] Varchar(30),
[Adjustment Type] Varchar(30)
);
INSERT tbl_FFIEC101 VALUES ( 1, '100', '01 Jul 2012', 'DataSource1', 'InterfaceName1', 'XXELIM01' );
INSERT tbl_FFIEC101 VALUES ( 2, '102', '02 Jul 2012', 'WC-AFS', 'InterfaceName2', 'XXELIM02' );
SELECT * FROM tbl_FFIEC101;
SELECT [Row ID],
[Reporting Period End Date],
--[Data Source] + '-' + CAST([Row Id] as Varchar(10)) AS [Unique Record Identifier],
URI.[Unique Record Identifier],
[Data Source],
[FDW Interface Name],
CASE
WHEN [Data Source] Like 'WC-AFS' THEN 'AIR' + CAST([Row Id] as Varchar(10))
ELSE [CFR ID] END AS [CFR ID],
CASE
WHEN [Adjustment Type] Like '%POST%'
OR [Adjustment Type] Like '%ELIM%'
Or [Adjustment Type] Like '%ADD%'
Or [Adjustment Type] Like '%REV%'
Or [Adjustment Type] Like '%DEL%' THEN [Adjustment Type] + '-' + URI.[Unique Record Identifier]
ELSE [Adjustment Type] END AS [Adjustment Type]
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY [ID]) AS [Row ID], * From [tbl_FFIEC101]) SRC
CROSS APPLY (SELECT [Data Source] + '-' + CAST([Row Id] as Varchar(10)) AS [Unique Record Identifier]) URI
By the way, using LIKE with a preceding % will be very slow for large rowcounts.
July 24, 2012 at 10:01 am
Hi Gaz,
thanks for your response, I got this idea and was trying to implement the way you suggested. The query is working fine but here I am getting redundant column names like [ROW ID], [Unique record Identifier] etc, which i dont want to show. Also I want to persist the order which i in general have.
Thanks,
Srini
July 24, 2012 at 10:06 am
Do you mean you don't want to show [Row Id] etc? If so, you can just remove the columns from the main select list (but not from the derived table & cross apply).
Views cannot be ordered. If you want to display the data in a specific order you will need to say 'select * from view_1 order by xxx', or create a stored procedure with an 'order by' clause instead of a view.
July 24, 2012 at 10:22 am
Yep, the * in the selects will do that. Remove them & just list the fields you actually want, in the order you want.
It's generally bad practice to use * in production code anyway. 🙂
July 24, 2012 at 12:16 pm
yes thanks gaz, am not using * in my actual query.. i have 150+ columns which i cant show you here in my example hence placed *....
Thanks all again, my query is working fine...
July 25, 2012 at 4:46 am
Cool 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply