Subject Re: Query error
From Tom <IHaveNoEmail@ddress>
Date Sun, 3 Sep 2023 08:14:43 -0400
Newsgroups dbase.getting-started

On 9/3/23 2:30 AM, Akshat Kapoor wrote:
> Good Morning Tom,
 >
>>           sql = [Select CAST(TestDate as Char(10)) d.TestDate,
>> d.Provider, d.Client, ;
 >>          d.Category, d.Allergen, d.TestLevel, d.StdTestRange,
>> n.Demodata_Notes ;
 >>          FROM DemoData d Left Join DemoNotes n On n.Fk_DemoData =
>> d.Pk_DemoData WHERE ;
 >>          d.TestDate LIKE :TestDate AND ;
 >>          LOWER(d.Category) LIKE :Category AND ;
 >>          LOWER(d.Allergen) LIKE:Allergen ;
 >>          ORDER BY TestDate, Category, Allergen ]
 >
> In cases like these where sql is creating an issue remove all where
> statements. and bare minimum fields as Mervyn has suggested.
 >
> Navigate to the folder where these tables are residing.
 >
> And in the command window execute the following.
 >
> Select CAST(TestDate as Char(10)) d.TestDate, d.Provider, d.Client,
> d.Category, d.Allergen, d.TestLevel, d.StdTestRange FROM DemoData d
 >
 *
Above worked to perfection ...
*
> Browse //to view the results
 > If it works then you have a base. Spelling errors would cause issues here.
 >
> Next step would be
 >
> Select CAST(TestDate as Char(10)) d.TestDate, d.Provider, d.Client,
> d.Category, d.Allergen, d.TestLevel, d.StdTestRange, n.Demodata_Notes
> FROM DemoData d Left Join DemoNotes n On n.Fk_DemoData = d.Pk_DemoData
 >
 *
* Above worked to perfection ...
*
> browse
 >
> If this is working then you have a better base.
 > Next step would be where with fixed values
 >
> Select CAST(TestDate as Char(10)) d.TestDate,d.Provider, d.Client,  
> d.Category, d.Allergen, d.TestLevel, d.StdTestRange, n.Demodata_Notes
>            FROM DemoData d Left Join DemoNotes n On n.Fk_DemoData =
> d.Pk_DemoData WHERE d.TestDate LIKE '03.09.2023'
 >
*
Changed fixed value to '12/17/2013' a valid entry in the table.
*
Result:
Error message: Database Engine Error: Type mismatch in expression.
*
> This is the only way of solving sql issues. Once you have a fully
> working sql then copy and paste is somewhere for safe keeping.
 >
> Copy and paste it into wfm and there insert params.
 >
> Watch for word wraps and date formats. It is a slow process but will get
> you there.
 >
> Regards
 > Akshat
 *
Thanks for the effort,

Tom