Jump to content

Search the Community

Showing results for tags 'sql select'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Caspio Bridge
    • Caspio Apps for Ukraine
    • General Questions
    • Caspio JavaScript Solutions
    • Tables, Views and Relationships
    • Import/Export and DataHub
    • DataPages
    • Deployment
    • Security, Authentications, Roles, SAML
    • Styles and Localizations
    • Parameters
    • API and Integration
    • Calculations and aggregations
    • User JavaScript and CSS Discussions

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start




Website URL






Found 4 results

  1. Just wondering if there is a way to find the second lowest number in a view. I have done the SQL for lowest number: SELECT TOP(1) Drivers_Driver FROM _v_Race_Results_v WHERE Results_RID=[@field:RID] ORDER BY Results_Finish ASC I see it's possible by nesting queries using select distinct? Not sure. Just need to find the next lowest entry in this list. thanks
  2. Hi all, i'm trying to convert a very basic spreadsheet into Caspio but I'm really disappointed to learn that there is no alternative to INDEX/MATCH. Caspio seems incapable of working with arrays, there is no INDEX function nor MATCH function, calculated fields allow SQL but SQL queries do not allow for select * so I'm faced with a technical constraint it seems. What I'm trying to overcome (what Excel does effortlessly on the fly) is do some calculations to come to a measure, then this measure is compared to each user's custom model settings (using values in the user table) to tell the user which model to use. With an INDEX MATCH in Excel I'm able to do this 2 dimensionally with one formula. After struggling with Caspio, I found as only way to do this is with virtual fields as calculated value using CASE OK a lot of lines of code to write but hey, the result would be there ... or wouldn't it ? The result get's into place but ... it takes about 15 seconds to calculate. Does anyone have any other way to achieve this or am I bound to start looking for another platform ? Kind regards
  3. I am trying to use a SQL query in a calculated field to calculate the max value in a sub-group of data. When I hard code the formula as below, it works fine. SELECT Max(CY_Inc) FROM Pohl_Data_Temp WHERE cert = '8426' OR cert='7506' OR cert='660' OR cert='11681' OR cert='29679' OR cert='9507' What I am trying to do is fill in my where statement dynamically from a field in my database. When I use the following formula, it returns a blank result. SELECT Max(CY_Inc) FROM Pohl_Data_Temp WHERE cert = [@field:search_alpha] Field [search_alpha] = '8426' OR cert='7506' OR cert='660' OR cert='11681' OR cert='29679' OR cert='9507' Field [cert] is a unique ID field Since the hard coded formula works, I believe that I have the syntax correct, leading me to conclude that a SQL Select in a calculated field cannot use a field to provide the conditions for the where statement. I have not used a lot of SQL in calculated fields, so it is possible that I am missing something here. I appreciate any thoughts and insights!
  4. Hello, I'm using a tabular search and report for an invoicing application where I need to show aggregations of the RateAmount grouping by the RecIDL at the bottom of the RateAmount column. I want to pass the aggregated calculated field total to the invoice using an href link to my details page. Currently the link to the details page is showing an invoice for each RecIDL. I need the RecIDL to group on the tabular search and report with the aggregated total for each RecIDL. Here are the specifics: The Table name is 'Invoicing'. I have columns for 'RecIDL' and 'RateAmount'. For example: Table Name: Invoicing (Columns): RecIDL RateAmount (Vaules): L1 $100.00 L1 $100.00 L2 $150.00 L3 $175.00 I can aggregate totals and group by the RecIDL using the aggregate caspio field; however I need this information to pass to the actual invoice so a calculated field is required. I'd like to select the 'RecID column' and sum the 'RateAmount' and then group by the 'RecID.' I've tried the following select statements. SELECT RecIDL, SUM(IsNull(RateAmount,0)) FROM Invoicing_Loads GROUP BY RecIDL (*Expression Error Msg: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. ) SELECT RecIDL, SUM (RateAmount) FROM Invoicing GROUP BY RecIDL; (*Valid Formula per Caspio verify: When html page is opened "error in formula" message received; unable to view webpage) SELECT SUM(RateAmount) FROM Invoicing_Loads WHERE RecIDL = target.[@field:RecIDL] Group by RecIDL (*Statement is grouping the RateAmount for each RecIDL. The amount is appearing numerous times on the report; the total is correct.) This is what is showing: (The total should be $200.00 for L1) (Columns): RecIDL RateAmount (Vaules): L1 $200.00 L1 $200.00 L2 $150.00 L3 $175.00 This is what I need to see: Total Aggregations for each RecIDL; preferably at the bottom of each RecIDL group. (Columns): RecIDL RateAmount (Total L1): L1 $200.00 (Total L2) L2 $150.00 (Total L3) L3 $175.00 I'm not a strong sql user so I'm not certain what the "expression" error is speaking to. Based on the knowledge that I do have I believe it should work. I have searched the forums but could not locate any information specific to my need. I'd be grateful for any assistance provided. As always, Thank you! Bre
  • Create New...