Don't want to fetch single column (name)-CodePudding

def fetchProposalByStudio(studioId: Int): List[ProposalDetails] = {
ConnectionPoolManager.getDB(config = appConfig).localTx { implicit session: DBSession =>

  logger.info("Querying proposal table to fetch all the proposals")
  SQL("""SELECT [except name] p.id, id, proposal_title, challenge, possible_solution, explanation,
        |  submission_date, status, submitted_by, remark
        | FROM proposal p inner join knolder k on k.id = p.knolder_id
        | where k.studio_id =? order by p.id desc""".stripMargin)
    .bind(studioId)
    .map(rs =>
      ProposalDetails(
        rs.int("id"),
        rs.int("id"),
        rs.string("proposal_title"),
        rs.string("challenge"),
        rs.string("possible_solution"),
        rs.string("explanation"),
        rs.string("submission_date"),
        Some(ProposalStatus.withName(rs.string("status"))),
        rs.string("submitted_by"),
        rs.string("remark"),
        **rs.string("name")**
      )
    )
    .list().apply()
}

}

I don't want to fetch this column name in my query but without involving this in the query i am getting this error due to using case class.

13:28:24.446 [default-akka.actor.default-dispatcher-8] INFO c.k.l.b.ProposalImpl - Something went wrong while fetching the proposals. Exception message: ERROR: syntax error at or near "[" Position: 8

CodePudding user response：

Smells of a syntax problem... Perhaps:

SELECT [except name]  -- should be 
SELECT `except name` -- in mysql

CodePudding user response：

If you don't want a particular column in an SQL resultset, you simply don't mention it in the SELECT.

There is no notion of SELECT * EXCEPT FirstName FROM person - if Person has FirstName, LastName, Age, Address and you don't want FirstName, you don't put it in the select list:

SELECT LastName, Age, Address FROM Person

   ^^^^^^^
  no FirstName mentioned here

Mention every column you do want, do not mention any column you don't want.

If the complaint is "but there are 527 columns and I want all except one" - you can do something like:

SELECT CONCAT(column_name, ',') FROM information_schema.columns WHERE table_name = 'Person' and column_name <> 'FirstName'

which produces a resultset like:

LastName,
Age,
Address,
... 523 other columns

And you can then copy that resultset and paste it into your code, and it already has commas on the end..

If you want the columns all on one line, use GROUP_CONCAT or use a decent text editor to replace \r\n with nothing. If you want to surround the column name in backticks, put it into the CONCAT.. The ultimate point here is that you're a software developer: you can write code that writes code, then you can copy the output, which is valid code, and paste it into some other code somewhere else