Hello Community,
Can someone help me compile a query that will select a table/file based on item in a row.
For example, I have attached three tables. These tables arrive in folder.
As you can see TableOne has a load_id of 1111, TableTwo has a load_id of 1122, and TableThree has a load_id of 1133.
I'm trying to write a query that will select a table in the folder based on the value of the load_id number. So, if TableThree arrived in the folder before before TableTwo I 'don't' want my query to select that table. Likewise, if table TableTwo arrived in the folder before before TableOne I don't want my query to select that table.
The best way I think I can explain it is like this:
select * from TableThree where LOAD_ID = 1133, but only if in a previous select statement I checked for LOAD_ID from a previous table(TableTwo in this case) and successfully found LOAD_ID = 1122.
I hope want I've describe makes sense.
Please let me know if you need further clarification
Cheers
Carlton
Is this for microsoft sql server
Sorry but I don't get the idea of tables "coming/arriving" into "folders" ..
@yosiasz is right: is that MS SQL Server?
Hi guys, thanks for reaching out.
Yes, this is for MS SQL Server.
As regards, tables "coming/arriving" into "folders", basically TableOne etc.. is a .csv file that is copied into a folder - I hope that makes it a little clearer
Thanks
hi tables "coming/arriving" into folders
you can use
folder create date ..
does this help ?
Hi Harish,
Thanks again for getting in touch. I think too much emphasis is placed on the statement " tables "coming/arriving" into folders.
If the community can focus on helping write a sql query that will **
select * from TableThree where LOAD_ID = 1133, but only if in a previous select statement I checked for LOAD_ID from a previous table(TableTwo in this case) and successfully found LOAD_ID = 1122
**.
That would be great.
Really appreciate your feedback
Hi Guys,
I think the answer might be 'for loop' or something like that
Hi guys,
When I say folder I mean database
hi
will this help ????
old style programming !!!! i
if you want SQL i can try ,,,
declare @rowcnt1 int = 0
declare @rowcnt2 int = 0
declare @rowcnt3 int = 0
select @rowcnt1 = count(*) from table1 where LOAD_ID = 1111
if @rowcnt1 = 0
select @rowcnt2 = count(*) from table2 where LOAD_ID = 1122
if @rowcnt1 <> 0
select * from table1 where LOAD_ID = 1111
if @rowcnt2 = 0
select @rowcnt3= count(*) from table3 where LOAD_ID = 1133
if @rowcnt2 <> 0
select * from table2 where LOAD_ID = 1122
if @rowcnt3 <> 0
select * from table3 where LOAD_ID = 1133
1 Like
harishgg1,
I will give this a try in about an hour and let you know how I get on.
In the meantime thanks for this.. I'm very excited to see it work
Dirty solution using dynamic SQL.
harishgg1's solution is cleaner.
CREATE TABLE dbo.Loaded(
id int identity(1, 1) NOT NULL,
blabla CHAR(20),
load_id int NOT NULL,
CONSTRAINT PK_Loaded PRIMARY KEY (id)
)
GO
INSERT into dbo.Loaded(blabla, load_id) VALUES
('Hello world!', 1122)
GO
CREATE TABLE dbo.map_load_id_2_table(
id int identity(1, 1) NOT NULL,
load_id int NOT NULL,
table_name sysname NOT NULL,
CONSTRAINT PK_map_load_id_2_table PRIMARY KEY (id)
)
GO
INSERT INTO dbo.map_load_id_2_table(load_id, table_name) VALUES
(1111, 'TableOne'),
(1122, 'TableTwo'),
(1133, 'TableThree');
GO
Dynamically creating SELECT script
DECLARE @load_id int;
DECLARE @SQL VARCHAR(1000);
DECLARE @table_name SYSNAME;
SELECT @load_id = load_id FROM dbo.Loaded
SELECT @table_name = table_name FROM dbo.map_load_id_2_table WHERE load_id = @load_id
-- Warning! Dirty dynamic SQL ahead:
SET @SQL = '
SELECT *
FROM dbo.' + @table_name
--EXEC (@sql)
print @SQL
---- output:
--SELECT *
--FROM dbo.TableTwo
-- Dirty dynamic SQL ends here.
Clean up.
DROP TABLE dbo.map_load_id_2_table;
DROP TABLE dbo.Loaded;
GO
Wow... Wim_leys I'm looking forward to trying out this solution
Going attempt this solutions today.
Thanks guys.
I will keep you posted.