SQL Server - solving query
Asked By SVK N
03-Feb-12 07:04 AM
SELECT a.NAME,(case when (a.XLNAME IS null) OR (a.XLNAME = ' '))
a.NAME else a.XLNAME end) as E_COL ,[order],width,b.column_name,
b.data_type FROM (SELECT * FROM XLS_XLS where status=1 and [order]<>0)
as A Left Join
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='STUDENT')
as B ON A.NAME=B.COLUMN_NAME ORDER BY A.[order]
this was working fine but started giving synatx error
D Company replied to SVK N
hello friend,
at a glance nothing seems wrong in your query, do one thing just run every select statement and see. if you can figure out some specific problem. and also try to avoid using more than one inner query.
Regards
D
SVK N replied to D Company
i get synatx errors near as and )
as i said it was working fine not made any changes in teh query
in asp debugging i get
Message = "Incorrect syntax near ')'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'."
Sandeep Mittal replied to SVK N
There is bracket issue in case, a bracket is missing before "
(a.XLNAME IS null) OR (a.XLNAME = ' '))"
case when (a.XLNAME IS null) OR (a.XLNAME = ' '))
Also find modified query to handle null and space in a single condition
SELECT a.NAME
, CASE WHEN ISNULL(a.XLNAME, ' ') THEN a.NAME else a.XLNAME end as E_COL
, [order]
, width,b.column_name
, b.data_type
FROM (
SELECT * FROM XLS_XLS
WHERE status=1 and [order]<>0
) A Left Join (
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='STUDENT'
) B ON A.NAME=B.COLUMN_NAME ORDER BY A.[order]
JOIN order - never matters? SQL Server To the best of my knowledge, the following are always equivalent (INNER & LEFT join can be specified in any order, results will always be the same). SELECT . . . . FROM . . . INNER JOIN A. . . LEFT JOIN B. . . LEFT JOIN C SELECT . . . . FROM . . . LEFT JOIN C INNER JOIN A. . . LEFT JOIN B. . . I am
Left join question SQL Server Hi, Just a general question about left outer join: select a.*, b.columna, c.columnb from a left join b on a.id1 = b.id left join c on a.id2 = c.id Will number of result set rows ALWAYS be
sql question SQL Server Hello, I want to do a left join on a table and a select statement such as follow: SELECT tbl1.*, q1.* FROM tbl1 LEFT JOIN (SELECT tbl2.* FROM tbl2 WHERE (((tbl2.key2) = 9));) as q1 ON tbl1.key1 = q1.key1 WHERE database? If yes how? I know that I can create a query for the inner select and use it, but since I am generating the select string automatically in C#, I
sum with group by hi, i have following query select ExpenditureDetails.categeoryid, (ExpenditureDetails.Amount) as Totaladvance, (travel.travelamt+transport.transportamt+miscellaneous.miscellaneousamt+local.localamt+ labour.labouramt+genset.gensetamt+food.foodamt+equipment.equipmentamt+board.boardamt, 0) as Totalsubmitted_amt from ExpenditureDetails left join (select categeoryid, SUM(amount)as travelamt from ExpenditureTravelling group by categeoryid)travel on ExpenditureDetails.categeoryid = travel categeoryid left join (select categeoryid, SUM(amount)as transportamt from ExpenditureTransportation group by categeoryid)transport on ExpenditureDetails.categeoryid = transport i write that bolded text as groupby Use SUM function with all column like below select ExpenditureDetails.categeoryid , (ExpenditureDetails.Amount) as Totaladvance , SUM (travel.travelamt)+ SUM (transport.transportamt)+ SUM (miscellaneous.miscellaneousamt
query that returns me the various figures for particular salesperon : My query goes like this : Select Usr.user_name as SalesPerson, isnull(ReqNo, 0) ReqNo, isnull(ResSubToSalesPerNo, 0) ResSubToSalesPerNo, isnull(ResSubToClientNo, 0 isnull(InterviewNo, 0) InterviewNo, isnull(ConfirmNo, 0) ConfirmNo, isnull(RejectionNo, 0) RejectionNo from xr_user Usr left Join (select sales_person_id, count(requisition_date) ReqNo from xr_sales_reqs where requisition_date between convert(varchar, '5 / 1 / 2006', 101 convert(varchar, '5 / 29 / 2006', 101) group by sales_person_id) Reg on (Usr.User_Id = Reg.sales_person_id) left join (select A.sales_person_id, count(date_sub_sales_per) ResSubToSalesPerNo from xr_sales_reqs A left join xr_candidate_links B on A.sales_req_id = B.sales_req_id where B.date_sub_sales_per between convert(varchar, '5