Re: [sqlite] can column name come from a table?

[Darren Duncan]

On 2013.07.29 6:08 PM, Roman Fleysher wrote:

Thank you, SQLiters, for confirmation. Yes, the set up is unusual. I have a 
table which essentially maps existing column names to the names the users want 
to see.

columnRenameTable(table, column, showAS)

The goal was to use this mapping for

SELECT table.column AS showAS FROM table;

when displaying the table.


What kind of users are these?  Shouldn't you be doing that in the presentation 
layer anyway?  Besides the names you'd probably show fields formatted in 
different ways too, depending on their type or meaning.


Also, SQLite does support views, you could define views having the names that 
the users want.  In fact, that's what views are for, letting different users 
have different interfaces to the same database.


-- Darren Duncan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can column name come from a table?

[Jay A. Kreibich]

> I was going to say, try using a SQL prepared statement.
> 
> For example, in Postgres you can write something like this:
> 
>   execute format( 'SELECT %I FROM table that has that columnName',
>(SELECT columnName FROM columnNameTable WHERE condition how to
> select limit 1) );
> 
> But I don't know if SQLite can do that in SQL; you might have to use
> the host language.

  You have to use a host language.  Bind parameters in prepared
  statements also won't work, because you cannot bind an identifier.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can column name come from a table?

[Roman Fleysher]

Thank you, SQLiters, for confirmation. Yes, the set up is unusual. I have a 
table which essentially maps existing column names to the names the users want 
to see.

columnRenameTable(table, column, showAS)

The goal was to use this mapping for

SELECT table.column AS showAS FROM table;

when displaying the table.

Thank you,

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [i...@tandetnik.org]
Sent: Monday, July 29, 2013 8:48 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] can column name come from a table?

On 7/29/2013 8:32 PM, Roman Fleysher wrote:
> I think the answer to my question is "NO", but may be I missed something...
>
> Can column name come from a table, i.e. from another select? Example:
>
> SELECT (SELECT columnName FROM columnNameTable WHERE condition how to select 
> limit 1)
> FROM table which has that columnName;

The answer to your question is, indeed, "NO". I mean, the query you show
is legal, but does something quite different from what you want.

> Or this is not doable within SQL and I must execute internal select 
> separately and have application compose second (external) select?

Yes. Note that your design is extremely unusual. Replace that wide table
that you want to index by column name with a narrow table having a
ColumnName column, and original column names as values. Then you can write

select Value from MyNarrowTable where ColumnName in
   (select columnName from columnNameTable ...);

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can column name come from a table?

[Darren Duncan]

On 2013.07.29 5:32 PM, Roman Fleysher wrote:

Dear SQLiters,

I think the answer to my question is "NO", but may be I missed something...

Can column name come from a table, i.e. from another select? Example:

SELECT (SELECT columnName FROM columnNameTable WHERE condition how to select 
limit 1)
FROM table which has that columnName;

Or this is not doable within SQL and I must execute internal select separately 
and have application compose second (external) select?

Thank you,

Roman


I was going to say, try using a SQL prepared statement.

For example, in Postgres you can write something like this:

  execute format( 'SELECT %I FROM table that has that columnName',
   (SELECT columnName FROM columnNameTable WHERE condition how to select limit 
1) );


But I don't know if SQLite can do that in SQL; you might have to use the host 
language.


-- Darren Duncan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can column name come from a table?

[Greg Jarzab]

You should be able to craft the query outside of the database and then
treat it like a normal query.  You have to be very careful about doing
this, and it generally isn't a good idea.
If you do take this approach, make sure that you properly sanitize the
column names, especially if they are provided by users.

On Mon, Jul 29, 2013 at 7:32 PM, Roman Fleysher
 wrote:
> Dear SQLiters,
>
> I think the answer to my question is "NO", but may be I missed something...
>
> Can column name come from a table, i.e. from another select? Example:
>
> SELECT (SELECT columnName FROM columnNameTable WHERE condition how to select 
> limit 1)
> FROM table which has that columnName;
>
> Or this is not doable within SQL and I must execute internal select 
> separately and have application compose second (external) select?
>
> Thank you,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can column name come from a table?

[Igor Tandetnik]

On 7/29/2013 8:32 PM, Roman Fleysher wrote:

I think the answer to my question is "NO", but may be I missed something...

Can column name come from a table, i.e. from another select? Example:

SELECT (SELECT columnName FROM columnNameTable WHERE condition how to select 
limit 1)
FROM table which has that columnName;


The answer to your question is, indeed, "NO". I mean, the query you show 
is legal, but does something quite different from what you want.



Or this is not doable within SQL and I must execute internal select separately 
and have application compose second (external) select?


Yes. Note that your design is extremely unusual. Replace that wide table 
that you want to index by column name with a narrow table having a 
ColumnName column, and original column names as values. Then you can write


select Value from MyNarrowTable where ColumnName in
  (select columnName from columnNameTable ...);

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users