Ошибка 1248 mysql

I cant for the life of me figure out why this is erroring SELECT * FROM SELECT c.city_id, p.prop_ynow_id, p.propertyid, p.prop_add_value, p.name, picture, ifnull(p.address,'') as`location`, ifnull(

I cant for the life of me figure out why this is erroring

SELECT * FROM 
SELECT 
c.city_id,
p.prop_ynow_id,
p.propertyid,
p.prop_add_value,
p.name,
picture,
ifnull(p.address,'') as`location`,
ifnull(city,'')as`city`,
ifnull(ShortCut,'') as `state`,
ifnull(p.zip,'') as `zip`,
min(if(pr.minrent = 0,99999999,pr.minrent)) as minrent, 
max(pr.maxrent) as maxrent,
'' as service,
hood_id,
ifnull(p.phone,'') as `phone`,
latitude,
longitude,
min(CAST(pu.fullBath AS UNSIGNED)) as`minbath`,
max(CAST(pu.fullBath AS UNSIGNED)) as`maxbath`,
min(CAST(pu.Bed AS UNSIGNED)) as`minbed` ,
max(CAST(pu.Bed AS UNSIGNED)) as`maxbed`,
'' as url,
'' as source_id,
'' as source_name,
'' as addresscode,
'' as citycode,
'' as ctime,
'' as paid,
'' as similar_url,
'' as created_at,
'' as updated_at,
'' as city_name,
'' as service_listing_id

FROM 
wiki_city_list c join propertyinfo p on c.city_id=p.city 
join ynow_rentwiki.Property_Unitlayout pu on p.prop_ynow_id=pu.P_Ident
join (SELECT CAST(substring_index(if(Rent >0 ,Rent,RentLow),'.',1) AS UNSIGNED) as minrent, CAST(substring_index(if(Rent >0,Rent,Renthigh),'.',1) AS UNSIGNED) as maxrent,PRE_Ident,P_Ident,UNL_Ident,RTY_Ident from ynow_rentwiki.Property_rents where P_Ident in (3958, 4576, 4577) and (Rent!='' or (Rentlow!='' and Renthigh!='')) )  as pr on pu.UNL_Ident=pr.UNL_Ident
join state s on (p.state = s.stateid OR p.state = s.ShortCut ) 
WHERE 
pu.Status='Active'
and p.delete_date='0000-00-00'

GROUP BY 
c.city_id, p.prop_ynow_id

UNION 
SELECT 
c.city_id,
p.prop_ynow_id,
p.propertyid,
p.prop_add_value,
p.name,
picture,
ifnull(p.address,'') as`location`,
ifnull(city,'')as`city`,
ifnull(ShortCut,'') as `state`,
ifnull(p.zip,'') as `zip`,
min(if(pr.minrent = 0,99999999,pr.minrent)) as minrent, 
max(pr.maxrent) as maxrent,
'' as service,
hood_id,
ifnull(p.phone,'') as `phone`,
latitude,
longitude,
min(CAST(pu.fullBath AS UNSIGNED)) as`minbath`,
max(CAST(pu.fullBath AS UNSIGNED)) as`maxbath`,
min(CAST(pu.Bed AS UNSIGNED)) as`minbed` ,
max(CAST(pu.Bed AS UNSIGNED)) as`maxbed`,
'' as url,
'' as source_id,
'' as source_name,
'' as addresscode,
'' as citycode,
'' as ctime,
'' as paid,
'' as similar_url,
'' as created_at,
'' as updated_at,
'' as city_name,
'' as service_listing_id

FROM 
wiki_city_list c join propertyinfo p on c.city_id=p.city 
join ynow_rentwiki.Property_Unitlayout pu on p.prop_ynow_id=pu.P_Ident
join (SELECT CAST(substring_index(if(Rent >0 ,Rent,RentLow),'.',1) AS UNSIGNED) as minrent, CAST(substring_index(if(Rent >0,Rent,Renthigh),'.',1) AS UNSIGNED) as maxrent,PRE_Ident,P_Ident,UNL_Ident,RTY_Ident from ynow_rentwiki.Property_rents where P_Ident in (9744) and (Rent!='' or (Rentlow!='' and Renthigh!='')) )  as pr on pu.UNL_Ident=pr.UNL_Ident
join state s on (p.state = s.stateid OR p.state = s.ShortCut ) 
WHERE 
pu.Status='Active'
and p.delete_date='0000-00-00'

GROUP BY 
c.city_id, p.prop_ynow_id

UNION 
SELECT  
'' as prop_ynow_id, 
id as propertyid, 
0 as prop_add_value,
t.name as name,
'' as picture,  
t.address as location,
t.city as city, 
s.ShortCut as state, 
t.zip as zip,   
CAST(REPLACE(REPLACE(t.price,'$',''),',','') as UNSIGNED) as minrent, 
'' as maxrent,
t.service as service, 
'' as hood_id, 
'' as phone, 
t.latitude as latitude, 
t.longitude as longitude, 
t.bathrooms as minbath, 
'' as maxbath, 
t.bedrooms as minbed,
'' as maxbed,   
t.url as url,   
t.source_id as source_id, 
t.source_name as source_name, 
t.addresscode as addresscode, 
t.citycode as citycode, 
t.ctime as ctime, 
t.paid as paid,
t.similar_url as similar_url, 
t.created_at as created_at, 
t.updated_at as updated_at, 
SUBSTRING_INDEX(c.city_name,'_',1) as city_name,    
t.service_listing_id as service_listing_id

FROM LBCPrimary.third_party_properties as t, LBCPrimary.wiki_city_list as c, LBCPrimary.state as s
WHERE 
t.city in ( '230' ) 
and 
address <> '' and 
t.city = c.city_id and 
c.city_state = s.stateid

order by t.ctime 
desc
limit 46 as a limit 0,50

Источник

Вопрос:

Я получаю сообщение об ошибке при использовании этого запроса в MySQL.

Логика запроса верна, и я пробовал ее в Oracle, и она работает нормально, но я получаю сообщение об ошибке при работе в MySQL.

Я просмотрел предыдущие вопросы о StackOverflow, но не нашел что-то, чтобы помочь мне.

Вот запрос:

select * from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
from RATOR_IMP.PROCESS_MONITOR as PM
JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
ON PM.ID = PMS.PROCESS_MONITOR_ID
WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
order by PMS.PROCESS_START_DATE desc)
limit 10000;

И вот ошибка:

Error Code: 1248. Every derived table must have its own alias
No soultion found for query

Лучший ответ:

Вам нужно предоставить псевдоним для подзапроса, например:

select * from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
 from RATOR_IMP.PROCESS_MONITOR as PM
 JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
 ON PM.ID = PMS.PROCESS_MONITOR_ID
 WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
 order by PMS.PROCESS_START_DATE desc) as s
limit 10000;

В документации,

Подзапросы являются законными в предложении FROM SELECT. Фактический синтаксис:

SELECT… FROM (подзапрос) [AS] name…

Предложение имени [AS] является обязательным, поскольку каждая таблица в предложении FROM должна иметь имя. Любые столбцы в списке выбора подзапроса должны иметь уникальные имена.

Ответ №1

Да вам нужно указать псевдоним для полученных данных

select x.* from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
from RATOR_IMP.PROCESS_MONITOR as PM
JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
ON PM.ID = PMS.PROCESS_MONITOR_ID
WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
order by PMS.PROCESS_START_DATE desc)x <-- here
limit 10000;
Источник

i have problem here,
when im trying to select with query below, i got error message

Error Code : 1248 Every derived table must have its own alias

SELECT B.BRANCH_CODE, B.BRANCH_NAME, C.COMPANY_CODE, C.NAME, TSK.DATE_CREATE,
  CASE TB.BULK_TYPE
    WHEN 1 THEN 'Bulk Transfer'
    WHEN 2 THEN 'Bulk Payment'
    ELSE 'Payroll'
  END AS TRX_METHOD_E, 
  CASE TB.BULK_TYPE
    WHEN 1 THEN 'Bulk Transfer'
    WHEN 2 THEN 'Bulk Pembayaran Tagihan'
    ELSE 'Pembayaran Gaji'
  END AS TRX_METHOD_I,
  TB.TOTAL_RECORD,
  TB.ACCOUNT_NO,
  TSK.TRX_TOTAL,
  TC.TOTAL_CHARGE,
  DATE(TSK.DATE_TRX) AS DATE_TRX,
  TB.REF_ID,
  CASE
    WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'All Success'
    WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'All Failed'
    WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Partial Success (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
    ELSE 'Pending Execution'
  END AS TRX_STATUS_E,
  CASE
    WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'Berhasil Semua'
    WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'Gagal Semua'
    WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Berhasil Sebagian (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
    ELSE 'Tunggu Eksekusi'
  END AS TRX_STATUS_I  
FROM CB_TASK_BULKS TB
  JOIN CB_TASKS TSK ON TSK.REF_ID = TB.REF_ID
  JOIN CB_COMPANIES C ON C.COMPANY_ID = TSK.COMPANY_ID
  JOIN CB_BRANCHES B ON B.BRANCH_CODE = C.BRANCH_CODE,
(
  SELECT REF_ID, SUM(CHARGE) AS TOTAL_CHARGE
  FROM
  (
    SELECT XTB.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS XT
    JOIN CB_TASK_BULK_DETAILS XTBD ON XTBD.BULK_DETAIL_ID = XT.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB ON XTB.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB2.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS_DOM XTD
    JOIN CB_TASK_BULK_DETAILS XTBD2 ON XTBD2.BULK_DETAIL_ID = XTD.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB2 ON XTB2.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB3.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_PAYMENTS XP
    JOIN CB_TASK_BULK_DETAILS XTBD3 ON XTBD3.BULK_DETAIL_ID = XP.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB3 ON XTB3.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID
  )
  GROUP BY REF_ID
) TC
WHERE TC.REF_ID = TSK.REF_ID
AND (TSK.TRX_COUNT_SUCCESS > 0 OR TSK.TRX_COUNT_FAIL > 0);

can somebody help me to find my problem here,
i think i’ve given all tables with his own «name» like TB for CB_BULKS_DATA (example)

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка 1243 фортнайт
  • Ошибка 1242 камаз камминз
  • Ошибка 1242 mysql
  • Ошибка 1242 cummins как исправить
  • Ошибка 1241 субару