89703

Firebird SQL challenge - return one row that has the data when select returned two rows

I have a quite unique need to make select always return one row

My SQL:

select * from table1 Where (table1.pk = :p1) or (table1.fk1 = :p1)

The above SQL always has two cases for return:

1- my Select return two records: The only different is one of the records has data while the other has only the ID filled with data while the rest of its fields are null. I need in this case to return <strong>only</strong> the one that has data in other fields.

2- my Select return one record In this case the record returned has only the ID field filled with data while the rest of the fields are null however this is what I want and no need for any further processing.

Please advise if is it possible to do that in one plain Select SQL. I can not use stored procedure.

Answer1:

You can use the first clause of the select statement to get only 1 row.

Given your specific conditions, you can order the result set descending by the rest of the fields to be sure the null row is selected only in case there's no data row (null goes first in firebird 2.5, but AFAIK this changed somewhere in the last versions, so check your specific version before applying this).

Your final query will look like this:

select first 1 * from table1 where (table1.pk = :p1) or (table1.fk1 = :p1) order by somecolumn;

somecolumn being the most relevant of the other fields that can contain null values.

you can test this with this statements:

--two rows, one with ID and values and the other with ID and null with q1 as ( select 1 id, 'data' othercolumn from rdb$database union select 2 id, null othercolumn from rdb$database ) select first 1 * from q1 order by othercolumn nulls last; --versus: --onw row with ID and null with q1 as ( select 2 id, null othercolumn from rdb$database ) select first 1 * from q1 order by othercolumn nulls last;

Recommend

  • How to execute javascript method from server side?
  • Django : Model Instance history
  • Plotting Route with Multiple Points in iOS
  • Python PIL to extract number from image
  • .NET video play library which allows to change the playback rate?
  • How to 'create temp table as select' in Slick?
  • Optimizing the print function in Matlab
  • Security issues with PHP's Readfile method
  • MySQL Order by column = x, column asc?
  • Stop Bash Script if Hive Fails
  • How can the INSERT … ON CONFLICT (id) DO UPDATE… syntax be used with a sequence ID?
  • Marklogic : Query response time is very high
  • Caching attributes in superclass
  • Why querying a date BC is changed to AD in Java?
  • Record samples being played with OpenAL
  • Is it possible to access block's scope in method?
  • SignalR .NET Client Invoke throws an exception
  • How can I send an e-mail from a vbs script
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Retrieving value from sql ExecuteScalar()
  • Fetching methods from BroadcastReceiver to update UI
  • Can Jackson SerializationFeature be overridden per field or class?
  • How to make Safari send if-modified-since header?
  • Weird JavaScript statement, what does it mean?
  • How to pass list parameters for each object using Spring MVC?
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • KeystoneJS: Relationships in Admin UI not updating
  • WPF Applying a trigger on binding failure
  • Hits per day in Google Big Query
  • how does django model after text[] in postgresql [duplicate]
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file
  • JaxB to read class hierarchy
  • Checking variable from a different class in C#
  • How can i traverse a binary tree from right to left in java?
  • Unable to use reactive element in my shiny app