mysql - SQL coalesce(): what type does the combined column have? -


lets use coalesce() combine 2 columns 1 in select , subsequently view constructed around such select.

tables:

values_int id integer(11) primary key value integer(11)  values_varchar id integer(11) primary key value varchar(255)  vals id integer(11) primary key value integer(11) //foreign key both values_int , values_varchar

the primary keys between values_int , values_varchar unique , allows me do:

select vals.id, coalesce(values_int.value, values_varchar.value) value vals join values_int on values_int.id = vals.value join values_varchar on values_varchar.id = vals.value

this produces nice assembled view id column , combined value column contains actual values 2 other tables combined single column.

  • what type combined column have?
  • when turned view , queried clause using combined "value" column, how handled type-wise? i.e. where value > 10

som rambling thoughts in need (most wrong): reason asking alternative design have 3 tables merged 1 int values in 1 column , varchar in other. of course produce lots of null values in both columns saved me joins. reason not solution because require additional type checking choose right column , deal null values maybe presented design require same (if resulting column varchar). hope passes clause down view source (so column not have type per se) wrong that.

you query should explicit clear, in case mysql using varchar.

i write query clear

coalesce(values_int.value,cast(values_varchar.value integer), 0)

or

coalesce(cast(values_int.value varchar(20)),values_varchar.value,'0')

you should put in last value unless want column null if both columns null.


Comments

Post a Comment

Popular posts from this blog

searchKeyword not working in AngularJS filter -

i writing app using angularjs on front end. want search through table word/field; 1 search box everything. according article here: http://hello-angularjs.appspot.com/searchtable can use filter: searchkeyword this. this code, , not working expected. <label>search: <input ng-model="searchkeyword"></label> <table ng-repeat="post in posts | orderby: sort | filter: searchkeyword"> <tr> <td> {{index(post)}} </td> <td> {{post.title}} </td> <td> {{post.author}} </td> <td> {{post.content}} </td> <td> {{post.date | date: "d/m/yyyy"}} </td> </tr> </table> what should do? thank you try this: controller $scope.posts = [ {title:'title one', author:'author one', content: 'content one'}, {title:'title two', author:'author two', content: 'cont...
Read more

sequelize.js - Sequelize: sort by enum cases -

i have model ( article ) field (the name type ) of type enum('source', 'translated') , return 1 article ordered type field. source articles should returned before translated articles. like: article.findone(order: [ { type: [ 'source', 'translated' ] } ]); unfortunately there appears no built-in solution sorting enums. instead have sort by case , couldn't find documentation on how sequelize. what conventional approach here? sorting case hard in sequelize, sort field (the second answer in linked post) should possible: order: sequelize.fn('field', sequelize.col('type'), 'source', 'translated') it's supported mysql far know though
Read more

user interface - how to replace an ongoing process of image capture from another process call over the same ImageLabel in python's GUI TKinter -

i trying use following code create interactive gui changes color-space(hsv, rbg, grayscale etc) on event of button click. display opencv video in tkinter using multiprocessing being new python having problems multiprocessing , attempts on making gui change it's color space on button clicks hangs entire system. on it's implementation highly appreciated. thankyou. below code: import cv2 pil import image,imagetk import tkinter tk import numpy np multiprocessing import process , queue def quit_it(root,process): root.destroy() process.terminate() def black_andwhite(root,process): process.terminate p=process(target=capture_image, args=(5,queue, )) p.start() root.after(0, func=lambda: update_all(root, image_label, queue)) def update_image(image_label, queue): frame = queue.get() = image.fromarray(frame) b = imagetk.photoimage(image=a) image_label.configure(image=b) image_label._image_cache = b root.update() def update_all(root...
Read more