Skip to Content

PLEASE ! CD&S View and Associations Error

Hello everyone,

I have this issue right now, thanks in advance for any guidence.

File: Movies.cds:

namespace dhana;
type int: Integer;
type decimal: Decimal(13,3);

entity Movies{
	key MOVIEID: Integer;
	ratings: association to many Ratings on ratings.MOVIEID = MOVIEID; 
        // using $self got Syntax error: unexpected token "$SELF"
	TITLE: longString;
	GENRES: longString;
}

entity Ratings{
	key USERID: Integer;
	key MOVIEID: Integer;
	movies: association[1] to Movies on movies.MOVIEID = MOVIEID;
        // also tried only: association to Movies;
	RATING: Decimal(13,3);
	TIMESTAMP: Integer;
}



File: my-service.cds 
using dhana.Movies as MoviesTable from '../db/data-model';
using dhana.Ratings as RatingsTable from '../db/data-model';

service CatalogService {
  entity Movies @(
	title: '{i18n>moviesService}',
	Capabilities:{
		InsertRestrictions: {Insertable:false},
		UpdateRestrictions: {Updatable: false},
		DeleteRestrictions: {Deletable: false}
	}
  ) as projection on MoviesTable;
  
  entity Ratings @(
	title: '{i18n>moviesService}',
	Capabilities:{
		InsertRestrictions: {Insertable:false},
		UpdateRestrictions: {Updatable: false},
		DeleteRestrictions: {Deletable: false}
	}
  ) as projection on RatingsTable;  
}



Error:

Error: column ambiguously defined: T62FD280C30B9823D99FA1E3649EF293B_MOVIEID:

Another testings:

/* Also tried : */
ratings: association[0..*] to Ratings on ratings.MOVIEID = MOVIEID;
ratings: association[*] to Ratings on ratings.MOVIEID = MOVIEID;
ratings: association to many Ratings on ratings.MOVIEID = MOVIEID;
ratings: association to Ratings;

Another testing was creating a view just like in documentation example: 

define view MovieSample select from Movies{
   key MOVIEID,
   TITLE,
   ratings
} group by MOVIEID, TITLE;

And finally, tried changing column name "MOVIEID" in "Ratings" table to "MOVIE_ID",
buy again, same "ambiguously column".

The expected result :

http://myhana:x90x/Catalogs/Movies?$expand=ratings

{
value: [
{
    MOVIEID: 1,
    TITLE: "Toy Story (1995)",
    GENRES: "a|b|c",
    ratings: [
       { 1: ...}, { 2: ...}, { 3: ...}
    ]
}]
}

Please some help!

Thanks!

xOCh

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    Mar 20 at 09:27 PM

    That is a different project structure than what you posted earlier. You've switched to the SQLDDL based hdbtable for the base model and using the proxy entities. So I've switch my project over to match. I've entered the same data. And I still don't get your error. It works perfectly fine. I've updated my github repo if you want to compare them. Perhaps we are using a different version of @sap/cds or something like. I don't see any material difference between the two projects otherwise.

    https://github.com/jungsap/moviesQuestion

    Of course you can always clone my project and try and build it on your system as well.


    Add comment
    10|10000 characters needed characters exceeded

    • Thomas, is it possible to download & install the version of Web IDE that you have? If is not possible, am i able to update directly from code Web IDE wizards?

      Also, now im trying to apply associations to a view declared as an entity but got empty array (using your project). Is it another bug?

      Thanks!

  • Mar 20 at 07:19 PM

    I'm not exactly sure why you are getting Ambiguous column. Perhaps it has something to do with your data. Although you have a key on MOVIEID, so I don't see how you could have duplicate entries there. I recreated your project in my system added the same data you showed to the tables and tested and it works perfectly fine.

    My code is in github if you want to compare it; but I copied right from your posting for the most part.

    https://github.com/jungsap/moviesQuestion


    Add comment
    10|10000 characters needed characters exceeded

  • Mar 20 at 08:46 PM

    I removed all data an added manually, but still getting same error.

    Heres how i have the project:

    movies.hdbtable :

    column table "DHANA_MOVIES"(
    MOVIEID Integer cs_int generated by default as identity not null,
    TITLE varchar(255) not null,
    GENRES varchar(255) not null,
    primary key(
    "MOVIEID"
    )
    ) with associations(join "DHANA_RATINGS" as "Ratings" on "Ratings"."MOVIEID" = "MOVIEID")
    comment 'Movies Table'
    unload priority 5 auto merge

    ratings.hdbtable :

    column table "DHANA_RATINGS"(
    "USERID" Integer not null,
    "MOVIEID" Integer not null,
    "RATING" Decimal(3,2) not null,
    "TIMESTAMP" Integer not null,
    primary key(
    "USERID",
    "MOVIEID",
    "TIMESTAMP"
    )
    ) with associations(join "DHANA_MOVIES" as "Movies" on "Movies"."MOVIEID" = "MOVIEID")
    comment 'Ratings Table'
    unload priority 5 auto merge

    data-model.cds :

    using from '../db/Movies';

    Movies.cds :

    namespace dhana;
    type shortString: String(180);
    type longString: String(255);
    type int: Integer;
    type decimal: Decimal(13,3);
    
    @cds.persistence.exists
    entity Movies{
       key MOVIEID: Integer;
       ratings: association to many Ratings on ratings.MOVIEID = MOVIEID;
       TITLE: longString;
       GENRES: longString;
    }
    
    @cds.persistence.exists
    entity Ratings{
      key USERID: Integer;
      key MOVIEID: Integer;
      RATING: Decimal(13,3);
      key TIMESTAMP: Integer; // new added, just for test old data
    }
    


    my-service.cds

    using dhana.Movies as MoviesTable from '../db/data-model';
    using dhana.Ratings as RatingsTable from '../db/data-model';
    service CatalogService {
      entity Movies @(
          title: '{i18n>moviesService}',
          Capabilities:{
               InsertRestrictions: {Insertable: false},
               UpdateRestrictions: {Updatable: false},
              DeleteRestrictions: {Deletable: false}
         }
      ) as projection on MoviesTable;
      
    
      entity Ratings @(
           title: '{i18n>ratingsService}',
           Capabilities:{
                 InsertRestrictions: {Insertable: false},
                 UpdateRestrictions: {Updatable: false},
                 DeleteRestrictions: {Deletable: false}
           }
      ) as projection on RatingsTable;
    }


    http://myhana/catalog/$metadata



    http://myhana/catalog/Movies?$expand=ratings


    Error from WebIDE Console:

    And finally, data from Movies and Ratings:

    I dont understand what's causing this error.

    Thanks again for all your help!

    xOCh


    Add comment
    10|10000 characters needed characters exceeded

  • Mar 21 at 12:01 PM

    Thomas thank you for everything!

    In my hana if i start from scratch it does not work, but if i clone your repo works. I see differences between many package.json in both projects (your's and mine), and here's the obvious question:

    Where or how did you generated those package.json ?

    For instance in main package.json you have this:

    [...]
    
    "engines":{
          "node": "^8.9"
    },
    "dependencies":{
        "@sap/cds":"^3.5.2",
        "express":"^4.16.4",
        "hdb":"^0.17.0"
    }
    [...]

    And when i start a new "SAP Cloud Platform Business Application" by default package.json contains:

    {
      [...]
      "dependencies": {
        "@sap/cds": "2.x"
      },
      "scripts": {
        "build": "cds build/all --clean",
        "watch": "nodemon -w . -i node_modules/**,.git/** -e cds -x npm run build"
      },
      "cds": {
        "data": {
          "model": "db/"
        },
        "service": {
          "model": "srv/"
        }
      },
      "private": true
    }
    
    
    - I ommited name, description and so generated by default from wizard.

    So, your package.json and mine from wizard, are pretty differents, may i ask how did you create the project with those package configs?

    Thanks a lot for all your kindness.

    xOCh

    PS.- I tried to update my HANAExpress using HXECheckUpdate and it said that im updated!

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 21 at 06:53 PM

    >Thomas, is it possible to download & install the version of Web IDE that you have?

    No because its SPS 04 which isn't out yet. It will be in a few weeks. But really only the template generation is different. You can just manually update the package.json and get the latest version of @sap/cds since it comes from the public npm repository regardless.

    Add comment
    10|10000 characters needed characters exceeded

    • I have created a new GitRepository, on it there's the new issue. Changed all tables to CD&S but only one view using DDL as your exercise "SAP HANA Basics for Developers" when you create a view using DDL and create an entity in cds. So, the issue it's pretty much the same as this thread.

      The entity called: "MovieRates", rating's association shows a empty array!

      Can you please take a quick review?

      GitRepository

      Basically,

      MovieRates.hdbview

      view "DHANA_MOVIERATES"(
      "MOVIEID",
      "TITLE",
      "USERCOUNT",
      "AVGRATES"
      ) as select t."MOVIEID", t."TITLE", count(a."USERID") as userCount, avg(a."RATING") as AVGRates from "DHANA_MOVIES" t inner join "DHANA_RATINGS" a on a."MOVIEID" = t."MOVIEID" group by t."MOVIEID", t."TITLE"
      with read only
      //also tested : 
      // with associations (JOIN "DHANA_RATINGS" as "Ratings" on "Ratings"."MOVIEID" = "MOVIEID")
      
      


      Movies.cds:

      @cds.persistence.exists
      entity MovieRates{
      key MOVIEID: Integer;
      TITLE: longString;
      USERCOUNT: int;
      AVGRATES: decimal;
      ratings: association to many Ratings on ratings.MOVIEID = MOVIEID;
      };


      my-service.cds

      /* extend MovieRatesView with{
          ratings: association to many Ratings on ratings.MOVIEID = MOVIEID;
      }*/
      
      entity MovieRates @(
          title: '{i18n>movieRatesService}',
          Capabilities:{
               InsertRestrictions: {Insertable: false},
               UpdateRestrictions: {Updatable: false},
               DeleteRestrictions: {Deletable: false}
          }
      )as projection on MovieRatesView;

      I have create this proyect using based on your package's configuration.

      Thanks again!

      xOCh

      hana-failure-5.png (30.9 kB)
  • Mar 21 at 07:43 PM

    >I have created a new GitRepository, on it there's the new issue.

    Actually I don't see any issue. The first few records have an empty array but that's just because there are no matching ratings records for those ids. I checked your data and there are no matches. But if you scroll down a little bit to Happy Gilmore (1996) - excellent movie by the way - you will see that you do have ratings.


    Add comment
    10|10000 characters needed characters exceeded

    • >Actually I don't see any issue. The first few records have an empty array but that's just because there are no matching ratings records for those ids. I checked your data and there are no matches. But if you scroll down a little bit to Happy Gilmore (1996) - excellent movie by the way - you will see that you do have ratings.

      Maybe im wrong but i think results are not correct since MOVIEID = 8807 has 34 "UserCounts" means that 34 users "Rate" that movie, and checked using a simple query:

      Is not supposed so show all ratings even when totalUser is 1?

      Thanks again!

      Updates: using http://myhana/catalog/MovieRates(35347)?$expand=ratings << works correctly and shows "Ratings".

      PS.- Indeed "Happy Gilmore (1996) - excellent movie" :D

      hana-failure-6.png (140.2 kB)
  • Mar 21 at 10:25 PM

    This has got to be some sort of bug. Because look through the service call results. There are more than a few records where the ratings are correct. Notice that all the correct values are relatively lower numbers. Like less than 1300. Anything below that range and the association works. Anything greater in value for the MOVIEID key and it doesn't work. There's enough data here to know this isn't just a coincidence. But for the life of me I can't figure out why a certain threshold is causing this.

    The thing is I think you are hitting some sort of limit on the merged results or maybe some problem with the results not being ordered. If I use a orderby and skiptoken, then I get ratings for all the records in the results.


    Add comment
    10|10000 characters needed characters exceeded