Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Reference a value in a table to use in another SQL power Query

Hi All   I have a value held in another PQ table. 1 row 1 column containing an invoice number. - Is it possible to use this particular invoice number and use in a SQL query without having to go int...
Need Help
Tips and Tricks
  • KNP's avatar
    KNP
    4 years ago

    Double quote missing before the closing square bracket I think. 

    I notice you're missing a table alias for your SHIP_DATE_RETURN, not sure if that will cause you any abiguity issues.

    This is just the query portion, make sure it goes inside the correct ().

    [Query="
SELECT rd.SERIAL_NUMBER
     , rd.REF_NUM
     , rd.MODEL_ASC
     , pm.MODEL
     , rd.MOTO_PART_NUMBER
     , rd.ASC_SITE_ID
     , ras.ASC_SITE_NAME
     , rd.REPAIR_TYPE
     , acm.ASC_CUSTOMER_NAME
     , rd.WARR_CODE
     , rd.RMA_CREATION_DATE
     , rd.EXPIRY_DATE
     , rd.RECV_DATE
     , rd.SHIP_DATE_RETURN
     , rd.UNIT_STATUS
     , rd.PROJECT_NAME
     , rd.FINAL_UNIT_DETERMINATION
     , rd.INVOICE_NUMBER
     , rd.INVOICE_DATE
     , ROUND((SHIP_DATE_RETURN - rd.RECV_DATE)) AS AGE
     , (rd.RECV_DATE + 30)                      AS TAT
     , rd.LABCOST
     , rd.FREIGHT_COST
FROM rdwadmin.rdw_repair_details rd
   , rdwadmin.rdw_part_master pm
   , rdwadmin.rdw_asc_customer_master acm
   , rdwadmin.rdw_asc_site ras
WHERE rd.moto_part_number = pm.moto_part_number
      AND rd.ASC_CUSTOMER_NUMBER = acm.ASC_CUSTOMER_NUMBER
      AND rd.ASC_SITE_ID = ras.ASC_SITE_ID
      AND rd.INVOICE_NUMBER = '"& #"Current-Invoice"[CurrentInvoice]{0} &"'

"]

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

So i amended the name, but it didnt like the extra ' at the end as you can see...

 

 

I removed the extra ' from the end the, PQ then started loading then came up with the below:

 

 

Though the name is spelled correctly?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi KNP - Do you have any further suggestions on this, I think we are so close on this.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    KNP Thank you this is now fixed. I did the old google on the firewall piece and it was down to the privacy settings. - I can't thank you enough as this will save so much time!

     

    Anyone else who comes across the firewall issue, just amend your privacy settings.

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Double quote missing before the closing square bracket I think. 

    I notice you're missing a table alias for your SHIP_DATE_RETURN, not sure if that will cause you any abiguity issues.

    This is just the query portion, make sure it goes inside the correct ().

    [Query="
SELECT rd.SERIAL_NUMBER
     , rd.REF_NUM
     , rd.MODEL_ASC
     , pm.MODEL
     , rd.MOTO_PART_NUMBER
     , rd.ASC_SITE_ID
     , ras.ASC_SITE_NAME
     , rd.REPAIR_TYPE
     , acm.ASC_CUSTOMER_NAME
     , rd.WARR_CODE
     , rd.RMA_CREATION_DATE
     , rd.EXPIRY_DATE
     , rd.RECV_DATE
     , rd.SHIP_DATE_RETURN
     , rd.UNIT_STATUS
     , rd.PROJECT_NAME
     , rd.FINAL_UNIT_DETERMINATION
     , rd.INVOICE_NUMBER
     , rd.INVOICE_DATE
     , ROUND((SHIP_DATE_RETURN - rd.RECV_DATE)) AS AGE
     , (rd.RECV_DATE + 30)                      AS TAT
     , rd.LABCOST
     , rd.FREIGHT_COST
FROM rdwadmin.rdw_repair_details rd
   , rdwadmin.rdw_part_master pm
   , rdwadmin.rdw_asc_customer_master acm
   , rdwadmin.rdw_asc_site ras
WHERE rd.moto_part_number = pm.moto_part_number
      AND rd.ASC_CUSTOMER_NUMBER = acm.ASC_CUSTOMER_NUMBER
      AND rd.ASC_SITE_ID = ras.ASC_SITE_ID
      AND rd.INVOICE_NUMBER = '"& #"Current-Invoice"[CurrentInvoice]{0} &"'

"]

     

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Anonymous - sorry, I think this is my typo that has confused things.

    I think it should be...

    '"& #"Current-Invoice"[CurrentInvoice]{0} &"'

    It was missing the double quote to close off the table reference.

    Give that a try and let me know how you go.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    KNP Thanks for the reply. 

     

    I have added the change: It says there is an invalid literal

     

    Here is the query section, not sure if this makes it easier?

     

    [Query="select rd.SERIAL_NUMBER, rd.REF_NUM, rd.MODEL_ASC, pm.MODEL, rd.MOTO_PART_NUMBER, rd.ASC_SITE_ID, ras.ASC_SITE_NAME, rd.REPAIR_TYPE, acm.ASC_CUSTOMER_NAME, rd.WARR_CODE, rd.RMA_CREATION_DATE, rd.EXPIRY_DATE, rd.RECV_DATE, rd.SHIP_DATE_RETURN, rd.UNIT_STATUS, rd.PROJECT_NAME, rd.FINAL_UNIT_DETERMINATION, rd.INVOICE_NUMBER, rd.INVOICE_DATE, round((SHIP_DATE_RETURN - rd.RECV_DATE)) as AGE, (rd.RECV_DATE+30) AS TAT, rd.LABCOST, rd.FREIGHT_COST #(lf)from rdwadmin.rdw_repair_details rd, rdwadmin.rdw_part_master pm, rdwadmin.rdw_asc_customer_master acm, rdwadmin.rdw_asc_site ras#(lf)where rd.moto_part_number = pm.moto_part_number#(lf)AND rd.ASC_CUSTOMER_NUMBER = acm.ASC_CUSTOMER_NUMBER #(lf)AND rd.ASC_SITE_ID = ras.ASC_SITE_ID #(lf)AND rd.INVOICE_NUMBER = '"& #"Current-Invoice"[CurrentInvoice]{0} &"'])

     

    Thanks, really apprecite your help on this ğŸ™‚

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Good spot on the table ref, it wasnt an issue as i have used this via sql into excel previously, but now i know its there I have amended it 🙂

     

    I think we are almsot there! It started to work and then came up with something i have never seen before: