I need help joining a decimal type on a string. We before were able to run query that could join a decimal on a string. Now with using prisma my schema is invalid when i try joining these two different data types.

Hey @Collin 👋 Can you please share the query which you were invoking? Does this happen after upgrading to a new Prisma version?

Here is my schema i would like to do a @relation on oeHdr.orderNo to inv_tran.sub_document_number. although these are different data types they are both the same same value and i was able to use a join statement between them in raw sql. Prisma doesn't seem to like joining a string to a decimal because they are different datatypes. would i be better off creating a view within my database that converts sub_document_no to type string?

model oeHdr {
  orderNo              String           @id(map: "pk_oe_hdr") @db.VarChar(8) @map("order_no")
  deliveryInstructions String?          @db.VarChar(255) @map("delivery_instructions")
  oeHdrUid            Int              @unique(map: "ak_oe_hdr_uid") @map("oe_hdr_uid")
  ship2Add1            String?          @db.VarChar(50) @map("ship2_add1")
  ship2Add2            String?          @db.VarChar(50) @map("ship2_add2")
  ship2Country         String?          @db.VarChar(50) @map("ship2_country")
  ship2City            String?          @db.VarChar(50) @map("ship2_city")
  ship2State           String?          @db.VarChar(50) @map("ship2_state")
  ship2Zip             String?          @db.VarChar(10) @map("ship2_zip")
  oePickTicket           oePickTicket[]

  @@map("oe_hdr")
} 
model inv_tran {
  transaction_number    Decimal                 @id(map: "pk_inv_tran", clustered: false) @db.Decimal(19, 0)
  trans_type            String                  @db.VarChar(5)
  document_no           Decimal                 @db.Decimal(19, 0)
  sub_document_no       Decimal                 @db.Decimal(19, 0)
  inv_mast_uid          Int
  inventoryReceiptsLine inventoryReceiptsLine[]

  @@unique([document_no, inv_mast_uid])
}

@Collin 👋 Apologies in delay in getting back to you, but you figured it correctly, as the relation connecting ids in

oeHdr

and

inv_tran

models are of different type, prisma will throw an error stating that the ids should be of same type.

would i be better off creating a view within my database that converts sub_document_no to type string

Yes, you can definitely try this approach, if both the ids are of string type then there shouldn’t be an issue.