Use Free-Hand SQL to Increase the Likelihood of Getting an Answer

[nscheaffer]

We have all seen questions with sample data provided as text like this...

ID | Some Description | Some Date | Some Number
1  | Whatever…        | 1/15/2024 | 150
2  | Something else…  | 1/31/2024 | 200
3  | Still more…      | 2/1/2024  | 500

Or in a table...

ID	Some Description	Some Date	Some Number
1	Whatever...	1/15/2024	150
2	Something else...	1/31/2024	200
3	Still more...	2/1/2024	500

Or even worse, as an image...

Regardless of the question, I am more likely to work out an answer if easily usable data is provided. I may know the answer or have an idea or something to try, but I want to test it out to make sure my solution works before posting it.

With a few simple steps you can provide the sample data you posted as text, a table, or in an image as a free-hand SQL statement with which anyone wanting to attempt to answer can build a report in their environment. I my example I will be using SQL Server syntax which I understand will not work for everyone.

1. Mock up data in a spreadsheet
   Create your sample data in Excel, Google Sheets, or whatever spreadsheet software you use.
   
2. Convert sample data to SQL
   Copy your sample data from your spreadsheet and paste it into a CSV to SQL conversion tool. I like to use https://www.convertcsv.com/csv-to-sql.htm.
   
   

   There are a lot of options. Take some time to experiment. Ultimately, I want a series select statements (one for each row of data) unioned together. Remember, we cannot create tables within a free-hand SQL statement.

   
3. Test SQL in Web Intelligence
   Drop that SQL into a free-hand SQL query.
   

   You may need to adjust the SQL to force some data types to be what you want.

   

   As you can see the Some Date object is being recognized as a "String". I want it to be a "Date/Time". To force that, I just need to add a function around the date value in the first line like this (the other lines will follow suit)...
   
   CONVERT(DATE,'1/15/2024')

   
   You many need to change the Qualification of some objects to match your exact situation.
   
   
   And there you have it...
   
4. Share your free-hand SQL
   Now you could just post that generated free-hand SQL as it is...
   
   SELECT 1 AS ID,'Whatever…' AS [Some Description],CONVERT(DATE, '1/15/2024') AS [Some Date],150 AS [Some Number]
   UNION ALL
   SELECT 2,'Something else…','1/31/2024',200
   UNION ALL
   SELECT 3,'Still more…','2/1/2024',500
   
   However, if it is more than a few rows that can be cumbersome. I prefer to use a tool like db<>fiddle. I can work with my SQL code there to ensure that it is working how I want it to.
   
   

   Each execution of a SQL statement in db<>fiddle with generate a its own unique URL which you can then copy and share. Then, whomever wants to work on your question can navigate to that URL, copy the SQL, and paste it into their a free-hand SQL query in their BusinessObjects environment.

I hope this helps drive better questions and more answers.

Noel