I have a table;
CREATE TABLE theLiterals (
theKey varchar (255) NOT NULL ,
theValue varchar (255) NULL
)
INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\')
INSERT INTO theLiterals VALUES('defaultShare','MyShare\')
INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\')
INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')
I then try;
SELECT
defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
FROM theLiterals
and I get;
defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ NULL NULL NULL
NULL MyShare\ NULL NULL
NULL NULL MyFolder\ NULL
NULL NULL NULL MyFile.dat
but I want it COALESCEd like this;
defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ MyShare\ MyFolder\ MyFile.dat
....but my syntax is incorrect. Is there an efficient way of doing this.
I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')
and then my one-row recordset will be...
RS(0) will = '\\MyServer\'
RS(1) will = 'MyShare\'
RS(2) will = 'MyFolder\'
RS(3) will = 'MyFile.dat'
Thanks for any help! 5 1602
Just add MIN():
SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue END)
FROM theLiterals
--
David Portas
SQL Server MVP
--
Hi
Maybe:
SELECT A.defaultServer, B.defaultShare, C.defaultFolder, D.defaultFile
FROM
( SELECT theValue AS defaultServer
FROM theLiterals
WHERE theKey = 'defaultServer' ) A,
( SELECT theValue AS defaultShare
FROM theLiterals
WHERE theKey = 'defaultShare' ) B,
( SELECT theValue AS defaultFolder
FROM theLiterals
WHERE theKey = 'defaultFolder' ) C,
( SELECT theValue AS defaultFile
FROM theLiterals
WHERE theKey = 'defaultFile' ) D
OR
SELECT
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultServer' ) AS defaultServer ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultShare' ) AS defaultShare ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFolder' ) AS defaultFolder ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFile' ) AS defaultFile
You should put a unique or primary key on theKey to make sure only one row
is returned.
John
"Steve" <st*******@cognizantdesign.com> wrote in message
news:27**************************@posting.google.c om... I have a table;
CREATE TABLE theLiterals ( theKey varchar (255) NOT NULL , theValue varchar (255) NULL ) INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\') INSERT INTO theLiterals VALUES('defaultShare','MyShare\') INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\') INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')
I then try;
SELECT defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END, defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END, defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END, defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END FROM theLiterals
and I get;
defaultServer defaultShare defaultFolder defaultFile \\MyServer\ NULL NULL NULL NULL MyShare\ NULL NULL NULL NULL MyFolder\ NULL NULL NULL NULL MyFile.dat
but I want it COALESCEd like this;
defaultServer defaultShare defaultFolder defaultFile \\MyServer\ MyShare\ MyFolder\ MyFile.dat
...but my syntax is incorrect. Is there an efficient way of doing this.
I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile') and then my one-row recordset will be...
RS(0) will = '\\MyServer\' RS(1) will = 'MyShare\' RS(2) will = 'MyFolder\' RS(3) will = 'MyFile.dat'
Thanks for any help!
How about this:
SELECT TOP 1
defaultServer = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultServer'),
defaultShare = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultShare'),
defaultFolder = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFolder'),
defaultFile = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFile')
FROM #theLiterals
That returns the desired record:
\\MyServer\ MyShare\ MyFolder\ MyFile.dat
Or you could create a function that takes 4 parameters like 'defaultServer'
and returns a one-record table populated with the results from those 4
SELECTs.
Jim Geissman
Missed the beginning of this thread, but if #theLiterals is not trivially
small,
you get an (avg) 2:1 speedup by doing:
SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM #theLiterals
"Jim Geissman" <ji**********@countrywide.com> wrote in message
news:b8**************************@posting.google.c om... How about this:
SELECT TOP 1 defaultServer = (SELECT theValue FROM #theLiterals WHERE theKey = 'defaultServer'), defaultShare = (SELECT theValue FROM #theLiterals WHERE theKey = 'defaultShare'), defaultFolder = (SELECT theValue FROM #theLiterals WHERE theKey = 'defaultFolder'), defaultFile = (SELECT theValue FROM #theLiterals WHERE theKey = 'defaultFile') FROM #theLiterals
That returns the desired record:
\\MyServer\ MyShare\ MyFolder\ MyFile.dat
Or you could create a function that takes 4 parameters like
'defaultServer' and returns a one-record table populated with the results from those 4 SELECTs.
Jim Geissman
And first place for minimum reads goes to David Portas!
Thanks everyone for the help. I originally thought doing an aggregate
function
to get rid of NULLS would be inefficient, but by looking at the TRACE
it looks
like it has the most efficient execution plan.
FYI, I listed each of your solutions and the number of reads each
took and some additional questions.
NOTE: The 'theLiterals' table would never be big enough to cause more
than a seconds execution but it is always best to strive for
efficiency anyway. I hope you agree.
-- David Portas
-- 6 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX?
SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue
END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue
END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue
END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue
END)
FROM theLiterals
-- Mischa Sandberg
-- 18 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX or is it the way the CASE-WHEN
is
-- formatted?
SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM theLiterals
-- John Bell
-- 24 reads
SELECT
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultServer' ) AS
defaultServer ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultShare' ) AS
defaultShare ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFolder' ) AS
defaultFolder ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFile' ) AS
defaultFile
-- John Bell
-- 24 reads
SELECT A.defaultServer, B.defaultShare, C.defaultFolder,
D.defaultFile
FROM
( SELECT theValue AS defaultServer FROM theLiterals WHERE theKey =
'defaultServer' ) A,
( SELECT theValue AS defaultShare FROM theLiterals WHERE theKey =
'defaultShare' ) B,
( SELECT theValue AS defaultFolder FROM theLiterals WHERE theKey =
'defaultFolder' ) C,
( SELECT theValue AS defaultFile FROM theLiterals WHERE theKey =
'defaultFile' ) D
-- Jim Geissman
-- 80 reads
-- Taking off the outside 'FROM theLiterals' returns only the one
record rather
-- than four duplicate records. Therefore the TOP function is then
not needed.
-- So the query becomes the same as John Bell's above with 24 reads
SELECT TOP 1
defaultServer = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultServer'),
defaultShare = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultShare'),
defaultFolder = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFolder'),
defaultFile = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFile')
FROM theLiterals This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: glenn |
last post by:
I'm really scratching my head over this one. I'm working with CSV data exported
from Excel, which explains why it's a mess to begin with.
Within a table (or via any other means someone might be...
|
by: Ilan |
last post by:
Hi all
I need to add data from two Excel sheets (both on the same workbook)
to an existing table in my SQL DB.
The problem is that each sheet holds different fields for the same
record, though...
|
by: Steve |
last post by:
I have a people table of about 25 fields. The table is initially created each
year from 5 different sources. The records from each source are appended to the
people table. Any person may be in one...
|
by: dskillingstad |
last post by:
I've been struggling with this problem for some time and have tried
multiple solutions with no luck.
Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my...
|
by: Will |
last post by:
I have a table, tblManinstructions with fields Code & InstructionID, one
Code can have many InstructionID. I also have tblinstructions (fields
instructionID & instruction). What I want to do is...
|
by: NumberCruncher |
last post by:
Hi All,
I am struggling with setting up my first system of tables, forms,and reports, and could use your help!
I am setting up a database to keep track of the production of a produced item. The...
|
by: rdraider |
last post by:
Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need help
with the select statement.
example tables:
CREATE TABLE ...
|
by: tjm0713 |
last post by:
Not sure this can be done but here is my current situation. I have a table containing millions of records. Each record has 28 fields. One of the fields I am trying to return the single record for...
|
by: Ken Fine |
last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets
and DataTables should be able to answer fairly easily. The basic question is
how I can efficiently match data from one...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |