smyers051972
asked on
Problem trying to test for existence of a column inside table conditional column select
Hi all,
I am trying to test for the existence of a column within a table. The reason for this is we have two different versions of our gaming system 1 of the versions has the column whereas the other does not. I really would like to avoid having two different versions of my scripting out there and run the same VB scripts and crystal reports & templates out there.
My thought was this, test for the columns existence, if it exists then select that column with a isnull handler, if it does not, then select 0 to fill in 0's in the results.
I am having a problem with the syntax on that and could use help, below is my code, I seperated the lines where I am having the most difficulty.
Thank you again!
I am trying to test for the existence of a column within a table. The reason for this is we have two different versions of our gaming system 1 of the versions has the column whereas the other does not. I really would like to avoid having two different versions of my scripting out there and run the same VB scripts and crystal reports & templates out there.
My thought was this, test for the columns existence, if it exists then select that column with a isnull handler, if it does not, then select 0 to fill in 0's in the results.
I am having a problem with the syntax on that and could use help, below is my code, I seperated the lines where I am having the most difficulty.
Thank you again!
use winoasis
DECLARE @Casino VARCHAR(254),
@VAR INT
SET @Casino = (SELECT RIGHT(CASINONAME, LEN(CASINONAME)-9) FROM CDS_CONFIG (nolock))
if exists (select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='CDS_SLOTMAST' and COLUMN_NAME='MaxCoins' )
BEGIN
SELECT @VAR = '1'
END
if not exists (select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='CDS_SLOTMAST' and COLUMN_NAME='MaxCoins' )
BEGIN
SELECT @VAR = '0'
END
select @CASINO as Casino,
a.slotnumber,
a.locationstring,
IsNull(a.nReels, ' ') as nReels,
IsNull(a.Reels, ' ') as Reels,
IsNull(a.nCoins, ' ') as nCoins,
IsNull(a.nPaylines, ' ') as nPaylines,
IsNull(a.MaxHandPay, '.00') as MaxHandPay,
CASE
WHEN SUM(@VAR) = 1 THEN (SELECT IsNull(a.MaxCoins, '0'))
WHEN SUM(@VAR) = 0 THEN 0
END AS MaxCoins,
IsNull(a.SealNumber, 'None') as SealNumber,
IsNull(a.SerialNumber, 'None') as SerialNumber,
IsNull(a.EpromId, 'None') as EpromId,
IsNull(a.EpromId2, 'None') as EpromId2,
a.denomination,
b.cabinetdesc,
c.styledesc,
d.manufacturer,
a.description,
IsNull(g.CalcDesc, 'None') as CalcID,
IsNull(e.leasedesc, 'Not Leased') as LeaseInfo,
IsNull(f.ProgDesc, 'Non-Progressive') as ProgressiveType,
IsNull(h.Description, 'None') as PointCalc_ID,
a.active
from cds_slotmast A (nolock)
inner join bb_cabinet B (nolock) on a.cabinet_id = b.cabinet_id
inner join bb_style C (nolock) on a.style_id = c.style_id
inner join bb_mfr D (nolock) on a.mfr_id = d.mfr_id
left outer join bb_lease E (nolock) on a.lease_id = e.lease_id
left outer join bb_ProgressiveType F (nolock) on a.ProgressiveType_ID = f.ProgressiveType_ID
left outer join bb_Calc G (nolock) on a.Calc_ID = G.Calc_ID
left outer join cds_pointcalc H (nolock) on a.PointCalc_ID = H.PointCalc_ID
where a.active = 'Y' and a.currentrevision = 'y'
END
ASKER
The issue is merely a single column exists in 1 DB and the other DB everything else BUT that 1 column (CDS_SLOTMAST.MaxCoins) does exist.
I created that SQL query to be run as a command in crystal which then in turn exports the data to excel. I use crystal as sort of a helper to export to excel.
I am unsure why we can not have two versions of that query however? IF-THEN-ELSE type statement ???
I created that SQL query to be run as a command in crystal which then in turn exports the data to excel. I use crystal as sort of a helper to export to excel.
I am unsure why we can not have two versions of that query however? IF-THEN-ELSE type statement ???
ASKER
Let me add this:
I guess what would help is if we can have SQL do a conditional evaluation, if the column exists in the table, run the query this way, if the column does not exist run it the other way, I dont mind having two versions of the query existing in the same query but I would have to duplicate like 5 different scripts and crystal reports to accomodate this with out a conditional column selection.
The DB data is essentially the same, our gaming provider simply changed the structure of that table not to include MaxCoins column for whatever reason.
I guess what would help is if we can have SQL do a conditional evaluation, if the column exists in the table, run the query this way, if the column does not exist run it the other way, I dont mind having two versions of the query existing in the same query but I would have to duplicate like 5 different scripts and crystal reports to accomodate this with out a conditional column selection.
The DB data is essentially the same, our gaming provider simply changed the structure of that table not to include MaxCoins column for whatever reason.
Would this help?
DECLARE @COLCOUNT INT;
set @COLCOUNT = 0
SELECT @COLCOUNT = COUNT(NAME) FROM SYSCOLUMNS WHERE NAME = <COLUMN NAME HERE> AND ID = (SELECT ID FROM SYSOBJECTS WHERE NAME = <INSERT TABLENAME HERE>)
IF @COLCOUNT > 0
BEGIN
<WRITE FIRST SELECT STATEMENT HERE>
END
IF @COLCOUNT = 0
BEGIN
<WRITE SECOND SELECT STATEMENT HERE>
END
You may have to adjust that a little as it's pseudocode, but the concept should work.
DECLARE @COLCOUNT INT;
set @COLCOUNT = 0
SELECT @COLCOUNT = COUNT(NAME) FROM SYSCOLUMNS WHERE NAME = <COLUMN NAME HERE> AND ID = (SELECT ID FROM SYSOBJECTS WHERE NAME = <INSERT TABLENAME HERE>)
IF @COLCOUNT > 0
BEGIN
<WRITE FIRST SELECT STATEMENT HERE>
END
IF @COLCOUNT = 0
BEGIN
<WRITE SECOND SELECT STATEMENT HERE>
END
You may have to adjust that a little as it's pseudocode, but the concept should work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you'd used stored procedures you could have created a different proc that rolled out with each version of the database. Is there some reason why you don't do that? Why would you want to put a query in a script?